Re: index prefetching

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: Rui Zhao <zhaorui126(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(at)vondra(dot)me>, Andres Freund <andres(at)anarazel(dot)de>, Alexandre Felipe <o(dot)alexandre(dot)felipe(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Melanie Plageman <melanieplageman(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Georgios <gkokolatos(at)protonmail(dot)com>, Konstantin Knizhnik <knizhnik(at)garret(dot)ru>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Subject: Re: index prefetching
Date: 2026-10-18 12:51:59
Message-ID: CAH2-Wzn2Gh1aQq+BXzoPnyQY8AvxgoSG3f=XUQbPOS94UcLgmg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, Oct 10, 2026 at 8:11 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> Attached is v37.

Attached is v38. This is mostly just to keep the patch series
applying, but there is one new patch that deals with regressions in
io_method=sync.

Testing by Tomas found that prefetching under io_method=sync has widly
varying performance. There are significant wins and significant
losses. I concluded that the best solution was to simply avoid having
the read stream issue posix_fadvise(WILLNEED) hints under
io_method=sync. After all, sync is supposed to be compatible with
historical pre-aio behavior.

We already have a way to do exactly that: READ_STREAM_SEQUENTIAL. But
it would be weird to pass READ_STREAM_SEQUENTIAL when creating a read
stream for index prefetching. The new patch deals with that: it simply
renames READ_STREAM_SEQUENTIAL to READ_STREAM_NO_ADVICE, without
changing anything about its behavior. The only thing that
READ_STREAM_SEQUENTIAL/READ_STREAM_NO_ADVICE ever did was suppress
posix_fadvise under io_method=sync. This is exactly what we want for
index prefetching.

If at some point somebody wants to invent a new read stream behavior
that is truly only useful with a sequential access pattern, they can
invent a new flag for that. Something with a name like
READ_STREAM_SEQUENTIAL. But I see no need for that now.

--
Peter Geoghegan

Attachment Content-Type Size
v38-0009-Allow-read_stream_reset-to-not-wait-for-IO-compl.patch application/octet-stream 20.6 KB
v38-0011-aio-Fix-pgaio_io_wait-for-staged-IOs-B.patch application/octet-stream 6.3 KB
v38-0012-WIP-aio-bufmgr-Fix-race-condition-leading-to-dea.patch application/octet-stream 3.1 KB
v38-0001-Add-amgetbatch-interface-and-adopt-it-in-nbtree.patch application/octet-stream 279.3 KB
v38-0010-WIP-Add-read-stream-request-deduplication.patch application/octet-stream 26.2 KB
v38-0006-Rename-READ_STREAM_SEQUENTIAL-to-READ_STREAM_NO_.patch application/octet-stream 4.6 KB
v38-0008-Add-EXPLAIN-IO-support-for-plain-index-scans.patch application/octet-stream 13.8 KB
v38-0007-heapam-Add-index-scan-I-O-prefetching.patch application/octet-stream 55.1 KB
v38-0005-WIP-Adopt-amgetbatch-interface-in-SP-GiST-index-.patch application/octet-stream 72.8 KB
v38-0004-WIP-Adopt-amgetbatch-interface-in-GiST-index-AM.patch application/octet-stream 113.9 KB
v38-0003-Adopt-amgetbatch-interface-in-hash-index-AM.patch application/octet-stream 77.4 KB
v38-0002-heapam-Optimize-pin-transfers-during-index-scans.patch application/octet-stream 6.2 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Xuneng Zhou 2026-10-18 13:07:33 Re: Improve read_local_xlog_page_guts by replacing polling with latch-based waiting
Previous Message David Geier 2026-10-18 11:42:44 Re: Reducing relcache memory usage 2: shrink sizeof(RelationData)