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

Hi,

While doing some benchmarks to compare 17 vs. 18, I ran into a
regression that I ultimately tracked to commit 92fe23d93aa.

commit 92fe23d93aa3bbbc40fca669cabc4a4d7975e327
Author: Peter Geoghegan <pg(at)bowt(dot)ie>
Date: Fri Apr 4 12:27:04 2025 -0400

Add nbtree skip scan optimization.

The workload is very simple - pgbench scale 1 with 100 partitions, an
extra index and a custom select script (same as the other regression I
just reported, but with low client counts):

pg_ctl -D data init
pg_ctl -D data -l pg.log start

createdb test

psql test -c 'create index on pgbench_accounts(bid)'

and a custom script with a single query:

select count(*) from pgbench_accounts where bid = 0

and then simply run this for a couple client counts:

for m in simple prepared; do
for c in 1 4 32; do
pgbench -n -f select.sql -M $m -T 10 -c $c -j $c test | grep tps;
done;
done;

And the results for 92fe23d93aa and 3ba2cdaa454 (the commit prior to the
skip scan one) look like this:

mode #c 3ba2cdaa454 92fe23d93aa diff
-------------------------------------------------------
simple 1 2617 1832 70%
4 8332 6260 75%
32 11603 7110 61%
------------------------------------------------------
prepared 1 11113 3646 33%
4 25379 11375 45%
32 37319 14097 38%

The number are throughput, as reported by pgbench, and for this
workload, we're often losing ~50% of throughput with 92fe23d93aa.

Despite that, I'm not entirely sure how serious this is. This was meant
to be a micro-benchmark stressing the locking, but maybe it's considered
unrealistic in practice. Not sure.

I'm also not sure about the root cause, but while investigating it one
of the experiments I tried was tweaking the glibc malloc by setting

export MALLOC_TOP_PAD_=$((64*1024*1024))

which keeps a 64MB "buffer" in glibc, to reduce the amount of malloc
syscalls. And with that, the results change to this:

mode #c 3ba2cdaa454 92fe23d93aa diff
-------------------------------------------------------
simple 1 3168 3153 100%
4 9172 9171 100%
32 12425 13248 107%
------------------------------------------------------
prepared 1 11104 11460 103%
4 25481 25737 101%
32 36795 38372 104%

So the difference disappears - what remains is essentially run to run
variability. The throughout actually improves a little bit for 3ba2cd.

My conclusion from this is that 92fe23d93aa ends up doing a lot of
malloc calls, and this is what makes causes the regression. Otherwise
setting the MALLOC_TOP_PAD_ would not help like this. But I haven't
looked at the code, and I wouldn't have guessed the query to have
anything to do with skip scan ...

regards

--
Tomas Vondra