pg_stats
A system catalogue view providing non-superuser access to pg_statistic
pg_stats is a system catalogue view providing non-superuser access to pg_statistic, and also presents the information contained in pg_statistic in a more readable format.
pg_stats was added in PostgreSQL 7.3.
Definition by PostgreSQL version
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[] | | |
Documentation: 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[] | | |
Documentation: 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[] | | |
Documentation: 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[] | | |
Documentation: 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[] | | |
Documentation: 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[] | | |
Documentation: 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[] |
Documentation: 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[] |
Documentation: 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[] |
Documentation: 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[] |
Documentation: 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[] |
Documentation: 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 |
Documentation: 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 |
Documentation: 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);
Documentation: 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);
Documentation: 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);
Documentation: pg_stats
Change history
- PostgreSQL 9.2
- following columns added (commit 0e5e167a):
most_common_elemsmost_common_elem_freqselem_count_histogram
- following columns added (commit 0e5e167a):
- PostgreSQL 9.0
- column
inheritedadded (commit 649b5ec7)
- column
- PostgreSQL 7.3
- added (commit 16ea152b)
Examples
Sample row from 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 |
References
- PostgreSQL documentation: pg_stats
- PostgreSQL documentation: Single-Column Statistics