Recap — Five Things to Look For

# Five Things to Look For `EXPLAIN ANALYZE SELECT ...` runs the query and returns the plan with timings. Analysts ignore this output 95% of the time. Reading just 5 things in it solves most of the queries you'd otherwise hand to engineering. ## 1) Seq Scan vs Index Scan ``` -> Seq Scan on orders (cost=0.00..18584.00 rows=1000000 width=64) Filter: (status = 'shipped') ``` **Seq Scan** means "read every row". For a 1M-row table with a filter that returns 1% of rows, this is almost always wrong — you should have an index. The fix is usually `CREATE INDEX orders_status_idx ON orders(status);`. **Index Scan** is what you want when filtering on a subset. Exception: if your filter returns most of the table (>20%), Seq Scan can actually be faster than Index Scan because Index Scan jumps around the disk. The query planner knows this and chooses accordingly. ## 2) Rows estimated vs rows actual ``` -> Index Scan on orders (cost=0.42..8.45 rows=10 width=64) (actual time=0.04..0.20 rows=10000 loops=1) ``` The planner thought 10 rows. Reality: 10,000 rows. This is a **statistics problem** — usually fixed by `ANALYZE table_name;` or by Postgres' autovacuum. When estimates are off by 100x or more, the planner picks the wrong join algorithm and the query gets slow. Always check estimate vs actual side by side. ## 3) Nested Loop vs Hash Join vs Merge Join The three join algorithms: - **Nested Loop** — fast for small left side (≤1000 rows), terrible for large. Each row on the left triggers a lookup on the right. - **Hash Join** — builds a hash table from one side, scans the other. Best when both sides are large. - **Merge Join** — sorts both sides, walks them in order. Good when data is already sorted (rare in OLTP). If you see **Nested Loop** with millions of rows on the left, that's your problem. The planner picked it because the row estimate was wrong (see #2). ## 4) Sort vs Index used for ORDER BY ``` -> Sort (cost=...) (actual time=2500ms ...) Sort Method: external merge Disk: 250MB ``` Disk-spilling sort = bad. Either: - Add an index on the ORDER BY column (the index returns rows in order, no sort needed), or - Increase `work_mem` so the sort happens in memory. ## 5) Filter applied after the join ``` -> Hash Join ... Hash Cond: (o.product_id = p.id) Filter: (o.created_at > '2026-01-01') ``` The `Filter` line is applied *after* the join. If `o.created_at` is highly selective (only 5% of orders match), you're doing the full join on all orders, then throwing away 95%. The fix: move the filter to a CTE or subquery so it's applied first. ## Reading the plan top-down EXPLAIN output is read **bottom-up**. The deepest nested node runs first. Top-level node is the final operation. The numbers in parens — `cost=A..B rows=R width=W` — are estimates. `(actual time=A..B rows=R loops=L)` (only present with EXPLAIN ANALYZE) are real measurements. When investigating slow queries: 1. Find the node with the highest `actual time`. 2. Check if `rows estimated` ≠ `rows actual` for that node. 3. Check the operation type (Seq Scan? Nested Loop?). 4. Walk up to see if a Sort or Filter is being applied wastefully. ## When NOT to optimize - One-time queries: just let them run. - Already <100ms: probably fine. - Dashboards refreshed daily: 30s is acceptable. - Live, customer-facing: <100ms target. Premature optimization is a real cost. Profile, fix the slow ones, leave the rest. ## Takeaway You don't need to understand every line of an EXPLAIN plan. Find the slow node, check estimate vs actual, classify the operation. 80% of slow queries are diagnosable in 60 seconds with this checklist.