pg_stats_extpg_statistic_ext 和 pg_statistic_ext_data 系统目录表中存储的信息的、安全且易读的访问。
pg_stats_ext 在 PostgreSQL 12 中引入。
按 PostgreSQL 版本定义
pg_stats_ext (PostgreSQL 19)
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 18)
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 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
分类
另请参阅
反馈
请在此处提交任何关于 "pg_stats_ext" 的评论、建议或更正。