pg_stats_extpg_statistic_ext 和 pg_statistic_ext_data 系统目录表中的信息的安全且易于阅读的访问。
pg_stats_ext 添加于 PostgreSQL 12。
按 PostgreSQL 版本定义
pg_stats_ext (PostgreSQL 17)
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[] | | |
文档: pg_stats_ext
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[] | | |
文档: 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[] | | |
文档: 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[] | | |
文档: 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[] | | |
文档: 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[] | | |
文档: pg_stats_ext
更改历史记录
- PostgreSQL 15
- 添加了列
inherited(提交 269b532a)
- 添加了列
- PostgreSQL 14
- 添加了列
exprs(提交 a4d75c86)
- 添加了列
- PostgreSQL 12
- 添加 (提交 aa087ec6)
示例
使用 CREATE STATISTICS 文档 中显示的示例表和统计信息，基础表 pg_statistic_ext 和 pg_statistic_ext_data 包含以下值
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 提供了包含在视图中的信息的更人性化的组合
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 |
参考文献
- PostgreSQL 文档： pg_stats_ext