Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcjah-00ABE3-0d for pgsql-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 12:21:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vcjag-001x1w-0G for pgsql-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 12:21:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcjaf-001x1m-1p for pgsql-hackers@lists.postgresql.org; Mon, 05 Jan 2026 12:21:42 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vcjad-004fct-23 for pgsql-hackers@lists.postgresql.org; Mon, 05 Jan 2026 12:21:42 +0000 Received: by mail-ed1-x52d.google.com with SMTP id 4fb4d7f45d1cf-64b7318f1b0so16688009a12.2 for ; Mon, 05 Jan 2026 04:21:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=garret-ru.20230601.gappssmtp.com; s=20230601; t=1767615697; x=1768220497; darn=lists.postgresql.org; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=GcyVKKFaY3v62pDjPpYzzCoDG6YBealy6KKDonRY14o=; b=EAMPLel9B0jXhEQ4HTCyUgiNRzIkA2W/2vqDPBJ6EQGifc+5Mkqj49L5w7Nay6vWHS m3zduEjkoiyLCp03hSxb8DxhOxyf6vt+C2dl40h+PyFMOtbk9z2Oeuh6ZmjHtxL+d/lq zRHDJo5a8ry0haW4UT06OqtZSoNMYQCmhw9XoIdgJS3k1fZMD+MSpNP0ci9D4FZa0XYc afl1ePn2pBW33kJSlqrvwNDuIAMYp9sjTramq8lTJFr91mMXEaW1SglI7p+SlwOWXT00 56NjKHIX+zOyyZVRA0yHqTqRkzJoXKIYa+kUS5YLuQu5WXQ1uY7H8EWNeO/9Vk9h6QlU 7VBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1767615697; x=1768220497; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:x-gm-gg:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=GcyVKKFaY3v62pDjPpYzzCoDG6YBealy6KKDonRY14o=; b=ejk+lOhJO7XIGeCjlxCzxAkK4+qQuKRU5T8cwMZl8lAnq76TfLosn0xcuI6ZjxNUnk hTQwZDYZ7WK8Lk5hoBOQ/2KtihXb/hqN35gwrYqYEoi8Xxt6E1T93/X57cKTNQrWlCUH a9YGUI1edxDQTklIWndAFEuqGUgvjpqAZ7vGsBmfZi693TdN1ap3TKvPi8inZAor9688 AI9HWPzWG99N2nFD3WR3Ifq5xKYAN8D6O9SlxkJ06EhHLQ8QZiW48Eo9kS89joiQhai7 7nsb/utOd8NCWXeM2urLUi+obslr4nOVscf/1CSec2zRsw9K+YdrjUWj2XiUOPuX8WZv VNmg== X-Forwarded-Encrypted: i=1; AJvYcCU4GIZiF09guvaNOX6hQgLjdxHUamu4ZvPUvjubCdh4sxp6Lzhp5xu963VGc28jux43fHGTPqXhP0La0XmT@lists.postgresql.org X-Gm-Message-State: AOJu0YxfrwI3fWzcVNoFQ5P1AUwnBONTcID7WN/Dl4C4VNowscskhhzg WXs4zCeI7srHZJZHo1Yh+28qJnzvnP4HC6fQ5o7yUKt6IOPqnUO7qrNtyTDxH81VoA== X-Gm-Gg: AY/fxX5wN2BExZseDlDz9Oa1ILF0hzKjnT+79ADTNZ6MRHKO2CyoJrpt0B9XZIO1bc6 skOJeEmWy0CK47AsAtxRbchftcnW1CyxyYa3EQ640ux3nZYHoHpYw2SrhO8dgP06uLdrdCiwRkz 2q8rkHML5AnfSLzSxQwInNO+pho9bNao8qQ5HW1AN+yTRXqywfDsUhdbfplpKgAUcj+kNLxYqZp NQjKtsWBHGctw9QZPuRScSCTAYHe63eLH/OoL46xd+6dA1iK+0oHT0/eFxb+CfwMzY5Z+1PNXtH wapS/jENgYLnNs0BDn1fv4ySHDSJ/YYWs68kpLK9VB5KHbx+aKrVtsbR7ClYd7TiWwwxrvb17Zq k5ttbClztsTCDdArhurse2yDQysflGprNBArqTYBnKk05U07sCjjTZSKKy4ZFaxo5QV1pSSL9C3 7I+YwBmrlh3rtnfGShvKqU X-Google-Smtp-Source: AGHT+IEzZRUAWeCckc/QkMplTHwU4qf1oPJugkZJcT4M76h9so2xs0SyeQsUym2rPjhatpfa0czSBg== X-Received: by 2002:a17:907:3da8:b0:b76:b76e:112a with SMTP id a640c23a62f3a-b8036ece49dmr4996321066b.11.1767615696429; Mon, 05 Jan 2026 04:21:36 -0800 (PST) Received: from [10.248.37.148] ([130.41.208.2]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-b8037ad806asm5657182266b.23.2026.01.05.04.21.33 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 05 Jan 2026 04:21:35 -0800 (PST) Content-Type: multipart/alternative; boundary="------------zOJGx0brsAK5BkEIFUneleca" Message-ID: Date: Mon, 5 Jan 2026 14:21:31 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: index prefetching To: Tomas Vondra , Peter Geoghegan Cc: Andres Freund , Thomas Munro , Nazir Bilal Yavuz , Robert Haas , Melanie Plageman , PostgreSQL Hackers , Georgios , Dilip Kumar References: <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me> <8f123089-49ed-4c35-ab28-e3df70d28043@garret.ru> <69908539-3cc5-4572-b69f-055fa40d3bea@garret.ru> <95b6c6ef-4ba1-44f6-b1ac-eef0bbb5327a@vondra.me> <6ba8a743-2b58-4c5a-8a39-28ccc130a069@garret.ru> Content-Language: en-US From: Konstantin Knizhnik In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------zOJGx0brsAK5BkEIFUneleca Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 05/01/2026 2:21 AM, Tomas Vondra wrote: > Even a small fixes overhead can be quite visible for extremely short > queries that can't possibly benefit from prefetching (like the LIMIT 1). > > That's what we were concerned about when we invented this heuristics > (which only prefetches from the 2nd batch). I agree it's crude, no > argument there. I'm not against having something better, as long as we > can make it reliable. I completely agree that we should not create read stream from the very beginning despite to small overhead. My concern is that second batch is not good criteria: depending on key size and position of the first key on the page, it can be too short. > One of the problems with read_stream is that it may look ahead very far > ahead. Much further than the query will need. Imagine an index-only > scan, with 99.99% pages being all-visible. At some point the scan will > hit an item that requires reading the heap page. Which triggers the > look-ahead, and a search for the *next* heap page. > > But that next index entry may be many leafs ahead. And maybe the scan > never even gets to actually need that, which means the work will be > wasted. If the query only needs a couple tuples, this may cause a bad > regression. Sorry, I do not completely understand how it can happen. Read stream can only fetch heap pages which are referenced by TIDs in leave pages (leaf=batch). So read stream can not advance more than TIDs from currently processes leaf page, can it? > IIRC the same thing can happen for plain index scans. You may need to > try with correlated indexes (I think the read_stream callback skips > duplicate TIDs). Another problem is that different TIDs can refer to the same heap page (which happens with clustered index or table populated in key ascending order). But number of SMGR reads criteria also should work good in this case. > When all data is cached in shared buffers, then we do not perform IO at > all. > It means there it doesn't matter whether and when we initialize > read_stream. > We can do it after processing 10 items (current default), or immediately > - it should not affect performance. > And this is what I have tested: performance actually not depends on > `read_stream_threshold` (if data fits in shared buffers). > At least it is within few percents and may be it is just random > fluctuations. > Obviously there is no 25% degradation. > > I'm not sure it's this simple. Even if we perform no physical I/O, the > read_stream callback will need to go through the whole batch and pass > all the items to the read_stream. The data may be cached, but not in > shared buffers, in which case the read_stream will do the IPC etc. You mean that data is present in OS file cache and reading it is fast and will not benefit from AIO? It certainly can happen but it seems to be quite obvious problem of double buffering. > It definitely doesn't mean that it is not possible to find scenario > where this approach with enabling prefetch after processing N items will > show worse performance than master or v5. We just need to properly > choose cache hit rate. But the same is true IMHO for v5 itself: it is > possible to find workload where it will show the same degradation > comparing with master. > > I'm sure such "adversarial" data sets exist, even if we don't have a > good example at hand. The question is how plausible / realistic such > data sets are, though. > > I'm not sure about v5, though. The assumption was the overhead is most > visible for short queries, and once we get to the 2nd batch the query is > expensive enough to to not be affected too much. > > When you say "we just need to properly choose cache hit rate", how would > we do that? And how would we know/determine the optimal threshold? Sorry, may be I was unclear. Saying about "choosing cache hit rate" I didn't not mean to use it for determining proper threshold. I was thinking about the best/worst scenario for index prefetch. Originally in my "benchmark" I considered case when no heap pages are present in shared buffer. In your scenario - size of shared buffers is larger than size of table, so all pages are present in shared buffers. First case is certainly the best scenario for index prefetch where we can expect to get the largest improvement. In your case there is certainly no improvement, but as we can see - overhead is also not so large (just because we do not read any page). I expect that the worst result will be in some intermediate case - when some pages are present in shared buffer, some - not. It will be especially true for "second batch" criteria, because it can happen that we need to read just the single heap page for the whole batch and using read stream for it will just add extra overhead. >> More precise heuristic should IMHO take in account actual number of >> performed disk read. > I'm not sure "disk reads" is the right term. The data may be in page > cache, and we just need to copy them into shared buffers. I agree, but there seems to be no chance to determine if page was actually read from disk or cache. But as I wrote above, it is problem of double buffering. If we can avoid it (i.e. using direct IO), then there will be no such problem. >> Please notice that I do not want to predict number of disk reads - i.e. >> check if candidates for prefetch are present in shared buffers. >> It will really adds significant overhead. I think that it is better to >> use as threshold number of performed reads. >> > Not sure I understand what this says. Can you elaborate? I sent proposed patch in the previous mail. Did you have a chance to look at it? --------------zOJGx0brsAK5BkEIFUneleca Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit


On 05/01/2026 2:21 AM, Tomas Vondra wrote: 
Even a small fixes overhead can be quite visible for extremely short
queries that can't possibly benefit from prefetching (like the LIMIT 1).

That's what we were concerned about when we invented this heuristics
(which only prefetches from the 2nd batch). I agree it's crude, no
argument there. I'm not against having something better, as long as we
can make it reliable.

I completely agree that we should not create read stream from the very beginning despite to small overhead.
My concern is that second batch is not good criteria: depending on key size and position of the first key on the page, it can be too short.


One of the problems with read_stream is that it may look ahead very far
ahead. Much further than the query will need. Imagine an index-only
scan, with 99.99% pages being all-visible. At some point the scan will
hit an item that requires reading the heap page. Which triggers the
look-ahead, and a search for the *next* heap page.

But that next index entry may be many leafs ahead. And maybe the scan
never even gets to actually need that, which means the work will be
wasted. If the query only needs a couple tuples, this may cause a bad
regression.

Sorry, I do not completely understand how it can happen.
Read stream can only fetch heap pages which are referenced by TIDs in leave pages (leaf=batch).
So read stream can not advance more than TIDs from currently processes leaf page, can it?

IIRC the same thing can happen for plain index scans. You may need to
try with correlated indexes (I think the read_stream callback skips
duplicate TIDs).

Another problem is that different TIDs can refer to the same heap page (which happens with clustered index or table populated in key ascending order).
But number of SMGR reads criteria also should work good in this case.

When all data is cached in shared buffers, then we do not perform IO at
all.
It means there it doesn't matter whether and when we initialize
read_stream.
We can do it after processing 10 items (current default), or immediately
- it should not affect performance.
And this is what I have tested: performance actually not depends on
`read_stream_threshold` (if data fits in shared buffers).
At least it is within few percents and may be it is just random
fluctuations.
Obviously there is no 25% degradation.


I'm not sure it's this simple. Even if we perform no physical I/O, the
read_stream callback will need to go through the whole batch and pass
all the items to the read_stream. The data may be cached, but not in
shared buffers, in which case the read_stream will do the IPC etc.

You mean that data is present in OS file cache and reading it is fast and will not benefit from AIO?
It certainly can happen but it seems to be quite obvious problem of double buffering.

It definitely doesn't mean that it is not possible to find scenario
where this approach with enabling prefetch after processing N items will
show worse performance than master or v5. We just need to properly
choose cache hit rate. But the same is true IMHO for v5 itself: it is
possible to find workload where it will show the same degradation
comparing with master.


I'm sure such "adversarial" data sets exist, even if we don't have a
good example at hand. The question is how plausible / realistic such
data sets are, though.

I'm not sure about v5, though. The assumption was the overhead is most
visible for short queries, and once we get to the 2nd batch the query is
expensive enough to to not be affected too much.

When you say "we just need to properly choose cache hit rate", how would
we do that? And how would we know/determine the optimal threshold?

Sorry, may be I was unclear.
Saying about "choosing cache hit rate" I didn't not mean to use it for determining proper threshold.
I was thinking about the best/worst scenario for index prefetch.
Originally in my "benchmark" I considered case when no heap pages are present in shared buffer.
In your scenario - size of shared buffers is larger than size of table, so all pages are present in shared buffers.
First case is certainly the best scenario for index prefetch where we can expect to get the largest improvement.
In your case there is certainly no improvement, but as we can see - overhead is also not so large (just because we do not read any page).
I expect that the worst result will be in some intermediate case - when some pages are present in shared buffer, some - not.
It will be especially true for "second batch" criteria, because it can happen that we need to read just the single heap page for the whole batch and using read stream for it will just add extra overhead.

More precise heuristic should IMHO take in account actual number of
performed disk read.

I'm not sure "disk reads" is the right term. The data may be in page
cache, and we just need to copy them into shared buffers.

I agree, but there seems to be no chance to determine if page was actually read from disk or cache.
But as I wrote above, it is problem of double buffering. If we can avoid it (i.e. using direct IO), then there will be no such problem.

Please notice that I do not want to predict number of disk reads - i.e.
check if candidates for prefetch are present in shared buffers.
It will really adds significant overhead. I think that it is better to
use as threshold number of performed reads.


Not sure I understand what this says. Can you elaborate?

I sent proposed patch in the previous mail.
Did you have a chance to look at it?


--------------zOJGx0brsAK5BkEIFUneleca--