postgres statistics table - ghdrako/doc_snipets GitHub Wiki

Postgres gathers statistics on tables when you run ANALYZE or when autovacuum runs automatically.

Postgres provides the pg_stats view, which shows a user-friendly version of the pg_statistics table. The pg_statistics table is optimized for disk space and isn’t easy for users to look at directly.

In Postgres, you have two types of statistics that the planner makes use of when it tries to understand how many rows will be matched by a particular query.

• Single column statistics - are always automatically collected. You don't have to do anything for that.
• Extended statistics - Postgres will not do this automatically

Extended statistics

define three types of stats on a column set: MCV (Most Common Values), ndistinct, and dependencies. Those are visible in pg_stat_ext

CREATE STATISTICS ON country,primary_fuel FROM power_plants;
ANALYZE; / ndistinct

create statistics s_ext_depend(dependencies) on a,b from ext_stats ;
analyze VERBOSE ext_stats ;
 explain analyze select * from ext_stats where a=1 and b=0;

postgres=# create statistics counties_cities_distinct (ndistinct)
 on state_code  , country_code, state_name, country_name
 from countries_cities;

postgres=# analyse countries_cities;