Postgres 14

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?.
-- tiny test table from question
CREATE TABLE measures (dt timestamp, measure integer);

INSERT INTO measures VALUES
  ('2015-01-13 12:05', 10)
, ('2015-01-13 12:30', 8)
, ('2015-01-13 13:02', 16) 
, ('2015-01-13 13:30', 12) 
, ('2015-01-13 14:15', 7)
;


-- big table to test performance
CREATE TABLE big (dt timestamp, measure integer);

INSERT INTO big
SELECT '2012-01-01 0:0'::timestamp + sum(i) OVER (ORDER BY g), (random() * 100)::int
FROM  (
   SELECT g, interval '1h' * random()^4 AS i
   FROM   generate_series(0,100000) g
   ) g;

-- crucial index
CREATE INDEX big_foo_idx ON big (dt, measure);

CREATE TABLE



INSERT 0 5



CREATE TABLE



INSERT 0 100001



CREATE INDEX


VACUUM ANALYZE big;

VACUUM


-- tiny test
WITH RECURSIVE cte AS (
   SELECT t.dt, m.measure
   FROM  (SELECT dt FROM measures ORDER BY 1 LIMIT 1) t -- no lower bound
   JOIN   measures m ON m.dt < t.dt + interval '1h'  -- excl. upper bound

   UNION ALL
   SELECT t.dt, m.measure
   FROM  (
      SELECT m.dt
      FROM  (SELECT dt FROM cte LIMIT 1) c
      JOIN   measures m ON m.dt >= c.dt + interval '1h'
      ORDER  BY 1
      LIMIT  1
      ) t
   JOIN   measures m ON m.dt >= t.dt                 -- incl. lower bound
                    AND m.dt <  t.dt + interval '1h' -- excl. upper bound
   )
SELECT dt AS hour_start
     , round(avg(measure), 2) AS avg_measure, count(*) AS ct
FROM   cte
GROUP  BY 1
ORDER  BY 1;


hour_start
avg_measure
ct


2015-01-13 12:05:00
11.33
3

2015-01-13 13:30:00
9.50
2



SELECT 2


-- big test for a random day period
WITH RECURSIVE cte AS (
   SELECT t.dt, m.measure
   FROM  (SELECT dt FROM big
          WHERE  dt >= '2013-02-02' ORDER BY 1 LIMIT 1) t -- outer incl. lower bound 
   JOIN   big m ON m.dt >= t.dt                  -- incl. lower bound
               AND m.dt <  t.dt + interval '1h'  -- excl. upper bound

   UNION ALL
   SELECT t.dt, m.measure
   FROM  (
      SELECT m.dt
      FROM  (SELECT dt FROM cte LIMIT 1) c
      JOIN   big m ON m.dt >= c.dt + interval '1h'
                  AND m.dt <  '2013-02-03' -- outer excl. upper bound 
      ORDER  BY 1
      LIMIT  1
      ) t
   JOIN   big m ON m.dt >= t.dt                 -- incl. lower bound
               AND m.dt <  t.dt + interval '1h' -- excl. upper bound
   )
SELECT dt AS hour_start, round(avg(measure), 2) AS avg_measure, count(*) AS ct
FROM   cte
GROUP  BY 1
ORDER  BY 1;


hour_start
avg_measure
ct


2013-02-02 00:24:13.415018
42.29
7

2013-02-02 02:10:29.911286
52.10
10

2013-02-02 03:32:20.678757
53.50
2

2013-02-02 04:38:21.876093
75.60
5

2013-02-02 05:50:38.257584
39.25
8

2013-02-02 07:33:56.001093
81.40
5

2013-02-02 08:39:56.515722
42.20
5

2013-02-02 09:45:23.93757
49.00
8

2013-02-02 11:19:38.011161
36.50
4

2013-02-02 12:36:20.554662
71.50
4

2013-02-02 13:49:01.742429
31.20
5

2013-02-02 15:07:02.899753
55.44
9

2013-02-02 16:20:50.059154
50.14
7

2013-02-02 17:27:16.140006
33.00
11

2013-02-02 18:38:51.990508
38.22
9

2013-02-02 19:58:24.975064
56.50
8

2013-02-02 21:03:42.29511
56.23
13

2013-02-02 22:04:39.360148
63.67
6

2013-02-02 23:04:41.869558
53.79
14



SELECT 19


