Content-Type: multipart/alternative;
 boundary="------------fICM1Sy856110lwTF4LXw8px"
Message-ID: <5e5df8c7-790f-492f-b8f6-f9ee09321d47@garret.ru>
Date: Tue, 16 Jan 2024 18:07:16 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Custom explain options
Content-Language: en-US
To: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <6cd5caa7-06e1-4460-bf35-00a59da3f677@garret.ru>
 <629afb5e-9327-422c-a29f-4484279939b4@enterprisedb.com>
 <04aeec17-ad57-4d90-b2f1-7881369b7920@garret.ru>
 <50bf548d-44a1-49d8-aac9-6e4988badaae@enterprisedb.com>
 <a9ee8faa-f6ee-45f9-9246-92943f2b2377@garret.ru>
 <c791e16b-1658-4369-bc10-d65cc1cf23c8@enterprisedb.com>
 <57bc73d0-5f70-40bb-8d9e-ba4bc1f59d47@garret.ru>
 <e654159f-2033-4333-9404-c9bb898a1866@enterprisedb.com>
 <de6001a6-3f58-4ee8-abef-b046908b04a4@garret.ru>
 <f7a48eec-0053-4d78-a1c7-5ec91774fb9c@enterprisedb.com>
From: Konstantin Knizhnik <knizhnik@garret.ru>
In-Reply-To: <f7a48eec-0053-4d78-a1c7-5ec91774fb9c@enterprisedb.com>
Archived-At: <https://www.postgresql.org/message-id/5e5df8c7-790f-492f-b8f6-f9ee09321d47%40garret.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------fICM1Sy856110lwTF4LXw8px
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 16/01/2024 5:38 pm, Tomas Vondra wrote:
> By "broken" you mean that you prefetch items only from a single leaf
> page, so immediately after reading the next one nothing is prefetched.
> Correct?


Yes, exactly. It means that reading first heap page from next leaf page 
will be done without prefetch which in case of Neon means roundtrip with 
page server (~0.2msec within one data center).


>   Yeah, I had this problem initially too, when I did the
> prefetching in the index AM code. One of the reasons why it got moved to
> the executor.

Yeh, it works nice for vanilla Postgres. You call index_getnext_tid() 
and when it reaches end of leaf page it reads next read page. Because of 
OS read-ahead this read is expected to be fast even without prefetch. 
But not in Neon case - we have to download this page from page server 
(see above). So ideal solution for Neon will be to prefetch both leave 
pages and referenced heap pages. And prefetch of last one should be 
initiated as soon as leaf page is loaded. Unfortunately it is 
non-trivial to implement and current index scan prefetch implementation 
for Neon is not doing it.


--------------fICM1Sy856110lwTF4LXw8px
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 16/01/2024 5:38 pm, Tomas Vondra
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:f7a48eec-0053-4d78-a1c7-5ec91774fb9c@enterprisedb.com"><span
      style="white-space: pre-wrap">By "broken" you mean that you prefetch items only from a single leaf</span>
      <pre class="moz-quote-pre" wrap="">page, so immediately after reading the next one nothing is prefetched.
Correct?</pre>
    </blockquote>
    <p><br>
    </p>
    <p>Yes, exactly. It means that reading first heap page from next
      leaf page will be done without prefetch which in case of Neon
      means roundtrip with page server (~0.2msec within one data
      center).<br>
    </p>
    <p><br>
    </p>
    <blockquote type="cite"
      cite="mid:f7a48eec-0053-4d78-a1c7-5ec91774fb9c@enterprisedb.com">
      <pre class="moz-quote-pre" wrap=""> Yeah, I had this problem initially too, when I did the
prefetching in the index AM code. One of the reasons why it got moved to
the executor.</pre>
    </blockquote>
    <p>Yeh, it works nice for vanilla Postgres. You call <span
      style="white-space: pre-wrap">index_getnext_tid() and when it reaches end of leaf page it reads next read page.
Because of OS read-ahead this read is expected to be fast even without prefetch. But not in Neon case - we have to download this page from page server (see above).
So ideal solution for Neon will be to prefetch both leave pages and referenced heap pages. And prefetch of last one should be initiated as soon as leaf page is loaded.
Unfortunately it is non-trivial to implement and current index scan prefetch implementation for Neon is not doing it.</span></p>
    <p><span style="white-space: pre-wrap">
</span></p>
    <br>
  </body>
</html>

--------------fICM1Sy856110lwTF4LXw8px--