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

On 5/9/25 18:36, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 12:28 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>> Not sure if it matters, but this uses index-only scans, and the pages
>> are all-visible, so maybe it's not much more expensive.
>
> You're still going to have to scan 85 full index pages on your
> pgbench_accounts.bid index -- that's pretty expensive, even with an
> index-only scan.
>

Not sure I understand. Why would it need to scan 85 index pages? There's
only 100 matching tuples total, spread over the 100 partitions. We'll
need to scan maybe 1 page per partition.

> Even if there was some kind of really obvious regression in
> preprocessing (which seems almost impossible), I'd still expect it to
> be drowned out for queries such as these.
>
>> Not sure, I did multiple tests with different queries, and it'd be a bit
>> strange if this was the only one affected. Not impossible.
>
> The only thing that substantially differs between this
> pgbench_accounts.bid query and traditional pgbench SELECT queries (on
> pgbench_accounts.aid) is 1.) this query is quite a bit more expensive
> at execution time, and 2.) that it involves the use of partitioning.
>
> I made sure to test pgbench SELECT quite thoroughly -- that certainly
> wasn't regressed. So it seems very likely to have something to do with
> partitioning.
>

Yeah. This type of query amplifies any overhead in the index scan,
because it does one for every partition. I haven't seen the regression
without the partitioning.

regards

--
Tomas Vondra