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. 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 . 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. 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 higher leaf_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 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). 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. Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen