Reading EXPLAIN ANALYZE Without a DBA: A 15-Minute Guide
Reading EXPLAIN ANALYZE Without a DBA: A 15-Minute Guide
Most developers avoid `EXPLAIN ANALYZE` because the output looks like a wall of indentation and numbers. In practice, the vast majority of slow queries trace back to just a handful of recognizable patterns. Learning to spot these four takes about fifteen minutes, and it will resolve most performance complaints before anyone needs to escalate to a database specialist.
Before diving in, remember the basics: run `EXPLAIN (ANALYZE, BUFFERS)` on the actual slow query, and read the plan from the innermost (bottom) nodes outward. The bottom nodes run first; the top node is the final result returned to the client.
Pattern 1: Sequential Scans on Large Tables
A `Seq Scan` means Postgres is reading every row in a table. This is normal and often fastest for small tables, but on a table with millions of rows, it's usually the cause of a slow query. Look at the `rows` estimate versus `actual rows` — a large gap suggests stale statistics, while a seq scan with a `Filter` line and a high number of "rows removed by filter" is a strong signal that an index is missing on the filtered column.
The fix is rarely mysterious: add a B-tree index on the column used in the `WHERE` clause. But indexing isn't free — each index adds write overhead and disk space, so this is where understanding index types and workload patterns matters. Not every column deserves an index, and the wrong index type (e.g., B-tree on a low-cardinality boolean) won't help.
Pattern 2: Nested Loops Gone Wrong
Nested loop joins are efficient when one side of the join is small. They become a problem when the planner underestimates row counts and ends up looping over a much larger set than expected. This shows up as a `Nested Loop` node with a huge disparity between the estimated and actual loop counts, or an actual execution time far higher than the estimated cost would suggest.
When this happens, the root cause is usually outdated statistics. Running `ANALYZE` on the relevant tables refreshes the planner's row estimates and can immediately fix a bad plan without touching the query itself. If the estimates are already accurate and the nested loop is still slow, it may be a sign that a hash join or merge join would perform better — which usually means the planner needs more memory (`work_mem`) to consider that alternative.
Pattern 3: Sorts That Spill to Disk
Look for `Sort Method: external merge` in the plan output. This means the sort operation didn't fit in memory and spilled to disk, which is dramatically slower than an in-memory sort. This is one of the easiest wins available: increasing `work_mem` for the session or query often converts an external sort into a quick in-memory one.
The tradeoff is that `work_mem` is allocated per sort or hash operation, per connection — set it too high globally and concurrent queries can exhaust server memory. A targeted `SET work_mem` before a specific reporting query is usually safer than a blanket increase.
Pattern 4: Underestimated Row Counts Cascading Upward
Sometimes no single node looks alarming, but a small misestimate near the bottom of the plan compounds as it propagates through several joins above it. This is why comparing estimated versus actual rows at every level — not just the slowest-looking node — matters. Tools that visualize query plans can make these cascading errors easier to spot than scanning raw text output, and platforms like Querk are built specifically to surface this kind of discrepancy automatically.
Once these four patterns are familiar, most `E