extended-statisticslisted

PostgreSQL's extended statistics — `CREATE STATISTICS`, `pg_statistic_ext`, `pg_statistic_ext_data` — the 4 stat kinds (dependencies / ndistinct / mcv / expressions) that help the planner handle correlated columns, multi-column NDVs, and expression cardinality. Covers `src/backend/statistics/extended_stats.c` + `dependencies.c` + `mcv.c` + `mvdistinct.c`. Loads when the user asks about extended stats syntax, when to `CREATE STATISTICS`, why a query has bad estimates on correlated columns, ndistinct for GROUP BY estimation, MCV lists in the extended-stats context, or the planner's `clauselist_selectivity_ext` selectivity path. Skip when the ask is about `pg_statistic` (single-column stats — the sibling; see `analyze-block-and-reservoir-sampling` idiom) or about ANALYZE mechanics (that's `analyze.c` — different code path).
matejformanek/postgres-claude · ★ 0 · AI & Automation · score 70

Install: claude install-skill matejformanek/postgres-claude

# extended-statistics — CREATE STATISTICS + planner integration Column stats live in `pg_statistic` (one row per column). But real queries often have correlated columns (`state = 'CA' AND city = 'SF'` — where `state` and `city` aren't independent) or expression predicates (`WHERE lower(name) = 'alice'`) that per-column stats can't estimate accurately. **Extended statistics** let the user create statistics OVER MULTIPLE COLUMNS OR EXPRESSIONS. The planner then consults them during selectivity estimation. ## The file map | File | Role | |---|---| | `src/backend/statistics/extended_stats.c` | Umbrella — ANALYZE-time computation dispatcher + `BuildRelationExtStatistics` + fetching. | | `src/backend/statistics/dependencies.c` | Functional-dependency stat kind: "column A determines column B (mostly)". | | `src/backend/statistics/mcv.c` | Multi-column MCV list — Most-Common Value lists for combinations of columns. | | `src/backend/statistics/mvdistinct.c` | Multi-column ndistinct — the N-distinct count of multi-column groupings. | | `src/backend/optimizer/path/clausesel.c` | Planner-side consumer — `clauselist_selectivity` calls into extended stats when applicable. | | `src/include/statistics/statistics.h` | Public API. | | `pg_statistic_ext.h` + `pg_statistic_ext_data.h` | Catalog definitions. Two catalogs: `pg_statistic_ext` (the DEFINITION — CREATE STATISTICS entry) + `pg_statistic_ext_data` (the ACTUAL SAMPLED STATS — populated by ANALYZE). | ## The 4 stat kinds Set via `C