What do the cost numbers in an EXPLAIN plan actually mean?
cost=A..B gives the estimated startup cost (before the first row) and total cost, in arbitrary planner units based on page fetches and CPU work. They are for comparing plans, not seconds or milliseconds.
Explain it simply
The planner prices each way of running your query, the way you compare auto fares from two routes. The number is not rupees or minutes; it is a score. A sequential page read costs 1.0 unit. Fetching a random page costs 4.0 (setting random_page_cost). Processing a row costs 0.01 (cpu_tuple_cost).
cost=0.00..1834.00 means: 0.00 is the startup cost, work needed before the first row can be returned, and 1834.00 is the total cost to return all rows. A sort has high startup cost because it must read everything before emitting a row. A plain scan has startup near zero.
Example
EXPLAIN SELECT * FROM orders ORDER BY total;
-- Sort (cost=1534.20..1559.20 rows=10000 width=48)
-- Sort Key: total
-- -> Seq Scan on orders (cost=0.00..183.00 rows=10000 width=48)Here the Sort's startup cost (1534) is close to its total, since it must finish sorting before returning anything. width is the average row size in bytes.
Costs of different queries are not comparable with each other as time. Only use them to compare plans of the same query.
What to say in the interview
"Costs are unitless estimates from page and CPU settings. The first number is startup cost, the second is total. They are meant for comparing plans of one query, not for predicting seconds, so I rely on EXPLAIN ANALYZE for real timing."
Common mistakes
- Treating cost as milliseconds.