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.

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