F.30. pgstattuple

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 Table F-21.

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.

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 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:

As with pgstattuple, the results are accumulated page-by-page, and should not be expected to represent an instantaneous snapshot of the whole index.

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 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:

pg_relpages returns the number of pages in the relation.

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.