Slow Query Diagnosis
pg_stat_statements, slow query log, EXPLAIN ANALYZE, missing indexes, N+1 queries, and query rewrites.
Overview
Overview
Slow queries are one of the most common causes of production performance degradation. They fall into a handful of categories, and each one leaves a different fingerprint in the execution plan or the application logs.
Categories
- Missing index — Full sequential scan
- Inefficient join — Cartesian product or the wrong join type
- N+1 query pattern — Application code issues one query per row of a result set
- Parameter sniffing — A bad plan is cached and reused
- Lock contention — Queries queue behind a lock holder
Tools by database
- PostgreSQL — pg_stat_statements — Aggregated statistics per normalized query
- PostgreSQL — Slow query log — Queries exceeding log_min_duration_statement milliseconds
- PostgreSQL — EXPLAIN ANALYZE — The actual execution plan with timing
- MySQL — Slow query log — Queries exceeding long_query_time
- MySQL — EXPLAIN FORMAT=JSON, Performance Schema — Plan and runtime detail
Reading EXPLAIN ANALYZE
EXPLAIN ANALYZE is the definitive tool. It shows which join algorithms, indexes and sort methods were chosen, estimated vs actual rows (a large gap means stale statistics), actual time per node, and buffer usage (shared hit = from cache, read = from disk).
- Seq Scan on a large table — Should usually be an Index Scan for highly selective queries
- Nested loop with a large outer relation — O(n*m) cost
- Sort without an index — Materializes rows in memory or spills to disk
- HashAggregate with large hash tables — Heavy memory use
N+1 detection
> [!TIP] If application logs show 1000 database queries for a page that conceptually needs 1, you have an N+1 problem; ORMs such as Hibernate and ActiveRecord have eager-loading options (JOIN FETCH, includes) to fix it.
Key concepts
- pg_stat_statements
- PostgreSQL extension tracking per-query statistics: total time, calls, mean time, standard deviation. Run SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC to find the worst offenders.
- EXPLAIN (ANALYZE, BUFFERS)
- Shows actual execution plan with real timing and buffer usage. "Actual rows" vs "Estimated rows" mismatch means stale statistics — run ANALYZE. Shared hit = cache; read = disk I/O.
- N+1 query
- Application issues one query for a list, then one query per item in the list. 1 + N queries instead of 1-2. Fix: JOIN or batch load (SELECT * WHERE id IN (...)).
- Covering index
- An index that includes all columns needed by a query (WHERE + SELECT). The database can satisfy the query entirely from the index without accessing the table heap (index-only scan).
- log_min_duration_statement
- PostgreSQL config: log queries that take longer than this threshold (in ms). Set to 1000 (1 second) in production. Set to 0 for full logging (very verbose — only for debugging).
When to use it
- Finding the slow query causing a p99 latency spike using pg_stat_statements
- Using EXPLAIN ANALYZE to determine why a specific query is doing a sequential scan
- Identifying N+1 query patterns in an ORM-based application
- Planning an index strategy for a new query before deploying to production
Interview questions
- How do you find and fix an N+1 query problem in a production application?
- A query that used to take 10ms now takes 30 seconds. Nothing changed in the code. What happened?
Sign up free to read the full topic
More Troubleshooting topics
Start free on Cariara · Coding problems · System design studio