EXPLAIN ANALYZE in PostgreSQL: reading plans without guessing

Plans are trees: read from the most indented node outward. Child nodes feed their parents.

Scan types

• Seq Scan — reads the whole table. Perfectly fine for small tables or when you need most rows.
• Index Scan — walks an index and fetches matching rows from the table.
• Index Only Scan — answers from the index alone, provided the visibility map says the pages are all-visible (keep VACUUM healthy!).
• Bitmap Index Scan + Bitmap Heap Scan — collects matching row locations first, then reads table pages in physical order. Great for medium selectivity and for combining several indexes.

The number one clue: estimates vs. actuals

If the planner estimated rows=5 and got rows=50000, it probably chose a nested loop that should have been a hash join. Fix the estimate before tuning anything else:

ANALYZE orders;                       -- refresh statistics
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- more detail for skewed columns
CREATE STATISTICS orders_cust_status (dependencies) ON customer_id, status FROM orders;  -- correlated columns

Turning the plan into a fix

In the example above, the index found 1,791 rows for customer 42, and the filter threw away 1,604 of them. A composite index lets the index do both jobs:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at);

Re-run EXPLAIN (ANALYZE, BUFFERS): the filter line disappears, and shared buffer hits drop from 1,650 to a handful.

Other things to look for

• Sort Method: external merge Disk: … — the sort spilled to disk; consider raising work_mem for that query or adding an index that provides the order.
• Hash Batches > 1 — the hash join didn't fit in memory.
• Nested Loop with a large loops count on the inner side — often a sign of an underestimated outer input.

Key takeaways

1. Use EXPLAIN (ANALYZE, BUFFERS) and read nodes from the inside out.
2. Remember that time and rows are per loop.
3. Fix bad row estimates first — they cause most bad plans.
4. Rows Removed by Filter points you to missing composite indexes.

How PostgreSQL MVCC and VACUUM actually work

Every UPDATE in PostgreSQL leaves a dead row behind. Here's how MVCC works under the hood, what VACUUM cleans up, and how to keep autovacuum ahead of your workload.

#concurrency#internals#maintenance