Re: doc: explain pgstatindex fragmentation

On Mon, 27 Jan 2025 at 11:21, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> > He reported to our team, that he did a test with two indexes on the same
> > data. They had the same density but one had no fragmentation while the
> > other had 100%. He got an execution time of ~90ms (0 frag) vs ~340ms
> > 100% frag).
> >
> > I get similar result with my laptor (except my disk is significantly
> > worse: ~152ms vs ~833ms).
>
> Thanks for checking.
> I'll set the patch "ready for committer".
> I personally would still like to know how fragmentation slows down performance.

Probable reason is that scanning an unfragmented index results in
sequential I/O patterns that the kernel read-ahead mechanism detects
and does prefetching for us. Fragmented index looks like random I/O
and gets to wait for the full disk latency for every index page.

This is one of the tricky parts to fix for AIO, as directIO will also
bypass this mechanism. PostgreSQL would need to start issuing those
prefetches itself to not have a regression there.

In a theoretical world, where we would be able to drive prefetches
from an inner B-tree page, the difference between fragmented and
unfragmented indexes would be much less.

--
Ants Aasma