Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
insert into measurement
values 
(1, date '2006-02-05', 1, 1),
(2, date '2006-03-06', 2, 2);
2 rows affected

select *, tableoid::regclass
from measurement;


city_id
logdate
peaktemp
unitsales
tableoid


1
2006-02-05
1
1
measurement_y2006m02

2
2006-03-06
2
2
measurement_y2006m03

city_id	logdate	peaktemp	unitsales	tableoid
1	2006-02-05	1	1	measurement_y2006m02
2	2006-03-06	2	2	measurement_y2006m03