What does it mean when estimated rows and actual rows differ a lot in EXPLAIN ANALYZE?
The planner chose its plan from a wrong guess about row counts. Usual causes are stale statistics, correlated columns, or expressions it cannot estimate. Run ANALYZE, raise the statistics target, or create extended statistics.
Explain it simply
A caterer plans food after guessing 100 guests and then 1,000 arrive. Every later decision, how many cooks, which stove, is wrong. The planner does the same: it estimates how many rows each step returns and builds the whole plan on that. A big gap between rows= (estimate) and actual rows= is the first thing to look for in a slow plan.
Example
EXPLAIN ANALYZE
SELECT * FROM orders WHERE city = 'Pune' AND pincode = '411001';
-- Index Scan ... (cost=... rows=1 ...) (actual time=... rows=8400 loops=1)
-- ^ estimated 1 ^ actual 8400Typical fixes:
ANALYZE orders; -- refresh statistics
ALTER TABLE orders ALTER COLUMN city SET STATISTICS 500;
ANALYZE orders; -- finer histogram
CREATE STATISTICS orders_city_pin (dependencies)
ON city, pincode FROM orders; -- columns are correlated
ANALYZE orders;By default the planner assumes columns are independent, so it multiplies selectivities. City and pincode are not independent, hence the underestimate.
What to say in the interview
"A large gap between estimated and actual rows means the planner is flying blind. I run ANALYZE first, then raise the statistics target on the column, and use CREATE STATISTICS when columns are correlated."
Common mistakes
- Tweaking cost settings before checking whether the row estimates are right.