F.30. pgstattuple

The pgstattuple module provides various functions to obtain tuple-level statistics.

F.30.1. Functions

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

Table F-21. pgstattuple Output Columns

ColumnTypeDescription
table_lenbigintPhysical relation length in bytes
tuple_countbigintNumber of live tuples
tuple_lenbigintTotal length of live tuples in bytes
tuple_percentfloat8Percentage of live tuples
dead_tuple_countbigintNumber of dead tuples
dead_tuple_lenbigintTotal length of dead tuples in bytes
dead_tuple_percentfloat8Percentage of dead tuples
free_spacebigintTotal free space in bytes
free_percentfloat8Percentage 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(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:

ColumnTypeDescription
versionintegerB-tree version number
tree_levelintegerTree level of the root page
index_sizebigintTotal number of pages in index
root_block_nobigintLocation of root block
internal_pagesbigintNumber of "internal" (upper-level) pages
leaf_pagesbigintNumber of leaf pages
empty_pagesbigintNumber of empty pages
deleted_pagesbigintNumber of deleted pages
avg_leaf_densityfloat8Average density of leaf pages
leaf_fragmentationfloat8Leaf 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(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:

ColumnTypeDescription
versionintegerGIN version number
pending_pagesintegerNumber of pages in the pending list
pending_tuplesbigintNumber of tuples in the pending list

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.

F.30.2. Authors

Tatsuo Ishii and Satoshi Nagayasu