Re: index prefetching

On 2/15/24 00:06, Peter Geoghegan wrote:
> On Wed, Feb 14, 2024 at 4:46 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
>
>> ...
>
> 2. Are you sure that the leaf-page-at-a-time thing is such a huge
> hindrance to effective prefetching?
>
> I suppose that it might be much more important than I imagine it is
> right now, but it'd be nice to have something a bit more concrete to
> go on.
>

This probably depends on which corner cases are considered important.

The page-at-a-time approach essentially means index items at the
beginning of the page won't get prefetched (or vice versa, prefetch
distance drops to 0 when we get to end of index page).

That may be acceptable, considering we can usually fit 200+ index items
on a single page. Even then it limits what effective_io_concurrency
values are sensible, but in my experience quickly diminish past ~32.

> 3. Even if it is somewhat important, do you really need to get that
> part working in v1?
>
> Tomas' original prototype worked with the leaf-page-at-a-time thing,
> and that still seemed like a big improvement to me. While being less
> invasive, in effect. If we can agree that something like that
> represents a useful step in the right direction (not an evolutionary
> dead end), then we can make good incremental progress within a single
> release.
>

It certainly was a great improvement, no doubt about that. I dislike the
restriction, but that's partially for aesthetic reasons - it just seems
it'd be nice to not have this.

That being said, I'd be OK with having this restriction if it makes v1
feasible. For me, the big question is whether it'd mean we're stuck with
this restriction forever, or whether there's a viable way to improve
this in v2.

And I don't have answer to that :-( I got completely lost in the ongoing
discussion about the locking implications (which I happily ignored while
working on the PoC patch), layering tensions and questions which part
should be "in control".

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company