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 domain_lifecycle (
domain varchar,
timestamp timestamp,
lifecycle varchar);
insert into domain_lifecycle values
('example_1.com', '2020-01-01 01:01:00', 'start'),
('example_1.com', '2020-01-01 01:01:05', 'end'),
('example_2.com', '2020-01-01 01:01:07', 'start'),
('example_2.com', '2020-01-01 01:01:17', 'end'),
('example_3.com', '2020-01-01 01:01:20', 'start'),
('example_1.com', '2020-01-01 01:01:25', 'start'),
('example_3.com', '2020-01-01 01:01:23', 'end'),
('example_1.com', '2020-01-01 01:01:38', 'end'),
('example_3.com', '2020-01-01 01:01:40', 'start'),
('example_3.com', '2020-01-01 01:01:42', 'end');
10 rows affected
SELECT DISTINCT domain
FROM domain_lifecycle dlc
WHERE dlc.lifecycle = 'start'
AND EXISTS
(SELECT 1
FROM
(SELECT MIN(TIMESTAMP) "end_time"
FROM domain_lifecycle dlc2
WHERE dlc.domain = dlc2.domain
AND dlc2.timestamp > dlc.timestamp
AND dlc2.lifecycle = 'end'
) t
WHERE extract(epoch from t.end_time - dlc.timestamp) >= 5
);
| domain |
|---|
| example_1.com |
| example_2.com |