Re: Index Bloat - how to tell?

Dave Crooke wrote:
> There is a plugin called pgstattuple which can be quite informative
> .... however, it actually does a full scan of the table / index files,
> which may be a bit invasive depending on your environment and load.
>
> http://www.postgresql.org/docs/current/static/pgstattuple.html
>
> It's in the contrib (at least for 8.4), and so you have to import its
> functions into your schema using the script in the contrib directory.
>
> Cheers
> Dave
I tried it with one of my databases:


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 |     827392 |             3 |              0
|        100 |           0 |             0 |            70.12
|                 22
(1 row)


What is "leaf_fragmentation"? How is it defined? I wasn't able to find
out any definition of that number. How is it calculated. I verified that
running reindex makes it 0:


testtrack=# reindex table public.defects;
REINDEX
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)


--

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