Re: Custom explain options

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.