Re: index prefetching

On Wed, Jul 16, 2025 at 3:40 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> As a first thing I just wanted to get a feel for the improvements we can get.
> I had a scale 5 tpch already loaded, so I ran a bogus query on that to see.

Cool.

> Test:
>
> Peter's:

To be clear, the "complex" patch is still almost all Tomas' work -- at
least right now. I'd like to do a lot more work on this project,
though.

So far, my main contribution has been debugging advice, and removing
code/simplifying things on the nbtree side.

> I call that a hell of an impressive improvement with either patch - it's
> really really hard to find order of magnitude improvements in anything close
> to realistic cases.

Nice.

> Peter, it'd be rather useful if your patch also had an enable/disable GUC,
> otherwise it's more work to study the performance effects. The
> effective_io_concurrency approach isn't great, because it also affects
> bitmap scans, seqscans etc.

FWIW I took out the GUC because it works by making indexam.c use the
amgettuple interface. The "complex" patch completely gets rid of
btgettuple, whereas the simple patch keeps btgettuple in largely its
current form.

I agree that having such a GUC is important during development, and
will try to add it back soon. It'll have to work in some completely
different way, but that still shouldn't be difficult.

--
Peter Geoghegan