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 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_elems
  - most_common_elem_freqs
  - elem_count_histogram
PostgreSQL 9.0
- column inherited added (commit 649b5ec7)
PostgreSQL 7.3
- added (commit 16ea152b)

References

PostgreSQL documentation: pg_stats
PostgreSQL documentation: Single-Column Statistics

Version charts

Useful overviews

Useful links

Contents

pg_stats

Definition by PostgreSQL version

pg_stats (PostgreSQL 14)

pg_stats (PostgreSQL 13)

pg_stats (PostgreSQL 12)

pg_stats (PostgreSQL 11)

pg_stats (PostgreSQL 10)

pg_stats (PostgreSQL 9.6)

pg_stats (PostgreSQL 9.5)

pg_stats (PostgreSQL 9.4)

pg_stats (PostgreSQL 9.3)

pg_stats (PostgreSQL 9.2)

pg_stats (PostgreSQL 9.1)

pg_stats (PostgreSQL 9.0)

pg_stats (PostgreSQL 8.4)

pg_stats (PostgreSQL 8.3)

pg_stats (PostgreSQL 8.2)

Change history

References

Categories

See also