v2-0001-doc-explain-pgstatindex-fragmentation.patch

From 9b93682e5e7b882c78130abb2280e655e0ead360 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Thu, 23 Jan 2025 14:44:29 +0100
Subject: [PATCH v2] 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
Discussion: https://postgr.es/m/bf110561-f774-4957-a890-bb6fab6804e0%40dalibo.com
Discussion: https://postgr.es/m/4c5dee3a-8381-4e0f-b882-d1bd950e8972@dalibo.com
---
 doc/src/sgml/pgstattuple.sgml | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index 4071da4ed94..4b26b56930a 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -270,6 +270,14 @@ 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.
+     The higher <literal>leaf_fragmentation</literal> is, the less the physical
+     order of the index leaf pages corresponds to the logical order it would
+     have just after creation.
     </para>
 
     <para>
-- 
2.48.1