EXPLAIN shows the plan PostgreSQL intends to use. EXPLAIN ANALYZE actually runs the query and shows what happened. Together they are the fastest route from "this is slow" to "this is why".
The command you'll use 90% of the time
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id =42AND o.created_at >= now() -interval'30 days';
ANALYZE executes the query and adds actual timings and row counts.
BUFFERS shows how many 8 kB pages came from shared buffers (hit) versus from disk or the OS cache (read). Since PostgreSQL 18, BUFFERS is included automatically with ANALYZE.
⚠️ EXPLAIN ANALYZE really runs the statement. For INSERT, UPDATE or DELETE, wrap it in a transaction and roll back:
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status ='x'WHERE id =1;
ROLLBACK;
Anatomy of a plan node
Index Scan using orders_customer_id_idx on orders o
(cost=0.43..812.50 rows=210 width=16)
(actual time=0.041..3.912 rows=187 loops=1)
Index Cond: (customer_id = 42)
Filter: (created_at >= (now() - '30 days'::interval))
Rows Removed by Filter: 1604
Buffers: shared hit=1650
cost — startup..total cost in the planner's arbitrary units.
After an import or migration, every insert collides with an existing id because the sequence is behind. Reset one or all sequences, and prevent it with identity columns.
rows (first parentheses) — the planner's estimate.
actual time / rows — reality, per loop.
loops — how many times this node ran. Multiply actual time and rows by loops for totals.
Rows Removed by Filter — work the index didn't save you.
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 statisticsALTER TABLE orders ALTERCOLUMN status SET STATISTICS 1000; -- more detail for skewed columnsCREATE 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
Use EXPLAIN (ANALYZE, BUFFERS) and read nodes from the inside out.
Remember that time and rows are per loop.
Fix bad row estimates first — they cause most bad plans.
Rows Removed by Filter points you to missing composite indexes.
A founder's guide to choosing PostgreSQL or MySQL: team experience, managed hosting, scaling paths, extensions, licensing, switching cost, and a clear "pick PostgreSQL unless" rule.
timestamptz doesn't store a time zone, and timestamp silently drops the offset you pass in. What each type really stores, where timestamp causes bugs and the one case where it's right.
