F.30. pgstattuple
pgstattuple 模組提供各種函數來取得 tuple 層級的統計資訊。
由於這些函數會回傳詳細的 page-level 資訊,因此預設是限制存取的。 預設情況下,只有角色 pg_stat_scan_tables 具有 EXECUTE 權限。超級使用者當然可以繞過此限制。安裝此延伸功能後,使用者可以發出 GRANT 指令來授予函數的權限,以允許其他人執行它們。但是,最好還是將這些使用者加到 pg_stat_scan_tables 角色群組之中。

F.30.1. Functions

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:
1
test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
2
-[ RECORD 1 ]------+-------
3
table_len | 458752
4
tuple_count | 1470
5
tuple_len | 438896
6
tuple_percent | 95.67
7
dead_tuple_count | 11
8
dead_tuple_len | 3157
9
dead_tuple_percent | 0.69
10
free_space | 8932
11
free_percent | 1.95
Copied!
The output columns are described in Table F.22.

Table F.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

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(regclass) returns record
pgstatindex returns a record showing information about a B-tree index. For example:
1
test=> SELECT * FROM pgstatindex('pg_cast_oid_index');
2
-[ RECORD 1 ]------+------
3
version | 2
4
tree_level | 0
5
index_size | 16384
6
root_block_no | 1
7
internal_pages | 0
8
leaf_pages | 1
9
empty_pages | 0
10
deleted_pages | 0
11
avg_leaf_density | 54.27
12
leaf_fragmentation | 0
Copied!
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(regclass) returns record
pgstatginindex returns a record showing information about a GIN index. For example:
1
test=> SELECT * FROM pgstatginindex('test_gin_index');
2
-[ RECORD 1 ]--+--
3
version | 1
4
pending_pages | 0
5
pending_tuples | 0
Copied!
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:
1
test=> select * from pgstathashindex('con_hash_index');
2
-[ RECORD 1 ]--+-----------------
3
version | 4
4
bucket_pages | 33081
5
overflow_pages | 0
6
bitmap_pages | 1
7
unused_pages | 32455
8
live_items | 10204006
9
dead_items | 0
10
free_percent | 61.8005949100872
Copied!
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(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(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:
1
test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
2
-[ RECORD 1 ]--------+-------
3
table_len | 573440
4
scanned_percent | 2
5
approx_tuple_count | 2740
6
approx_tuple_len | 561210
7
approx_tuple_percent | 97.87
8
dead_tuple_count | 0
9
dead_tuple_len | 0
10
dead_tuple_percent | 0
11
approx_free_space | 11996
12
approx_free_percent | 2.09
Copied!
The output columns are described in Table F.23.
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).

Table F.23. pgstattuple_approx 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.

F.30.2. Authors

Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen
Export as PDF
Copy link
Edit on GitHub