Feedback-ID: id4a34324:Fastmail
Date: Thu, 28 Aug 2025 15:52:12 -0400
From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas@vondra.me>
Cc: Peter Geoghegan <pg@bowt.ie>, Thomas Munro <thomas.munro@gmail.com>,
	Nazir Bilal Yavuz <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Georgios <gkokolatos@protonmail.com>,
 Konstantin Knizhnik <knizhnik@garret.ru>,
	Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: index prefetching
Message-ID: <5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
References: 
 <CAH2-WzkWNtCRTcUajGYrCkp9-+btteAthg21BzxbKV09AJuSrA@mail.gmail.com>
 <CAH2-WzkgkvbN_GqR+pfE7uKwhWxQ6h4jst7Rpjgrt68Vc1=FDA@mail.gmail.com>
 <5v2wuxg65l5e3s6uf373zskcqqoukmraxiucnvgn4t7b5cmeqx@5mhqsurdj6xn>
 <6butbqln6ewi5kuxz3kfv2mwomnlgtate4mb4lpa7gb2l63j4t@stlwbi2dvvev>
 <ce485718-f49e-4cbc-99de-5b935fe98926@vondra.me>
 <y32ow7vfdrrcchxrwgfr5tbwg3ncm4f3rlp65svcbqorbm3tmn@cwvipbplnqyt>
 <0dd33755-cab8-49c8-b1ed-698732577fbb@vondra.me>
 <1c9302da-c834-4773-a527-1c1a7029c5a3@vondra.me>
 <onbn3rx35x6k7mfnsmejnebt4nahnii3qnjrac2jzdh3puwo6t@dzjzsx5ppaj7>
 <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
Archived-At: 
 <https://www.postgresql.org/message-id/5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield%40ahnstpgof46i>
Precedence: bulk

Hi,

On 2025-08-28 19:08:40 +0200, Tomas Vondra wrote:
> On 8/28/25 18:16, Andres Freund wrote:
> >> So I think the IPC overhead with "worker" can be quite significant,
> >> especially for cases with distance=1. I don't think it's a major issue
> >> for PG18, because seq/bitmap scans are unlikely to collapse the distance
> >> like this. And with larger distances the cost amortizes. It's much
> >> bigger issue for the index prefetching, it seems.
> > 
> > I couldn't keep up with all the discussion, but is there actually valid I/O
> > bound cases (i.e. not ones were we erroneously keep the distance short) where
> > index scans end can't have a higher distance?
> > 
> 
> I don't know, really.
> 
> Is the presented exaple really a case of an "erroneously short
> distance"?

I think the query isn't actually measuring something particularly useful in
the general case. You're benchmarking something were the results are never
looked at - which means the time between two index fetches is unrealistically
short. That means any tiny latency increase matters a lot more than with
realistic queries.

And this is, IIUC, on a local SSD. I'd bet that on cloud latencies AIO would
still be a huge win.


> From the 2x regression (compared to master) it might seem like that, but
> even with the increased distance it's still slower than master (by 25%). So
> maybe the "error" is to use AIO in these cases, instead of just switching to
> I/O done by the backend.

If it's slower at a higher distance, we're missing something.


> It may be a bit worse for non-btree indexes, e.g. for for ordered scans
> on gist indexes (getting the next tuple may require reading many leaf
> pages, so maybe we can't look too far ahead?). Or for indexes with
> naturally "fat" tuples, which limits how many tuples we see ahead.

I am not worried at all about those cases. If you have to read a lot of index
leaf pages to get a heap fetch, a distance of even just 2 will be fine,
because the IPC overhead is a neglegible cost compared to the index
processing. Similarly, if you have to do very deep index traversals due to
wide index tuples, there's going to be more time between two table fetches.


> > Obviously you can construct cases with a low distance by having indexes point
> > to a lot of tiny tuples pointing to perfectly correlated pages, but in that
> > case IO can't be a significant factor.
> > 
> 
> It's definitely true the examples the script finds are "adversary", but
> also not entirely unrealistic.

I think doing index scans where the results are just thrown out are entirely
unrealistic...


> I suppose there will be such cases for any heuristics we come up with.

Agreed.


> There's probably more cases like this, where we end up with many hits.
> Say, a merge join may visit index tuples repeatedly, and so on. But then
> it's likely in shared buffers, so there won't be any IPC.

Yea, I'd not expect a meaningful impact of any of this in a workload like
that.

Greetings,

Andres Freund