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?.
select version();
| version |
|---|
| PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
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'
);
create table account (
size int,
id int,
name text
);
insert into account values
(100, 1, 'John'), (200, 2, 'Mary'), (300, 3, 'Jane'),
(400, 4, 'Anne'), (100, 5, 'Mike'), (600, 6, 'Joanne');
6 rows affected
with cte as(
SELECT *
, sum_cap(size, '600') OVER ( ORDER BY id) AS capped_at_600
, sum_cap(size, '600') OVER ( ORDER BY id) >=600 as large_or_eq_600
FROM account)
select * from cte order by id;
| size | id | name | capped_at_600 | large_or_eq_600 |
|---|---|---|---|---|
| 100 | 1 | John | 100 | f |
| 200 | 2 | Mary | 300 | f |
| 300 | 3 | Jane | 600 | t |
| 400 | 4 | Anne | 400 | f |
| 100 | 5 | Mike | 500 | f |
| 600 | 6 | Joanne | 1100 | t |
select id, name, size
, sum(size) over (order by id) as size_total
, ((sum(size) over (order by id) - 1) / 600) + 1 as size_group
from account
order by id, name;
| id | name | size | size_total | size_group |
|---|---|---|---|---|
| 1 | John | 100 | 100 | 1 |
| 2 | Mary | 200 | 300 | 1 |
| 3 | Jane | 300 | 600 | 1 |
| 4 | Anne | 400 | 1000 | 2 |
| 5 | Mike | 100 | 1100 | 2 |
| 6 | Joanne | 600 | 1700 | 3 |