Extensions
pgstattuple

pgstattuple

pgstattuple : show tuple-level statistics

Overview

ID Extension Package Version Category License Language
6970
pgstattuple
pgstattuple
 1.5
STAT
PostgreSQL
C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--s-d--
No
Yes
No
Yes
no
no
Relationships
See Also
pageinspect
pg_freespacemap
pg_visibility
pg_rewrite
pg_checksums
pg_catcheck
amcheck
toastinfo

Packages

PG18 PG17 PG16 PG15 PG14
1.5
1.5
1.5
1.5
1.5

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

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.

Last updated on
pg_visibilityauto_explain