pgstattuple The pgstattuple module provides various functions to obtain tuple-level statistics. pgstattuple pgstattuple(regclass) returns record pgstattuple returns a relation's physical length, percentage of dead tuples, and other info. This may help users to determine whether vacuum is necessary or not. The argument is the target relation's name (optionally schema-qualified) or OID. For example: test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); -[ RECORD 1 ]------+------- table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 The output columns are described in . Column Type Description table_len bigint Physical relation length in bytes tuple_count bigint Number of live tuples tuple_len bigint Total length of live tuples in bytes tuple_percent float8 Percentage of live tuples dead_tuple_count bigint Number of dead tuples dead_tuple_len bigint Total length of dead tuples in bytes dead_tuple_percent float8 Percentage of dead tuples free_space bigint Total free space in bytes free_percent float8 Percentage of free space pgstattuple acquires only a read lock on the relation. So the results do not reflect an instantaneous snapshot; concurrent updates will affect them. pgstattuple judges a tuple is dead if HeapTupleSatisfiesDirty returns false. pgstattuple(text) returns record This is the same as pgstattuple(regclass), except that the target relation is specified by TEXT. This function is kept because of backward-compatibility so far, and will be deprecated in the future release. pgstatindex pgstatindex(regclass) returns record pgstatindex returns a record showing information about a B-tree index. For example: test=> SELECT * FROM pgstatindex('pg_cast_oid_index'); -[ RECORD 1 ]------+------ version | 2 tree_level | 0 index_size | 8192 root_block_no | 1 internal_pages | 0 leaf_pages | 1 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 50.27 leaf_fragmentation | 0 The output columns are: Column Type Description version integer B-tree version number tree_level integer Tree level of the root page index_size bigint Total number of pages in index root_block_no bigint Location of root block internal_pages bigint Number of internal (upper-level) pages leaf_pages bigint Number of leaf pages empty_pages bigint Number of empty pages deleted_pages bigint Number of deleted pages avg_leaf_density float8 Average density of leaf pages leaf_fragmentation float8 Leaf page fragmentation As with pgstattuple, the results are accumulated page-by-page, and should not be expected to represent an instantaneous snapshot of the whole index. pgstatindex(text) returns record This is the same as pgstatindex(regclass), except that the target index is specified by TEXT. This function is kept because of backward-compatibility so far, and will be deprecated in the future release. pgstatginindex pgstatginindex(regclass) returns record pgstatginindex returns a record showing information about a GIN index. For example: test=> SELECT * FROM pgstatginindex('test_gin_index'); -[ RECORD 1 ]--+-- version | 1 pending_pages | 0 pending_tuples | 0 The output columns are: Column Type Description version integer GIN version number pending_pages integer Number of pages in the pending list pending_tuples bigint Number of tuples in the pending list pg_relpages pg_relpages(regclass) returns bigint pg_relpages returns the number of pages in the relation. pg_relpages(text) returns bigint This is the same as pg_relpages(regclass), except that the target relation is specified by TEXT. This function is kept because of backward-compatibility so far, and will be deprecated in the future release. Tatsuo Ishii and Satoshi Nagayasu