Horton

Re: vacuumdb changes for stats import/export

Nathan Bossart <nathandbossart@gmail.com>

View thread View on postgr.es
From: Nathan Bossart <nathandbossart@gmail.com>
To: John Naylor <johncnaylorls@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Bruce Momjian <bruce@momjian.us>, Jeff Davis <pgsql@j-davis.com>, Corey Huinker <corey.huinker@gmail.com>, jian he <jian.universality@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Magnus Hagander <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, alvherre@alvh.no-ip.org
Date: 2025-03-14T19:05:30Z
Lists: pgsql-hackers 
Out of curiosity, I generated many relations with the following command
(stolen from [0]):

do $$
begin
for i in 1..100000 loop
  execute format('create table t%s (f1 int unique, f2 int unique);', i);
  execute format('insert into t%s select x, x from generate_series(1,1000) x',
                 i);
  if i % 100 = 0 then commit; end if;
end loop;
end
$$;

And then I ran a database-wide ANALYZE.  Without --missing-only, vacuumdb's
catalog query took 65 ms.  With --missing-only, it took 735 ms.  While
that's a big jump, this query will only run once for a given vacuumdb, and
--missing-only is likely to save a lot of time elsewhere.

If no feedback or objections materialize, I'm planning to commit these
early next week.

[0] https://postgr.es/m/3612876.1689443232%40sss.pgh.pa.us

-- 
nathan

Commits

  1. doc: Adjust documentation for vacuumdb --missing-stats-only.

  2. Update guidance for running vacuumdb after pg_upgrade.

  3. vacuumdb: Add option for analyzing only relations missing stats.

  4. vacuumdb: Teach vacuum_one_database() to reuse query results.