Re: index prefetching

On Tue, Aug 12, 2025 at 1:51 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> One more detail I just noticed - the DESC scan apparently needs more
> buffers (~87k vs. 57k). That probably shouldn't cause such massive
> regression, though.

I can reproduce this.

I wondered if the difference might be attributable to the issue with
posting lists and backwards scans (this index has fairly large posting
lists), which is addressed by this patch of mine:

https://commitfest.postgresql.org/patch/5824/

This makes the difference in buffers read identical between the
forwards and backwards scan case. However, it makes exactly no
difference to the execution time of the backwards scan case -- it's
still way higher.

I imagine that this is down to some linux readahead implementation
detail. Maybe it is more willing to speculatively read ahead when the
scan is mostly in ascending order, compared to when the scan is mostly
in descending order. The performance gap that I see is surprisingly
large, but I agree that it has nothing to do with this prefetching
work/the issue that I saw with backwards scans.

I had imagined that we'd be much less sensitive to these kinds of
differences once we don't need to depend on heuristic-driven OS
readahead. Maybe that was wrong.

--
Peter Geoghegan