Postgres 9.6

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 TEMPORARY TABLE temp_Patients 
(
    cfirstname character(30) COLLATE pg_catalog."default" NOT NULL,
    ddob       date
);
INSERT INTO temp_Patients (cfirstname, ddob)
VALUES ('Frank', '1980-05-05'),
       ('John', '1972-01-12'),
       ('Joe', '1990-12-02'),
       ('Alan', '2020-05-01');

do
$$
    declare
        jdStart date;
        jdEnd   date;
    begin
        jdStart = '2019-12-01';
        jdEnd = '2020-01-31';

create temporary table results on commit drop as
        SELECT cfirstname
             , dDob
             , dDob + date_trunc('year', age(jdStart, dDob)) as date1
             , dDob + date_trunc('year', age(jdEnd, dDob)) as date2
        FROM temp_Patients
 
        WHERE ddob is NOT null
          AND (
                dDob + date_trunc('year', age(jdStart, dDob))
                    BETWEEN jdStart AND jdEnd
                OR 
                dDob + date_trunc('year', age(jdEnd, dDob))
                    BETWEEN jdStart AND jdEnd
            )

ORDER BY cfirstname;
    end;
$$;

select * from results;

drop table temp_Patients;

4 rows affected

cfirstname	ddob	date1	date2
Joe	1990-12-02	2018-12-02 00:00:00	2019-12-02 00:00:00
John	1972-01-12	2019-01-12 00:00:00	2020-01-12 00:00:00