Thread

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

BharatDB <bharatdbpg@gmail.com> — 2025-09-10T06:55:45Z

Dear Team,

With reference to the conversation ongoing in message ID :
c562dc2a-6e36-46f3-a5ea-cd42eebd7118,
As a follow-up to the skip scan regression discussion, I tested a small
patch that introduces *static allocation/caching of `IndexAmRoutine` *objects
in `amapi.c`, removing the malloc/free overhead.

*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 residual slowdown (*~2-15%*).

*Patch summary :*
- Cache `IndexAmRoutine` instances per AM OID instead of malloc/free per
call.
- Avoid `pfree(amroutine)` in hot paths.
- Keeps allocations stable across lookups, reducing malloc churn.

*Proposal :*
I suggest adopting this static allocation approach for PG18 to prevent
performance cliffs. Longer term, we can explore lighter-weight caching
mechanisms or further executor tuning.

*Patch attached for discussion.*

Thanks & Regards,
Athiyaman M