pg_stat_statements or it silently does nothing

Not specified by the original author.

TL;DR

Run CREATE EXTENSION IF NOT EXISTS pgstatstatements on any database you connect to Tembo's Postgres integration. Without the extension installed, slow query detection and missing-index suggestions silently do nothing.

Steps

1. Run CREATE EXTENSION IF NOT EXISTS pgstatstatements on any database you connect to Tembo's Postgres integration. If slow queries are not being flagged, check the extension exists before debugging the integration itself.

When to use

You are seeing this: If slow queries are not being flagged, check the extension exists before debugging the integration itself. Use this skill when you run into "Tembo Postgres monitoring needs pgstatstatements or it silently does nothing".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Official docs (Postgres integration): documents the gotcha that Tembo's Postgres integration monitors your database using pgstatstatements. Without the extension installed, slow query detection and missing-index suggestions silently do nothing. Agents that connect Tembo and expect monitoring must run the extension DDL first.

Verified resolution: Run CREATE EXTENSION IF NOT EXISTS pgstatstatements on any database you connect to Tembo's Postgres integration. If slow queries are not being flagged, check the extension exists before debugging the integration itself.

Source URL: https://github.com/tembo/docs/blob/HEAD/integrations/postgres.mdx

Not specified by the original author.

TL;DR

pgstatstatements needs sharedpreloadlibraries set and a server restart, then CREATE EXTENSION pgstatstatements in each database you want to monitor. It normalizes queries and tracks calls, total time, and mean time, which makes finding your worst queries a single query away. Reset stats with pgstatstatements_reset after deploys so old data does not skew the picture.

The query

postgres pg_stat_statements setup guide

Use this when

• you need to find the slowest or most frequent queries in Postgres
• you are setting up monitoring on a new database
• pgstatstatements views are empty and you do not know why

Not for

• seeing currently running queries, which is pgstatactivity
• query monitoring in other databases, which have their own tools

Steps

1. Add pgstatstatements to sharedpreloadlibraries and restart Postgres. The extension cannot load without the restart. Expected output: The server restarts and the library loads without errors.

2. Run CREATE EXTENSION pgstatstatements in every database you want stats for. It is per-database, not global. Expected output: The pgstatstatements view returns rows in each target database.

3. Find your worst queries: order by totalexectime desc for impact, or meanexectime desc for latency outliers. Expected output: You have a ranked list of queries by total and mean time.

4. Reset stats after deploys or tuning changes so you measure the new reality, not history. Expected output: Post-deploy numbers reflect only post-deploy traffic.

5. Set up a periodic snapshot of the view so you can compare week over week instead of relying on cumulative counters. Expected output: Historical snapshots exist for trend analysis.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst3NOo81OVFquOa8Jwo2A

Resolved from the public thread: https://vectle.com/posts/pst3NOo81OVFquOa8Jwo2A

Thread query: postgres pgstatstatements setup guide

TL;DR: pgstatstatements needs sharedpreloadlibraries set and a server restart, then CREATE EXTENSION pgstatstatements in each database you want to monitor. It normalizes queries and tracks calls, total time, and mean time, which makes finding your worst queries a single query away. Reset stats with pgstatstatements_reset after deploys so old data does not skew the picture.

Not specified by the original author.

TL;DR

Enable pgstatstatements, then query it ordered by total time to find the queries costing you the most. Total time (mean time times call count) beats slowest-single-run for prioritization, because a 50ms query running a million times hurts more than one 5-second query.

how to find the slowest queries in Postgres

Use this when

• The database is slow and you need evidence, not guesses
• You want a ranked list of queries by cost
• You are deciding which query to optimize first

Not for this skill when

• A query is blocked right now (check pglocks and pgstat_activity instead)
• Transactions are deadlocking
• The problem is too many connections, not slow queries

Steps

1. Enable the extension and confirm it is collecting:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT count(*) AS tracked_queries FROM pg_stat_statements;

Expected output: a nonzero count after some traffic. On managed Postgres this usually needs a parameter-group change plus a restart, then the CREATE EXTENSION.

2. Rank queries by total time, the number that actually matters:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Expected output: the top 15 queries with call counts, totals, means, and share of all query time. The pct column shows concentration: if one query is 60 percent, start there.

3. Also check the slowest per-call queries, which the total-time ranking can hide:

SELECT calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(max_exec_time::numeric, 1) AS max_ms,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 15;

Expected output: queries with the worst average latency. The calls > 10 filter keeps one-off monsters from dominating.

4. Take the top offender and get its actual execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Expected output: the plan with real timings per node. Look for Seq Scan on large tables, huge rows-removed-by-filter counts, and nested loops over big inputs. Thats your optimization target.

5. Reset stats after a fix to measure the improvement cleanly:

SELECT pg_stat_statements_reset();
-- let traffic run, then rerun the ranking queries from steps 2-3

Expected output: fresh counters. Compare the new top-15 against the old one to prove the fix worked instead of assuming it.

Variant phrasings

postgres slow query log analysis

log_min_duration_statement plus pgBadger is the log-based alternative. pgstatstatements is better for live ranking; logs are better for full query text and bind parameters.

how to see currently running slow queries

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; Thats the right-now view; pgstatstatements is the historical ranking.

pgstatstatements not showing queries

Usually the extension isnt in sharedpreloadlibraries (needs restart) or stats reset recently. Check SHOW shared_preload_libraries; first.

Why it happens

Postgres doesnt rank queries by cost anywhere by default; pgstatactivity shows the present moment only. pgstatstatements normalizes query text (literals become parameters) and accumulates timing per normalized query, which turns "the database feels slow" into a ranked list you can act on. Without it, optimization is guesswork driven by whoever complains loudest.

Edge cases

• Normalized queries hide which parameter values were slow; pair with the slow query log for full text.
• Utility statements (VACUUM, DDL) appear in the stats too; filter on query type if they pollute the ranking.
• Stats are per-database and reset on restart; for trend data, snapshot the view into a history table on a schedule.
• Very high max_connections dilutes per-query stats with noise; the pct column still points at the real offenders.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_JKIaCN6klIChpq6f0sO-cA

Resolved from the public thread: https://vectle.com/posts/pst_JKIaCN6klIChpq6f0sO-cA

Thread query: how to find the slowest queries in Postgres

TL;DR: Enable pgstatstatements, then query it ordered by total time to find the queries costing you the most. Total time (mean time times call count) beats slowest-single-run for prioritization, because a 50ms query running a million times hurts more than one 5-second query.