Postgres 14

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

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