Re: doc: explain pgstatindex fragmentation

On 1/22/25 12:34, Bertrand Drouvot wrote:
> Hi,
>
> On Tue, Nov 05, 2024 at 06:36:47PM +0100, Frédéric Yhuel wrote:
>> Hi, I thought it would be nice to give the user a better idea of what
>> avg_leaf_density and leaf_fragmentation mean.
>>
>> Patch attached. What do you think?
>
> Yeah, I think that can not hurt to give more details, thanks for the proposal!
>

Hi Bertrand, thanks for your review!

> A few comments:
>
> === 1
>
> + <literal>avg_leaf_density</literal> can be seen as the inverse of bloat,
>
> I'm not sure it's good to describe something as the inverse of "something
> else". See my proposal below.
>

Yeah... bloat is a more familiar concept, so I wanted to link these two
metrics... but "inverse" is confusing... or maybe something like that:

A small <literal>avg_leaf_density</literal> means that the index is bloated.

> === 2
>
> I’m not sure we need to add the extra details in a paragraph below the fields
> description. What about changing the fields description?
>
> Something concise enough like?
>
> avg_leaf_density: shows how full leaf pages currently are (100 if full)

That should do :-)

> leaf_fragmentation: shows how much physical and logical ordering of leaf pages
> differ (zero if they don't)
>

It looks good to me.

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?

I used the following psql scripts to test the effect of
leaf_fragmentation (the first one calls the second one):

https://github.com/dalibo/misc/blob/main/fyhuel/leaf_fragmentation.sql
https://github.com/dalibo/misc/blob/main/fyhuel/evict_from_both_caches.sql

> Also the comments made in [1], [2] and [3] are not linked to this main thread,
> adding them for reference here (but better to keep the conversation going
> by replying to this email).
>
> [1]: https://www.postgresql.org/message-id/4c5dee3a-8381-4e0f-b882-d1bd950e8972%40dalibo.com
> [2]: https://www.postgresql.org/message-id/c70fcc72-eed6-475b-81c8-508422299351%40dalibo.com
> [3]: https://www.postgresql.org/message-id/e8a6db36-073e-4ca3-b38c-b42d7094cba8%40dalibo.com
>

Indeed, I think Benoît mistakenly thought that thread aggregation was
based on thread titles alone. He appended the second conversation to the
commitfest entry.