Thread: Re: doc: explain pgstatindex fragmentation

On Mon, 2025-01-27 at 10:13 +0100, Benoit Lobréau wrote:
> On 1/25/25 7:07 PM, Laurenz Albe wrote:
> > Looks good to me.  I have one question left: the explanation for the performance
> > penalty of a high leaf fragmentation sounds like it would only be relevant for
> > disks where sequential reads are faster.  If that is correct, perhaps it would be
> > worth mentioning.
>
> Frederic noticed a performance hit even for on his laptop with a SSD.
>
> On Fri, 2025-01-24 at 15:41 +0100, Frédéric Yhuel wrote:
>  > I've noticed that maximum leaf_fragmentation can have a huge impact on
>  > a range index-only scan, when reading all blocs from disks, even on my
>  > laptop machine with SSD, but I don't know if this is the right place
>  > to document this?
>
> 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.

Yours,
Laurenz Albe

On Mon, 27 Jan 2025 at 11:21, Laurenz Albe <laurenz.albe@cybertec.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