pg_stats_ext
A system view providing access to the information stored in the underlying system statistics tables
is a system catalogue view providing safe and readable access to the information stored in the pg_stats_ext
pg_statistic_ext
and pg_statistic_ext_data
system catalogue tables.
pg_stats_ext
was added in PostgreSQL 12.
Definition by PostgreSQL version
pg_stats_ext (PostgreSQL 16)
View "pg_catalog.pg_stats_ext" Column | Type | Collation | Nullable | Default ------------------------+--------------------+-----------+----------+--------- schemaname | name | | | tablename | name | | | statistics_schemaname | name | | | statistics_name | name | | | statistics_owner | name | | | attnames | name[] | | | exprs | text[] | | | kinds | "char"[] | | | inherited | boolean | | | n_distinct | pg_ndistinct | C | | dependencies | pg_dependencies | C | | most_common_vals | text[] | | | most_common_val_nulls | boolean[] | | | most_common_freqs | double precision[] | | | most_common_base_freqs | double precision[] | | |
Documentation: pg_stats_ext
pg_stats_ext (PostgreSQL 15)
View "pg_catalog.pg_stats_ext" Column | Type | Collation | Nullable | Default ------------------------+--------------------+-----------+----------+--------- schemaname | name | | | tablename | name | | | statistics_schemaname | name | | | statistics_name | name | | | statistics_owner | name | | | attnames | name[] | | | exprs | text[] | | | kinds | "char"[] | | | inherited | boolean | | | n_distinct | pg_ndistinct | C | | dependencies | pg_dependencies | C | | most_common_vals | text[] | | | most_common_val_nulls | boolean[] | | | most_common_freqs | double precision[] | | | most_common_base_freqs | double precision[] | | |
Documentation: pg_stats_ext
pg_stats_ext (PostgreSQL 14)
View "pg_catalog.pg_stats_ext" Column | Type | Collation | Nullable | Default ------------------------+--------------------+-----------+----------+--------- schemaname | name | | | tablename | name | | | statistics_schemaname | name | | | statistics_name | name | | | statistics_owner | name | | | attnames | name[] | | | exprs | text[] | | | kinds | "char"[] | | | n_distinct | pg_ndistinct | C | | dependencies | pg_dependencies | C | | most_common_vals | text[] | | | most_common_val_nulls | boolean[] | | | most_common_freqs | double precision[] | | | most_common_base_freqs | double precision[] | | |
Documentation: pg_stats_ext
pg_stats_ext (PostgreSQL 13)
View "pg_catalog.pg_stats_ext" Column | Type | Collation | Nullable | Default ------------------------+--------------------+-----------+----------+--------- schemaname | name | | | tablename | name | | | statistics_schemaname | name | | | statistics_name | name | | | statistics_owner | name | | | attnames | name[] | | | kinds | "char"[] | | | n_distinct | pg_ndistinct | C | | dependencies | pg_dependencies | C | | most_common_vals | text[] | | | most_common_val_nulls | boolean[] | | | most_common_freqs | double precision[] | | | most_common_base_freqs | double precision[] | | |
Documentation: pg_stats_ext
pg_stats_ext (PostgreSQL 12)
View "pg_catalog.pg_stats_ext" Column | Type | Collation | Nullable | Default ------------------------+--------------------+-----------+----------+--------- schemaname | name | | | tablename | name | | | statistics_schemaname | name | | | statistics_name | name | | | statistics_owner | name | | | attnames | name[] | | | kinds | "char"[] | | | n_distinct | pg_ndistinct | C | | dependencies | pg_dependencies | C | | most_common_vals | text[] | | | most_common_val_nulls | boolean[] | | | most_common_freqs | double precision[] | | | most_common_base_freqs | double precision[] | | |
Documentation: pg_stats_ext
Change history
- PostgreSQL 15
- column
inherited
added (commit 269b532a)
- column
- PostgreSQL 14
- column
exprs
added (commit a4d75c86)
- column
- PostgreSQL 12
- added (commit aa087ec6)
Examples
Using the sample table and stastics shown the CREATE STATISTICS documentation, the base tables pg_statistic_ext and pg_statistic_ext_data contain following values:
postgres=# SELECT * FROM pg_statistic_ext WHERE stxrelid = 't1'::regclass; oid | stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxkind -------+----------+---------+--------------+----------+---------+--------- 16458 | 16455 | s1 | 16390 | 10 | 1 2 | {f} (1 row) postgres=# SELECT * FROM pg_statistic_ext_data WHERE stxoid = 16458; stxoid | stxdndistinct | stxddependencies | stxdmcv --------+---------------+----------------------+--------- 16458 | | {"1 => 2": 1.000000} | (1 row)
pg_stats_ext
provides a user-friendlier combination of the information contained into a view:
postgres=# SELECT * FROM pg_stats_ext WHERE tablename = 't1'; -[ RECORD 1 ]----------+--------------------- schemaname | postgres tablename | t1 statistics_schemaname | postgres statistics_name | s1 statistics_owner | postgres attnames | {a,b} kinds | {f} n_distinct | dependencies | {"1 => 2": 1.000000} most_common_vals | most_common_val_nulls | most_common_freqs | most_common_base_freqs |
References
- PostgreSQL documentation: pg_stats_ext