EXPLAIN & Query Plans
debt(d5/e3/b3/t5)
Closest to 'specialist tool catches it' (d5). Query plan issues are detected through specialist tools like mysql-slow-query-log, pganalyze, and explain.depesz.com as listed in detection_hints. These aren't default linters but dedicated database performance tools that catch suboptimal execution plans and stale statistics.
Closest to 'simple parameterised fix' (e3). The quick_fix indicates running EXPLAIN ANALYZE to identify the issue, then ANALYZE TABLE to update statistics — a straightforward diagnostic-then-fix pattern. However, acting on the plan may require adding indexes or restructuring queries across a few files, pushing slightly beyond a one-liner but staying within a single component's scope.
Closest to 'localised tax' (b3). Query plan analysis applies to database interactions in web and cli contexts per applies_to, but the knowledge and discipline to check plans is localised to database-touching code. It doesn't impose system-wide architectural constraints — it's a skill and practice that pays off in specific areas without reshaping the entire codebase.
Closest to 'notable trap' (t5). The misconception explicitly states developers believe query plans are static and always the same for a given query, when in fact they're dynamic based on table statistics and data distribution. This is a documented gotcha that most developers eventually learn through painful production incidents with stale statistics, matching the t5 anchor.
Also Known As
TL;DR
Explanation
EXPLAIN shows the execution plan the query planner chose; EXPLAIN ANALYZE actually executes and shows real timing alongside estimates. Key plan nodes: Seq Scan (full table read — fine for small tables or non-selective queries), Index Scan (uses index, follows heap pointers), Index Only Scan (PostgreSQL — data from index alone, no heap access), Bitmap Heap Scan (batches index lookups then fetches heap rows), Hash Join / Merge Join / Nested Loop (join strategies). Red flags: Seq Scan on a large table with a filter, high rows estimate vs actual rows (stale statistics — run ANALYZE), Nested Loop on large result sets. MySQL: EXPLAIN FORMAT=JSON or EXPLAIN FORMAT=TREE. PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) shows cache hit rates. Use explain.dalibo.com to visualise PostgreSQL plans.
Common Misconception
Why It Matters
Common Mistakes
- Not running EXPLAIN before adding an index — the plan shows whether it would be used.
- Reading estimated rows instead of actual rows — use EXPLAIN ANALYZE for actual execution stats.
- Optimising based on a dev dataset that doesn't reflect production data distribution.
- Ignoring 'Seq Scan' on large tables — almost always needs an index.
Code Examples
-- Slow query, no investigation:
SELECT * FROM events WHERE user_id = 42 AND type = 'login' ORDER BY created_at DESC;
-- Just add EXPLAIN ANALYZE:
-- Seq Scan on events (cost=0.00..45231.00 rows=1000000)
-- -> Missing index on (user_id, type, created_at)-- PostgreSQL EXPLAIN ANALYZE — read this output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;
-- Key nodes to understand:
-- Seq Scan: reads every row — OK for small tables, bad on large ones
-- Index Scan: uses index, then fetches heap row
-- Index Only Scan: reads from index alone (fastest for covered queries)
-- Hash Join: hashes smaller table, probes it for each row of larger table
-- Nested Loop: for each outer row, scans inner — fast for small inner sets
-- 'actual rows=1 loops=1000' → inner loop ran 1000 times — consider Hash Join
-- 'Buffers: hit=5000 read=0' → all from cache — good
-- 'Buffers: hit=100 read=4900' → lots of disk reads — needs index or caching