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 2
CREATE TABLE
INSERT 0 6
| deviceid | Date | state | state_date |
|---|---|---|---|
| 1 | 2020-12-09 00:00:00 | L7 | 2020-12-05 15:18:00 |
| 1 | 2020-12-10 00:00:00 | L1 | 2020-12-10 00:15:00 |
| 1 | 2020-12-11 00:00:00 | L5 | 2020-12-11 15:00:00 |
| 1 | 2020-12-12 00:00:00 | L5 | 2020-12-11 15:00:00 |
| 2 | 2020-12-09 00:00:00 | L2 | 2020-12-08 14:00:00 |
| 2 | 2020-12-10 00:00:00 | L2 | 2020-12-08 14:00:00 |
| 2 | 2020-12-11 00:00:00 | L2 | 2020-12-08 14:00:00 |
| 2 | 2020-12-12 00:00:00 | L2 | 2020-12-08 14:00:00 |
SELECT 8