Horton

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

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Heikki Linnakangas <hlinnaka@iki.fi>, pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: 2025-05-09T18:07:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. nbtree: Always set skipScan flag on rescan.

  2. meson: Build numeric.c with -ftree-vectorize.

  3. Fix "variable not found in subplan target lists" in semijoin de-duplication.

  4. Revert "nbtree: Remove useless row compare arg."

  5. nbtree: Remove useless row compare arg.

  6. Prevent premature nbtree array advancement.

  7. nbtree: tighten up array recheck rules.

  8. Avoid treating nonrequired nbtree keys as required.

  9. Adjust overstrong nbtree skip array assertion.

  10. Make NULL tuple values always advance skip arrays.

  11. Avoid extra index searches through preprocessing.

  12. Improve nbtree skip scan primitive scan scheduling.

  13. Further optimize nbtree search scan key comparisons.

  14. Add nbtree skip scan optimization.

  15. Improve nbtree array primitive scan scheduling.

  16. nbtree: Make BTMaxItemSize into object-like macro.

  17. Show index search count in EXPLAIN ANALYZE, take 2.

  18. Make parallel nbtree index scans use an LWLock.

  19. Show index search count in EXPLAIN ANALYZE.

  20. Avoid nbtree parallel scan currPos confusion.

  21. nbtree: Remove useless 'strat' local variable.

  22. Normalize nbtree truncated high key array behavior.

  23. Refactor handling of nbtree array redundancies.

  24. Fix nbtree pgstats accounting with parallel scans.

  25. Avoid parallel nbtree index scan hangs with SAOPs.

  26. Show Parallel Bitmap Heap Scan worker stats in EXPLAIN ANALYZE

  27. Enhance nbtree ScalarArrayOp execution.

  28. Skip checking of scan keys required for directional scan in B-tree

  29. Instead of using a numberOfRequiredKeys count to distinguish required

Attachments


On 5/9/25 19:30, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 8:58 AM Tomas Vondra <tomas@vondra.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.
> 


Yes, I'm sure it's doing index only scan - did you update "bid" or did
you leave it as generated by "pgbench -i"?. Because then there's only
one value "1", and it'd make sense to use seqscan. The exact steps I did
for the "bid = 1" case are:

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

and then I get the proper index-only scans, with pretty much the same
behavior as for bid=0.

Also, I did some profiling and the (attached) flamegraphs confirm this.
The "slow" is on master, "fast" is on 3ba2cdaa454. Both very clearly
show IndexOnlyScan callbacks, etc. And the "slow" flamegraph also shows
a lot of time spent in malloc(), unlike the fast one.

(AFAIK the profiles for bid=0 and bid=1 look exactly the same.)

In fact, all of the malloc() calls seem to happen in AllocSetAllocLarge,
which matches the guess that something tripped over allocChunkLimit. Not
sure what, though.


regards

-- 
Tomas Vondra