Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-16T19:28:06Z
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. aio: io_uring: Trigger async processing for large IOs

  2. read stream: Split decision about look ahead for AIO and combining

  3. read_stream: Only increase read-ahead distance when waiting for IO

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

  6. Fix rare bug in read_stream.c's split IO handling.

  7. Fix multiranges to behave more like dependent types.

  8. Add EXPLAIN (MEMORY) to report planner memory consumption

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

  13. Reduce pinning and buffer content locking for btree scans.

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

On Wed, Jul 16, 2025 at 3:00 PM Tomas Vondra <tomas@vondra.me> wrote:
> Yes, sounds like a fair summary.

Cool.

> Perhaps, although I don't quite see why the simpler patch couldn't
> address some of those problems (within the limit of a single leaf page,
> of course). I don't think there's anything that's prevent collecting the
> "details" somewhere (e.g. in the IndexScanDesc), and querying it from
> the callbacks. Or something like that.

That is technically possible. But ISTM that that's just an inferior
version of the "complex" patch, that duplicates lots of things across
index AMs.

> I understand you may see the "one leaf page" as a limitation of various
> optimizations, and that's perfectly correct, ofc. I also saw it as a
> crude limitation of how "bad" the things can go.

I'm not opposed to some fairly crude mechanism that stops the
prefetching from ever being too aggressive based on index
characteristics. But the idea of exclusively relying on leaf page
boundaries to do that for us doesn't even seem like a good stopgap
solution. On average, the cost of accessing leaf pages is relatively
insignificant. But occasionally, very occasionally, it's the dominant
cost. I don't think that you can get away with making a static
assumption about how much leaf page access costs matter -- it doesn't
average out like that. I think that you need at least a simple dynamic
approach, that mostly doesn't care too much about how many leaf pages
we've read, but occasionally makes heap prefetching much less
aggressive in response to the number of leaf pages the scan needs to
read being much higher than is typical.

I get the impression that you're still of the opinion that the
"simple" approach might well have the best chance of success. If
that's still how you view things, then I genuinely don't understand
why you still see things that way. That perspective definitely made
sense to me 6 months ago, but no longer.

Do you imagine that (say) Thomas will be able to add pause-and-resume
to the read stream interface some time soon, at which point the
regressions we see with the "simple" patch (but not the "complex"
patch) go away?

-- 
Peter Geoghegan