pg_stats 是一个提供对 pg_statistic 非超级用户访问权限的系统目录视图，并且以更易读的格式显示了 pg_statistic 中包含的信息。
pg_stats 添加于 PostgreSQL 7.3。
按 PostgreSQL 版本定义
pg_stats (PostgreSQL 17)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
range_length_histogram | anyarray | | |
range_empty_frac | real | | |
range_bounds_histogram | anyarray | | |
文档: pg_stats
pg_stats (PostgreSQL 16)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 15)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 14)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 13)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 12)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 11)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 10)
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
attname | name | | |
inherited | boolean | | |
null_frac | real | | |
avg_width | integer | | |
n_distinct | real | | |
most_common_vals | anyarray | | |
most_common_freqs | real[] | | |
histogram_bounds | anyarray | | |
correlation | real | | |
most_common_elems | anyarray | | |
most_common_elem_freqs | real[] | | |
elem_count_histogram | real[] | | |
文档: pg_stats
pg_stats (PostgreSQL 9.6)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
文档: pg_stats
pg_stats (PostgreSQL 9.5)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
文档: pg_stats
pg_stats (PostgreSQL 9.4)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
文档: pg_stats
pg_stats (PostgreSQL 9.3)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
文档: pg_stats
pg_stats (PostgreSQL 9.2)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
文档: pg_stats
pg_stats (PostgreSQL 9.1)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
-------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
文档: pg_stats
pg_stats (PostgreSQL 9.0)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
-------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
文档: pg_stats
pg_stats (PostgreSQL 8.4)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
-------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
CASE
WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stavalues1
WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stavalues2
WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stavalues3
WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stavalues4
ELSE NULL::anyarray
END AS most_common_vals,
CASE
WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stanumbers1
WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stanumbers2
WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stanumbers3
WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stanumbers4
ELSE NULL::real[]
END AS most_common_freqs,
CASE
WHEN s.stakind1 = 2 THEN s.stavalues1
WHEN s.stakind2 = 2 THEN s.stavalues2
WHEN s.stakind3 = 2 THEN s.stavalues3
WHEN s.stakind4 = 2 THEN s.stavalues4
ELSE NULL::anyarray
END AS histogram_bounds,
CASE
WHEN s.stakind1 = 3 THEN s.stanumbers1[1]
WHEN s.stakind2 = 3 THEN s.stanumbers2[1]
WHEN s.stakind3 = 3 THEN s.stanumbers3[1]
WHEN s.stakind4 = 3 THEN s.stanumbers4[1]
ELSE NULL::real
END AS correlation
FROM pg_statistic s
JOIN pg_class c ON c.oid = s.starelid
JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text);
文档: pg_stats
pg_stats (PostgreSQL 8.3)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
-------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
CASE 1
WHEN s.stakind1 THEN s.stavalues1
WHEN s.stakind2 THEN s.stavalues2
WHEN s.stakind3 THEN s.stavalues3
WHEN s.stakind4 THEN s.stavalues4
ELSE NULL::anyarray
END AS most_common_vals,
CASE 1
WHEN s.stakind1 THEN s.stanumbers1
WHEN s.stakind2 THEN s.stanumbers2
WHEN s.stakind3 THEN s.stanumbers3
WHEN s.stakind4 THEN s.stanumbers4
ELSE NULL::real[]
END AS most_common_freqs,
CASE 2
WHEN s.stakind1 THEN s.stavalues1
WHEN s.stakind2 THEN s.stavalues2
WHEN s.stakind3 THEN s.stavalues3
WHEN s.stakind4 THEN s.stavalues4
ELSE NULL::anyarray
END AS histogram_bounds,
CASE 3
WHEN s.stakind1 THEN s.stanumbers1[1]
WHEN s.stakind2 THEN s.stanumbers2[1]
WHEN s.stakind3 THEN s.stanumbers3[1]
WHEN s.stakind4 THEN s.stanumbers4[1]
ELSE NULL::real
END AS correlation
FROM pg_statistic s
JOIN pg_class c ON c.oid = s.starelid
JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE has_table_privilege(c.oid, 'select'::text);
文档: pg_stats
pg_stats (PostgreSQL 8.2)
View "pg_catalog.pg_stats"
Column | Type | Modifiers
-------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
CASE 1
WHEN s.stakind1 THEN s.stavalues1
WHEN s.stakind2 THEN s.stavalues2
WHEN s.stakind3 THEN s.stavalues3
WHEN s.stakind4 THEN s.stavalues4
ELSE NULL::anyarray
END AS most_common_vals,
CASE 1
WHEN s.stakind1 THEN s.stanumbers1
WHEN s.stakind2 THEN s.stanumbers2
WHEN s.stakind3 THEN s.stanumbers3
WHEN s.stakind4 THEN s.stanumbers4
ELSE NULL::real[]
END AS most_common_freqs,
CASE 2
WHEN s.stakind1 THEN s.stavalues1
WHEN s.stakind2 THEN s.stavalues2
WHEN s.stakind3 THEN s.stavalues3
WHEN s.stakind4 THEN s.stavalues4
ELSE NULL::anyarray
END AS histogram_bounds,
CASE 3
WHEN s.stakind1 THEN s.stanumbers1[1]
WHEN s.stakind2 THEN s.stanumbers2[1]
WHEN s.stakind3 THEN s.stanumbers3[1]
WHEN s.stakind4 THEN s.stanumbers4[1]
ELSE NULL::real
END AS correlation
FROM pg_statistic s
JOIN pg_class c ON c.oid = s.starelid
JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE has_table_privilege(c.oid, 'select'::text);
文档: pg_stats
更改历史记录
- PostgreSQL 17
- 添加了以下列 (提交 bc3c8db8)
range_length_histogramrange_empty_fracrange_bounds_histogram
- 添加了以下列 (提交 bc3c8db8)
- PostgreSQL 9.2
- 添加了以下列 (提交 0e5e167a)
most_common_elemsmost_common_elem_freqselem_count_histogram
- 添加了以下列 (提交 0e5e167a)
- PostgreSQL 9.0
- 添加了列
inherited(提交 649b5ec7)
- 添加了列
- PostgreSQL 7.3
- 添加 (提交 16ea152b)
示例
来自 pg_stats 的示例行
postgres=# SELECT * FROM pg_stats where tablename = 'foo' AND attname='id';
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | foo
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.86
most_common_vals | {8,22,23,48,69,74}
most_common_freqs | {0.06,0.04,0.04,0.04,0.04,0.04}
histogram_bounds | {4,5,9,13,14,15,17,18,19,26,32,33,34,37,39,40,45,47,49,54,55,56,60,61,70,71,77,78,79,80,82,85,88,91,95,98,99}
correlation | 0.4055222
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |