Stale Statistics After a Bulk Load Make the Planner Plan for an Empty Table
Statistics are refreshed by ANALYZE, usually triggered by autovacuum after enough rows change. Right after a bulk load, a restore, a big migration or a newly created table, the planner may still believe the table holds a handful of rows, so it picks a nested loop over what is now ten million. The same happens at the other end: rows loaded a minute ago are newer than the histogram's maximum value, so a created_at > yesterday filter is estimated as almost nothing. Slowness that appears immediately after a data load is stale statistics until proven otherwise.
Questions this Concept answers
Why does a freshly loaded table often end up with a nested loop over millions of rows?
J
jeremy
Video
How PostgreSQL Estimates Rows Beyond Its Statistics Range with get_actual_variable_range
A cost-based query planner estimates the selectivity of a predicate from column statistics (most-common values and histograms) gathered by a periodic sampling process, and those estimates drive plan …