There is a Postgres extension in the contrib directory, named
pgstattuple, which collects valuable statistics for tables and indexes.
One of the calculated numbers is called "leaf_fragmentation". How exactly
is id defined, does anybody have a formula or a simple explanation? Is it
related to the photosynthesis? Here is an example:

testtrack=# select * from pgstatindex('public.defects_pkey');
version | tree_level | index_size | root_block_no | internal_pages |
leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation

---------+------------+------------+---------------+----------------
+------------+-------------+---------------+------------------
+-------------------
-
2 | 1 | 647168 | 3 | 0

What do numbers "leaf_density" and "leaf_fragmentation" mean? I googled
but was unable to come up with a decent explanation.


--
http://mgogala.byethost5.com

On Thu, 16 Dec 2010 20:37:07 +0000, Mladen Gogala wrote:

To answer my own question, the "leaf_fragmentation" measures the number
of free space fragments in the index. It is a synthetic value. I am
still not sure about the significance of this value on the performance
of an index access. Fragmented free space should only affect the insert
performance. I am not sure what amount of fragmentation should be taken
as an indication for re-indexing.


snprintf(values[j++], 32, "%.2f", (double) indexStat.fragments /
(double) indexStat.leaf_pages * 100.0);





--
http://mgogala.byethost5.com