Feedback-ID: id4a34324:Fastmail
Date: Mon, 25 Aug 2025 19:48:17 -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: <y32ow7vfdrrcchxrwgfr5tbwg3ncm4f3rlp65svcbqorbm3tmn@cwvipbplnqyt>
References: <DC2DFSYXBDSJ.2N7WXLD4JT05H@bowt.ie>
 <6wyxbnry2unm3kbcu2sabhzhs7baoedlg77xqm42chpofjq45g@igst42zpl7ok>
 <CAH2-WzntgDeopLJpyEbUh23Qr1vgoYv5jbFkYsymTScEKxBj7A@mail.gmail.com>
 <CAH2-WzkaTHg2X9R-gLRNBEoL82t2mkrQq-3f=y3GAzrj40fFZw@mail.gmail.com>
 <kvyser45imw3xmisfvpeoshisswazlzw35el3fq5zg73zblpql@f56enfj45nf7>
 <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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <ce485718-f49e-4cbc-99de-5b935fe98926@vondra.me>
Archived-At: 
 <https://www.postgresql.org/message-id/y32ow7vfdrrcchxrwgfr5tbwg3ncm4f3rlp65svcbqorbm3tmn%40cwvipbplnqyt>
Precedence: bulk

Hi,

On 2025-08-25 15:00:39 +0200, Tomas Vondra wrote:
> Thanks. Based on the testing so far, the patch seems to be a substantial
> improvement. What's needed to make this prototype committable?

Mainly some testing infrastructure that can trigger this kind of stream. The
logic is too finnicky for me to commit it without that.


> I assume this is PG19+ improvement, right? It probably affects PG18 too,
> but it's harder to hit / the impact is not as bad as on PG19.

Yea. It does apply to 18 too, but I can't come up with realistic scenarios
where it's a real issue. I can repro a slowdown when using many parallel
seqscans with debug_io_direct=data - but that's even slower in 17...


> On a related note, my test that generates random datasets / queries, and
> compares index prefetching with different io_method values found a
> pretty massive difference between worker and io_uring. I wonder if this
> might be some issue in io_method=worker.

> while with index prefetching (with the aio prototype patch), it looks
> like this:
> 
>                                 QUERY PLAN
>   ----------------------------------------------------------------------
>    Index Scan using idx on t (actual rows=9048576.00 loops=1)
>      Index Cond: ((a >= 16150) AND (a <= 4540437))
>      Index Searches: 1
>      Prefetch Distance: 2.032
>      Prefetch Count: 868165
>      Prefetch Stalls: 2140228
>      Prefetch Skips: 6039906
>      Prefetch Resets: 0
>      Stream Ungets: 0
>      Stream Forwarded: 4
>      Prefetch Histogram: [2,4) => 855753, [4,8) => 12412
>      Buffers: shared hit=2577599 read=455610
>    Planning:
>      Buffers: shared hit=78 read=26 dirtied=1
>    Planning Time: 1.032 ms
>    Execution Time: 3150.578 ms
>   (16 rows)
> 
> So it's about 2x slower. The prefetch distance collapses, because
> there's a lot of cache hits (about 50% of requests seem to be hits of
> already visited blocks). I think that's a problem with how we adjust the
> distance, but I'll post about that separately.
> 
> Let's try to simply set io_method=io_uring:
> 
>                                 QUERY PLAN
>   ----------------------------------------------------------------------
>    Index Scan using idx on t  (actual rows=9048576.00 loops=1)
>      Index Cond: ((a >= 16150) AND (a <= 4540437))
>      Index Searches: 1
>      Prefetch Distance: 2.032
>      Prefetch Count: 868165
>      Prefetch Stalls: 2140228
>      Prefetch Skips: 6039906
>      Prefetch Resets: 0
>      Stream Ungets: 0
>      Stream Forwarded: 4
>      Prefetch Histogram: [2,4) => 855753, [4,8) => 12412
>      Buffers: shared hit=2577599 read=455610
>    Planning:
>      Buffers: shared hit=78 read=26
>    Planning Time: 2.212 ms
>    Execution Time: 1837.615 ms
>   (16 rows)
> 
> That's much closer to master (and the difference could be mostly noise).
> 
> I'm not sure what's causing this, but almost all regressions my script
> is finding look like this - always io_method=worker, with distance close
> to 2.0. Is this some inherent io_method=worker overhead?

I think what you might be observing might be the inherent IPC / latency
overhead of the worker based approach. This is particularly pronounced if the
workers are idle (and the CPU they get scheduled on is clocked down). The
latency impact of that is small, but if you never actually get to do much
readahead it can be visible.

Greetings,

Andres Freund