Reading EXPLAIN Cost Estimates and Row Counts
Learn how to interpret the cost numbers, estimated rows, and width values that EXPLAIN attaches to each plan node, and how to spot when the planner's estimates are wrong.
Reading EXPLAIN Cost Estimates and Row Counts is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What the Cost Numbers Mean
Every node in an EXPLAIN plan shows a cost=startup..total pair. These are arbitrary planner units, not milliseconds. The planner uses them only to compare alternative plans and pick the cheapest one.
Startup vs Total Cost
The two numbers tell different stories:
- Startup cost: work before the first row is returned (e.g. building a hash table)
- Total cost: work to return all rows
A node that must sort or aggregate everything has a high startup cost.
A Sample Plan
Run EXPLAIN on a simple query and read the cost on each line.
EXPLAIN SELECT * FROM orders WHERE total > 100;The rows Estimate
Each node shows rows=N, the planner's estimated number of rows it will emit. The planner derives this from table statistics gathered by ANALYZE.
Bad estimates lead to bad plans, so this number matters a lot.
The width Value
The width=N field is the estimated average row size in bytes. Multiplied by rows it estimates memory and I/O needs, which influences whether sorts or hashes spill to disk.
Estimated vs Actual
EXPLAIN alone shows only estimates. Add ANALYZE to also run the query and see real timings and real row counts side by side.
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 100;Spotting Estimate Errors
Compare rows (estimate) with actual rows in EXPLAIN ANALYZE output. A large gap — say estimate 10 but actual 100000 — signals stale or insufficient statistics.
Why Estimates Drift
Estimates go stale when:
- Data changed a lot since the last
ANALYZE - Columns are correlated and the planner assumes independence
- The default statistics target is too low for skewed data
Refreshing Statistics
Run ANALYZE to recompute statistics for a table. Better stats produce better row estimates and therefore better plans.
ANALYZE orders;Raising the Statistics Target
For columns with skewed distributions, increase how many distinct values are sampled. A higher target means more accurate estimates at the cost of slightly slower ANALYZE.
ALTER TABLE orders ALTER COLUMN total SET STATISTICS 500;
ANALYZE orders;Cost Multipliers in postgresql.conf
Constants like seq_page_cost, random_page_cost, and cpu_tuple_cost scale how the planner weighs disk vs CPU. On SSDs, lowering random_page_cost makes index scans look cheaper.
Quick Check
Test your understanding of cost estimates.
Recap
You learned to read EXPLAIN's numbers:
- Cost is in arbitrary units; startup vs total cost differ
rowsandwidthare estimates from statistics- Compare estimated vs actual rows to spot bad stats
- Fix drift with
ANALYZEand a higher statistics target - Cost constants tune disk-vs-CPU weighting
Frequently asked questions
Is the “Reading EXPLAIN Cost Estimates and Row Counts” lesson free?
Yes — the full text of “Reading EXPLAIN Cost Estimates and Row Counts” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Reading EXPLAIN Cost Estimates and Row Counts”?
Learn how to interpret the cost numbers, estimated rows, and width values that EXPLAIN attaches to each plan node, and how to spot when the planner's estimates are wrong. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Reading EXPLAIN Cost Estimates and Row Counts” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Introduction to EXPLAIN and ANALYZE
- Interpreting Plan Nodes
- Identifying Performance Bottlenecks
- Reading EXPLAIN Cost Estimates and Row Counts