From 293913568e6a7a86fd1479e1cff8e2ecb58d6568 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 15:44:03 +0200 Subject: Adding upstream version 16.2. Signed-off-by: Daniel Baumann --- doc/src/sgml/pgstattuple.sgml | 629 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 629 insertions(+) create mode 100644 doc/src/sgml/pgstattuple.sgml (limited to 'doc/src/sgml/pgstattuple.sgml') diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml new file mode 100644 index 0000000..4071da4 --- /dev/null +++ b/doc/src/sgml/pgstattuple.sgml @@ -0,0 +1,629 @@ + + + + pgstattuple — obtain tuple-level statistics + + + pgstattuple + + + + The pgstattuple module provides various functions to + obtain tuple-level statistics. + + + + Because these functions return detailed page-level information, access is + restricted by default. By default, only the + role pg_stat_scan_tables has EXECUTE + privilege. Superusers of course bypass this restriction. After the + extension has been installed, users may issue GRANT + commands to change the privileges on the functions to allow others to + execute them. However, it might be preferable to add those users to + the pg_stat_scan_tables role instead. + + + + Functions + + + + + + pgstattuple + + 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.95 + + The output columns are described in . + + + + <function>pgstattuple</function> 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 + + + + +
+ + + + 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 + + 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 + + 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: + + + + + + Column + Type + Description + + + + + + version + integer + GIN version number + + + + pending_pages + integer + Number of pages in the pending list + + + + pending_tuples + bigint + Number of tuples in the pending list + + + + + + + + + + + + + pgstathashindex + + pgstathashindex(regclass) returns record + + + + + pgstathashindex returns a record showing information + about a HASH index. For example: + +test=> select * from pgstathashindex('con_hash_index'); +-[ RECORD 1 ]--+----------------- +version | 4 +bucket_pages | 33081 +overflow_pages | 0 +bitmap_pages | 1 +unused_pages | 32455 +live_items | 10204006 +dead_items | 0 +free_percent | 61.8005949100872 + + + + + The output columns are: + + + + + + Column + Type + Description + + + + + + version + integer + HASH version number + + + + bucket_pages + bigint + Number of bucket pages + + + + overflow_pages + bigint + Number of overflow pages + + + + bitmap_pages + bigint + Number of bitmap pages + + + + unused_pages + bigint + Number of unused pages + + + + live_items + bigint + Number of live tuples + + + + dead_tuples + bigint + Number of dead tuples + + + + free_percent + float + Percentage of free space + + + + + + + + + + + + + pg_relpages + + 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. + + + + + + + + pgstattuple_approx + + pgstattuple_approx(regclass) returns record + + + + + pgstattuple_approx is a faster alternative to + pgstattuple 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 | 573440 +scanned_percent | 2 +approx_tuple_count | 2740 +approx_tuple_len | 561210 +approx_tuple_percent | 97.87 +dead_tuple_count | 0 +dead_tuple_len | 0 +dead_tuple_percent | 0 +approx_free_space | 11996 +approx_free_percent | 2.09 + + The output columns are described in . + + + + Whereas pgstattuple 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). + + + + <function>pgstattuple_approx</function> Output Columns + + + + Column + Type + Description + + + + + + table_len + bigint + Physical relation length in bytes (exact) + + + scanned_percent + float8 + Percentage of table scanned + + + approx_tuple_count + bigint + Number of live tuples (estimated) + + + approx_tuple_len + bigint + Total length of live tuples in bytes (estimated) + + + approx_tuple_percent + float8 + Percentage of live tuples + + + dead_tuple_count + bigint + Number of dead tuples (exact) + + + dead_tuple_len + bigint + Total length of dead tuples in bytes (exact) + + + dead_tuple_percent + float8 + Percentage of dead tuples + + + approx_free_space + bigint + Total free space in bytes (estimated) + + + approx_free_percent + float8 + Percentage of free space + + + + +
+ + + In the above output, the free space figures may not match the + pgstattuple output exactly, because the free + space map gives us an exact figure, but is not guaranteed to be + accurate to the byte. + + + + + + + + + + Authors + + + Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen + + + + -- cgit v1.2.3