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 | 78 | 0
0 | 89.67 | 0
(1 row)

What do numbers "leaf_density" and "leaf_fragmentation" mean? I googled
but was unable to come up with a decent explanation. I am looking for
the formula or, at least, a good heuristic explanation.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Search Discussions

  • Mladen Gogala at Dec 17, 2010 at 5:28 pm

    Mladen Gogala wrote:
    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 | 78 | 0
    0 | 89.67 | 0
    (1 row)

    What do numbers "leaf_density" and "leaf_fragmentation" mean? I googled
    but was unable to come up with a decent explanation. I am looking for
    the formula or, at least, a good heuristic explanation.
    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);





    --

    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    http://www.vmsinfo.com
    The Leader in Integrated Media Intelligence Solutions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 16, '10 at 8:49p
activeDec 17, '10 at 5:28p
posts2
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Mladen Gogala: 2 posts

People

Translate

site design / logo © 2022 Grokbase