Plain EXPLAIN shows only the plan and the planner's estimates. EXPLAIN (ANALYZE, BUFFERS) actually runs the query and reports real rows, time, and page accesses. The gap between the estimate and the actual is usually what points to the problem.
Actual vs estimated numbers
Every plan node shows two pairs of numbers. The estimates are cost and rows (what the planner assumed before running). The actuals are actual time and rows (what really happened).
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;
A typical node line looks like this:
Seq Scan on orders (cost=0.00..1934.00 rows=12 width=40)
(actual time=0.40..18.7 rows=9 loops=1)
cost=0.00..1934.00 is an estimate in arbitrary units: startup, then full scan.rows=12 was expected, actual ... rows=9 is what really came back.actual time=0.40..18.7 is milliseconds to the first and to the last row.
Read the plan bottom-up and inside-out: leaf nodes execute first.
Loops: multiply by the number of passes
In a nested loop the inner node runs many times, and its actual time/rows are reported per single pass. Multiply by loops to get the total.
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE';
If the inner Index Scan shows actual time=0.02 rows=3 loops=5000, that is not 0.02 ms but roughly 100 ms in total and 15000 rows. Gotcha: a tiny per-loop time with a large loops count is the most common way to underestimate an expensive node.
The estimate-to-actual gap
A big gap between estimated rows and actual rows is the key signal. The planner picks its strategy from the estimate; if the estimate lies, it picks a bad plan.
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'paid' AND created_at >= '2026-01-01';
- Estimate off by tens of times usually means stale statistics: run
ANALYZE.
- The gap survives
ANALYZE: likely correlated predicates; extended statistics (CREATE STATISTICS) or an index helps.
- An overestimate makes the planner waste effort on a Seq Scan instead of an Index Scan.
BUFFERS: shared hit and read
BUFFERS shows memory work at each node. shared hit is pages found in cache; shared read is pages fetched from disk (slow).
Seq Scan on orders (actual time=0.4..210 rows=2000000 loops=1)
Buffers: shared hit=128 read=16234
shared hit is a hit in shared_buffers, cheap.shared read is a cache miss, a physical read; many read means the node is I/O heavy.Buffers tells you whether the query was slow because of disk or because of a bad plan.
A Seq Scan that should be an Index Scan
The main practical case: a query filters on a selective column, yet you see a Seq Scan with huge read and actual time.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;
CREATE INDEX idx_orders_user ON orders (user_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;
After the index, the Seq Scan turns into an Index Scan, and Buffers: shared read drops from thousands to a handful.
- MySQL:
EXPLAIN ANALYZE (8.0+) gives actual time and loops; there are no PostgreSQL-style buffers, so check Handler_read* and the profiler.
- ClickHouse: instead of a plan with actuals you use
EXPLAIN for structure and the system logs (query_log) for real read metrics.
Plain
EXPLAINshows only the plan and the planner's estimates.EXPLAIN (ANALYZE, BUFFERS)actually runs the query and reports real rows, time, and page accesses. The gap between the estimate and the actual is usually what points to the problem.Actual vs estimated numbers
Every plan node shows two pairs of numbers. The estimates are
costandrows(what the planner assumed before running). The actuals areactual timeandrows(what really happened).EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;A typical node line looks like this:
cost=0.00..1934.00is an estimate in arbitrary units: startup, then full scan.rows=12was expected,actual ... rows=9is what really came back.actual time=0.40..18.7is milliseconds to the first and to the last row.Read the plan bottom-up and inside-out: leaf nodes execute first.
Loops: multiply by the number of passes
In a nested loop the inner node runs many times, and its
actual time/rowsare reported per single pass. Multiply byloopsto get the total.EXPLAIN (ANALYZE, BUFFERS) SELECT u.email, o.amount FROM users u JOIN orders o ON o.user_id = u.id WHERE u.country = 'DE';If the inner
Index Scanshowsactual time=0.02 rows=3 loops=5000, that is not 0.02 ms but roughly 100 ms in total and 15000 rows. Gotcha: a tiny per-loop time with a largeloopscount is the most common way to underestimate an expensive node.The estimate-to-actual gap
A big gap between estimated
rowsand actualrowsis the key signal. The planner picks its strategy from the estimate; if the estimate lies, it picks a bad plan.ANALYZE orders; -- rebuild statistics EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01';ANALYZE.ANALYZE: likely correlated predicates; extended statistics (CREATE STATISTICS) or an index helps.BUFFERS: shared hit and read
BUFFERSshows memory work at each node.shared hitis pages found in cache;shared readis pages fetched from disk (slow).shared hitis a hit inshared_buffers, cheap.shared readis a cache miss, a physical read; manyreadmeans the node is I/O heavy.Bufferstells you whether the query was slow because of disk or because of a bad plan.A Seq Scan that should be an Index Scan
The main practical case: a query filters on a selective column, yet you see a
Seq Scanwith hugereadand actual time.-- before: Seq Scan, reads the whole table for 9 rows EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5; CREATE INDEX idx_orders_user ON orders (user_id); -- after: Index Scan, pinpoint read EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;After the index, the
Seq Scanturns into anIndex Scan, andBuffers: shared readdrops from thousands to a handful.EXPLAIN ANALYZE(8.0+) givesactual timeandloops; there are no PostgreSQL-style buffers, so checkHandler_read*and the profiler.EXPLAINfor structure and the system logs (query_log) for real read metrics.