DBMS deep dive
Query Optimization (EXPLAIN, slow queries): deep dive
Query optimization means reading the plan the database chose, finding where its estimates or access paths go wrong, and then changing the index, the query or the statistics. This lesson walks through a real before-and-after EXPLAIN and the patterns that cause most slow queries.
How to read a plan
A plan is a tree, and you read it from the most-indented node outwards. Each node shows estimated cost=startup..total (in planner units, not milliseconds) and estimated rows. With EXPLAIN (ANALYZE, BUFFERS) you also get actual time, actual rows, loops and pages read. The most useful habit is to compare estimated rows with actual rows. If they are off by 10× or more, the planner chose its strategy on bad information.
Before: a slow customer order page
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;
-- Limit (actual time=812.4..812.4 rows=20 loops=1)
-- -> Sort (actual time=812.4..812.4 rows=20 loops=1)
-- Sort Key: created_at DESC
-- Sort Method: top-N heapsort Memory: 27kB
-- -> Seq Scan on orders (cost=0.00..198340.00 rows=310 width=24)
-- (actual time=0.9..811.7 rows=356 loops=1)
-- Filter: ((customer_id = 42) AND (status = 'shipped'))
-- Rows Removed by Filter: 4999644
-- Buffers: shared read=73340
-- Execution Time: 812.6 msThe warning signs: a sequential scan reads 73k pages and throws away about 5 million rows to keep 356, and then those rows are sorted. The estimate (310) is close to the actual count (356), so statistics are fine. The problem is the access path.
After: an index that matches the query
CREATE INDEX CONCURRENTLY idx_orders_cust_status_created
ON orders (customer_id, status, created_at DESC);
-- Limit (actual time=0.04..0.09 rows=20 loops=1)
-- -> Index Scan using idx_orders_cust_status_created on orders
-- (actual time=0.04..0.08 rows=20 loops=1)
-- Index Cond: ((customer_id = 42) AND (status = 'shipped'))
-- Buffers: shared hit=24
-- Execution Time: 0.12 msThe Sort node is gone, because the index already returns rows in created_at DESC order, and LIMIT stops after 20 rows. About 24 buffer hits replace 73k reads. CONCURRENTLY builds the index without blocking writes on a live table. Why the column order matters is covered in database indexes.
Plan nodes worth recognising
| Node | What it means | When it is a problem |
|---|---|---|
| Seq Scan | Reads every page of the table | Large table, few rows kept (high Rows Removed by Filter) |
| Index Scan | Walks the index, fetches each heap row | Many rows returned, causing lots of random I/O |
| Bitmap Heap Scan | Collects matching row locations, then reads pages in physical order | Usually fine; 'lossy' blocks mean work_mem is too small |
| Index Only Scan | Answers from the index alone | High Heap Fetches means vacuum is behind |
| Nested Loop | Runs the inner side once per outer row | loops count is huge because the outer side was underestimated |
| Hash Join | Builds a hash table on one input | Batches > 1 means it spilled to disk |
| Sort | Sorts its input | Sort Method: external merge means it spilled to disk |
Rewrites that often help
- Keyset pagination instead of a large
OFFSET.WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 20stays fast on page 1000, whileOFFSET 20000reads and discards 20,000 rows. - Sargable predicates: write
created_at >= '2026-10-01' AND created_at < '2026-10-02'instead ofDATE(created_at) = '2026-10-01'. - Split OR across columns (
a = 1 OR b = 2) into aUNION ALLof two indexed queries when the planner will not combine the indexes. - Pre-aggregate before joining, so a join does not multiply rows that you then
GROUP BYback down. - Refresh statistics with
ANALYZEafter bulk loads, and use extended statistics (CREATE STATISTICS) for correlated columns such as city and pincode.
Common mistakes
- Running EXPLAIN without ANALYZE and trusting the estimates. Only ANALYZE shows reality. Wrap DML in a transaction you roll back, because EXPLAIN ANALYZE really executes it.
- Tuning on a laptop with 1,000 rows. Plans change with data size and distribution.
- Adding an index for every slow query without checking whether an existing composite index can be reordered or extended.
- Reading
costas milliseconds, or comparing costs across different queries. - Missing that a 2 ms query runs 5,000 times per request (N+1). Check
pg_stat_statementsfor total time, not just per-call time.
Interview questions
›How do you find which queries to optimize first?
Rank by total time (calls × mean), not by the slowest single run. Use pg_stat_statements in PostgreSQL or performance_schema and the slow query log in MySQL. Then EXPLAIN ANALYZE the top few.
›What does 'rows estimated 1, actual 50,000' tell you?
Bad cardinality estimation, usually from stale statistics, correlated columns, or a function or parameter the planner cannot see through. Downstream it often leads to a nested loop that should have been a hash join. Fix it with ANALYZE, extended statistics or a rewrite.
›Why can a prepared statement be slower than the same literal query?
After a few executions PostgreSQL may switch to a generic plan that ignores the actual parameter values. For skewed data, such as one huge customer, that plan can be badly wrong. plan_cache_mode can force custom plans.
›What is the difference between EXISTS and IN for subqueries?
Modern planners usually turn both into the same semi join. They differ for NOT IN versus NOT EXISTS, where a NULL in the subquery makes NOT IN return nothing, and NOT EXISTS lets the planner use an anti join.
›How would you speed up a query whose sort spills to disk?
Give the query an index that already provides the order, cut the rows before sorting with a better filter or LIMIT, select fewer or narrower columns, or raise work_mem for that session only.