MIME-Version: 1.0
References: <CAEze2Wj9SgwOpe_1CWnS_D-txQaQyXArR=dm4DTnha93=yua4g@mail.gmail.com>
 <CANtu0ohFr7OzNSbxqBhUpR0mXDYyt0Xt6+=Tbq0EC7as7kr+Lg@mail.gmail.com>
 <CANtu0oh4PwBn_h+4p_MxFigRAyJvF-0nA9Tm5NFRwfsWWjZQiA@mail.gmail.com>
 <CANtu0ojHEVU9U_bxgViRmtqNTJ92LnF+76-yzn4axYjGsK2kqQ@mail.gmail.com>
 <CANtu0ogS871NkdUnZW9P_LVpLzhSJ1+cETK0b55cYjs=v2qbPA@mail.gmail.com>
 <CANtu0ohRVBDf4x7Ge3oVzgf4NzMb_DhmTM1ae0u1WUA+CD0UqA@mail.gmail.com>
 <CANtu0ogTfyng-H4yWr3Pm_+PXX+XvDx1AM1sXTy1V7DM6jJ+Bw@mail.gmail.com>
 <CANtu0oi+nbipJUsMZcoUfodCyuTN_DAXD22UstjMTYWG=tJ4jw@mail.gmail.com>
 <CANtu0oiuUF7L0wTGxOHfumyoVge3n7C4rAjdmFo=efeEwobXbg@mail.gmail.com>
 <CANtu0oiD-AvXdygYqYP-WkFq=7vSL78Wj8UU-PUX+3huPNqroQ@mail.gmail.com>
 <Z2uV2TIpYisYPipp@paquier.xyz> <CANtu0og-4pvn4+TCWH6U9ghyd7x7NBAZSgi4ZWyBZdBWH6OpWA@mail.gmail.com>
 <CANtu0oj6EXRq2US8gkz7ehELeECDsVAXq4R0iRfOaRW9GK=5Dw@mail.gmail.com>
In-Reply-To: <CANtu0oj6EXRq2US8gkz7ehELeECDsVAXq4R0iRfOaRW9GK=5Dw@mail.gmail.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Sat, 4 Jan 2025 02:12:58 +0100
Message-ID: <CAEze2Wj1sY+A6zKJtWhdXSmA9wjmP8jiazHgyH1O37NnSjH5SQ@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Michail Nikolaev <michail.nikolaev@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 
	Andrey Borodin <amborodin86@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAEze2Wj1sY%2BA6zKJtWhdXSmA9wjmP8jiazHgyH1O37NnSjH5SQ%40mail.gmail.com>
Precedence: bulk

On Wed, 1 Jan 2025 at 17:17, Michail Nikolaev
<michail.nikolaev@gmail.com> wrote:
>
> Hello, everyone!
>
> I=E2=80=99ve added several updates to the patch set:
>
> * Automatic auxiliary index removal where applicable.
> * Documentation updates to reflect recent changes.
> * Optimization for STIR indexes: skipping datum setup, as they store only=
 TIDs.
> * Numerous assertions to ensure that MyProc->xmin is invalid where necess=
ary.
>
> I=E2=80=99d like to share some initial benchmark results (see attached gr=
aphs).
> This involves building a B-tree index on (aid, abalance) in a pgbench set=
up with scale 2000 (with WAL), while running a concurrent pgbench workload.
>
> The patched version built the index in 68 seconds, compared to 117 second=
s with the master branch (mostly because of a single heap scan).
> There appears to be no effect on the throughput of the concurrent pgbench=
.
> The maximum snapshot age remains near zero.

Thank you for continuing working on this, these are some nice results.
I'm sorry I can't spend the time I want on this every time, but I
still think it's important this can eventually get committed, so thank
you for your work.

> (mostly because of a single heap scan).

Isn't there a second heap scan, or do you consider that an index scan?

> I am going to continue to benchmark with different options: different HOT=
 setup, unique index, different index types and DB size (100+ GB).
> If someone has some ideas about possible benchmark scenarios - please sha=
re.

I think a good benchmark could show how bloat is actually prevented,
i.e. through result table size comparisons on an update-heavy
workload, both with and without the patch.
I think it shouldn't be too difficult to show how such workloads
quickly regress to always extending the table as no cleanup can
happen, while patched they'd have much more leeway due to page
pruning. Presumably a table with a fillfactor <100 would show the best
results.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)