Re: Adding skip scan (including MDAM style range skip scan) to nbtree - Mailing list pgsql-hackers

On 5/9/25 16:22, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:59 AM Peter Geoghegan <pg@bowt.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