public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Andres Freund <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Alexandre Felipe <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Wed, 18 Feb 2026 16:39:30 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <cdpcdaaiceonmplfbl6oujf57gzzgdijknwyo2wg4d3lj2zxx3@zp7sbpvi5h5k>
References: <64a2re223ajj4popowsyu4xekbuvvyfwkrihn5yzyrkwsmsuvp@2lls3tpww5dl>
	<a67mvhyi2q45eg4eimhpwdg6l3s3dmpahti2svffvmvzwmss27@r4nohusvndbq>
	<[email protected]>
	<il7jtfowpatrlg33qb5plj7v7pferes4ogerq5fdczszi4kokh@sbwvb2ukfgos>
	<[email protected]>
	<ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
	<CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>
	<64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>
	<CAH2-Wz=GQaLKy3qaq1iYuKZOKt_2YVx13_wxcTf+xcLMEO4qyg@mail.gmail.com>
	<[email protected]>
	<cdpcdaaiceonmplfbl6oujf57gzzgdijknwyo2wg4d3lj2zxx3@zp7sbpvi5h5k>



On 2/18/26 05:21, Andres Freund wrote:
> Hi,
> 
> On 2026-02-17 22:36:53 +0100, Tomas Vondra wrote:
>> On 2/17/26 21:16, Peter Geoghegan wrote:
>>> On Tue, Feb 17, 2026 at 2:27 PM Andres Freund <[email protected]> wrote:
>>>> On 2026-02-17 12:16:23 -0500, Peter Geoghegan wrote:
>>>>> On Mon, Feb 16, 2026 at 11:48 AM Andres Freund <[email protected]> wrote:
>>>>> I agree that the current heuristics (which were invented recently) are
>>>>> too conservative. I overfit the heuristics to my current set of
>>>>> adversarial queries, as a stopgap measure.
>>>>
>>>> Are you doing any testing on higher latency storage?  I found it to be quite
>>>> valuable to use dm_delay to have a disk with reproducible (i.e. not cloud)
>>>> higher latency (i.e. not just a local SSD).
>>>
>>> I sometimes use dm_delay (with the minimum 1ms delay) when testing,
>>> but don't do so regularly. Just because it's inconvenient to do so
>>> (perhaps not a great reason).
>>>
>>>> Low latency NVMe can reduce the
>>>> penalty of not enough readahead so much that it's hard to spot problems...
>>>
>>> I'll keep that in mind.
>>>
>>
>> So, what counts as "higher latency" in this context? What delays should
>> we consider practical/relevant for testing?
> 
> 0.5-4ms is the range I've seen in various clouds across their reasonable
> storage products (i.e. not spinning disks or other ver bulk oriented things).
> 
> Unfortunately dm_delay doesn't support < 1ms delays, but it's still much
> better than nothing.
> 
> I've been wondering about teaching AIO to delay IOs (by adding a sleep to
> workers and linking a IORING_OP_TIMEOUT submission with the actually intended
> IO) to allow testing smaller delays.
> 

Could be useful testing facility, if it's done in a way that does not
limit the IO concurrency (i.e. the delay should probably be when
consuming the IO, depending on the timestamp of the IO start).

> 
>>> That would make sense. You can already tell when that's happened by
>>> comparing the details shown by EXPLAIN ANALYZE against the same query
>>> execution on master, but that approach is inconvenient. Automating my
>>> microbenchmarks has proven to be important with this project. There's
>>> quite a few competing considerations, and it's too easy to improve one
>>> query at the cost of regressing another.
>>>
>>
>> What counts as "unconsumed IO"? The IOs the stream already started, but
>> then did not consume? That shouldn't be hard, I think.
> 
> Yes, the number of IOs that were started but not consumed. Or, even better,
> the number of IOs that completed but were not consumed - but that'd be harder
> to get right now.
> 
> I agree that started-but-not-consumed should be pretty easy.
> 

I'll try to add it to the EXPLAIN.


regards

-- 
Tomas Vondra

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: index prefetching
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox