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

On Fri, 9 May 2025 at 20:38, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Fri, May 9, 2025 at 2:04 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> > Yes, I'm sure it's doing index only scan
>
> Looks that way, from the pair of flame graphs you sent. Thanks for that.
>
> > did you update "bid" or did
> > you leave it as generated by "pgbench -i"?.
>
> I didn't bother with updating, or running VACUUM FULL. I did run
> VACUUM ANALYZE, though (can confirm no heap accesses for the
> index-only scans).
>
> > In fact, all of the malloc() calls seem to happen in AllocSetAllocLarge,
> > which matches the guess that something tripped over allocChunkLimit. Not
> > sure what, though.

> The difference shown by your flame graph is absolutely enormous --
> that's *very* surprising to me. btbeginscan and btrescan go from being
> microscopic to being very prominent. But skip scan simply didn't touch
> either function, at all, directly or indirectly. And neither function
> has really changed in any significant way in recent years. So right
> now I'm completely stumped.

I see some 60.5% of the samples under PostgresMain (35% overall) in
the "bad" flamegraph have asm_exc_page_fault on the stack, indicating
the backend(s) are hit with a torrent of continued page faults.
Notably, this is not just in btree code: ExecInitIndexOnlyScan's
components (ExecAssignExprContext,
ExecConditionalAssignProjectionInfo, ExecIndexBuildScanKeys,
ExecInitQual, etc.) are also very much affected, and none of those
call into index code. Notably, this is before any btree code is
executed in the query.

In the "good" version, asm_exc_page_fault does not show up, at all;
nor does sysmalloc.

@Tomas
Given the impact of MALLOC_TOP_PAD_, have you tested with other values
of MALLOC_TOP_PAD_?

Also, have you checked the memory usage of the benchmarked backends
before and after 92fe23d93aa, e.g. by dumping
pg_backend_memory_contexts after preparing and executing the sample
query, or through pg_get_process_memory_contexts() from another
backend?

Kind regards,

Matthias van de Meent