From d78c787de917068c07dea96d247795fbe256df7d Mon Sep 17 00:00:00 2001 From: Laurenz Albe 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-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..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 internal_pages + leaf_pages + empty_pages + deleted_pages, because it also includes the index's metapage. + avg_leaf_density 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. + leaf_fragmentation represents a measure of disorder. + A higher leaf_fragmentation 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. -- 2.45.2