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

On Fri, May 9, 2025 at 8:58 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> select count(*) from pgbench_accounts where bid = 0

What kind of plan are you getting? Are you sure it's index-only scans?

With 100 partitions, I get a parallel sequential scan when I run
EXPLAIN ANALYZE with this query from psql -- though only with "bid =
1". With your original "bid = 0" query I do get index-only scans.

What ends up happening (when index-only scans are used) is that we
scan only one index leaf page per partition index scanned. The
individual index-only scans don't need to scan too much (even when the
"bid = 1" variant query is forced to use index-only similar scans), so
I guess it's plausible that something like a regression in
preprocessing could be to blame, after all. As I mentioned just now,
these indexes each have only one index leaf page (the thing about 85
leaf pages only applies when partitioning isn't in use).

I find that the execution time for index-only scans with "bid = 0"
with a warm cache are:

Planning Time: 0.720 ms
Serialization: time=0.001 ms output=1kB format=text
Execution Time: 0.311 ms

Whereas the execution times for index-only scans with "bid = 1" are:

Planning Time: 0.713 ms
Serialization: time=0.001 ms output=1kB format=text
Execution Time: 16.491 ms

So you can see why I'd find it so hard to believe that any underlying
regression wouldn't at least be well hidden (by all of the other
overhead) in the case of the "bid = 1" variant query. There's no
reason to expect the absolute number of cycles added by some
hypothetical regression in preprocessing to vary among these two
variants of your count(*) query.

--
Peter Geoghegan