curl -s https://raw.githubusercontent.com/gvenzl/sample-data/master/countries-cities-currencies/install.sql | psql
psql
-- how many countries with more than 1000 people per square meter?
select name,region_id,population,area_sq_km,population/area_sq_km,population
from countries where population/area_sq_km > 1000
;
-- what's the query planner estimation?
explain
select name,region_id,population,area_sq_km,population/area_sq_km,population
from countries where population/area_sq_km > 1000
;
-- estimated to 65 rows. 10x overestimated
-- can be bad with futher joins :(
-- in PG 14 we can create extended statistics for that:
create statistics countries_population_per_area_sq_km
on (population/area_sq_km) from countries
;
explain select name,region_id,population,area_sq_km,population/area_sq_km,population
from countries where population/area_sq_km > 1000
;
-- nothing changed but let's analyze...
analyze countries
;
-- try again:
explain select name,region_id,population,area_sq_km,population/area_sq_km,population
from countries where population/area_sq_km > 1000
;
-- great :) estimation of 6 rows thanks to histograms:
\x
select * from pg_stats_ext_exprs;
\q
