Contents

pg_stats_ext

A system view providing access to the information stored in the underlying system statistics tables

pg_stats_ext is a system catalogue view providing safe and readable access to the information stored in the pg_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 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[] |           |          |

Documentation: 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[] |           |          |

Documentation: 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[] |           |          |

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

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 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 |

Categories

System catalogue

See also

pg_stats, pg_stats_ext_exprs