pgstattuple extension for indexes

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org, ishii(at)sraoss(dot)co(dot)jp
Subject: pgstattuple extension for indexes
Date: 2006-06-28 07:22:31
Message-ID: 20060628154451.FC33.ITAGAKI.TAKAHIRO@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

This is an extension of pgstattuple to query information from indexes.
It supports btree, hash and gist. Gin is not supported.
It scans only index pages and does not read corresponding heap tuples.
Therefore, 'dead_tuple' means the number of tuples with LP_DELETE flag.

Also, I added an experimental feature for btree indexes. It checks
fragmentation factor of indexes. If an leaf has the right link on the next
adjacent page in the file, it is assumed to be continuous (not fragmented).
It will help us to decide when to REINDEX.

Suggestions welcome.

----
$ pgbench -i
$ pgbench -n -t 100 -c 10
# select * from pgstattuple('accounts_pkey');
NOTICE: 0.36% fragmented
HINT: continuous=273, forward=1, backward=0
-[ RECORD 1 ]------+--------
table_len | 2260992
tuple_count | 100996 -- 996 tuples are dead practically,
tuple_len | 1615936 but no LP_DELETE yet.
tuple_percent | 71.47
dead_tuple_count | 4
dead_tuple_len | 64 -- 64 tuples are marked as LP_DELETE.
dead_tuple_percent | 0
free_space | 208188
free_percent | 9.21
----

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Attachment Content-Type Size
pgstattuple.patch application/octet-stream 22.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Hallgren 2006-06-28 08:22:45 Re: UUID's as primary keys
Previous Message Martijn van Oosterhout 2006-06-28 07:13:06 Re: UUID's as primary keys

Browse pgsql-patches by date

  From Date Subject
Next Message Magnus Hagander 2006-06-28 09:23:28 Re: Client build of MSVC6+ patch
Previous Message Hiroshi Saito 2006-06-28 04:45:20 Re: Client build of MSVC6+ patch