Re: Avoiding superfluous buffer locking during nbtree backwards scans

On Sun, 11 Aug 2024 at 21:44, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Tue, Aug 6, 2024 at 6:31 PM Matthias van de Meent
> <boekewurm+postgres(at)gmail(dot)com> wrote:
> > +1, LGTM.
> >
> > This changes the backward scan code in _bt_readpage to have an
> > approximately equivalent handling as the forward scan case for
> > end-of-scan cases, which is an improvement IMO.

Here's a new patch that further improves the situation, so that we
don't try to re-lock the buffer we just accessed when we're stepping
backward in index scans, reducing buffer lock operations in the common
case by 1/2.

It also further decreases the number of code differences between
forward and backward scans in _bt_steppage and _bt_readnextpage, with
mostly only small differences remaining in the code paths shared
between the two scan modes.

The change in lwlock.c is to silence a warning when LWLOCK_STATS is enabled.

Kind regards,

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

I've validated my results by compiling with LWLOCK_STATS enabled (e.g.
#define LWLOCK_STATS), and testing backward index scans, e.g.

CREATE TABLE test AS SELECT generate_series(1, 1000000) as num;
CREATE INDEX ON test (num);
VACUUM (FREEZE) test;
\c -- reconnect to get fresh lwlock stats
SELECT COUNT(num ORDER BY num DESC) FROM test;
\c -- reconnect to dump stats of previous session

Before this patch I consistently got `BufferContent 0xYYYYYYYY: shacq
2` in the logs, but with this patch that has been decreased to
`BufferContent 0xYYYYYYYY: shacq 1`