On 1/24/25 14:58, Laurenz Albe wrote:
On Fri, 2025-01-24 at 13:34 +0000, Bertrand Drouvot wrote:
+ Since indexes have a default fillfactor of 90, this should be around 0.9 for
+ newly built indexes
I think 0.9 should be replaced by 90 (that's the actual kind of output we'd
get).
Damn! I missed that one too...
But having said that, I'm not sure we should mention those 90 stuff because it
depends of the amount of data indexed (I mean if the index has a very few
leaf pages, say < 5, then it's easy to be << 90 since it's an average). That's
probably not the majority of indexes though so maybe just nuance the sentence a
bit.
Sorry about the 0.9.
Perhaps the wording could be more careful: ... this should be around 90 for
most newly built indexes of non-neglectable size.
It looks good to me (apart from the typo). v4 attached
Thanks! :-)
From d78c787de917068c07dea96d247795fbe256df7d 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 v4] 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..c747a5818ab 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 90 for newly built indexes of non-negligible size,
+ 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
