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
pgstattuple.patch
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster