pgstattuple
The pgstattuple module provides various functions to obtain tuple-level statistics.
Since these functions return detailed page-level information, default access is restricted. By default, only the pg_stat_scan_tables role has EXECUTE privilege. Superusers can bypass this restriction, of course. After the extension is installed, users can issue GRANT commands to change the privileges on the functions to allow other users to execute them. However, it is preferable to add those users to the pg_stat_scan_tables role.
1. Functions
pgstattuple(regclass) returns record
pgstattuple returns a relation's physical length, percentage of tuples, and other information. This can help users determine whether a vacuum is needed.
The argument is the name of the target relation (optionally schema-qualified) or its OID. For example:
test=## SELECT * FROM pgstattuple('pg_catalog.pg_proc');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
1589248 | 4423 | 1053235 | 66.27 | 235 | 53752 | 3.38 | 441192 | 27.76
(1 row)
The output columns are described in Table C.22.
Table C.22. 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 |
| Note: table_len will always be greater than the sum of tuple_len, dead_tuple_len, and free_space. The difference is due to fixed page overhead, per-page pointer tables pointing to tuples, and padding to ensure proper tuple alignment. |
|---|
pgstattuple requires only a read lock on the relation. Therefore, the results do not reflect an instantaneous snapshot, and concurrent updates may affect the results.
pgstattuple judges a tuple to be "dead" if HeapTupleSatisfiesDirty returns false.
pgstattuple(text) returns record
Same as pgstattuple(regclass), except that the target relation is specified by TEXT. This function is retained only for backward compatibility and will be deprecated in a future release.
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');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------
4 | 0 | 16384 | 1 | 0 | 1 | 0 | 0 | 66.78 | 0
(1 row)
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 the 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 typically corresponds to internal_pages + leaf_pages + empty_pages + deleted_pages plus one, because it also includes the index metapage.
As with pgstattuple, the results are accumulated page by page and should not be expected to represent an instantaneous snapshot of the entire index.
pgstatindex(text) returns record — Same as pgstatindex(regclass), except that the target index is specified by TEXT. This function is retained only for backward compatibility and will be deprecated in a future release.
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(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 fields are:
| Field | 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(regclass) returns bigint
pg_relpages returns the number of pages in a relation.
pg_relpages(text) returns bigint
Same as pg_relpages(regclass), except that the target relation is specified by TEXT. This function is retained only for backward compatibility.
pgstattuple_approx(regclass) returns record
pgstattuple_approx is a faster alternative to pgstattuple that returns approximate results. The argument is the OID or name of the target relation. For example:
test=## SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent
-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
1589248 | 22 | 4467 | 1084306 | 68.22761456990979 | 235 | 53752 | 3.382228576030928 | 439564 | 27.658615898840207
(1 row)
The output columns are described in Table C.23.
Whereas pgstattuple always performs a full table scan and returns exact counts, sizes, and free space for live and dead tuples, pgstattuple_approx tries to avoid a full table scan and returns exact statistics for dead tuples, and approximate counts and sizes for live tuples and free space.
This function achieves this by skipping pages that contain only visible tuples according to the visibility map (if a page's corresponding VM bit is set, it means it does not contain dead tuples). For such pages, it obtains the free space value from the free space map and assumes the remaining space on the page is occupied by live tuples.
For pages that cannot be skipped, it scans each tuple, records its presence and size in the appropriate counters, and tallies the free space on the page. Finally, it estimates the total number of live tuples based on the number of scanned pages and tuples (using the same method that VACUUM uses to estimate pg_class.reltuples).
Table C.23. pgstattuple_approx Output Columns
| Column | Type | Description |
|---|---|---|
| table_len | bigint | Physical relation length in bytes (exact) |
| scanned_percent | float8 | Percentage of the 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 exactly match the output of pgstattuple, because the free space map gives a figure that is not guaranteed to be an exact byte count.