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

On 5/9/25 16:22, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:59 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> I don't actually think that this kind of scan would have been affected
>> by those known regressions -- since they don't use array keys. But it
>> is definitely true that the queries that you're looking at very much
>> rely on the optimization from commit 8a510275 (or its predecessor
>> optimization, the "pstate.prechecked" optimization). As I said, my
>> performance validation didn't target individual commits.
>
> Wait, that's not it, either. Since the index scan that you use won't
> find any matching tuples at all. It should land on the leftmost leaf
> page, find that there are no tuples "WHERE bid = 0", ending the scan
> before it ever really began.
>

I see the regression even with variants that actually match some rows.
For example if I do this:

update pgbench_accounts set bid = aid;
vacuum full;

and change the query to search for "bid = 1", I get exactly the same
behavior. Even with

update pgbench_accounts set bid = aid / 100;
vacuum full;

so that the query matches 100 rows, I get the same behavior.

--
Tomas Vondra