Supported Versions: Current (15) / 14 / 13 / 12 / 11
Development Versions: devel
Unsupported versions: 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

F.24. pgstattuple

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

F.24.1. Functions

pgstattuple(text) 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). 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:

Table F-27. pgstattuple output columns

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 HeapTupleSatisfiesNow returns false.

pgstattuple(oid) returns record

This is the same as pgstattuple(text), except that the target relation is specified by OID.

pgstatindex(text) returns record

pgstatindex returns a record showing information about a btree 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:

Table F-28. pgstatindex output columns

Column Type Description
version integer Btree version number
tree_level integer Tree level of the root page
index_size integer Total number of pages in index
root_block_no integer Location of root block
internal_pages integer Number of "internal" (upper-level) pages
leaf_pages integer Number of leaf pages
empty_pages integer Number of empty pages
deleted_pages integer 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.

pg_relpages(text) returns integer

pg_relpages returns the number of pages in the relation.

F.24.2. Author

Tatsuo Ishii