Postgres-XC 1.0.3 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Appendix F. Additional Supplied Modules | Fast Forward | Next |
Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly.
The pgstattuple module provides various functions to obtain tuple-level statistics.
Note: The following description applies only to Postgres-XC
Functions of this module returns information about connecting Coordinators locally. To get information of a Datanode, you can connect to the Datanode using psql directly. In this case, statements will be handled by local transaction control without GTM, you will have warnings and the visibility could be somewhat inconsistent.
pgstattuple(text) returns record
Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly.
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-20.
Table F-20. 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.
pg_relpages(text) returns bigint
pg_relpages
returns the number of pages in the
relation.
Tatsuo Ishii and Satoshi Nagayasu