Movatterモバイル変換

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          | 458752tuple_count        | 1470tuple_len          | 438896tuple_percent      | 95.67dead_tuple_count   | 11dead_tuple_len     | 3157dead_tuple_percent | 0.69free_space         | 8932free_percent       | 1.95

The output columns are described inTable F.24.

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” ifHeapTupleSatisfiesDirty returns false.

This is the same aspgstattuple(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 returns a record showing information about a B-tree index. For example:

test=> SELECT * FROM pgstatindex('pg_cast_oid_index');-[ RECORD 1 ]------+------version            | 2tree_level         | 0index_size         | 16384root_block_no      | 1internal_pages     | 0leaf_pages         | 1empty_pages        | 0deleted_pages      | 0avg_leaf_density   | 54.27leaf_fragmentation | 0

The output columns are:

The reportedindex_size will normally correspond to one more page than is accounted for byinternal_pages + leaf_pages + empty_pages + deleted_pages, because it also includes the index's metapage.avg_leaf_density is the fraction of the index size that is taken up by user data. Since indexes have a default fillfactor of 90, this should be around 90 for newly built indexes of non-negligible size, but usually deteriorates over time.leaf_fragmentation represents a measure of disorder. A higherleaf_fragmentation indicates that the physical order of the index leaf pages increasingly deviates from their logical order. This can have a significant impact if a large part of the index is read from disk.

As withpgstattuple, the results are accumulated page-by-page, and should not be expected to represent an instantaneous snapshot of the whole index.

This is the same aspgstatindex(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 returns a record showing information about a GIN index. For example:

test=> SELECT * FROM pgstatginindex('test_gin_index');-[ RECORD 1 ]--+--version        | 1pending_pages  | 0pending_tuples | 0

The output columns are:

pgstathashindex returns a record showing information about a HASH index. For example:

test=> select * from pgstathashindex('con_hash_index');-[ RECORD 1 ]--+-----------------version        | 4bucket_pages   | 33081overflow_pages | 0bitmap_pages   | 1unused_pages   | 32455live_items     | 10204006dead_items     | 0free_percent   | 61.8005949100872

The output columns are:

pg_relpages returns the number of pages in the relation.

This is the same aspg_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.

pgstattuple_approx is a faster alternative topgstattuple that returns approximate results. The argument is the target relation's name or OID. For example:

test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);-[ RECORD 1 ]--------+-------table_len            | 573440scanned_percent      | 2approx_tuple_count   | 2740approx_tuple_len     | 561210approx_tuple_percent | 97.87dead_tuple_count     | 0dead_tuple_len       | 0dead_tuple_percent   | 0approx_free_space    | 11996approx_free_percent  | 2.09

The output columns are described inTable F.25.

Whereaspgstattuple always performs a full-table scan and returns an exact count of live and dead tuples (and their sizes) and free space,pgstattuple_approx tries to avoid the full-table scan and returns exact dead tuple statistics along with an approximation of the number and size of live tuples and free space.

It does this by skipping pages that have only visible tuples according to the visibility map (if a page has the corresponding VM bit set, then it is assumed to contain no dead tuples). For such pages, it derives the free space value from the free space map, and assumes that the rest of the space on the page is taken up by live tuples.

For pages that cannot be skipped, it scans each tuple, recording its presence and size in the appropriate counters, and adding up the free space on the page. At the end, it estimates the total number of live tuples based on the number of pages and tuples scanned (in the same way that VACUUM estimates pg_class.reltuples).

In the above output, the free space figures may not match thepgstattuple output exactly, because the free space map gives us an exact figure, but is not guaranteed to be accurate to the byte.