Re: Prefetch the next tuple's memory during seqscans

On Wed, Nov 23, 2022 at 11:03:22AM -0500, Bruce Momjian wrote:
> > CPUs have several different kinds of 'hardware prefetchers' (worth
> > reading about), that look out for sequential and striding patterns and
> > try to get the cache line ready before you access it. Using the
> > prefetch instructions explicitly is called 'software prefetching'
> > (special instructions inserted by programmers or compilers). The
> > theory here would have to be that the hardware prefetchers couldn't
> > pick up the pattern, but we know how to do it. The exact details of
> > the hardware prefetchers vary between chips, and there are even some
> > parameters you can adjust in BIOS settings. One idea is that the
> > hardware prefetchers are generally biased towards increasing
> > addresses, but our tuples tend to go backwards on the page[1]. It's
> > possible that some other CPUs can detect backwards strides better, but
> > since real world tuples aren't of equal size anyway, there isn't
> > really a fixed stride at all, so software prefetching seems quite
> > promising for this...
> >
> > [1] https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-PAGE-LAYOUT-FIGURE
>
> I remember someone showing that having our item pointers at the _end_ of
> the page and tuples at the start moving toward the end increased
> performance significantly.

Ah, I found it, from 2017, with a 15-25% slowdown:

https://www.postgresql.org/message-id/20171108205943.tps27i2tujsstrg7%40alap3.anarazel.de

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson