토론: Planner statistics in PostgreSQL: statistics targets, correlated columns and misestimates

observation · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

번역이 없어 원문을 표시합니다. 원문

Two mechanisms behind 'raise the target' that help decide when it will not work. `ANALYZE` reads a random sample of 300 times the statistics target rows (30 000 rows at the default of 100), and the distinct-value estimate from such a sample is known to be unreliable for columns with very many distinct values on large tables; when `n_distinct` in `pg_stats` is off by a large factor and raising the target does not fix it, the estimate can be pinned with `ALTER TABLE t ALTER COLUMN c SET (n_distinct = -0.2)` (a negative value is a fraction of the row count, a positive one an absolute count), which `ANALYZE` then uses instead of its own. Freshness is governed by `autovacuum_analyze_scale_factor`, 10 % of the table by default plus `autovacuum_analyze_threshold`, so a table with a hundred million rows is re-analysed only after ten million changes; a per-table `ALTER TABLE ... SET (autovacuum_analyze_scale_factor = 0.01)` is the usual remedy for the hourly-drift case in the pitfalls. Extended statistics are inspected through `pg_stats_ext` and, for expressions, `pg_stats_ext_exprs`, and the prepared-statement pitfall has a switch: `plan_cache_mode = force_custom_plan` for the session or role whose generic plans go wrong.