Postgresql 中文操作指南

F.33. pgstattuple — obtain tuple-level statistics #

pgstattuple 模块提供各种函数来获取元组级统计信息。

The pgstattuple module provides various functions to obtain tuple-level statistics.

由于这些函数返回详细的页面级信息，因此访问权限默认受限。默认情况下，只有 pg_stat_scan_tables 角色具有 EXECUTE 权限。当然，超级用户会绕过此限制。在安装扩展程序后，用户可以发出 GRANT 命令来更改函数上的权限，以允许其他人执行它们。但是，最好将这些用户添加到 pg_stat_scan_tables 角色中。

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.

F.33.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:

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 Table 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” 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:

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:

  • 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:

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

  • The output columns are:

    • 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:

    • 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:

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

  • 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).

  • 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.

Table F.24. pgstattuple Output Columns

Table F.24. 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 将始终大于 tuple_len、dead_tuple_len 和 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.

Table F.25. pgstattuple_approx Output Columns

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

F.33.2. Authors #

Tatsuo Ishii、Satoshi Nagayasu 和 Abhijit Menon-Sen

Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen