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


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 <[EMAIL PROTECTED]>:
Tom Lane <[EMAIL PROTECTED]> 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?

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to