Postgres 9.6

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 time_intervals (
   "user" text,
   start_time timestamp without time zone,
   end_time timestamp without time zone
);

INSERT INTO time_intervals VALUES
('user1', '2018-09-26T02:16:52.023453', '2018-09-26T03:12:04.404477'),
('user1', '2018-09-25T22:15:49.593296', '2018-09-26T00:15:52.016497'),
('user1', '2018-09-25T20:13:02.358192', '2018-09-25T22:15:49.593296');

SELECT * FROM time_intervals;


user
start_time
end_time


user1
2018-09-26 02:16:52.023453
2018-09-26 03:12:04.404477

user1
2018-09-25 22:15:49.593296
2018-09-26 00:15:52.016497

user1
2018-09-25 20:13:02.358192
2018-09-25 22:15:49.593296



SELECT 3


    SELECT 
        "user",
        lag(end_time) OVER (PARTITION BY "user" ORDER BY start_time) as prev_end_time,
        start_time,
        end_time
    FROM time_intervals 
    ORDER BY start_time


user
prev_end_time
start_time
end_time


user1
null
2018-09-25 20:13:02.358192
2018-09-25 22:15:49.593296

user1
2018-09-25 22:15:49.593296
2018-09-25 22:15:49.593296
2018-09-26 00:15:52.016497

user1
2018-09-26 00:15:52.016497
2018-09-26 02:16:52.023453
2018-09-26 03:12:04.404477



SELECT 3


SELECT
    "user",
    prev_end_time as gap_start_time,
    start_time as gap_end_time
FROM (
    SELECT 
        *, 
        lag(end_time) OVER (PARTITION BY "user" ORDER BY start_time) as prev_end_time
    FROM time_intervals 
    ORDER BY start_time
) s
WHERE start_time > prev_end_time


user
gap_start_time
gap_end_time


user1
2018-09-26 00:15:52.016497
2018-09-26 02:16:52.023453



SELECT 1

user	start_time	end_time
user1	2018-09-26 02:16:52.023453	2018-09-26 03:12:04.404477
user1	2018-09-25 22:15:49.593296	2018-09-26 00:15:52.016497
user1	2018-09-25 20:13:02.358192	2018-09-25 22:15:49.593296

user	prev_end_time	start_time	end_time
user1	null	2018-09-25 20:13:02.358192	2018-09-25 22:15:49.593296
user1	2018-09-25 22:15:49.593296	2018-09-25 22:15:49.593296	2018-09-26 00:15:52.016497
user1	2018-09-26 00:15:52.016497	2018-09-26 02:16:52.023453	2018-09-26 03:12:04.404477