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 (numeric, numeric)
  RETURNS numeric LANGUAGE sql AS
'SELECT CASE WHEN $1 > 50 THEN 0 ELSE $1 END + $2';

CREATE AGGREGATE sum_cap50 (numeric) (
  sfunc    = f_sum_cap50
, stype    = numeric
, 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 10:57:04.765445
5
100
t

2
25
2020-07-06 11:07:04.765445
5
25
f

3
30
2020-07-06 11:17:04.765445
5
55
t

4
45
2020-07-06 11:27:04.765445
5
45
f

5
10
2020-07-06 11:37:04.765445
5
55
t

id	val	created	fk	running_sum_capped_at_50	threshold_met
1	100	2020-07-06 10:57:04.765445	5	100	t
2	25	2020-07-06 11:07:04.765445	5	25	f
3	30	2020-07-06 11:17:04.765445	5	55	t
4	45	2020-07-06 11:27:04.765445	5	45	f
5	10	2020-07-06 11:37:04.765445	5	55	t