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?.
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE
INSERT 0 121
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 2
| sensor_id | shift | occurence_id | status | status_id | start_date | end_date | reading_start | reading_end |
|---|---|---|---|---|---|---|---|---|
| 5 | Shift A | 123 | stationary | 8 | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:59+00 | 2 | 121 |
| 5 | Shift B | 124 | stationary | 8 | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:29+00 | 61 | 90 |
| 5 | Shift B | 124 | in_motion | 9 | 2020-01-01 00:01:30+00 | 2020-01-01 00:01:44+00 | 91 | 105 |
| 5 | Shift B | 124 | stationary | 10 | 2020-01-01 00:01:45+00 | 2020-01-01 00:01:59+00 | 106 | 120 |
SELECT 4
| sensor_id | shift | occurence_id | status | status_id | start_date | end_date | reading_start | reading_end | reading_min | reading_max |
|---|---|---|---|---|---|---|---|---|---|---|
| 5 | Shift A | 123 | stationary | 8 | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:59+00 | 121 | 60 | 2 | 121 |
| 5 | Shift B | 124 | stationary | 8 | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:29+00 | 61 | 90 | 61 | 90 |
| 5 | Shift B | 124 | in_motion | 9 | 2020-01-01 00:01:30+00 | 2020-01-01 00:01:44+00 | 91 | 105 | 91 | 105 |
| 5 | Shift B | 124 | stationary | 10 | 2020-01-01 00:01:45+00 | 2020-01-01 00:01:59+00 | 106 | 120 | 106 | 120 |
SELECT 4