Why the planner needs statistics
PostgreSQL’s query processing pipeline begins with parsing, then separates utility commands (ALTER, CREATE, DROP, etc.) via the traffic cop, passes through the rewrite system, and finally reaches the optimizer. The optimizer’s job is to produce the best execution plan by applying mathematical transformations and, crucially, using statistics to estimate how many rows a query will touch. Those estimates are what decide between a sequential scan, an index scan, or a parallel plan.
|
1 2 3 4 5 6 7 8 |
test=# CREATE TABLE t_test AS SELECT *, 'hans'::text AS name FROM generate_series(1, 1000000) AS id; SELECT 1000000 test=# ALTER TABLE t_test ALTER COLUMN id SET STATISTICS 10; ALTER TABLE test=# ANALYZE; ANALYZE |
With one million rows and statistics computed at a reduced target for readability, a simple filter shows the process in action:
|
1 2 3 4 5 6 |
test=# explain SELECT * FROM t_test WHERE id < 150000; QUERY PLAN --------------------------------------------------------------- Seq Scan on t_test (cost=0.00..17906.00 rows=145969 width=9) Filter: (id < 150000) (2 rows) |
Here, the planner expects 145,000 rows from a sequential scan. Based on that row count, the optimizer chose between two strategies:
- Sequential scan
- Parallel sequential scan
|
1 2 3 4 5 |
test=# explain SELECT * FROM t_test WHERE id < 1; QUERY PLAN --------------------------------------- ------------------------ Gather (cost=1000.00..11714.33 rows=1000 width=9) Workers Planned: 2 -> Parallel Seq Scan on t_test (cost=0.00..10614.33 rows=417 width=9) Filter: (id < 1) (4 rows) |
Changing a single value in the WHERE clause flips the plan entirely. The first query’s large expected result set makes parallel execution unattractive because aggregating rows in the gather node would be too expensive. When the same scan is expected to yield few rows, parallelism becomes worthwhile. Accurate statistics are therefore the foundation of good plan choices.
Inside pg_stats
The pg_stats view exposes what the planner knows about each column. Its key fields break down as follows:
- schemaname, tablename, attname: one row per column per table per schema
- inherited: whether the entry covers an inherited or partitioned table
- null_fraction: proportion of NULL values, relevant for
WHERE col IS NULL/IS NOT NULL - avg_width: expected average column width in bytes
- n_distinct: estimated number of distinct values
- most_common_vals: the most frequent values, important for skewed distributions
- most_common_freqs: frequency percentage of each most common value
- histogram_bounds: boundary values describing data distribution; at the default statistics target of 100, the histogram stores 101 entries representing 1% steps
- correlation: physical ordering of data on disk; sorted data allows range queries to touch fewer blocks, which also matters for BRIN indexes
Sample output from a real table illustrates how the planner uses these fields:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
test=# x Expanded display is on. test=# SELECT * FROM pg_stats WHERE tablename = 't_test'; -[ RECORD 1 ]----------+--------------------------------------------------------------------------- schemaname | public tablename | t_test attname | id inherited | f null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {47,102906,205351,301006,402747,503156,603102,700866,802387,901069,999982} correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram | -[ RECORD 2 ]----------+--------------------------------------------------------------------------- schemaname | public tablename | t_test attname | name inherited | f null_frac | 0 avg_width | 5 n_distinct | 1 most_common_vals | {hans} most_common_freqs | {1} histogram_bounds | correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram | |
For the id column, the histogram bounds suggest the minimum value is 47, ten percent of values are below 102,906, twenty percent below 205,351, and so on through the maximum of 999,982. An n_distinct of -1 indicates every value is unique, which directly informs GROUP BY estimates: the planner needs to know how many groups to expect. In the name column, "hans" accounts for 100% of values, so no histogram is needed.
Keeping statistics current
Statistics are normally maintained automatically by the autovacuum daemon, which refreshes them on a regular schedule. Manual collection is always possible with ANALYZE, but for most deployments autovacuum’s default behavior is sufficient.
