On Tue, 2024-11-05 at 18:36 +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?
I am all for explaining this better. Here is my take. I tried to avoid "bloat", since it is jargon that not everybody might be familiar with. I also didn't start a new paragraph and kept it together with the explanation for index_size. Yours, Laurenz Albe
From 9b93682e5e7b882c78130abb2280e655e0ead360 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 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-d1bd950e8...@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
