pg_stats: How Postgres Internal Stats Work

Why does Postgres sometimes choose a Sequential Scan over an Index Scan, even when it seems slower? The answer almost always lies in the statistics. The query planner relies on a mathematical model of your data distribution to make decisions, and when that model is wrong, performance suffers.

In this session, we will crack open the pg_stats view to understand exactly how Postgres "sees" your data. We will cover histograms, most common values (MCV), and correlation. We will also explore Extended Statistics, a powerful feature for fixing bad query plans on correlated columns (like City and State), ensuring your planner stops guessing and starts knowing.

As presented at POSETTE 2026 Livestream #2
June 16, 2026

Transcript

1. pg_stats How Postgres Internal Stats Work June 2026 Richard Yen POSETTE 2026
2. SELECT * FROM customers WHERE state = ' CA' ;
3. SELECT * FROM customers WHERE state = ' CA' ; CREATE TABLE customers ( id bigserial PRIMARY KEY, city text NOT NULL, state text NOT NULL, signup_date date NOT NULL ); -- Insert 1,000,000 rows
4. SELECT * FROM customers WHERE state = ' CA' ; QUERY PLAN ----------------------------------------------------------------- Seq Scan on customers (cost=0.00..19682.66 rows=173829 width=26) (actual time=0.025..120.574 rows=172001 loops=1) Filter: (state = 'CA'::text) Rows Removed by Filter: 827972 Buffers: shared hit=4601 read=2582 Planning: Buffers: shared hit=139 Planning Time: 0.371 ms Execution Time: 128.136 ms (8 rows)
5. SELECT * FROM customers WHERE state = ' CA' ; QUERY PLAN ----------------------------------------------------------------- Seq Scan on customers (cost=0.00..19682.66 rows=173829 width=26) (actual time=0.025..120.574 rows=172001 loops=1) Filter: (state = 'CA'::text) Rows Removed by Filter: 827972 Buffers: shared hit=4601 read=2582 Planning: Buffers: shared hit=139 Planning Time: 0.371 ms Execution Time: 128.136 ms (8 rows)
6. Query Plans are made by the Query Planner
7. Your Data Summary Read by the Planner customers pg_statistic (pg_stats)
8. pg_stats is a VIEW of pg_statistic
9. pg_stats is a VIEW of pg_statistic Human-Readable NOT Human-Readable
10. Postgres Trivia! SELECT relname , relkind FROM pg_class WHERE relname LIKE ' pg%' AND relkind in (' v','r ') ORDER BY relkind ;
11. ANALYZE customers; (Builds the summary in pg_statistic)
12. A Simple Example
13. CREATE TABLE customers ( id bigserial PRIMARY KEY, city text NOT NULL, state text NOT NULL, signup_date date NOT NULL ); -- Insert 1,000,000 rows ANALYZE customers;
14. SELECT attname,n_distinct,null_frac,correlation FROM pg_stats WHERE tablename = 'customers' ORDER BY attname ; attname | n_distinct | null_frac | correlation ------------- +------------ +----------- +-------------- city | 10106 | 0 | 0.0021338463 id | - 1 | 0 | 1 signup_date | 1822 | 0 | 1 state | 50 | 0 | 0.06440461 (4 rows)
15. SELECT attname,n_distinct,null_frac,correlation FROM pg_stats WHERE tablename = 'customers' ORDER BY attname ; attname | n_distinct | null_frac | correlation ------------- +------------ +----------- +-------------- city | 10106 | 0 | 0.0021338463 id | - 1 | 0 | 1 signup_date | 1822 | 0 | 1 state | 50 | 0 | 0.06440461 (4 rows)
16. SELECT attname,n_distinct,null_frac,correlation FROM pg_stats WHERE tablename = 'customers' ORDER BY attname ; attname | n_distinct | null_frac | correlation ------------- +------------ +----------- +-------------- city | 10106 | 0 | 0.0021338463 id | - 1 | 0 | 1 signup_date | 1822 | 0 | 1 state | 50 | 0 | 0.06440461 (4 rows) Range -1 to +1 Affects index scan selection
17. SELECT unnest( most_common_vals ::text::text[]) AS state, unnest( most_common_freqs ) AS frequency FROM pg_stats WHERE tablename = 'customers' AND attname = 'state' LIMIT 5; state | frequency ------- +------------- CA | 0.17403333 TX | 0.1165 NY | 0.08586667 FL | 0.0666 IL | 0.0474 (5 rows)
18. random_page_cost seq_page_cost cpu_tuple_cost Plans are generated based on cost calculation
19. Cost calculation affects: Scan Type • Sequential Scan • Index Scan • Bitmap Heap Scan Join Type • Nested Loop • Hash Join • Merge Join
20. EXPLAIN ANALYZE SELECT * FROM customers WHERE state = 'CA'; -- Seq Scan on customers (rows ~180000) EXPLAIN ANALYZE SELECT * FROM customers WHERE state = 'WY'; -- Index Scan using customers_state_idx (rows=400)
21. EXPLAIN ANALYZE SELECT * FROM customers WHERE state = 'CA'; -- Seq Scan on customers (rows ~180000) QUERY PLAN ------------------------------------------------------- Seq Scan on customers (cost=0.00..19682.66 rows=174029 ...) (actual time=0.042..50.257 rows=172001 loops=1) Filter: (state = 'CA'::text) Rows Removed by Filter: 827972 Planning Time: 0.222 ms Execution Time: 55.811 ms (8 rows)
22. EXPLAIN ANALYZE SELECT * FROM customers WHERE state = 'WY'; -- Index Scan using customers_state_idx (rows ~4000) QUERY PLAN --------------------------------------------------- Index Scan using customers_state_idx on customers (cost=0.42..13116.39 rows=4233 ...) (actual time=0.045..21.238 rows=4300 loops=1) Index Cond: (state = 'WY'::text) Planning Time: 0.051 ms Execution Time: 21.470 ms (5 rows)
23. SET enable_seqscan = off;
24. EXPLAIN ANALYZE SELECT * FROM customers WHERE state = 'CA'; -- Index Scan on customers (rows ~180000) QUERY PLAN --------------------------------------------------- Index Scan using customers_state_idx on customers (cost=0.42..32172.73 rows=170529 width=26) (actual time=0.053..75.656 rows=172001 loops=1) Index Cond: (state = 'CA'::text) Buffers: shared hit=288 read=7043 Planning Time: 0.080 ms Execution Time: 80.221 ms (5 rows)
25. EXPLAIN SELECT * FROM customers WHERE state = 'CA'; -- Index Scan on customers (rows ~180000) QUERY PLAN --------------------------------------------------- Index Scan using customers_state_idx on customers (cost=0.42.. 32172.73 rows=170529 width=26) (actual time=0.053..75.656 rows=172001 loops=1) Index Cond: (state = 'CA'::text) Buffers: shared hit=288 read=7043 Planning Time: 0.080 ms Execution Time: 80.221 ms (5 rows)
26. MCV helps select seq scan when data is skewed
27. Histograms
28. SELECT (unnest( histogram_bounds ::text::date[]))::date AS bucket_bound FROM pg_stats WHERE tablename = 'customers' AND attname = ' signup_date ' LIMIT 8; --- First 8 histogram boundaries for signup_date --- (default target = 100 buckets, each ~1% of rows) bucket_bound -------------- 2018 - 01 - 01 2018 - 03 - 01 2018 - 04 - 28 2018 - 07 - 04 2018 - 09 - 05 2018 - 10 - 28 2018 - 12 - 27 2019 - 02 - 14 (8 rows)
30. ALTER TABLE customers ALTER COLUMN signup_date SET STATISTICS 1000;
31. SELECT (unnest( histogram_bounds ::text::date[]))::date AS bucket_bound FROM pg_stats WHERE tablename = 'customers' AND attname = ' signup_date ' LIMIT 8; --- First 8 histogram boundaries for signup_date --- (now 1000 buckets, each ~0.1% of rows) bucket_bound -------------- 2018 - 01 - 01 2018 - 01 - 03 2018 - 01 - 05 2018 - 01 - 07 2018 - 01 - 10 2018 - 01 - 12 2018 - 01 - 14 2018 - 01 - 16 (8 rows)
32. SELECT (unnest( histogram_bounds ::text::date[]))::date AS bucket_bound FROM pg_stats WHERE tablename = 'customers' AND attname = ' signup_date ' LIMIT 8; --- First 8 histogram boundaries for signup_date --- (now 1000 buckets , each ~0.1% of rows) bucket_bound -------------- 2018 - 01 - 01 2018 - 01 - 03 2018 - 01 - 05 2018 - 01 - 07 2018 - 01 - 10 2018 - 01 - 12 2018 - 01 - 14 2018 - 01 - 16 (8 rows) bucket_bound -------------- 2018 - 01 - 01 2018 - 03 - 01 2018 - 04 - 28 2018 - 07 - 04 2018 - 09 - 05 2018 - 10 - 28 2018 - 12 - 27 2019 - 02 - 14 (8 rows) Beware of Trade - offs!
33. Now, about correlations!
34. EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Cheyenne' AND state = 'WY'; ... Index Scan on customers (cost=... rows=8 width=...) (actual rows=4012)
35. EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Cheyenne' AND state = 'WY’; ... Index Scan on customers (cost=... rows=8 width=...) (actual rows=4012 )
36. 𝑃(city = Cheyenne AND state = WY) = 𝑃(city) × 𝑃(state)
37. CREATE STATISTICS customers_city_state (dependencies, ndistinct ) ON city, state FROM customers; ANALYZE customers;
38. CREATE STATISTICS customers_city_state ( dependencies, ndistinct ) ON city, state FROM customers; ANALYZE customers; Statistic Types
39. EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Cheyenne' AND state = 'WY'; ... Index Scan on customers (cost=... rows=4087 width=...) (actual rows=4012)
40. Query Planning checklist 1. Compare estimated vs actual rows. Run EXPLAIN ANALYZE. Look for the deepest node where the estimate disagrees with reality. 2. Check pg_stats for that column. Look at n_distinct , the MCV list. Do they match what you know about your data? 3. If the stats look stale, run ANALYZE. Especially after a big batch load, a migration, or a partition swap. Autovacuum may not have caught up.
41. Query Planning checklist (cont’d) 4. If estimates are still off on a single column, raise the statistics target. ALTER COLUMN ... SET STATISTICS 1000 . 5. If the bad estimate involves two columns in the same WHERE clause, consider correlation. Create extended statistics on the pair. 6. Only after all of that should you consider rewriting the query.
42. The Query Planner is only as smart as the statistics you feed it.
43. © Copyright Microsoft Corporation. All rights reserved. Thank you for attending!