{"id":465875,"date":"2025-07-03T09:39:07","date_gmt":"2025-07-03T09:39:07","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=465875"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=465875","title":{"rendered":"Automated management of extended statistics in PostgreSQL<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

Extended statistics \u2014 what is it?<\/h4>\n

The\u00a0extended statistics\u00a0feature lets you tell PostgreSQL to collect additional statistics on a set of table columns. Why would you need that? Let me quickly explain using the public power plant dataset<\/a>. For instance, the\u00a0primary_fuel<\/code>\u00a0used by a power plant is implicitly tied to the\u00a0country<\/code>\u00a0column. So, if you run a simple query like:<\/p>\n

SELECT count(*) FROM power_plants WHERE country = '<XXX>' AND primary_fuel = 'Solar';<\/code><\/pre>\n
You\u2019ll see that Norway returns 0 rows, while Spain returns 243. That\u2019s obvious to us \u2014 latitude, climate, etc. \u2014 but the database doesn\u2019t know that. During query planning, PostgreSQL estimates the row count incorrectly: 93 for Norway and 253 for Spain. In a more complex query, where this estimate feeds into a JOIN or some other operator, the consequences can be unfortunate. Extended statistics calculate joint value distributions across column sets and help detect these dependencies.<\/p>\n
ORMs often make things worse. In this same power plant dataset, you might see filters on\u00a0country<\/code>\u00a0and\u00a0country_long<\/code>. From their descriptions, it\u2019s clear these two fields are directly related. But when an ORM generates a GROUP BY on both fields, the estimation fails dramatically:<\/p>\n
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, BUFFERS OFF, SUMMARY OFF) SELECT country, country_long FROM power_plants GROUP BY country, country_long;  HashAggregate  (rows=3494 width=16) (actual rows=167.00 loops=1)   Group Key: country, country_long   ->  Seq Scan on power_plants  (rows=34936 width=16)                                 (actual rows=34936.00 loops=1)<\/code><\/pre>\n
A human would never write that query, but in the age of AI and auto-generated queries, this is the kind of mess we need to deal with.<\/p>\n
So what does\u00a0extended statistics\u00a0actually offer? It lets you define three types of stats on a column set:\u00a0MCV\u00a0(Most Common Values),\u00a0ndistinct, and\u00a0dependencies.<\/p>\n
For scan filters,\u00a0MCV\u00a0is the most effective: if the value combo you\u2019re filtering on is common in the table, the planner will estimate it accurately. If the combo is rare (like solar power in Norway), then PostgreSQL uses rough estimates like\u00a0ntuples \/ ndistinct<\/code>, but can improve them by excluding values from the MCV list.<\/p>\n
For estimating group counts (GROUP BY, DISTINCT, IncrementalSort, Memoize, Hash Join), the\u00a0ndistinct<\/code>\u00a0stat on column combinations is very helpful.<\/p>\n
To see the effect of extended stats, let\u2019s apply it to our example:<\/p>\n
CREATE STATISTICS ON country,primary_fuel FROM power_plants; ANALYZE;<\/code><\/pre>\n
Now the earlier query gives much more accurate cardinality estimates for filtering and grouping by these two fields. For Norway, it estimates 1 row; for Spain, 253 \u2014 feel free to try it with\u00a0country = 'RUS'<\/code>\u00a0or\u00a0'AUT'<\/code>. Sure, the table isn\u2019t that big, but the tool clearly works.<\/p>\n
Yet I rarely see\u00a0extended statistics\u00a0used in the wild. Probably one reason is fear that\u00a0ANALYZE<\/code>\u00a0will take too long. But more likely, it\u2019s the complexity of knowing when and where to create the stats.<\/p>\n
Finding the right stats definition<\/h3>\n
Are there any empirical rules for when and what kind of stats to create? I came up with two rules of thumb:<\/p>\n
Rule 1 is based on index definitions<\/strong>. If a DBA risks creating an index on a set of columns, it probably means queries will often filter on those columns. And query performance on those filters is critical \u2014 a good reason to improve plan quality.<\/p>\n
Of course, not every multi-column filter suffers from bad estimates. That\u2019s a drawback of this rule \u2014 we might end up creating useless stats. And if the expected use case is point lookups, a small estimation error (1 vs 5 rows) doesn\u2019t matter much.<\/p>\n
Rule 2 based on real-world filter patterns<\/strong>. Here, we pick candidate queries using two factors: (1) how much load the query puts on the DB (e.g.\u00a0pages-read<\/a>), and (2) whether it contains multi-column scan filters. It would also be nice to only consider cases where the actual row count differs significantly from the estimate.<\/p>\n
This rule is more selective \u2014 it reduces the number of generated stats definitions. But it raises tricky questions:<\/p>\n