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 |