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 mytable
("PersonId" int, "Role" text, "TeamId" text, "EffectiveDate" date)
;

INSERT INTO mytable VALUES
    (0001813, 'admin', '005aba1ec', '2019-05-01 00:00:00'),
    (0001813, 'scrum master', '005aba1ec', '2019-05-01 00:00:00'),
    (0001813, 'team captain', '005aba1ec', '2019-05-01 00:00:00'),
    (0001813, 'admin', '005aba1ec', '2019-06-01 00:00:00'),
    (0001813, 'scrum master', '005aba1ec', '2019-06-01 00:00:00'),
    (0001813, 'team captain', '005aba1ec', '2019-06-01 00:00:00'),
    (0001813, 'delivery lead', '005aba1ec', '2019-06-01 00:00:00'),
    (0002817, 'product lead', '007aba338', '2019-07-01 00:00:00'),
    (0002817, 'finance partner', '007aba338', '2019-07-01 00:00:00'),
    (0002817, 'individual contributor', '007aba338', '2019-07-01 00:00:00'),
    (0002817, 'product lead', '007aba338', '2019-08-01 00:00:00'),
    (0002817, 'finance partner', '007aba338', '2019-08-01 00:00:00'),
    (0002817, 'individual contributor', '007aba338', '2019-08-01 00:00:00'),
    (0002817, 'admin', '007aba338', '2019-08-01 00:00:00');
    
SELECT * FROM mytable

CREATE TABLE



INSERT 0 14




PersonId
Role
TeamId
EffectiveDate


1813
admin
005aba1ec
2019-05-01

1813
scrum master
005aba1ec
2019-05-01

1813
team captain
005aba1ec
2019-05-01

1813
admin
005aba1ec
2019-06-01

1813
scrum master
005aba1ec
2019-06-01

1813
team captain
005aba1ec
2019-06-01

1813
delivery lead
005aba1ec
2019-06-01

2817
product lead
007aba338
2019-07-01

2817
finance partner
007aba338
2019-07-01

2817
individual contributor
007aba338
2019-07-01

2817
product lead
007aba338
2019-08-01

2817
finance partner
007aba338
2019-08-01

2817
individual contributor
007aba338
2019-08-01

2817
admin
007aba338
2019-08-01



SELECT 14


SELECT 
    *,
    MAX("EffectiveDate") OVER 
        (PARTITION BY "PersonId" ORDER BY "EffectiveDate" GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM mytable


PersonId
Role
TeamId
EffectiveDate
max


1813
admin
005aba1ec
2019-05-01
2019-06-01

1813
scrum master
005aba1ec
2019-05-01
2019-06-01

1813
team captain
005aba1ec
2019-05-01
2019-06-01

1813
admin
005aba1ec
2019-06-01
null

1813
scrum master
005aba1ec
2019-06-01
null

1813
team captain
005aba1ec
2019-06-01
null

1813
delivery lead
005aba1ec
2019-06-01
null

2817
product lead
007aba338
2019-07-01
2019-08-01

2817
finance partner
007aba338
2019-07-01
2019-08-01

2817
individual contributor
007aba338
2019-07-01
2019-08-01

2817
product lead
007aba338
2019-08-01
null

2817
finance partner
007aba338
2019-08-01
null

2817
individual contributor
007aba338
2019-08-01
null

2817
admin
007aba338
2019-08-01
null



SELECT 14


SELECT 
    *,
    COALESCE (
        MAX("EffectiveDate") OVER 
             (PARTITION BY "PersonId" ORDER BY "EffectiveDate" GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
        ,
        '9999-12-31 23:59:59'
    )
FROM mytable


PersonId
Role
TeamId
EffectiveDate
coalesce


1813
admin
005aba1ec
2019-05-01
2019-06-01

1813
scrum master
005aba1ec
2019-05-01
2019-06-01

1813
team captain
005aba1ec
2019-05-01
2019-06-01

1813
admin
005aba1ec
2019-06-01
9999-12-31

1813
scrum master
005aba1ec
2019-06-01
9999-12-31

1813
team captain
005aba1ec
2019-06-01
9999-12-31

1813
delivery lead
005aba1ec
2019-06-01
9999-12-31

2817
product lead
007aba338
2019-07-01
2019-08-01

2817
finance partner
007aba338
2019-07-01
2019-08-01

2817
individual contributor
007aba338
2019-07-01
2019-08-01

2817
product lead
007aba338
2019-08-01
9999-12-31

2817
finance partner
007aba338
2019-08-01
9999-12-31

2817
individual contributor
007aba338
2019-08-01
9999-12-31

2817
admin
007aba338
2019-08-01
9999-12-31



SELECT 14

PersonId	Role	TeamId	EffectiveDate
1813	admin	005aba1ec	2019-05-01
1813	scrum master	005aba1ec	2019-05-01
1813	team captain	005aba1ec	2019-05-01
1813	admin	005aba1ec	2019-06-01
1813	scrum master	005aba1ec	2019-06-01
1813	team captain	005aba1ec	2019-06-01
1813	delivery lead	005aba1ec	2019-06-01
2817	product lead	007aba338	2019-07-01
2817	finance partner	007aba338	2019-07-01
2817	individual contributor	007aba338	2019-07-01
2817	product lead	007aba338	2019-08-01
2817	finance partner	007aba338	2019-08-01
2817	individual contributor	007aba338	2019-08-01
2817	admin	007aba338	2019-08-01