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 |