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?.
--https://stackoverflow.com/q/78582498/5298879
create table period_of_hours("timestamp", "key")as values
('2024-05-06 10:00:00'::timestamp,'009eae73-0b55-3809-9938-4bc0a342e451'::text)
,('2024-05-06 11:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 12:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 13:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 14:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 15:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 17:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 18:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 19:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 20:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 21:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-07 00:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-07 01:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-07 03:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-07 04:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-07 05:00:00','009eae73-0b55-3809-9938-4bc0a342e451')
,('2024-05-06 10:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 11:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 12:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 13:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 14:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 15:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 17:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 18:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 19:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 20:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
,('2024-05-06 21:00:00','fc85b307-15d6-369b-86b5-a4aa19871dff')
SELECT 32
with continuous("timestamp") as (
select '2024-05-06 10:00:00'::timestamp+n*'1 hour'::interval
from generate_series( 0
,(select extract(epoch from max("timestamp")
-min("timestamp"))/3600
from period_of_hours
where "key"='009eae73-0b55-3809-9938-4bc0a342e451')
,1)as n)
select c."timestamp" from continuous c
left join period_of_hours p
on c."timestamp"=p."timestamp"
and p."key"='009eae73-0b55-3809-9938-4bc0a342e451'
where p."timestamp" is null;
| timestamp |
|---|
| 2024-05-06 16:00:00 |
| 2024-05-06 22:00:00 |
| 2024-05-06 23:00:00 |
| 2024-05-07 02:00:00 |
SELECT 4
select "timestamp"+n*'1h'::interval as "timestamp"
from (select *,extract(epoch from (lead("timestamp")over w1)-"timestamp")
/3600 as "diff"
from period_of_hours
where "key"='009eae73-0b55-3809-9938-4bc0a342e451'
window w1 as(order by "timestamp") )_
cross join lateral generate_series(1,"diff"-1,1) as n
where "diff">1;
| timestamp |
|---|
| 2024-05-06 16:00:00 |
| 2024-05-06 22:00:00 |
| 2024-05-06 23:00:00 |
| 2024-05-07 02:00:00 |
SELECT 4
select "key","timestamp"+n*'1h'::interval as "timestamp"
from (select *,extract(epoch from (lead("timestamp")over w1)-"timestamp")
/3600 as "diff"
from period_of_hours
window w1 as(partition by "key" order by "timestamp") )_
cross join lateral generate_series(1,"diff"-1,1) as n
where "diff">1;
| key | timestamp |
|---|---|
| 009eae73-0b55-3809-9938-4bc0a342e451 | 2024-05-06 16:00:00 |
| 009eae73-0b55-3809-9938-4bc0a342e451 | 2024-05-06 22:00:00 |
| 009eae73-0b55-3809-9938-4bc0a342e451 | 2024-05-06 23:00:00 |
| 009eae73-0b55-3809-9938-4bc0a342e451 | 2024-05-07 02:00:00 |
| fc85b307-15d6-369b-86b5-a4aa19871dff | 2024-05-06 16:00:00 |
| fc85b307-15d6-369b-86b5-a4aa19871dff | 2024-05-06 22:00:00 |
| fc85b307-15d6-369b-86b5-a4aa19871dff | 2024-05-06 23:00:00 |
| fc85b307-15d6-369b-86b5-a4aa19871dff | 2024-05-07 02:00:00 |
SELECT 8