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?.
select version();


version


PostgreSQL 9.6.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE sessions
(
        id serial NOT NULL PRIMARY KEY, 
        ctn INT NOT NULL, 
        day DATE NOT NULL,
        f_time TIME(0) NOT NULL,
        l_time TIME(0) NOT  NULL,
        CONSTRAINT ft_lt_lt CHECK (f_time < l_time),
        -- CONSTRAINT ft_uq UNIQUE (f_time),
        -- CONSTRAINT lt_uq UNIQUE (l_time)
        CONSTRAINT ft_lt_uq UNIQUE(f_time, l_time) 
        -- could put in a DISTINCT somewhere to counter this possibility or
        -- maybe have TIME(2) to ensure no duplicates?
);
INSERT INTO sessions (id, ctn, day, f_time, l_time)
VALUES
( 1, 707, '2019-06-18', '10:48:25', '10:56:17'), -- note - same l_times
( 2, 707, '2019-06-18', '10:48:33', '10:56:17'), -- need one with lowest f_time
( 3, 707, '2019-06-18', '10:53:17', '11:00:49'),
( 4, 707, '2019-06-18', '10:54:31', '10:59:43'), -- note - same f_times
                                                 -- need one with greatest l_time
( 5, 707, '2019-06-18', '10:54:31', '10:57:37'), -- GROUP 1

( 6, 707, '2019-06-18', '11:03:59', '11:10:39'), 
( 7, 707, '2019-06-18', '11:04:41', '11:08:02'), -- GROUP 2, record 6 completely
                                                 -- covers record 7
( 8, 707, '2019-06-18', '11:11:04', '11:19:39'), -- GROUP 3

( 9, 707, '2019-06-18', '12:15:15', '13:13:13'),
(10, 707, '2019-06-18', '13:04:41', '13:20:02'), 
(11, 707, '2019-06-18', '13:17:17', '13:22:22'), -- GROUP 4

(12, 707, '2019-06-18', '14:05:17', '14:14:14'); -- GROUP 5
12 rows affected

SELECT * FROM sessions
ORDER BY l_time;  -- note id's have been rearranged


id
ctn
day
f_time
l_time


1
707
2019-06-18
10:48:25
10:56:17

2
707
2019-06-18
10:48:33
10:56:17

5
707
2019-06-18
10:54:31
10:57:37

4
707
2019-06-18
10:54:31
10:59:43

3
707
2019-06-18
10:53:17
11:00:49

7
707
2019-06-18
11:04:41
11:08:02

6
707
2019-06-18
11:03:59
11:10:39

8
707
2019-06-18
11:11:04
11:19:39

9
707
2019-06-18
12:15:15
13:13:13

10
707
2019-06-18
13:04:41
13:20:02

11
707
2019-06-18
13:17:17
13:22:22

12
707
2019-06-18
14:05:17
14:14:14


-- removes one duplicate f_time

SELECT
  DISTINCT 
  ctn,
  MIN(f_time) AS mt,
  l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time


ctn
mt
lt


707
10:48:25
10:56:17

707
10:54:31
10:57:37

707
10:54:31
10:59:43

707
10:53:17
11:00:49

707
11:04:41
11:08:02

707
11:03:59
11:10:39

707
11:11:04
11:19:39

707
12:15:15
13:13:13

707
13:04:41
13:20:02

707
13:17:17
13:22:22

707
14:05:17
14:14:14


-- removes one duplicate l_time
SELECT
  DISTINCT 
  ctn,
  f_time,
  MAX(l_time)
FROM sessions
GROUP BY ctn, f_time
ORDER BY f_time;


ctn
f_time
max


707
10:48:25
10:56:17

707
10:48:33
10:56:17

707
10:53:17
11:00:49

707
10:54:31
10:59:43

707
11:03:59
11:10:39

707
11:04:41
11:08:02

707
11:11:04
11:19:39

707
12:15:15
13:13:13

707
13:04:41
13:20:02

707
13:17:17
13:22:22

707
14:05:17
14:14:14


SELECT s.*, t.mt, t.lt
FROM sessions s
JOIN
(
  SELECT
    DISTINCT 
    ctn,
    MIN(f_time) AS mt,
    l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time
) AS t
ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
ORDER BY s.l_time;


id
ctn
day
f_time
l_time
mt
lt