CREATE OR REPLACE FUNCTION f_dynamic_hourly_avg()
  RETURNS TABLE(hour_start timestamp, avg_measure numeric, ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
    _cursor CURSOR FOR
      SELECT dt, round(avg(measure) OVER w, 2), count(*) OVER w 
      FROM   measures
      WINDOW w AS (ORDER BY dt RANGE BETWEEN CURRENT ROW AND '1 hour' FOLLOWING);
BEGIN
    OPEN _cursor;
    FETCH _cursor INTO hour_start, avg_measure, ct;
    WHILE FOUND
    LOOP
      RETURN NEXT;
      FETCH RELATIVE ct FROM _cursor INTO hour_start, avg_measure, ct;
    END LOOP;
END
$func$;

SELECT * FROM f_dynamic_hourly_avg();

CREATE FUNCTION




hour_start
avg_measure
ct


2015-01-13 12:05:00
11.33
3

2015-01-13 13:30:00
9.50
2



SELECT 2


CREATE OR REPLACE FUNCTION f_dynamic_hourly_avg_big(_start_incl timestamp, _end_excl timestamp)
  RETURNS TABLE(hour_start timestamp, avg_measure numeric, ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
    _cursor CURSOR FOR
      SELECT dt, round(avg(measure) OVER w, 2), count(*) OVER w 
      FROM   big
      WHERE  dt >= _start_incl
      AND    dt <  _end_excl + interval '1 hour'
      WINDOW w AS (ORDER BY dt RANGE BETWEEN CURRENT ROW AND '1 hour' FOLLOWING)
      ORDER  BY dt;
BEGIN
    OPEN _cursor;
    FETCH _cursor INTO hour_start, avg_measure, ct;
    
    IF hour_start >= _end_excl THEN  -- first row after end: no valid result
       RETURN;
    END IF;
    
    WHILE FOUND
    LOOP
      RETURN NEXT;
      FETCH RELATIVE ct FROM _cursor INTO hour_start, avg_measure, ct;
    END LOOP;
END
$func$;

SELECT * FROM f_dynamic_hourly_avg_big('2013-02-02', '2013-02-03');

CREATE FUNCTION




hour_start
avg_measure
ct


2013-02-02 00:24:13.415018
42.29
7

2013-02-02 02:10:29.911286
52.10
10

2013-02-02 03:32:20.678757
53.50
2

2013-02-02 04:38:21.876093
75.60
5

2013-02-02 05:50:38.257584
39.25
8

2013-02-02 07:33:56.001093
81.40
5

2013-02-02 08:39:56.515722
42.20
5

2013-02-02 09:45:23.93757
49.00
8

2013-02-02 11:19:38.011161
36.50
4

2013-02-02 12:36:20.554662
71.50
4

2013-02-02 13:49:01.742429
31.20
5

2013-02-02 15:07:02.899753
55.44
9

2013-02-02 16:20:50.059154
50.14
7

2013-02-02 17:27:16.140006
33.00
11

2013-02-02 18:38:51.990508
38.22
9

2013-02-02 19:58:24.975064
56.50
8

2013-02-02 21:03:42.29511
56.23
13

2013-02-02 22:04:39.360148
63.67
6

2013-02-02 23:04:41.869558
53.79
14

2013-02-03 00:37:11.559341
61.00
4



SELECT 20


-- compare performance
EXPLAIN (ANALYZE, TIMING OFF)
WITH RECURSIVE cte AS (
   SELECT t.dt, m.measure
   FROM  (SELECT dt FROM big
          WHERE  dt >= '2013-02-02' ORDER BY 1 LIMIT 1) t -- outer incl. lower bound 
   JOIN   big m ON m.dt >= t.dt                  -- incl. lower bound
               AND m.dt <  t.dt + interval '1h'  -- excl. upper bound

   UNION ALL
   SELECT t.dt, m.measure
   FROM  (
      SELECT m.dt
      FROM  (SELECT dt FROM cte LIMIT 1) c
      JOIN   big m ON m.dt >= c.dt + interval '1h'
                  AND m.dt <  '2013-02-03' -- outer excl. upper bound 
      ORDER  BY 1
      LIMIT  1
      ) t
   JOIN   big m ON m.dt >= t.dt                 -- incl. lower bound
               AND m.dt <  t.dt + interval '1h' -- excl. upper bound
   )
SELECT dt AS hour_start, round(avg(measure), 2) AS avg_measure, count(*) AS ct
FROM   cte
GROUP  BY 1
ORDER  BY 1;


QUERY PLAN


Sort  (cost=11429.64..11430.14 rows=200 width=48) (actual rows=19 loops=1)

  Sort Key: cte.dt

  Sort Method: quicksort  Memory: 26kB

  CTE cte

    ->  Recursive Union  (cost=0.84..8057.92 rows=122221 width=12) (actual rows=140 loops=1)

          ->  Nested Loop  (cost=0.84..510.21 rows=11111 width=12) (actual rows=7 loops=1)

                ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual rows=1 loops=1)

                      ->  Index Only Scan using big_foo_idx on big  (cost=0.42..1724.39 rows=52113 width=8) (actual rows=1 loops=1)

                            Index Cond: (dt >= '2013-02-02 00:00:00'::timestamp without time zone)

                            Heap Fetches: 0

                ->  Index Only Scan using big_foo_idx on big m  (cost=0.42..398.64 rows=11111 width=12) (actual rows=7 loops=1)

                      Index Cond: ((dt >= big.dt) AND (dt < (big.dt + '01:00:00'::interval)))

                      Heap Fetches: 0

          ->  Nested Loop  (cost=0.84..510.33 rows=11111 width=12) (actual rows=7 loops=19)

                ->  Limit  (cost=0.42..0.57 rows=1 width=8) (actual rows=1 loops=19)

                      ->  Nested Loop  (cost=0.42..2428.80 rows=16003 width=8) (actual rows=1 loops=19)

                            Join Filter: (m_2.dt >= (cte_1.dt + '01:00:00'::interval))

                            Rows Removed by Join Filter: 47960

                            ->  Index Only Scan using big_foo_idx on big m_2  (cost=0.42..1588.59 rows=48010 width=8) (actual rows=47961 loops=19)

                                  Index Cond: (dt < '2013-02-03 00:00:00'::timestamp without time zone)

                                  Heap Fetches: 0

                            ->  Materialize  (cost=0.00..0.04 rows=1 width=8) (actual rows=1 loops=911256)

                                  ->  Limit  (cost=0.00..0.02 rows=1 width=8) (actual rows=1 loops=19)

                                        ->  WorkTable Scan on cte cte_1  (cost=0.00..2222.20 rows=111110 width=8) (actual rows=1 loops=19)

                ->  Index Only Scan using big_foo_idx on big m_1  (cost=0.42..398.64 rows=11111 width=12) (actual rows=7 loops=18)

                      Index Cond: ((dt >= m_2.dt) AND (dt < (m_2.dt + '01:00:00'::interval)))

                      Heap Fetches: 0

  ->  HashAggregate  (cost=3361.08..3364.08 rows=200 width=48) (actual rows=19 loops=1)

        Group Key: cte.dt

        Batches: 1  Memory Usage: 40kB

        ->  CTE Scan on cte  (cost=0.00..2444.42 rows=122221 width=12) (actual rows=140 loops=1)

Planning Time: 0.368 ms

Execution Time: 406.016 ms



EXPLAIN


-- MUCH faster for this small sample with few rows per period
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM f_dynamic_hourly_avg_big('2013-02-02', '2013-02-03');


QUERY PLAN


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

Planning Time: 0.039 ms

Execution Time: 0.617 ms



EXPLAIN
hour_start	avg_measure	ct
2013-02-02 00:24:13.415018	42.29	7
2013-02-02 02:10:29.911286	52.10	10
2013-02-02 03:32:20.678757	53.50	2
2013-02-02 04:38:21.876093	75.60	5
2013-02-02 05:50:38.257584	39.25	8
2013-02-02 07:33:56.001093	81.40	5
2013-02-02 08:39:56.515722	42.20	5
2013-02-02 09:45:23.93757	49.00	8
2013-02-02 11:19:38.011161	36.50	4
2013-02-02 12:36:20.554662	71.50	4
2013-02-02 13:49:01.742429	31.20	5
2013-02-02 15:07:02.899753	55.44	9
2013-02-02 16:20:50.059154	50.14	7
2013-02-02 17:27:16.140006	33.00	11
2013-02-02 18:38:51.990508	38.22	9
2013-02-02 19:58:24.975064	56.50	8
2013-02-02 21:03:42.29511	56.23	13
2013-02-02 22:04:39.360148	63.67	6
2013-02-02 23:04:41.869558	53.79	14