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