Re: Avoiding superfluous buffer locking during nbtree backwards scans

From: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Avoiding superfluous buffer locking during nbtree backwards scans
Date: 2024-08-19 11:43:35
Message-ID: CAEze2WgpBGRgTTxTWVPXc9+PB6fc1a7t+VyGXHzfnrFXcQVxnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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`

Attachment Content-Type Size
v1-0001-Avoid-unneeded-nbtree-backwards-scan-buffer-locks.patch application/octet-stream 18.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema-Nio 2024-08-19 11:57:07 Re: gitmaster server problem?
Previous Message Joe Conway 2024-08-19 11:39:27 Re: gitmaster server problem?