pg_stats_ext
A system view providing access to the information stored in the underlying system statistics tables
pg_stats_extpg_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 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[] | | |
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 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 much 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