On Mon, May 20, 2024 at 8:47 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> On 5/20/24 2:58 AM, John Naylor wrote:
> > Hi Jon,
> >
> > Regarding vacuum "has shown up to a 6x improvement in overall time to
> > complete its work" -- I believe I've seen reported numbers close to
> > that only 1) when measuring the index phase in isolation or maybe 2)
> > the entire vacuum of unlogged tables with one, perfectly-correlated
> > index (testing has less variance with WAL out of the picture). I
> > believe tables with many indexes would show a lot of improvement, but
> > I'm not aware of testing that case specifically. Can you clarify where
> > 6x came from?
>
> Sawada-san showed me the original context, but I can't rapidly find it
> in the thread. Sawada-san, can you please share the numbers behind this?
>
I referenced the numbers that I measured during the development[1]
(test scripts are here[2]). IIRC I used unlogged tables and indexes,
and these numbers were the entire vacuum execution time including heap
scanning, index vacuuming and heap vacuuming.
FYI today I've run the same script with PG17 and measured the
execution times. Here are results:
monotonically ordered int column index:
system usage: CPU: user: 1.72 s, system: 0.47 s, elapsed: 2.20 s
uuid column index:
system usage: CPU: user: 3.62 s, system: 0.89 s, elapsed: 4.52 s
int & uuid indexes in parallel:
system usage: CPU: user: 2.24 s, system: 0.44 s, elapsed: 5.01 s
These numbers are better than ones I measured with v62 patch set as we
now introduced some optimization into tidstore (8a1b31e6 and f35bd9b).
Regards,
[1] https://www.postgresql.org/message-id/CAD21AoBci3Hujzijubomo1tdwH3XtQ9F89cTNQ4bsQijOmqnEw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CANWCAZYqWibTRCWs5mV57mLj1A0nbKX-eV5G%2Bd-KmBOGHTVY-w%40mail.gmail.com
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com