1
707
2019-06-18
10:48:25
10:56:17
10:48:25
10:56:17

5
707
2019-06-18
10:54:31
10:57:37
10:54:31
10:57:37

4
707
2019-06-18
10:54:31
10:59:43
10:54:31
10:59:43

3
707
2019-06-18
10:53:17
11:00:49
10:53:17
11:00:49

7
707
2019-06-18
11:04:41
11:08:02
11:04:41
11:08:02

6
707
2019-06-18
11:03:59
11:10:39
11:03:59
11:10:39

8
707
2019-06-18
11:11:04
11:19:39
11:11:04
11:19:39

9
707
2019-06-18
12:15:15
13:13:13
12:15:15
13:13:13

10
707
2019-06-18
13:04:41
13:20:02
13:04:41
13:20:02

11
707
2019-06-18
13:17:17
13:22:22
13:17:17
13:22:22

12
707
2019-06-18
14:05:17
14:14:14
14:05:17
14:14:14


WITH cte1 AS 
(
SELECT s.*, t.mt, t.lt
FROM sessions s
JOIN
(
  SELECT
    DISTINCT 
    ctn,
    MIN(f_time) AS mt,
    l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time
) AS t
ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
ORDER BY s.l_time
)
SELECT c.* FROM cte1 c
ORDER BY c.l_time;


id
ctn
day
f_time
l_time
mt
lt


1
707
2019-06-18
10:48:25
10:56:17
10:48:25
10:56:17

5
707
2019-06-18
10:54:31
10:57:37
10:54:31
10:57:37

4
707
2019-06-18
10:54:31
10:59:43
10:54:31
10:59:43

3
707
2019-06-18
10:53:17
11:00:49
10:53:17
11:00:49

7
707
2019-06-18
11:04:41
11:08:02
11:04:41
11:08:02

6
707
2019-06-18
11:03:59
11:10:39
11:03:59
11:10:39

8
707
2019-06-18
11:11:04
11:19:39
11:11:04
11:19:39

9
707
2019-06-18
12:15:15
13:13:13
12:15:15
13:13:13

10
707
2019-06-18
13:04:41
13:20:02
13:04:41
13:20:02

11
707
2019-06-18
13:17:17
13:22:22
13:17:17
13:22:22

12
707
2019-06-18
14:05:17
14:14:14
14:05:17
14:14:14


WITH cte1 AS 
(
SELECT s.*, t.mt, t.lt
FROM sessions s
JOIN
(
  SELECT
    DISTINCT 
    ctn,
    MIN(f_time) AS mt,
    l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time
) AS t
ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
ORDER BY s.l_time
), 
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT * FROM cte2
ORDER BY ft;





ctn
ft
lt


707
10:48:25
10:56:17

707
10:53:17
11:00:49

707
10:54:31
10:59:43

707
11:03:59
11:10:39

707
11:04:41
11:08:02

707
11:11:04
11:19:39

707
12:15:15
13:13:13

707
13:04:41
13:20:02

707
13:17:17
13:22:22

707
14:05:17
14:14:14


WITH cte1 AS 
(
SELECT s.*, t.mt, t.lt
FROM sessions s
JOIN
(
  SELECT
    DISTINCT 
    ctn,
    MIN(f_time) AS mt,
    l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time
) AS t
ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
ORDER BY s.l_time
), 
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT 
  ROW_NUMBER() OVER () AS "Interval No.", 
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" , 
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop", 
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
SELECT 
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f, 
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
  s
FROM
(
SELECT 
  t1.ctn, t1.ft, t1.lt, t1.ovl,
  SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
FROM
(
SELECT 
  c.ctn AS ctn, c.ft AS ft, c.lt AS lt, 
  CASE
    WHEN LAG(c.lt) OVER () > ft THEN 0
    ELSE 1
  END AS ovl
FROM cte2 c
) AS t1
ORDER BY t1.lt
) AS t2
ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1
ORDER BY t3.rn;


Interval No.
 
Interval start
 
Interval stop
 
Duration


1
 Start time:
10:48:25
 End time:
11:00:49
 Duration:
00:12:24

2
 Start time:
11:03:59
 End time:
11:08:02
 Duration:
00:04:03

3
 Start time:
11:11:04
 End time:
11:19:39
 Duration:
00:08:35

4
 Start time:
12:15:15
 End time:
