From: | "Satoshi Nagayasu" <nagayasus(at)nttdata(dot)co(dot)jp> |
---|---|
To: | "ITAGAKI Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Bruce Momjian" <bruce(at)momjian(dot)us>, pgsql-patches(at)postgresql(dot)org, ishii(at)sraoss(dot)co(dot)jp |
Subject: | Re: pgstattuple extension for indexes |
Date: | 2006-07-24 23:33:03 |
Message-ID: | 1a53b89f0607241633m1804258ct9674a1132cbde8e4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2006-07-24 23:46:46 | Re: Help! - Slony-I - saving/setting/restoring GUC |
Previous Message | Josh Berkus | 2006-07-24 23:27:45 | Re: effective_cache_size is a real? |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-07-25 00:14:45 | Re: Resurrecting per-page cleaner for btree |
Previous Message | Alvaro Herrera | 2006-07-24 22:27:29 | Re: The vacuum-ignore-vacuum patch |