Re: pgsql: Improve nbtree skip scan primitive scan scheduling.

Peter, Matthias, thanks kindly for the good work on skipscans!

While admiring the recent performance improvements, I found a test case
which fails after commit 21a152b37f36c9563d1b0b058fe1436baf578b4c. Please
find a reproducible test case, attached.

Thanks!

On Fri, Apr 4, 2025 at 10:58 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> Improve nbtree skip scan primitive scan scheduling.
>
> Don't allow nbtree scans with skip arrays to end any primitive scan on
> its first leaf page without giving some consideration to how many times
> the scan's arrays advanced while changing at least one skip array
> (though continue not caring about the number of array advancements that
> only affected SAOP arrays, even during skip scans with SAOP arrays).
> Now when a scan performs more than 3 such array advancements in the
> course of reading a single leaf page, it is taken as a signal that the
> next page is unlikely to be skippable. We'll therefore continue the
> ongoing primitive index scan, at least until we can perform a recheck
> against the next page's finaltup.
>
> Testing has shown that this new heuristic occasionally makes all the
> difference with skip scans that were expected to rely on the "passed
> first page" heuristic added by commit 9a2e2a28. Without it, there is a
> remaining risk that certain kinds of skip scans will never quite manage
> to clear the initial hurdle of performing a primitive scan that lasts
> beyond its first leaf page (or that such a skip scan will only clear
> that initial hurdle when it has already wasted noticeably-many cycles
> due to inefficient primitive scan scheduling).
>
> Follow-up to commits 92fe23d9 and 9a2e2a28.
>
> Author: Peter Geoghegan <pg(at)bowt(dot)ie>
> Reviewed-By: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
> Discussion:
> https://postgr.es/m/CAH2-Wz=RVdG3zWytFWBsyW7fWH7zveFvTHed5JKEsuTT0RCO_A@mail.gmail.com
>
> Branch
> ------
> master
>
> Details
> -------
>
> https://git.postgresql.org/pg/commitdiff/21a152b37f36c9563d1b0b058fe1436baf578b4c
>
> Modified Files
> --------------
> src/backend/access/nbtree/nbtsearch.c | 16 +++++++
> src/backend/access/nbtree/nbtutils.c | 90
> +++++++++++++++++++++++------------
> src/include/access/nbtree.h | 3 +-
> 3 files changed, 78 insertions(+), 31 deletions(-)
>
>

--
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company