On 1/24/25 11:47, Bertrand Drouvot wrote:
Hi Frédéric,
On Thu, Jan 23, 2025 at 10:00:27AM +0100, Frédéric Yhuel wrote:
On 1/22/25 12:34, Bertrand Drouvot wrote:
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
Yeah but in the (rare?) case "bloat" is not known then one would have to make
sense of it first.
OK let's not talk about bloat then :-)
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 :-)
Thanks!
I don't know if you noticed Laurenz's suggestion, because he forgot to
CC you, but I like it very much. I think we should mention the default
fillfactor (90 for indexes).
leaf_fragmentation: shows how much physical and logical ordering of leaf pages
differ (zero if they don't)
It looks good to me.
Thanks!
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?
Yeah, that might be worth to mention. Maybe below the descriptions then?
(keeping
the changes above in the description).
OK, thanks. I've tried to put it all together, based on v2 patch from
Laurenz. Here is a v3 patch.
(I'm unsure who should be author or reviewer, but I guess the committer
will fix that anyway, if the patch were to be merged).
From ddd811b84116c0113b5bd4b0a6b8be2f3de25fa9 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Thu, 23 Jan 2025 14:44:29 +0100
Subject: [PATCH v3] doc: explain pgstatindex fragmentation
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
It was quite hard to guess what leaf_fragmentation meant without looking
at pgstattuple's code. This patch aims to give to the user a better
idea of what it means.
Author: Frédéric Yhuel
Author: Laurenz Albe
Reviewed-by: Bertrand Drouvot, Benoît Lobréau
Discussion: https://postgr.es/m/bf110561-f774-4957-a890-bb6fab6804e0%40dalibo.com
Discussion: https://postgr.es/m/4c5dee3a-8381-4e0f-b882-d1bd950e8...@dalibo.com
---
doc/src/sgml/pgstattuple.sgml | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index 4071da4ed94..4209e7a7770 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -270,6 +270,15 @@ leaf_fragmentation | 0
page than is accounted for by <literal>internal_pages + leaf_pages +
empty_pages + deleted_pages</literal>, because it also includes the
index's metapage.
+ <literal>avg_leaf_density</literal> is the fraction of the index size that
+ is taken up by user data. Since indexes have a default fillfactor of 90,
+ this should be around 0.9 for newly built indexes, but usually deteriorates
+ over time.
+ <literal>leaf_fragmentation</literal> represents a measure of disorder.
+ A higher <literal>leaf_fragmentation</literal> indicates that the
+ physical order of the index leaf pages increasingly deviates from their
+ logical order. This can have a significant impact if a large part
+ of the index is read from disk.
</para>
<para>
--
2.45.2
