Postgres 12

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?.

select version();


version


PostgreSQL 12.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table instructor_schedule(
    instructor_schedule_id bigserial primary key,
    instructor_name varchar(255),
    course_name varchar(255),
    start_date timestamp with time zone
)
alter table instructor_schedule add constraint no_dupes unique(instructor_name, course_name, start_date)

insert into instructor_schedule (instructor_name, course_name, start_date) values
('bob', 'databases', '2015-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2018-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2024-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2021-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2022-01-01 00:00:00.000000 +00:00');
5 rows affected

with data
  as (
     select *,row_number() over(partition by instructor_name,course_name order by start_date desc) as rnk
       from instructor_schedule
      where start_date<=now()  --restrict records to entries which have occured in the past
     )
select *
  from data x
 where x.rnk=1 --as we are ranking using the earliest start_date rnk=1, gets you the latest entry record.  


instructor_schedule_id
instructor_name
course_name
start_date
rnk


5
alice
databases
2022-01-01 00:00:00+00
1

2
bob
databases
2018-01-01 00:00:00+00
1


select orig.* 
  from instructor_schedule orig 
  join (select max(start_date) as max_start_date,
                   course_name,
                   instructor_name
              from instructor_schedule as is1
             where start_date<=now()             
          group by course_name, 
                   instructor_name
        ) inner1
    on orig.course_name=inner1.course_name
   and orig.instructor_name=inner1.instructor_name
   and orig.start_date=inner1.max_start_date


instructor_schedule_id
instructor_name
course_name
start_date


2
bob
databases
2018-01-01 00:00:00+00

5
alice
databases
2022-01-01 00:00:00+00

instructor_schedule_id	instructor_name	course_name	start_date	rnk
5	alice	databases	2022-01-01 00:00:00+00	1
2	bob	databases	2018-01-01 00:00:00+00	1