From: Nathan Bossart <nathandbossart@gmail.com>
To: John Naylor <johncnaylorls@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Corey Huinker <corey.huinker@gmail.com>
Cc: jian he <jian.universality@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: alvherre@alvh.no-ip.org
Subject: Re: vacuumdb changes for stats import/export
Date: Fri, 14 Mar 2025 14:05:30 -0500
Message-ID: <Z9R9-mFbxukqKmg4@nathan> (raw)
In-Reply-To: <Z9HhrtaZyFgMvJsM@nathan>
References: <Z8XXEYfbSatGrrty@nathan>
<CANWCAZYrk4ky6+AFp7Yv8yRyMrYHiMs6EnF8rmz3uey1rtkONA@mail.gmail.com>
<Z8c0s_eC7X2yWwC5@nathan>
<CANWCAZacHEinN0tYUwHahzeJguDoannP0Nksv9LnLPpFD2kb+g@mail.gmail.com>
<Z8oX2u2Db8x2Y1za@nathan>
<CANWCAZa3tOfy2t7PraGh7td0nuO8rLesE8Cp2D1QZ+vM0hRcxg@mail.gmail.com>
<Z88AgUXOR3e3ZsDV@nathan>
<Z9BsEfqEmwkUetgz@nathan>
<CANWCAZbiLHGeAZPYg6G6eT6MmCXM7CuYBEj3WhWw76e_HQejDA@mail.gmail.com>
<Z9HhrtaZyFgMvJsM@nathan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, johncnaylorls@gmail.com, sawada.mshk@gmail.com, bruce@momjian.us, pgsql@j-davis.com, corey.huinker@gmail.com, jian.universality@gmail.com, boekewurm+postgres@gmail.com, tgl@sss.pgh.pa.us, magnus@hagander.net, sfrost@snowman.net, ashutosh.bapat.oss@gmail.com, smithpb2250@gmail.com, pgsql-hackers@lists.postgresql.org, alvherre@alvh.no-ip.org
Subject: Re: vacuumdb changes for stats import/export
In-Reply-To: <Z9R9-mFbxukqKmg4@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox