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

2024-08-19

SELECT 1

select * from timestamps order by ts;

ts

2024-08-18 14:55:03.564251

2024-08-19 02:55:03.564251

2024-08-19 14:55:03.564251

2024-08-20 02:55:03.564251

2024-08-20 14:55:03.564251

SELECT 5

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

ts

2024-08-19 14:55:03.564251

2024-08-19 02:55:03.564251

SELECT 2

select * from timestamps_tz order by tstz;

tstz

2024-08-18 14:55:03.564251+01

2024-08-19 02:55:03.564251+01

2024-08-19 14:55:03.564251+01

2024-08-20 02:55:03.564251+01

2024-08-20 14:55:03.564251+01

SELECT 5

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

tstz

2024-08-19 14:55:03.564251+01

2024-08-19 02:55:03.564251+01

SELECT 2