13:22:22
 Duration:
01:07:07

5
 Start time:
14:05:17
 End time:
14:14:14
 Duration:
00:08:57


EXPLAIN (ANALYZE, BUFFERS)
WITH cte1 AS 
(
SELECT s.*, t.mt, t.lt
FROM sessions s
JOIN
(
  SELECT
    DISTINCT 
    ctn,
    MIN(f_time) AS mt,
    l_time AS lt
  FROM sessions
  GROUP BY ctn, l_time
  ORDER BY l_time
) AS t
ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
ORDER BY s.l_time
), 
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT 
  ROW_NUMBER() OVER () AS "Interval No.", 
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" , 
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop", 
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
SELECT 
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f, 
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
  s
FROM
(
SELECT 
  t1.ctn, t1.ft, t1.lt, t1.ovl,
  SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
FROM
(
SELECT 
  c.ctn AS ctn, c.ft AS ft, c.lt AS lt, 
  CASE
    WHEN LAG(c.lt) OVER () > ft THEN 0
    ELSE 1
  END AS ovl
FROM cte2 c
) AS t1
ORDER BY t1.lt
) AS t2
ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1
ORDER BY t3.rn;


QUERY PLAN


WindowAgg  (cost=134.29..134.32 rows=1 width=144) (actual time=0.447..0.453 rows=5 loops=1)

  Buffers: shared hit=23

  CTE cte1

    ->  Nested Loop  (cost=45.17..134.02 rows=1 width=44) (actual time=0.085..0.167 rows=11 loops=1)

          Buffers: shared hit=23

          ->  Unique  (cost=45.02..47.02 rows=200 width=20) (actual time=0.048..0.095 rows=11 loops=1)

                Buffers: shared hit=1

                ->  Sort  (cost=45.02..45.52 rows=200 width=20) (actual time=0.048..0.064 rows=11 loops=1)

                      Sort Key: sessions.l_time, sessions.ctn, (min(sessions.f_time))

                      Sort Method: quicksort  Memory: 25kB

                      Buffers: shared hit=1

                      ->  HashAggregate  (cost=35.38..37.38 rows=200 width=20) (actual time=0.018..0.022 rows=11 loops=1)

                            Group Key: sessions.l_time, sessions.ctn

                            Buffers: shared hit=1

                            ->  Seq Scan on sessions  (cost=0.00..24.50 rows=1450 width=20) (actual time=0.008..0.009 rows=12 loops=1)

                                  Buffers: shared hit=1

          ->  Index Scan using ft_lt_uq on sessions s  (cost=0.15..0.42 rows=1 width=28) (actual time=0.003..0.004 rows=1 loops=11)

                Index Cond: ((f_time = (min(sessions.f_time))) AND (l_time = sessions.l_time))

                Filter: (sessions.ctn = ctn)

                Buffers: shared hit=22

  CTE cte2

    ->  Unique  (cost=0.05..0.06 rows=1 width=20) (actual time=0.214..0.221 rows=10 loops=1)

          Buffers: shared hit=23

          ->  Sort  (cost=0.05..0.05 rows=1 width=20) (actual time=0.214..0.215 rows=10 loops=1)

                Sort Key: cte1.f_time, cte1.ctn, (max(cte1.lt))

                Sort Method: quicksort  Memory: 25kB

                Buffers: shared hit=23

                ->  HashAggregate  (cost=0.03..0.04 rows=1 width=20) (actual time=0.185..0.187 rows=10 loops=1)

                      Group Key: cte1.f_time, cte1.ctn

                      Buffers: shared hit=23

                      ->  CTE Scan on cte1  (cost=0.00..0.02 rows=1 width=20) (actual time=0.087..0.174 rows=11 loops=1)

                            Buffers: shared hit=23

  ->  Subquery Scan on t3  (cost=0.21..0.23 rows=1 width=24) (actual time=0.434..0.437 rows=5 loops=1)

        Filter: (t3.rn = 1)

        Rows Removed by Filter: 5

        Buffers: shared hit=23

        ->  Sort  (cost=0.21..0.22 rows=1 width=48) (actual time=0.418..0.419 rows=10 loops=1)

              Sort Key: t2.s, (row_number() OVER (?)), (min(t2.ft) OVER (?))

              Sort Method: quicksort  Memory: 25kB

              Buffers: shared hit=23

              ->  WindowAgg  (cost=0.18..0.20 rows=1 width=48) (actual time=0.390..0.399 rows=10 loops=1)

                    Buffers: shared hit=23

                    ->  Sort  (cost=0.18..0.18 rows=1 width=40) (actual time=0.388..0.388 rows=10 loops=1)

                          Sort Key: t2.s, t2.ft DESC, t2.lt

                          Sort Method: quicksort  Memory: 25kB

                          Buffers: shared hit=23

                          ->  WindowAgg  (cost=0.14..0.17 rows=1 width=40) (actual time=0.357..0.366 rows=10 loops=1)

                                Buffers: shared hit=23

                                ->  Sort  (cost=0.14..0.15 rows=1 width=32) (actual time=0.355..0.356 rows=10 loops=1)

                                      Sort Key: t2.s, t2.ft, t2.lt DESC

                                      Sort Method: quicksort  Memory: 25kB

                                      Buffers: shared hit=23

                                      ->  WindowAgg  (cost=0.11..0.13 rows=1 width=32) (actual time=0.332..0.339 rows=10 loops=1)

                                            Buffers: shared hit=23

                                            ->  Sort  (cost=0.11..0.12 rows=1 width=24) (actual time=0.324..0.325 rows=10 loops=1)

                                                  Sort Key: t2.s

                                                  Sort Method: quicksort  Memory: 25kB

                                                  Buffers: shared hit=23

                                                  ->  Subquery Scan on t2  (cost=0.09..0.10 rows=1 width=24) (actual time=0.301..0.305 rows=10 loops=1)

                                                        Buffers: shared hit=23

                                                        ->  Sort  (cost=0.09..0.09 rows=1 width=32) (actual time=0.299..0.300 rows=10 loops=1)

                                                              Sort Key: t1.lt

                                                              Sort Method: quicksort  Memory: 25kB

                                                              Buffers: shared hit=23

                                                              ->  WindowAgg  (cost=0.06..0.07 rows=1 width=32) (actual time=0.274..0.281 rows=10 loops=1)

                                                                    Buffers: shared hit=23

                                                                    ->  Sort  (cost=0.06..0.06 rows=1 width=20) (actual time=0.267..0.269 rows=10 loops=1)

                                                                          Sort Key: t1.ft

                                                                          Sort Method: quicksort  Memory: 25kB

                                                                          Buffers: shared hit=23

                                                                          ->  Subquery Scan on t1  (cost=0.00..0.05 rows=1 width=20) (actual time=0.229..0.249 rows=10 loops=1)

                                                                                Buffers: shared hit=23

                                                                                ->  WindowAgg  (cost=0.00..0.04 rows=1 width=24) (actual time=0.229..0.245 rows=10 loops=1)

                                                                                      Buffers: shared hit=23

                                                                                      ->  CTE Scan on cte2 c  (cost=0.00..0.02 rows=1 width=16) (actual time=0.216..0.226 rows=10 loops=1)

                                                                                            Buffers: shared hit=23

Planning time: 0.394 ms

Execution time: 0.715 ms
id	ctn	day	f_time	l_time
1	707	2019-06-18	10:48:25	10:56:17
2	707	2019-06-18	10:48:33	10:56:17
5	707	2019-06-18	10:54:31	10:57:37
4	707	2019-06-18	10:54:31	10:59:43
3	707	2019-06-18	10:53:17	11:00:49
7	707	2019-06-18	11:04:41	11:08:02
6	707	2019-06-18	11:03:59	11:10:39
8	707	2019-06-18	11:11:04	11:19:39
9	707	2019-06-18	12:15:15	13:13:13
10	707	2019-06-18	13:04:41	13:20:02
11	707	2019-06-18	13:17:17	13:22:22
12	707	2019-06-18	14:05:17	14:14:14
Interval No.		Interval start		Interval stop		Duration
1	Start time:	10:48:25	End time:	11:00:49	Duration:	00:12:24
2	Start time:	11:03:59	End time:	11:08:02	Duration:	00:04:03
3	Start time:	11:11:04	End time:	11:19:39	Duration:	00:08:35
4	Start time:	12:15:15	End time:	13:22:22	Duration:	01:07:07
5	Start time:	14:05:17	End time:	14:14:14	Duration:	00:08:57