On 5/19/25 22:29, Peter Geoghegan wrote:
> On Mon, May 19, 2025 at 4:17 PM Tomas Vondra <tomas@vondra.me> wrote:
>> Same effect as v1 for IOS, with regular index scans I see this:
>>
>> 64 clients: 0.7M tps
>> 96 clients: 1.5M tps
>>
>> So very similar improvement as for IOS.
>
> Just to be clear, you're saying my v2 appears to fix the problem
> fully, for both plain index scans and index-only scans? (You haven't
> mentioned bitmap index scans at all, I think, even though they are
> relevant to v2.)
With v2 the regression disappears, both for index-only scans and regular
index scans. I haven't tried anything with bitmap scans - I hit the
regression mostly by accident, on a workload that does IOS only. I may
try constructing something with bitmap scans, but I didn't have time for
that right now.
>
> I'd be surprised if my v2 *fully* fixed the issue with plain index
> scans. It's only going to avoid calls to BufferGetLSNAtomic()
> following _bt_readpage calls that return false, but I doubt that your
> particular pgbench-variant test queries really look like that.
>
Not sure. My workload is pretty simple, and similar to what I used in
the other nbtree regression (with malloc overhead), except that it's not
using partitioning.
pgbench -i -s 1 test
psql test <<EOF
update pgbench_accounts set bid = aid;
create index on pgbench_accounts(bid);
vacuum full;
analyze;
EOF
# select.sql
set enable_indexonlyscan = off;
select count(*) from pgbench_accounts where bid = 1
I don't know what "fully fix" means in this context. I see a massive
improvement with v2, I have no idea if that's the best we could do.
> *Looks again* Oh, wait. This is another one of those benchmarks with
> an index scan that returns no rows whatsoever (just like on the other
> recent thread involving regressions tied to the introduction of a new
> skip support support function to nbtree). Fully makes sense that my v2
> would fix that particular problem, even with plain index scans.
> But...what about slightly different queries, that actually do return
> some rows? Those will look different.
>
Actually, this particular query does return rows (one, to be precise).
But you're right - it seems sensitive to how many rows are returned, and
at some point the contention goes away and there's no regression.
I need to do proper automated testing, to get reliable results. I've
been doing manual testing, but it's easy to make mistakes that way.
Do you have any suggestions what cases you'd like me to test?
regards
--
Tomas Vondra
