Recap — 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:

Find the node with the highest actual time.
Check if rows estimated ≠ rows actual for that node.
Check the operation type (Seq Scan? Nested Loop?).
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.