| PostgreSQL 9.4.1 Documentation | |||
|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules | Next |
F.30. pgstattuple
Thepgstattuple module provides various functions to obtain tuple-level statistics.
F.30.1. Functions
-
pgstattuple(regclass) returns record pgstattuplereturns 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 | 458752tuple_count | 1470tuple_len | 438896tuple_percent | 95.67dead_tuple_count | 11dead_tuple_len | 3157dead_tuple_percent | 0.69free_space | 8932free_percent | 1.95The output columns are described inTable F-21.
Table F-21.
pgstattupleOutput ColumnsColumn 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 pgstattupleacquires only a read lock on the relation. So the results do not reflect an instantaneous snapshot; concurrent updates will affect them.pgstattuplejudges a tuple is"dead" ifHeapTupleSatisfiesDirtyreturns false.pgstattuple(text) returns recordThis 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(regclass) returns record pgstatindexreturns a record showing information about a B-tree index. For example:test=> SELECT * FROM pgstatindex('pg_cast_oid_index');-[ RECORD 1 ]------+------version | 2tree_level | 0index_size | 8192root_block_no | 1internal_pages | 0leaf_pages | 1empty_pages | 0deleted_pages | 0avg_leaf_density | 50.27leaf_fragmentation | 0The 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 recordThis 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(regclass) returns record pgstatginindexreturns a record showing information about a GIN index. For example:test=> SELECT * FROM pgstatginindex('test_gin_index');-[ RECORD 1 ]--+--version | 1pending_pages | 0pending_tuples | 0The output columns are:
-
pg_relpages(regclass) returns bigint pg_relpagesreturns the number of pages in the relation.pg_relpages(text) returns bigintThis 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.
F.30.2. Authors
Tatsuo Ishii and Satoshi Nagayasu