Loosing all the object statistics after a major version upgrade of PostgreSQL with pg_upgrade is one of the real paint points in PostgreSQL. Collecting/generating the statistics can take much longer than the actual upgrade which is quite painful. A first the to resolve this was already committed for PostgreSQL 18 and I’ve written about this here. Yesterday, another bit was committed in this area, and this time it is vacuumdb which got a new switch.

Before we can take a look at this we need an object without any statistics and an object which already has statistics::

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

The first insert will not trigger statistics collection, while second insert will trigger it:

postgres=# select relname, last_autoanalyze from pg_stat_all_tables where relname in ('t','tt');
 relname |       last_autoanalyze        
---------+-------------------------------
 tt      | 2025-03-20 10:18:03.745504+01
 t       | 
(2 rows)

What the new switch for vacuumdb is providing, is to only collect statistics on objects which do not have any:

postgres@pgbox:/home/postgres/ [pgdev] vacuumdb --help | grep missing
      --missing-stats-only        only analyze relations with missing statistics

Using that, we should see fresh statistics on the first table, but not on the second one:

postgres@pgbox:/home/postgres/ [pgdev] vacuumdb --analyze-only --missing-stats-only postgres
vacuumdb: vacuuming database "postgres"
postgres@pgbox:/home/postgres/ [pgdev] psql -c "select relname, last_analyze from pg_stat_all_tables where relname in ('t','tt');"
 relname |         last_analyze         
---------+------------------------------
 tt      | 
 t       | 2025-03-20 10:24:36.88391+01
(2 rows)

Nice. All the details here and, as usual, thanks to everybody involved in this.

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