Analyzing Postgres Tables
What happens when you run analyze in postgres?
In my previous blog post, I used analyze after defining extended statistics. But why do we run this statement?
When you run analyze on a postgres table, the pg_statistic table (and therefore the pg_stats view) gets updated. Those statistics give insight into the size and shape of the data in the tables so the query planner can choose an optimal lookup path. It is executed in the background during vacuum, so you hardly have to think about it (but see bonus note below).
Running analyze manually with the verbose option give some insight into the process.
analyze verbose acs_data , census_blocks , competitors , hud_crosswalk ;
INFO: analyzing "public.acs_data"
INFO: "acs_data": scanned 1370 of 1370 pages, containing 83364 live rows and 0 dead rows; 30000 rows in sample, 83364 estimated total rows
INFO: analyzing "public.census_blocks"
INFO: "census_blocks": scanned 30000 of 1064938 pages, containing 219992 live rows and 0 dead rows; 30000 rows in sample, 7809261 estimated total rows
INFO: analyzing "public.competitors"
INFO: "competitors": scanned 30000 of 135440 pages, containing 207742 live rows and 0 dead rows; 30000 rows in sample, 937886 estimated total rows
INFO: analyzing "public.hud_crosswalk"
INFO: "hud_crosswalk": scanned 2698 of 2698 pages, containing 189361 live rows and 0 dead rows; 30000 rows in sample, 189361 estimated total rows
The output demonstrates an important fact about analyze: the process collects statistics from a sample of rows, not every row. This means that every time you run the command (on a table of any size or complexity), you’ll get a different set of statistics.
analyze verbose census_blocks;
INFO: analyzing "public.census_blocks"
INFO: "census_blocks": scanned 30000 of 1064938 pages, containing 219245 live rows and 0 dead rows; 30000 rows in sample, 7782744 estimated total rows
analyze verbose census_blocks;
INFO: analyzing "public.census_blocks"
INFO: "census_blocks": scanned 30000 of 1064938 pages, containing 220017 live rows and 0 dead rows; 30000 rows in sample, 7810149 estimated total rows
In these two runs, it has captured statistics for 30K rows, but those are different rows each time. The 30K comes from 300 times the value of the GUC default_statistics_target which is by default 100. That 100 is how many mcv values, frequencies, or histogram bucket entries you’ll see in the stats. For example:
select attname, most_common_vals from pg_stats where tablename='census_blocks' and attname='name20';
attname | name20
most_common_vals | {"Block 1002","Block 1001","Block 1003","Block 1000","Block 1008","Block 2000","Block 2002","Block 2006","Block 1004","Block 1005","Block 2007","Block 2001","Block 1006","Block 1009","Block 2005","Block 1011","Block 2004","Block 1007","Block 2011","Block 2003","Block 1010","Block 1012","Block 2009","Block 2008","Block 1014","Block 1015","Block 2010","Block 1013","Block 1017","Block 1016","Block 2012","Block 2013","Block 3000","Block 3001","Block 2018","Block 1019","Block 3002","Block 2014","Block 1018","Block 2015","Block 2017","Block 3003","Block 1022","Block 2016","Block 1020","Block 1021","Block 2019","Block 3011","Block 3009","Block 2023","Block 3007","Block 2020","Block 2022","Block 3005","Block 3010","Block 1023","Block 3006","Block 3004","Block 1029","Block 2021","Block 1025","Block 3008","Block 1026","Block 3013","Block 1024","Block 1027","Block 2025","Block 3012","Block 2024","Block 2026","Block 1033","Block 3015","Block 2029","Block 1030","Block 2027","Block 1028","Block 3014","Block 1035","Block 3016","Block 4000","Block 1031","Block 2031","Block 3020","Block 2028","Block 3019","Block 3021","Block 1032","Block 1040","Block 3017","Block 2030","Block 2037","Block 1034","Block 2032","Block 3022","Block 2033","Block 1041","Block 3018","Block 3023","Block 1036","Block 1037"}
Or displayed so you can see the total count of most_common_vals:
SELECT idx, val
FROM pg_stats,
unnest(most_common_vals::text::text[]) WITH ORDINALITY AS t(val, idx)
WHERE tablename = 'census_blocks'
AND attname = 'name20';
idx | val
-----+------------
1 | Block 1002
2 | Block 1001
3 | Block 1003
4 | Block 1000
5 | Block 1008
6 | Block 2000
7 | Block 2002
8 | Block 2006
9 | Block 1004
10 | Block 1005
...
95 | Block 2033
96 | Block 1041
97 | Block 3018
98 | Block 3023
99 | Block 1036
100 | Block 1037
(100 rows)
But there are many more distinct values than the sample in reality:
select count(distinct name20) from census_blocks;
count
-------
5303
You can increase the statistics target globally, but that’s not recommended. If your planner is really having a hard time with choosing an optimal plan, it’s better to tune it for a specific column, like this:
alter table census_blocks alter column name20 set statistics 500;
A bonus note about partitioned tables and analyze
One thing to note, because I have seen a lot of performance issues on partitioned tables. It’s not the only reason for performance degradation, but: the parent table doesn’t have statistics until you explicitly run analyze on it, although the underlying partitions do. (This is because the parent table doesn’t itself have any rows to trigger the auto-analyze thresholds.)
Here’s a sample orders table that has been partitioned. After a set retention period, these partitions will be detached and archived. In older partitions, orders have been completed, and in the latest partition, orders may be pending or completed.
public | orders | partitioned table | vparham
public | orders_2025_01 | table | vparham
public | orders_2025_02 | table | vparham
public | orders_2025_03 | table | vparham
public | orders_2025_04 | table | vparham
public | orders_2025_05 | table | vparham
public | orders_2025_06 | table | vparham
public | orders_2025_07 | table | vparham
public | orders_2025_08 | table | vparham
public | orders_2025_09 | table | vparham
public | orders_2025_10 | table | vparham
public | orders_2025_11 | table | vparham
public | orders_2025_12 | table | vparham
Here you see that while the partitioned/parent table doesn’t have stats, the underlying partition/child tables do.
select * from pg_stats where tablename='orders' and attname='order_date';
(0 rows)
select * from pg_stats where tablename='orders_2025_01' and attname='order_date';
schemaname | public
tablename | orders_2025_01
attname | order_date
inherited | f
null_frac | 0
avg_width | 8
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {"2025-01-01 00:01:01.568318-05","2025-01-01 07:38:46.064784-05","2025-01-01 14:29:12.436855-05","2025-01-01 22:13:10.681144-05","2025-01-02 04:55:16.513978-05","2025-01-02 12:02:17.755635-05","2025-01-02 20:34:50.493629-05","2025-01-03 03:03:49.385977-05","2025-01-03 09:44:42.920939-05","2025-01-03 17:11:33.911772-05","2025-01-04 00:21:43.926642-05","2025-01-04 07:26:50.773816-05","2025-01-04 14:32:55.133094-05","2025-01-04 22:20:37.533564-05","2025-01-05 05:22:53.378794-05","2025-01-05 13:24:29.882334-05","2025-01-05 20:24:39.746631-05","2025-01-06 02:42:30.773519-05","2025-01-06 09:30:19.8242-05","2025-01-06 16:39:54.684772-05","2025-01-06 22:49:08.235435-05","2025-01-07 06:17:25.80298-05","2025-01-07 14:59:33.677541-05","2025-01-07 22:55:14.829331-05","2025-01-08 05:39:58.599199-05"...}
Not having statistics can hurt when using complex joins from the parent table. Here’s just one example I’ve seen. The query here might be run frequently for a dashboard display, so it needs to perform well.
explain (analyze, costs, verbose, buffers, format json)
select o.product_id, sum(o.total_amount)
from orders o
where o.order_date >= '2025-06-01'
and o.order_date <= '2025-12-31'
group by o.product_id
order by sum(o.total_amount) desc;
Running this query before and after analyzing the parent table shows that without any stats, the planner defaults to assuming 200 rows, underestimating by about 350 times the true number. With stats, it does a better job of estimating rows, only underestimating by about 4 times. With those stats, the planner can make a cleaner choice when using system resources: allocate memory large enough to process the query, avoid spilling to temp space when sorting, and reduce workers needed to process the query. Time to run the query also decreases.
Take home point: if you have just created a partitioned table, or have bulk loaded or changed a lot of data on partitions, manually run an analyze step on the parent table (and then schedule it with pg_cron). This is especially recommended if you are seeing high resource consumption, slow-down of queries, or other performance problems on partitioned tables.
