Postgres 13

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 tbl (Hero text, timestamp timestamp);
INSERT INTO tbl VALUES
  ('Batman'      , '2016-12-08 12:00:00')
, ('Batman'      , '2016-12-08 12:07:00')
, ('Batman'      , '2016-12-08 13:00:00')
, ('Batman'      , '2016-12-08 14:00:00')
, ('Wonder Woman', '2016-12-08 10:15:00')
, ('Wonder Woman', '2016-12-08 10:18:00')
, ('Wonder Woman', '2016-12-08 10:25:00')
, ('Wonder Woman', '2016-12-08 11:30:00');

CREATE TABLE



INSERT 0 8


-- 10 minute raster
SELECT hero
     , count(*) AS ct
     , min(timestamp)::text AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp)::text END AS end_time
FROM   tbl
GROUP  BY hero
     , date_trunc('hour', timestamp)
     , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 3;


hero
ct
start_time
end_time


Batman
2
2016-12-08 12:00:00
2016-12-08 12:07:00

Batman
1
2016-12-08 13:00:00
null

Batman
1
2016-12-08 14:00:00
null

Wonder Woman
2
2016-12-08 10:15:00
2016-12-08 10:18:00

Wonder Woman
1
2016-12-08 10:25:00
null

Wonder Woman
1
2016-12-08 11:30:00
null



SELECT 6


-- gaps of 10 minutes define groups
SELECT hero
     , count(*) AS ct  -- optional
     , min(timestamp)::text AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp)::text END AS end_time
FROM  (
   SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp
   FROM  (
      SELECT *
           , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
           < timestamp - interval '10 min' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY hero, grp
ORDER  BY hero, grp;


hero
ct
start_time
end_time


Batman
2
2016-12-08 12:00:00
2016-12-08 12:07:00

Batman
1
2016-12-08 13:00:00
null

Batman
1
2016-12-08 14:00:00
null

Wonder Woman
3
2016-12-08 10:15:00
2016-12-08 10:25:00

Wonder Woman
1
2016-12-08 11:30:00
null



SELECT 5

hero	ct	start_time	end_time
Batman	2	2016-12-08 12:00:00	2016-12-08 12:07:00
Batman	1	2016-12-08 13:00:00	null
Batman	1	2016-12-08 14:00:00	null
Wonder Woman	2	2016-12-08 10:15:00	2016-12-08 10:18:00
Wonder Woman	1	2016-12-08 10:25:00	null
Wonder Woman	1	2016-12-08 11:30:00	null