On Sun, 11 Aug 2024 at 21:44, Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Aug 6, 2024 at 6:31 PM Matthias van de Meent > <boekewurm+postg...@gmail.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`
v1-0001-Avoid-unneeded-nbtree-backwards-scan-buffer-locks.patch
Description: Binary data
