Re: pgstattuple extension for indexes

Hi,

I'm working on an utility for b-tree index, called `pgstatindex`.

It reports b-tree index statistics like a pgstattuple as below.
----------------------------------------------------------------
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey1');
-[ RECORD 1 ]------+---------
version | 2
tree_level | 2
index_size | 17956864
root_block_no | 361
internal_pages | 8
leaf_pages | 2184
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 90.07
leaf_fragmentation | 0

pgbench=#
----------------------------------------------------------------

I want to make this to contrib module like a pgstattuple,
and to make this open to public in a few days.

Do you think this is useful?

2006/7/24, ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > >> Also, I added an experimental feature for btree indexes. It checks
> > >> fragmentation factor of indexes.
>
> > The really serious problem with reporting this info via NOTICE is that
> > there's no way for a program to get its hands on the info. The output
> > tuple format needs to be extended instead.
>
> Ok, I added 'fragmented_percent' field to the output tuple. This
> information will help us to decide when to do REINDEX.
> However, it is only avaliable for btree index presently. Other indexes
> should have equivalent information, but I don't know how to determine it.
>
>
> BTW, should we change VACUUM VERBOSE in the same way? If we do so,
> autovacuum can handle the reports of VACUUM VERBOSE and plan when to
> do VACUUM FULL, REINDEX and/or CLUSTER using the information.
> Is this worth doing?
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>
>