Re: index prefetching

Robert Haas <robertmhaas@gmail.com>

From: Robert Haas <robertmhaas@gmail.com>

To: Tomas Vondra <tomas.vondra@enterprisedb.com>

Cc: Konstantin Knizhnik <knizhnik@garret.ru>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2024-01-16T17:08:14Z

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 →

aio: io_uring: Trigger async processing for large IOs
- a9ee66881744 19 (unreleased) landed
read stream: Split decision about look ahead for AIO and combining
- 8ca147d582a5 19 (unreleased) landed
read_stream: Only increase read-ahead distance when waiting for IO
- f63ca3379025 19 (unreleased) landed
read_stream: Prevent distance from decaying too quickly
- 6e36930f9aaf 19 (unreleased) landed
Reduce ExecSeqScan* code size using pg_assume()
- b227b0bb4e03 19 (unreleased) cited
Fix rare bug in read_stream.c's split IO handling.
- b421223172a2 19 (unreleased) cited
Fix multiranges to behave more like dependent types.
- 3e8235ba4f9c 17.0 cited
Add EXPLAIN (MEMORY) to report planner memory consumption
- 5de890e3610d 17.0 cited
Optimize nbtree backward scan boundary cases.
- c9c0589fda0e 17.0 cited
Increment xactCompletionCount during subtransaction abort.
- 90c885cdab8b 14.0 cited
Add nbtree Valgrind buffer lock checks.
- 4a70f829d86c 14.0 cited
Add nbtree high key "continuescan" optimization.
- 29b64d1de7c7 12.0 cited
Reduce pinning and buffer content locking for btree scans.
- 2ed5b87f96d4 9.5.0 cited
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited

On Tue, Jan 16, 2024 at 11:25 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
> > 3. It doesn't perform prefetch of leave pages for IOS, only referenced
> > heap pages which are not marked as all-visible. It seems to me that if
> > optimized has chosen IOS (and not bitmap heap scan for example), then
> > there should be large enough fraction for all-visible pages. Also index
> > prefetch is most efficient for OLAp queries and them are used to be
> > performance for historical data which is all-visible. But IOS can be
> > really handled separately in some other PR. Frankly speaking combining
> > prefetch of leave B-Tree pages and referenced heap pages seems to be
> > very challenged task.
>
> I see prefetching of leaf pages as interesting / worthwhile improvement,
> but out of scope for this patch. I don't think it can be done at the
> executor level - the prefetch requests need to be submitted from the
> index AM code (by calling PrefetchBuffer, etc.)

+1. This is a good feature, and so is that, but they're not the same
feature, despite the naming problems.

-- 
Robert Haas
EDB: http://www.enterprisedb.com