Section 2 of 3 · 7 minutes
Compare estimates with actual work
Your goal
What you will be able to do
Use EXPLAIN ANALYZE safely to compare estimated rows, actual rows, loops, and timing.
Why this matters at work
The practical reason
A plan that looks reasonable on estimates may perform much more work when it runs.
Learn
The idea in plain English
`EXPLAIN (ANALYZE, BUFFERS)` runs the statement and records actual work. Use it carefully: a changing statement will really change data unless you contain it in a transaction and roll it back.
Compare estimated rows with actual rows at each node. Multiply per-loop rows and time by the loop count when judging repeated work. Buffer information shows whether the node read cached or physical pages.
Remember these points
- EXPLAIN ANALYZE executes the statement.
- Read actual rows together with loops.
- Buffers help distinguish logical page work from elapsed time alone.
See it in SQL
Measure a read-only query
Collect runtime rows and buffer activity for a bounded SELECT.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM training_orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;The LIMIT bounds returned rows, but PostgreSQL may still inspect more rows depending on the access path.
Look for the largest estimate error and the node with the most repeated or discarded work before proposing a change.
An executed plan showing actual rows, loops, timing, and buffer activity for each node.
Useful words
Important terms
- Loops
- The number of times PostgreSQL repeated a plan node.
- Buffers
- Table or index pages read, found in cache, dirtied, or written by a plan.
Quick check · Not graded
Check your understanding
What extra risk does EXPLAIN ANALYZE have?
Your progress is saved to your signed-in account.