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 account AS
Select 1111 as device_id Union All
Select 2222;
Select * From account;
SELECT 2
| device_id |
|---|
| 1111 |
| 2222 |
SELECT 2
CREATE TABLE water_consumption AS
Select 1111 as meter_id, To_Timestamp('09/10/2024 00:00', 'mm/dd/yyyy hh24:mi') as start_read_time_local, To_Timestamp('09/10/2024 01:00', 'mm/dd/yyyy hh24:mi') as end_read_time_local, 79118.91 as start_read , 79118.93 as end_read, 0.02 as consumption Union All
Select 1111, To_Timestamp('09/10/2024 03:00', 'mm/dd/yyyy hh24:mi'), To_Timestamp('09/10/2024 04:00', 'mm/dd/yyyy hh24:mi'), 79122.47, 79123.99, 1.52 Union All
Select 2222, To_Timestamp('09/10/2024 00:00', 'mm/dd/yyyy hh24:mi'), To_Timestamp('09/10/2024 01:00', 'mm/dd/yyyy hh24:mi'), 1937135, 1937174, 39 Union All
Select 2222, To_Timestamp('09/10/2024 01:00', 'mm/dd/yyyy hh24:mi'), To_Timestamp('09/10/2024 02:00', 'mm/dd/yyyy hh24:mi'), 1937174, 1937191, 17 Union All
Select 2222, To_Timestamp('09/10/2024 02:00', 'mm/dd/yyyy hh24:mi'), To_Timestamp('09/10/2024 03:00', 'mm/dd/yyyy hh24:mi'), 1937191, 1937197, 6 Union All
Select 2222, To_Timestamp('09/10/2024 03:00', 'mm/dd/yyyy hh24:mi'), To_Timestamp('09/10/2024 04:00', 'mm/dd/yyyy hh24:mi'), 1937197, 1937202, 5;
Select * From water_consumption;
SELECT 6
| meter_id | start_read_time_local | end_read_time_local | start_read | end_read | consumption |
|---|---|---|---|---|---|
| 1111 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 79118.91 | 79118.93 | 0.02 |
| 1111 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 79122.47 | 79123.99 | 1.52 |
| 2222 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 1937135 | 1937174 | 39 |
| 2222 | 2024-09-10 01:00:00+00 | 2024-09-10 02:00:00+00 | 1937174 | 1937191 | 17 |
| 2222 | 2024-09-10 02:00:00+00 | 2024-09-10 03:00:00+00 | 1937191 | 1937197 | 6 |
| 2222 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 1937197 | 1937202 | 5 |
SELECT 6
WITH
grid AS
( Select a.device_id as meter_id,
DATE_TRUNC('day', wc.start_read_time_local) + interval '1 minute' * (gs * 60) as read_start,
DATE_TRUNC('day', wc.start_read_time_local) + interval '1 minute' * ((gs + 1) * 60) as read_end,
Min(wc.start_read_time_local) as min_start_time,
Max(wc.start_read_time_local) as max_end_time
From account a
Inner Join water_consumption wc ON(wc.meter_id = a.device_id)
Cross Join generate_series(0, 23) gs
Group By a.device_id, gs, DATE_TRUNC( 'day', wc.start_read_time_local)
)
Select g.meter_id,
Coalesce(wc.start_read_time_local, g.read_start) as start_read_time_local,
Coalesce(wc.end_read_time_local, g.read_end) as end_read_time_local,
wc.start_read, wc.end_read, wc.consumption,
Case When wc.start_read_time_local Is Null Then 'Missing Data' End as notes
From grid g
Left Join water_consumption wc ON(wc.meter_id = g.meter_id And
wc.start_read_time_local = g.read_start )
Where read_start >= min_start_time And read_start <= max_end_time
Order By g.meter_id, g.read_start
| meter_id | start_read_time_local | end_read_time_local | start_read | end_read | consumption | notes |
|---|---|---|---|---|---|---|
| 1111 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 79118.91 | 79118.93 | 0.02 | null |
| 1111 | 2024-09-10 01:00:00+00 | 2024-09-10 02:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 02:00:00+00 | 2024-09-10 03:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 79122.47 | 79123.99 | 1.52 | null |
| 2222 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 1937135 | 1937174 | 39 | null |
| 2222 | 2024-09-10 01:00:00+00 | 2024-09-10 02:00:00+00 | 1937174 | 1937191 | 17 | null |
| 2222 | 2024-09-10 02:00:00+00 | 2024-09-10 03:00:00+00 | 1937191 | 1937197 | 6 | null |
| 2222 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 1937197 | 1937202 | 5 | null |
SELECT 8
WITH
grid AS
( Select a.device_id as meter_id,
DATE_TRUNC('day', wc.start_read_time_local) + interval '1 minute' * (gs * 60) as read_start,
DATE_TRUNC('day', wc.start_read_time_local) + interval '1 minute' * ((gs + 1) * 60) as read_end,
Min(wc.start_read_time_local) as min_start_time,
Max(wc.start_read_time_local) as max_end_time
From account a
Inner Join water_consumption wc ON(wc.meter_id = a.device_id)
Cross Join generate_series(0, 23) gs
Group By a.device_id, gs, DATE_TRUNC( 'day', wc.start_read_time_local)
)
Select g.meter_id,
Coalesce(wc.start_read_time_local, g.read_start) as start_read_time_local,
Coalesce(wc.end_read_time_local, g.read_end) as end_read_time_local,
wc.start_read, wc.end_read, wc.consumption,
Case When wc.start_read_time_local Is Null Then 'Missing Data' End as notes
From grid g
Left Join water_consumption wc ON(wc.meter_id = g.meter_id And
wc.start_read_time_local = g.read_start )
Order By g.meter_id, g.read_start
| meter_id | start_read_time_local | end_read_time_local | start_read | end_read | consumption | notes |
|---|---|---|---|---|---|---|
| 1111 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 79118.91 | 79118.93 | 0.02 | null |
| 1111 | 2024-09-10 01:00:00+00 | 2024-09-10 02:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 02:00:00+00 | 2024-09-10 03:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 79122.47 | 79123.99 | 1.52 | null |
| 1111 | 2024-09-10 04:00:00+00 | 2024-09-10 05:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 05:00:00+00 | 2024-09-10 06:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 06:00:00+00 | 2024-09-10 07:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 07:00:00+00 | 2024-09-10 08:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 08:00:00+00 | 2024-09-10 09:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 09:00:00+00 | 2024-09-10 10:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 10:00:00+00 | 2024-09-10 11:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 11:00:00+00 | 2024-09-10 12:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 12:00:00+00 | 2024-09-10 13:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 13:00:00+00 | 2024-09-10 14:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 14:00:00+00 | 2024-09-10 15:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 15:00:00+00 | 2024-09-10 16:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 16:00:00+00 | 2024-09-10 17:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 17:00:00+00 | 2024-09-10 18:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 18:00:00+00 | 2024-09-10 19:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 19:00:00+00 | 2024-09-10 20:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 20:00:00+00 | 2024-09-10 21:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 21:00:00+00 | 2024-09-10 22:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 22:00:00+00 | 2024-09-10 23:00:00+00 | null | null | null | Missing Data |
| 1111 | 2024-09-10 23:00:00+00 | 2024-09-11 00:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 00:00:00+00 | 2024-09-10 01:00:00+00 | 1937135 | 1937174 | 39 | null |
| 2222 | 2024-09-10 01:00:00+00 | 2024-09-10 02:00:00+00 | 1937174 | 1937191 | 17 | null |
| 2222 | 2024-09-10 02:00:00+00 | 2024-09-10 03:00:00+00 | 1937191 | 1937197 | 6 | null |
| 2222 | 2024-09-10 03:00:00+00 | 2024-09-10 04:00:00+00 | 1937197 | 1937202 | 5 | null |
| 2222 | 2024-09-10 04:00:00+00 | 2024-09-10 05:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 05:00:00+00 | 2024-09-10 06:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 06:00:00+00 | 2024-09-10 07:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 07:00:00+00 | 2024-09-10 08:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 08:00:00+00 | 2024-09-10 09:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 09:00:00+00 | 2024-09-10 10:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 10:00:00+00 | 2024-09-10 11:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 11:00:00+00 | 2024-09-10 12:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 12:00:00+00 | 2024-09-10 13:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 13:00:00+00 | 2024-09-10 14:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 14:00:00+00 | 2024-09-10 15:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 15:00:00+00 | 2024-09-10 16:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 16:00:00+00 | 2024-09-10 17:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 17:00:00+00 | 2024-09-10 18:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 18:00:00+00 | 2024-09-10 19:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 19:00:00+00 | 2024-09-10 20:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 20:00:00+00 | 2024-09-10 21:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 21:00:00+00 | 2024-09-10 22:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 22:00:00+00 | 2024-09-10 23:00:00+00 | null | null | null | Missing Data |
| 2222 | 2024-09-10 23:00:00+00 | 2024-09-11 00:00:00+00 | null | null | null | Missing Data |
SELECT 48