Skip site navigation (1) Skip section navigation (2)

Re: Pgstatindex and leaf fragmentation.

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Pgstatindex and leaf fragmentation.
Date: 2010-12-17 17:28:12
Message-ID: 4D0B9DAC.8080404@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

pgsql-novice by date

Next:From: Majid AzimiDate: 2010-12-17 18:58:30
Subject: Very Large Table Partitioning
Previous:From: Tom LaneDate: 2010-12-17 15:01:57
Subject: Re: pg_largeobject and toast

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group