Feedback-ID: id4a34324:Fastmail
Date: Wed, 14 Feb 2024 21:03:11 -0800
From: Andres Freund <andres@anarazel.de>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Georgios <gkokolatos@protonmail.com>,
	Thomas Munro <thomas.munro@gmail.com>,
	Konstantin Knizhnik <knizhnik@garret.ru>,
	Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: index prefetching
Message-ID: <20240215050311.5zdmzvz2zckg3s6g@awork3.anarazel.de>
References: <482ec3ff-52ad-415d-96fd-f3832a894023@enterprisedb.com>
 <CAAKRu_ZPDhNwwFxQwS8NdeTFkycM1c=tNLKdU0J-M6KxCjdEmQ@mail.gmail.com>
 <56176b8d-956c-487e-ab09-310db4581c07@enterprisedb.com>
 <CAAKRu_ad8cywU4_X+5e4A9Wy_PZmrCx2aoLpdHPBRvb9inGDgQ@mail.gmail.com>
 <4867452a-b853-4813-a6da-9bb06a336f8b@enterprisedb.com>
 <CAAKRu_Y=go4u99udRaj3k0vuF6EAfWqM86BONhxB4MV9X4FRpQ@mail.gmail.com>
 <CAAKRu_ZPuaO5XwXfM7wowf4sSmPSJ2LT9+zfmD5=LQ=WhV_j=Q@mail.gmail.com>
 <CA+TgmoZemqR60MoiPN+R0qMe2XDYtOe=Tr1+3rX7S1Uua_C6wA@mail.gmail.com>
 <5fc8f150-f350-4cb3-b6b7-ffcc21ed7bc2@enterprisedb.com>
 <CA+TgmoaDfvFF_krboNjjMYzOiPsGM_ioYqfngL-pNaj-nSs1DA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CA+TgmoaDfvFF_krboNjjMYzOiPsGM_ioYqfngL-pNaj-nSs1DA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240215050311.5zdmzvz2zckg3s6g%40awork3.anarazel.de>
Precedence: bulk

Hi,

On 2024-02-15 09:59:27 +0530, Robert Haas wrote:
> I would have thought that the way this prefetching would work is that
> we would bring pages into shared_buffers sooner than we currently do,
> but not actually pin them until we're ready to use them, so that it's
> possible they might be evicted again before we get around to them, if
> we prefetch too far and the system is too busy.

The issue here is that we need to read index leaf pages (synchronously for
now!) to get the tids to do readahead of table data. What you describe is done
for the table data (IMO not a good idea medium term [1]), but the problem at
hand is that once we've done readahead for all the tids on one index page, we
can't do more readahead without looking at the next index leaf page.

Obviously that would lead to a sawtooth like IO pattern, where you'd regularly
have to wait for IO for the first tuples referenced by an index leaf page.

However, if we want to issue table readahead for tids on the neighboring index
leaf page, we'll - as the patch stands - not hold a pin on the "current" index
leaf page. Which makes index prefetching as currently implemented incompatible
with kill_prior_tuple, as that requires the index leaf page pin being held.


> Alternately, it also seems OK to read those later pages and pin them right
> away, as long as (1) we don't also give up pins that we would have held in
> the absence of prefetching and (2) we have some mechanism for limiting the
> number of extra pins that we're holding to a reasonable number given the
> size of shared_buffers.

FWIW, there's already some logic for (2) in LimitAdditionalPins(). Currently
used to limit how many buffers a backend may pin for bulk relation extension.

Greetings,

Andres Freund


[1] The main reasons that I think that just doing readahead without keeping a
pin is a bad idea, at least medium term, are:

a) To do AIO you need to hold a pin on the page while the IO is in progress,
as the target buffer contents will be modified at some moment you don't
control, so that buffer should better not be replaced while IO is in
progress. So at the very least you need to hold a pin until the IO is over.

b) If you do not keep a pin until you actually use the page, you need to
either do another buffer lookup (expensive!) or you need to remember the
buffer id and revalidate that it's still pointing to the same block (cheaper,
but still not cheap).  That's not just bad because it's slow in an absolute
sense, more importantly it increases the potential performance downside of
doing readahead for fully cached workloads, because you don't gain anything,
but pay the price of two lookups/revalidation.

Note that these reasons really just apply to cases where we read ahead because
we are quite certain we'll need exactly those blocks (leaving errors or
queries ending early aside), not for "heuristic" prefetching. If we e.g. were
to issue prefetch requests for neighboring index pages while descending during
an ordered index scan, without checking that we'll need those, it'd make sense
to just do a "throway" prefetch request.