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 tmp (
   date    timestamp UNIQUE NOT NULL
 , id_type integer NOT NULL
) ;

CREATE TABLE


INSERT INTO tmp (date, id_type)
VALUES
    ( '2017-01-10 07:19:21.0', 3 ),
    ( '2017-01-10 07:19:22.0', 3 ),
    ( '2017-01-10 07:19:23.1', 3 ),
    ( '2017-01-10 07:19:24.1', 3 ),
    ( '2017-01-10 07:19:25.0', 3 ),
    ( '2017-01-10 07:19:26.0', 5 ),
    ( '2017-01-10 07:19:27.1', 3 ),
    ( '2017-01-10 07:19:28.0', 5 ),
    ( '2017-01-10 07:19:29.0', 5 ),
    ( '2017-01-10 07:19:30.1', 3 ),
    ( '2017-01-10 07:19:31.0', 5 ),
    ( '2017-01-10 07:19:32.0', 3 ),
    ( '2017-01-10 07:19:33.1', 5 ),
    ( '2017-01-10 07:19:35.0', 5 ),
    ( '2017-01-10 07:19:36.1', 5 ),
    ( '2017-01-10 07:19:37.1', 5 );

INSERT 0 16


INSERT INTO tmp(date, id_type)
SELECT date + interval '1 day' * g, id_type
FROM   tmp, generate_series(1,8000) g;

INSERT 0 128000


VACUUM ANALYZE tmp;

VACUUM


CREATE OR REPLACE FUNCTION f_tmp_groups()
  RETURNS TABLE (id_type int, grp_begin timestamp, grp_end timestamp) AS
$func$
DECLARE
   _row  tmp;                       -- use table type for row variable
BEGIN
   FOR _row IN
      TABLE tmp ORDER BY date       -- add more columns to make order deterministic
   LOOP
      CASE _row.id_type = id_type 
      WHEN TRUE THEN                -- same group continues
         grp_end := _row.date;      -- remember last date so far
      WHEN FALSE THEN               -- next group starts
         RETURN NEXT;               -- return result for last group
         id_type   := _row.id_type;
         grp_begin := _row.date;
         grp_end   := _row.date;
      ELSE                          -- NULL for 1st row
         id_type   := _row.id_type; -- remember row data for starters
         grp_begin := _row.date;
         grp_end   := _row.date;
      END CASE;
   END LOOP;

   RETURN NEXT;                     -- return last result row      
END
$func$ LANGUAGE plpgsql;

CREATE FUNCTION


-- 1.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT id_type
     , min(date) AS begin
     , max(date) AS end
FROM  (
   SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp
   FROM  (
      SELECT date, id_type
           , lag(id_type) OVER (ORDER BY date) <> id_type AS step
      FROM   tmp
      ) sub1
   ) sub2
GROUP  BY id_type, grp
ORDER  BY min(date);


QUERY PLAN


Sort  (cost=12045.82..12046.82 rows=400 width=28) (actual rows=64008 loops=1)

  Sort Key: (min(tmp.date))

  Sort Method: external merge  Disk: 2640kB

  ->  HashAggregate  (cost=12024.53..12028.53 rows=400 width=28) (actual rows=64008 loops=1)

        Group Key: tmp.id_type, count((((lag(tmp.id_type) OVER (?) <> tmp.id_type)) OR NULL::boolean)) OVER (?)

        Batches: 5  Memory Usage: 4145kB  Disk Usage: 3680kB

        ->  WindowAgg  (cost=0.29..9464.21 rows=128016 width=20) (actual rows=128016 loops=1)

              ->  WindowAgg  (cost=0.29..6263.81 rows=128016 width=13) (actual rows=128016 loops=1)

                    ->  Index Scan using tmp_date_key on tmp  (cost=0.29..4023.53 rows=128016 width=12) (actual rows=128016 loops=1)

Planning Time: 0.536 ms

Execution Time: 540.065 ms



EXPLAIN


-- 2.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT min(date) AS begin
     , max(date) AS end
     , id_type
FROM  (
   SELECT date, id_type
        , row_number() OVER (ORDER BY date)
        - row_number() OVER (PARTITION BY id_type ORDER BY date) AS grp
   FROM   tmp
   ) sub
GROUP  BY id_type, grp
ORDER  BY min(date);


QUERY PLAN


Sort  (cost=36213.06..36214.06 rows=400 width=28) (actual rows=64008 loops=1)

  Sort Key: (min(tmp.date))

  Sort Method: external merge  Disk: 2640kB

  ->  HashAggregate  (cost=36191.77..36195.77 rows=400 width=28) (actual rows=64008 loops=1)

        Group Key: tmp.id_type, (row_number() OVER (?) - (row_number() OVER (?)))

        Batches: 5  Memory Usage: 4401kB  Disk Usage: 3664kB

        ->  WindowAgg  (cost=31071.13..33631.45 rows=128016 width=20) (actual rows=128016 loops=1)

              ->  Sort  (cost=31071.13..31391.17 rows=128016 width=20) (actual rows=128016 loops=1)

                    Sort Key: tmp.date

                    Sort Method: external merge  Disk: 4264kB

                    ->  WindowAgg  (cost=15022.73..17583.05 rows=128016 width=20) (actual rows=128016 loops=1)

                          ->  Sort  (cost=15022.73..15342.77 rows=128016 width=12) (actual rows=128016 loops=1)

                                Sort Key: tmp.id_type, tmp.date

                                Sort Method: external merge  Disk: 2768kB

                                ->  Seq Scan on tmp  (cost=0.00..1972.16 rows=128016 width=12) (actual rows=128016 loops=1)

Planning Time: 0.207 ms

Execution Time: 441.764 ms



EXPLAIN


-- 3.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM f_tmp_groups();


QUERY PLAN


Function Scan on f_tmp_groups  (cost=0.25..10.25 rows=1000 width=20) (actual rows=64008 loops=1)

Planning Time: 0.039 ms

Execution Time: 123.466 ms



EXPLAIN