This page in other versions: 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 8.3 / 8.4

F.29. pgstattuple

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

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

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

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(text) returns bigint

pg_relpages returns the number of pages in the relation.

F.29.2. Authors

Tatsuo Ishii and Satoshi Nagayasu

Add Comment

Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.

Proceed to the comment form.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group