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');
8 rows affected
SELECT DISTINCT
domain
FROM domain_lifecycle c1
CROSS JOIN LATERAL (SELECT min(timestamp) "timestamp"
FROM domain_lifecycle c2
WHERE c2.domain = c1.domain
AND c2.lifecycle = 'end'
AND c2.timestamp >= c1.timestamp) c3
WHERE c1.lifecycle = 'start'
AND c3.timestamp - c1.timestamp >= '5 seconds'::interval;
| domain |
|---|
| example_1.com |
| example_2.com |