| PostgreSQL 9.4.11 Documentation | |||
|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules | Next |
The pgstattuple module provides various functions to obtain tuple-level statistics.
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.95The output columns are described in Table F-23.
Table F-23. 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 |
Note: The table_len will always be greater than the sum of the tuple_len, dead_tuple_len and free_space. The difference is accounted for by fixed page overhead, the per-page table of pointers to tuples, and padding to ensure that tuples are correctly aligned.
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 as TEXT. This function is kept
because of backward-compatibility so far, and will be deprecated in
some 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 | 16384
root_block_no | 1
internal_pages | 0
leaf_pages | 1
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 54.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 index size in bytes |
| root_block_no | bigint | Location of root page (zero if none) |
| 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 |
The reported index_size will normally correspond to one more page than is accounted for by internal_pages + leaf_pages + empty_pages + deleted_pages, because it also includes the index's metapage.
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 as TEXT. This function is kept
because of backward-compatibility so far, and will be deprecated in
some 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:
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 as TEXT. This function is kept
because of backward-compatibility so far, and will be deprecated in
some future release.
Tatsuo Ishii and Satoshi Nagayasu