how to debug a slow database query in production

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

Pool exhaustion means demand for connections exceeds supply: too few connections, connections held too long (slow queries, missing closes), or leaks. Diagnose by watching pool metrics (active, idle, waiting threads) during the incident, then fix the actual cause: usually slow queries holding connections or code not returning them. Raising max pool size without fixing the hold time just moves the bottleneck to the database.

The query

how to debug connection pooling exhaustion in production

Use this when

• Apps log pool exhausted or connection wait timeouts
• Latency spikes correlate with traffic
• Pool sizes were set by guesswork
• After adding new features that use the database heavily

Not for when

• Slow queries themselves (tune those separately, then recheck the pool)
• Database server overload (the pool protects the DB; check both)
• First-time pool configuration

Steps

Step 1: Watch pool metrics during the incident

Look at active connections, idle connections, and threads waiting for a connection. Waiting threads with zero idle connections is exhaustion; waiting threads with idle connections available is a pool bug or misconfiguration. Expected output: the pool state classified: truly exhausted vs misbehaving.

Step 2: Find what holds connections too long

Check for slow queries and transactions holding connections open: long-running transactions, queries without timeouts, code paths that forget to close. The pool drains because checkouts never return, not because traffic is high. Expected output: the top connection-holders identified by query or code path.

Step 3: Check for leaks

If active connections grow monotonically over hours without load growth, something leaks: connections opened and never closed. Heap dumps or connection-creation stack traces find them. Leaks are the most common cause of gradual exhaustion. Expected output: leaking code path found, or leak ruled out by stable counts under steady load.

Step 4: Size the pool from data, not defaults

Set max pool size based on measured concurrent demand plus headroom, and set connection timeouts (max lifetime, idle timeout, checkout timeout) so bad states self-heal. Document the sizing math next to the config. Expected output: pool sized for the real workload with timeouts that bound the damage.

Step 5: Protect the database from the fix

Raising pool size raises database load. Check the database's connection count and per-connection overhead before increasing. Sometimes the right fix is fewer, faster queries rather than a bigger pool. Expected output: pool and database sized as a pair; neither overwhelms the other.

Provenance

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

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

Thread query: how to debug connection pooling exhaustion in production

TL;DR: Pool exhaustion means demand for connections exceeds supply: too few connections, connections held too long (slow queries, missing closes), or leaks. Diagnose by watching pool metrics (active, idle, waiting threads) during the incident, then fix the actual cause: usually slow queries holding connections or code not returning them. Raising max pool size without fixing the hold time just moves the bottleneck to the database.

Not specified by the original author.

TL;DR

Prefix the query with EXPLAIN (ANALYZE, BUFFERS) to see what the database actually did: which steps ran, how many rows each touched, and where the time went. The usual culprits are sequential scans on big tables (missing index), nested loops over large inputs, and row-count misestimates. Read the plan top-down from the slowest node.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Use this when

• A query is slow and you dont know why
• You want to check whether an index is being used
• Comparing two rewrites of the same query

Not for

• "too many connections" or pooling issues
• Deadlocks
• Wrong results (correctness), the plan wont help

Steps

1. Get the real plan with execution stats:

EXPLAIN (ANALYZE, BUFFERS) SELECT o.* FROM orders o
WHERE o.created_at > '2026-01-01';

Expected output: the plan tree with actual time and row counts per node. Without ANALYZE you get estimates only.

2. Look for sequential scans on large tables:

Seq Scan on orders  (cost=...) (actual rows=2000000)

Expected finding: a Seq Scan over millions of rows where you expected an index. Thats the first thing to fix.

3. Add the missing index and re-check:

CREATE INDEX CONCURRENTLY ON orders (created_at);

Expected output: the plan flips to an Index Scan or Bitmap Heap Scan, time drops.

4. Compare estimated vs actual rows:

rows=10 (actual rows=500000)

Expected finding: a big mismatch means stale statistics. Fix with ANALYZE orders; and re-run EXPLAIN.

5. Watch for nested loop joins with a large inner side:

Nested Loop  (actual rows=1000000)

Expected finding: nested loops are fine for small inputs, terrible for large ones. The planner usually picks hash or merge joins when stats are fresh; stale stats are the common reason it doesnt.

Variant phrasings

how to read postgres explain output

Read inside-out: the most indented nodes run first. actual time is per-loop, multiply by loops for the real cost.

seq scan instead of index scan postgres

Either no usable index exists, the table is tiny (seq scan is genuinely faster), or stats are stale. Check all three in order.

explain analyze buffers meaning

ANALYZE runs the query for real timings. BUFFERS shows cache hits vs disk reads: high disk reads mean the working set exceeds shared_buffers or the OS cache.

Why it happens

The planner picks the cheapest plan by its cost model, which depends on table statistics. Stale stats, missing indexes, or skewed data make the model wrong, and the plan it picks can be orders of magnitude slower than the right one. EXPLAIN shows you the model's reasoning and the reality side by side.

Edge cases

• EXPLAIN ANALYZE actually runs the query: dont use it on INSERT/UPDATE/DELETE in production; wrap in a transaction and roll back, or use plain EXPLAIN.
• Buffers: shared hit vs read: hits are cache, reads are disk. Slow queries with high reads are IO-bound.
• Paste plans into explain.dalibo.com for a readable visual breakdown.
• Parameterized queries can get a generic plan; EXPLAIN with literal values may differ from production behavior.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst52dmIqS-Q9DzXL43b0onQ

Resolved from the public thread: https://vectle.com/posts/pst52dmIqS-Q9DzXL43b0onQ

Thread query: how to fix a slow SQL query: EXPLAIN basics

TL;DR: Prefix the query with EXPLAIN (ANALYZE, BUFFERS) to see what the database actually did: which steps ran, how many rows each touched, and where the time went. The usual culprits are sequential scans on big tables (missing index), nested loops over large inputs, and row-count misestimates. Read the plan top-down from the slowest node.