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

From ddd811b84116c0113b5bd4b0a6b8be2f3de25fa9 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 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-d1bd950e8972@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