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 mytable (
id int,
group_ varchar(10),
favourite_colour varchar(10),
from_when varchar(10),
points int
);
insert into mytable values
(1, 'b', 'red', 'child', 30),
(2, 'b', 'blue', 'child', 50),
(2, 'b', 'red', 'adult', 50),
(3, 'c', 'white', 'adult', 22),
(4, 'c', 'white', 'adult', 24);
CREATE TABLE
INSERT 0 5
with cte_from_when as (
select group_, from_when,
array_agg(distinct id) as ids,
array_agg(distinct favourite_colour) as colors
from mytable
group by group_, from_when
having sum(points) > 70
),
cte_favourite_colour as (
select group_, favourite_colour
from mytable
group by group_, favourite_colour
having sum(points) > 70
)
select cw.group_, cw.from_when, min(ids) as ids, min(cw.colors) as favourite_colour, array_agg(t.id || '-' || cc.favourite_colour || '-' || t.from_when) as connection
from cte_from_when cw
inner join mytable t on t.group_ = cw.group_ and t.favourite_colour = ANY(colors)
inner join cte_favourite_colour cc on cc.group_ = t.group_ and cc.favourite_colour = t.favourite_colour
group by cw.group_, cw.from_when
| group_ | from_when | ids | favourite_colour | connection |
|---|---|---|---|---|
| b | child | {1,2} | {blue,red} | {1-red-child,2-red-adult} |
SELECT 1