how to find the slowest queries in Postgres

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.

Not specified by the original author.

TL;DR

Find the slow query first (pgstatstatements or the slow query log), run EXPLAIN ANALYZE on it in a read-only transaction, and look for the usual suspects: sequential scan on a big table, missing index, or a bad plan from stale statistics. Fix with an index or a query rewrite, and verify the plan changed before calling it done. Never run experimental DDL on production without testing the lock behavior first.

Error / query

how to debug a slow database query in production

Use this skill when

• p95 latency spiked and the database is the prime suspect
• An alert names a specific slow query or a specific endpoint backed by one query
• You need to decide between adding an index, rewriting the query, or scaling the DB
• A deploy made queries slower and you need the before/after plan

Not for this skill when

• The whole database is slow (connections exhausted, disk full); fix the host first
• The slowness is in the app (N+1 queries, chatty ORM); the database is the victim, not the cause
• You are designing schema for a new feature; that is design work, not debugging

Steps

Step 1: Find the slowest queries by total time

psql -c "SELECT query, calls, round(total_exec_time::numeric,2) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

Expected: the top queries by total time; the slowest one by mean time on high calls is your target.

Step 2: Get the execution plan without running side effects

psql -c "BEGIN READ ONLY; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 100; ROLLBACK;"

Expected: the plan with actual timings; look for "Seq Scan" on large tables, the single most common cause.

Step 3: Check whether the table statistics are stale

psql -c "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE relname = 'orders';"

Expected: recent analyze timestamps; if they are old or null, run ANALYZE and re-check the plan before adding indexes.

Step 4: Check for missing indexes on the filter columns

psql -c "SELECT indexname FROM pg_indexes WHERE tablename = 'orders';"

Expected: the index list; if there is no index on the WHERE/ORDER BY columns from step 2, that is very likely the fix.

Step 5: Verify the fix changed the plan and the timing

psql -c "BEGIN READ ONLY; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 100; ROLLBACK;" | grep -E 'Index|Seq Scan|Execution Time'

Expected: "Index Scan" replacing "Seq Scan" and a much lower execution time; confirm in production metrics, not just EXPLAIN.

Variant phrasings

"Slow query in MySQL"

Use the slow query log plus EXPLAIN FORMAT=JSON; the same seq-scan-versus-index logic applies.

"Query was fast yesterday, slow today"

Stale statistics or a plan flip after an ANALYZE or a data growth threshold; compare the current plan to yesterday's and check last_analyze.

"How to find N+1 queries"

They show up in pgstatstatements as huge call counts with tiny mean times; fix in the app (eager loading), not with indexes.

Why it happens

Queries slow down when the planner picks a bad path: no usable index, statistics that no longer describe the data, or a data volume that crossed the threshold where the old plan stops working. The plan tells you which one.

Edge cases and pitfalls

• CREATE INDEX on a big production table takes an ACCESS EXCLUSIVE lock without CONCURRENTLY; use CREATE INDEX CONCURRENTLY.
• EXPLAIN ANALYZE actually runs the query; wrap writes in a rolled-back transaction or test on a replica.
• Parameter sniffing: the plan for one parameter value can be terrible for another; test with representative values.
• Do not add an index per slow query blindly; every index slows writes and the fifth overlapping index helps nobody.

Provenance

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

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

Thread query: how to debug a slow database query in production

TL;DR: Find the slow query first (pgstatstatements or the slow query log), run EXPLAIN ANALYZE on it in a read-only transaction, and look for the usual suspects: sequential scan on a big table, missing index, or a bad plan from stale statistics. Fix with an index or a query rewrite, and verify the plan changed before calling it done. Never run experimental DDL on production without testing the lock behavior first.

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.