Re: Adding skip scan (including MDAM style range skip scan) to nbtree

On Wed, Sep 10, 2025 at 2:49 AM BharatDB <bharatdbpg@gmail.com> wrote:
> As a follow-up to the skip scan regression discussion, I tested a small patch that introduces static
allocation/cachingof `IndexAmRoutine` objects in `amapi.c`, removing the malloc/free overhead. 

I think that it's too late to be considering anything this invasive for 18.

> Test setup :
> - Baseline: PG17 (commit before skip scan)
> - After: PG18 build with skip scan (patched)
> - pgbench scale=1, 100 partitions
> - Query: `select count(*) from pgbench_accounts where bid = 0`
> - Clients: 1, 4, 32
> - Protocols: simple, prepared
>
> Results (tps, 10s runs) :
>
> Mode Clients Before (PG17) After (PG18 w/ static fix)
>
> simple 1 23856 20332 (~15% lower)
> simple 4 55299 53184 (~4% lower)
> simple 32 79779 78347 (~2% lower)
>
> prepared 1 26364 26615 (no regression)
> prepared 4 55784 54437 (~2% lower)
> prepared 32 84687 80374 (~5% lower)
>
> This shows the static fix eliminates the severe ~50% regression previously observed by Tomas, leaving only a small
residualslowdown (~2-15%). 

The regression that Tomas reported is extreme and artificial. IIRC it
only affects partition queries with a hundred or so partitions, each
with an index-only scan that always scans exactly 0 index tuples, from
a pgbench_accounts that has the smallest possible amount of rows that
pgbench will allow (these are the cheapest possible index-only scans).
Plain index scans are not affected at all, presumably because it just
so happens that we don't allocate a BLCKSZ*2 workspace for plain index
scans, which is enough to put us well under the critical glibc
allocation size threshold (the threshold that the introduction of a
new nbtree support function put us over).

I also couldn't see anything like the 50% regression that Tomas
reported. And I couldn't recreate any problem unless partitioning was
used.

--
Peter Geoghegan