| PostgreSQL 8.3.4 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:
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.
Tatsuo Ishii
No comments could be found for this page.
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.
In order to submit a comment, you must have a community account.
* denotes required field