pgstattuple

show tuple-level statistics

https://www.postgresql.org/docs/current/pgstattuple.html

Overview

Package	Version	Category	License	Language
`pgstattuple`	`1.5`	STAT	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
6970	`pgstattuple`	No	Yes	No	Yes	No	No	-

Related	`pg_repack` `pg_squeeze` `pg_rewrite` `pg_dirtyread` `pg_column_tetris` `toastinfo` `pageinspect` `pagevis` `pg_freespacemap` `pg_buffercache`

Version

PG18	PG17	PG16	PG15	PG14
1.5	1.5	1.5	1.5	1.5

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION pgstattuple;

Usage

pgstattuple: tuple-level statistics

pgstattuple provides functions to obtain tuple-level statistics for tables and indexes, including dead tuple counts and free space metrics.

Table Statistics

SELECT * FROM pgstattuple('my_table');

 table_len          | 81920
 tuple_count        | 1000
 tuple_len          | 72000
 tuple_percent      | 87.89
 dead_tuple_count   | 50
 dead_tuple_len     | 3600
 dead_tuple_percent | 4.39
 free_space         | 6320
 free_percent       | 7.71

Approximate (faster) version using visibility map and FSM:

SELECT * FROM pgstattuple_approx('my_table');
-- Returns: table_len, scanned_percent, approx_tuple_count, approx_tuple_len,
--          dead_tuple_count, dead_tuple_len, approx_free_space, ...

B-Tree Index Statistics

SELECT * FROM pgstatindex('my_index');

 version            | 4
 tree_level         | 2
 index_size         | 1327104
 root_block_no      | 3
 internal_pages     | 1
 leaf_pages         | 160
 empty_pages        | 0
 deleted_pages      | 0
 avg_leaf_density   | 89.27
 leaf_fragmentation | 0

GIN Index Statistics

SELECT * FROM pgstatginindex('my_gin_index');
-- Returns: version, pending_pages, pending_tuples

Hash Index Statistics

SELECT * FROM pgstathashindex('my_hash_index');
-- Returns: version, bucket_pages, overflow_pages, bitmap_pages,
--          unused_pages, live_items, dead_tuples, free_percent

Page Count

SELECT pg_relpages('my_table');

Access

Restricted to superusers and roles with pg_stat_scan_tables privileges.