The PostgreSQL optimizer relies on accurate statistics for finding and executing the most efficient plan for a given statement. Wrong or bad statistics usually lead to sub optimal execution plans and this usually is bad performance wise. This is not only true with PostgreSQL, all databases systems which use a cost based optimizer rely on statistics. Usually, you don’t need to care about the statistics, as PostgreSQL will collect them automatically based on the amount of changes which happened on a table since the last collection of the statistics. In this post we’ll look at how this works in general and then we’ll go into more details when it comes to partitioned tables.

Lets start by creating a simple table and populate it with one million rows:

postgres=# create table x ( a int, b text );
CREATE TABLE
postgres=# insert into x select i, 'aaa' from generate_series(1,1000000) i;
INSERT 0 1000000

If you ask for the available statistics in pg_stats right after population the table, you will most probably not see anything yet:

postgres=# select count(*) from pg_stats where tablename = 'x';
 count 
-------
     0
(1 row)

The reason is, that autovacuum (which is also responsible for automatically collecting the statistics) will sleep some time before it wakes up and checks if anything needs to be done. By default this is one minute:

postgres=# show autovacuum_naptime;
 autovacuum_naptime 
--------------------
 1min
(1 row)

After a maximum of one minute, you’ll see that statistics are available:

postgres=# select count(*) from pg_stats where tablename = 'x';
 count 
-------
     2
(1 row)

We’re not going to look at what those statistics are in detail in this post, just remember that autovacuum will collect them by default. When it will do this, depends on the following parameters:

postgres=# show autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor 
---------------------------------
 0.1
(1 row)

postgres=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold 
------------------------------
 50
(1 row)

For our table ‘x’ this means we would need to change approximately 10 percent of the table for the automatic gathering of statistics to kick in, and this can easily be demonstrated (again, you might need to wait up to a minute to see last_autoanalyze updated in pg_stat_all_tables):

postgres=# update x set b ='bbb' where a < 110000;
UPDATE 109999
-- Wait up to a minute
postgres=# select last_autoanalyze from pg_stat_all_tables where relname = 'x';
       last_autoanalyze        
-------------------------------
 2024-09-25 10:49:06.095124+02
(1 row)

For partitioned tables this works the same for the partitions, but not for the partitioned table itself. Before we look at why it is like this, lets create another simple test:

postgres=# create table y ( a int, b text ) partition by list (b);
CREATE TABLE
postgres=# create table y_1 partition of y for values in ('a');
CREATE TABLE
postgres=# create table y_2 partition of y for values in ('b');
CREATE TABLE
postgres=# \d+ y
                                      Partitioned table "public.y"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | integer |           |          |         | plain    |             |              | 
 b      | text    |           |          |         | extended |             |              | 
Partition key: LIST (b)
Partitions: y_1 FOR VALUES IN ('a'),
            y_2 FOR VALUES IN ('b')

This is a simple list partitioned table, containing two partitions. If we add some data PostgreSQL will take care of collecting the statistics, but there is a surprise:

postgres=# insert into y select i, 'a' from generate_series(1,1000000) i;
INSERT 0 1000000
postgres=# insert into y select i, 'b' from generate_series(1,1000000) i;
INSERT 0 1000000
-- Wait up to a minute
postgres=# select relname,last_autoanalyze from pg_stat_all_tables where relname like 'y%';
 relname |       last_autoanalyze        
---------+-------------------------------
 y       | 
 y_1     | 2024-09-25 11:22:07.463249+02
 y_2     | 2024-09-25 11:22:07.180454+02

We see that statistics have been collected on the individual partitions, but not on the partitioned table itself. The reason is, that the amount of changes are not tracked on the partitioned table, but only on the partitions:

postgres=# select relname,last_autoanalyze,n_mod_since_analyze from pg_stat_all_tables where relname like 'y%';
 relname |       last_autoanalyze        | n_mod_since_analyze 
---------+-------------------------------+---------------------
 y       |                               |                   0
 y_1     | 2024-09-25 11:22:07.463249+02 |                   0
 y_2     | 2024-09-25 11:22:07.180454+02 |                   0
(3 rows)

postgres=# update y set b = 'a' where a < 1000;
UPDATE 1998
postgres=# select relname,last_autoanalyze,n_mod_since_analyze from pg_stat_all_tables where relname like 'y%';
 relname |       last_autoanalyze        | n_mod_since_analyze 
---------+-------------------------------+---------------------
 y       |                               |                   0
 y_1     | 2024-09-25 11:22:07.463249+02 |                1998
 y_2     | 2024-09-25 11:22:07.180454+02 |                 999
(3 rows)

As the partitioned table itself does not contain any rows, there is nothing to track here. The consequence is, that there are no statistics at the partitioned table level, only on the partitions:

postgres=# select tablename,attname,null_frac,n_distinct from pg_stats where tablename like 'y%';
 tablename | attname | null_frac | n_distinct 
-----------+---------+-----------+------------
 y_2       | a       |         0 |         -1
 y_2       | b       |         0 |          1
 y_1       | a       |         0 |         -1
 y_1       | b       |         0 |          1
(4 rows)

If you want to have statistics on the partitioned table as well, you have to do that manually:

postgres=# analyze y;
ANALYZE
postgres=# select tablename,attname,null_frac,n_distinct from pg_stats where tablename like 'y%';
 tablename | attname | null_frac | n_distinct  
-----------+---------+-----------+-------------
 y         | a       |         0 |   -0.525885
 y         | b       |         0 |           2
 y_1       | a       |         0 | -0.99563634
 y_1       | b       |         0 |           1
 y_2       | a       |         0 |          -1
 y_2       | b       |         0 |           1
(6 rows)

Just be aware that this will not only collect the statistics for the partitioned table but also for all the partitions:

postgres=# select relname,last_analyze from pg_stat_all_tables where relname like 'y%';
 relname |         last_analyze          
---------+-------------------------------
 y       | 2024-09-25 11:34:53.503998+02
 y_1     | 2024-09-25 11:34:53.527063+02
 y_2     | 2024-09-25 11:34:53.545629+02
(3 rows)

If your partitions are huge, this can take quite some time and there is not much you can do about it. There was a patch committed yesterday to address this for PostgreSQL 18. When it will be released, probably end of September or beginning of October next year, you can ask analyze to only collect the statistics on the partitioned table:

postgres=# analyze only y;
ANALYZE
postgres=# select relname,last_analyze from pg_stat_all_tables where relname like 'y%';
 relname |         last_analyze          
---------+-------------------------------
 y       | 2024-09-25 11:41:00.619087+02
 y_1     | 2024-09-25 11:34:53.527063+02
 y_2     | 2024-09-25 11:34:53.545629+02
(3 rows)

postgres=#

This does not solve the problem of missing statistics on partitioned tables, but it avoids redundant work as statistics on the partitions will be collected automatically anyway.

Post Views: 208
Thumbnail [60x60]
by
Daniel Westermann