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