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 test(
  id        serial
, val       numeric     not null
, created   timestamp   not null default(current_timestamp)
, fk        integer     not null
);

INSERT INTO test(val, created, fk) VALUES
  (100, now() + interval '10 min', 5)
, (25,  now() + interval '20 min', 5)
, (30,  now() + interval '30 min', 5)
, (45,  now() + interval '40 min', 5)
, (10,  now() + interval '50 min', 5)
;
5 rows affected

CREATE OR REPLACE FUNCTION f_sum_cap50 (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql STRICT AS
$$SELECT CASE WHEN $1 > '50' THEN '0' ELSE $1 END + $2;$$;

CREATE AGGREGATE sum_cap50 (anyelement) (
  sfunc    = f_sum_cap50
, stype    = anyelement
, initcond = '0'
);
CREATE OR REPLACE FUNCTION f_sum_cap (anyelement, anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql STRICT AS
$$SELECT CASE WHEN $1 > $3 THEN '0' ELSE $1 END + $2;$$;

CREATE AGGREGATE sum_cap (anyelement, anyelement) (
  sfunc    = f_sum_cap
, stype    = anyelement
, initcond = '0'
);
SELECT *
     , sum_cap50(val) OVER (PARTITION BY fk ORDER BY created) AS running_sum_capped_at_50
     , sum_cap50(val) OVER (PARTITION BY fk ORDER BY created) > 50 AS threshold_met 
FROM   test
WHERE  fk = 5;


id
val
created
fk
running_sum_capped_at_50
threshold_met


1
100
2020-07-06 11:03:08.365753
5
100
t

2
25
2020-07-06 11:13:08.365753
5
25
f

3
30
2020-07-06 11:23:08.365753
5
55
t

4
45
2020-07-06 11:33:08.365753
5
45
f

5
10
2020-07-06 11:43:08.365753
5
55
t


SELECT *
     , sum_cap(val, '110') OVER (PARTITION BY fk ORDER BY created) AS running_sum_capped_at_110
     , sum_cap(val, '110') OVER (PARTITION BY fk ORDER BY created) > 110 AS threshold_met 
FROM   test
WHERE  fk = 5;


id
val
created
fk
running_sum_capped_at_110
threshold_met


1
100
2020-07-06 11:03:08.365753
5
100
f

2
25
2020-07-06 11:13:08.365753
5
125
t

3
30
2020-07-06 11:23:08.365753
5
30
f

4
45
2020-07-06 11:33:08.365753
5
75
f

5
10
2020-07-06 11:43:08.365753
5
85
f

id	val	created	fk	running_sum_capped_at_50	threshold_met
1	100	2020-07-06 11:03:08.365753	5	100	t
2	25	2020-07-06 11:13:08.365753	5	25	f
3	30	2020-07-06 11:23:08.365753	5	55	t
4	45	2020-07-06 11:33:08.365753	5	45	f
5	10	2020-07-06 11:43:08.365753	5	55	t