Postgres 10

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 dates(dte Date);

create index dte_idx on dates(dte);

insert into dates values (current_date);
insert into dates values (current_date + interval '1 day');
insert into dates values (current_date - interval '1 day');

create table timestamps(ts timestamp without time zone);

create index ts_idx on timestamps(ts);

insert into timestamps values (current_timestamp);
insert into timestamps values (current_timestamp + interval '1 day');
insert into timestamps values (current_timestamp + interval '12 hours');
insert into timestamps values (current_timestamp - interval '12 hours');
insert into timestamps values (current_timestamp - interval '1 day');

create table timestamps_tz(tstz timestamp with time zone);

create index tstz_idx on timestamps_tz(tstz);

insert into timestamps_tz values (current_timestamp);
insert into timestamps_tz values (current_timestamp + interval '24 hours');
insert into timestamps_tz values (current_timestamp + interval '12 hours');
insert into timestamps_tz values (current_timestamp - interval '12 hours');
insert into timestamps_tz values (current_timestamp - interval '24 hours');

CREATE TABLE

CREATE INDEX

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE

CREATE INDEX

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE

CREATE INDEX

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select * from dates 
where dte = current_date
;

dte

2022-12-16

SELECT 1

select * from timestamps order by ts;

ts

2022-12-15 15:13:42.913071

2022-12-16 03:13:42.913071

2022-12-16 15:13:42.913071

2022-12-17 03:13:42.913071

2022-12-17 15:13:42.913071

SELECT 5

select * from timestamps
where ts >= 'today'
and ts < 'tomorrow'
;

ts

2022-12-16 15:13:42.913071

2022-12-16 03:13:42.913071

SELECT 2

select * from timestamps_tz order by tstz;

tstz

2022-12-15 15:13:42.913071+00

2022-12-16 03:13:42.913071+00

2022-12-16 15:13:42.913071+00

2022-12-17 03:13:42.913071+00

2022-12-17 15:13:42.913071+00

SELECT 5

select * from timestamps_tz
where tstz >= 'today'
and tstz < 'tomorrow'
;

tstz

2022-12-16 15:13:42.913071+00

2022-12-16 03:13:42.913071+00

SELECT 2