PostgreSQL 8.4.22 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Appendix F. Additional Supplied Modules | Fast Forward | Next |
The pgstattuple module provides various functions to obtain tuple-level statistics.
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-25.
Table F-25. 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-26. pgstatindex
output columns
Column | Type | Description |
---|---|---|
version | integer | Btree 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.
pg_relpages(text) returns
bigint
pg_relpages
returns the
number of pages in the relation.
Tatsuo Ishii and Satoshi Nagayasu
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.