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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE assets (
id serial,
name text,
owner text,
PRIMARY KEY(id)
);
CREATE TABLE users (
id serial,
username text,
ref1 text,
ref2 text,
PRIMARY KEY(id)
);
INSERT INTO assets (name, owner) VALUES
('#1', 'a'),
('#2', 'b'),
('#3', 'c'),
('#4', 'a'),
('#5', 'c'),
('#6', 'd'),
('#7', 'e'),
('#8', 'd'),
('#9', 'a'),
('#10', 'a'),
('#11', 'z')
;
INSERT INTO users (username, ref1, ref2) VALUES
('bobo', 'a', 'd'),
('toto', 'b', 'e'),
('momo', 'c', 'd'),
('lolo', 'a', 'f'),
('popo', 'c', 'f');
CREATE TABLE
CREATE TABLE
INSERT 0 11
INSERT 0 5
CREATE TABLE
INSERT 0 3
with recursive
nodes as (
select u.id, r.ref
from users u
cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
),
edges as (
select distinct n1.id as id1, n2.id as id2
from nodes n1
inner join nodes n2 on n1.ref = n2.ref
),
rcte as (
select id1, id2, array[id1] as visited from edges where id1 = id2
union all
select r.id1, e.id2, r.visited || e.id2
from rcte r
inner join edges e on e.id1 = r.id2
where e.id2 <> all(r.visited)
),
groups as (
select id1 as id, array_agg(distinct id2 order by id2) as ids
from rcte
group by id1
)
select * from groups order by id
| id | ids |
|---|---|
| 1 | {1,3,4,5} |
| 2 | {2} |
| 3 | {1,3,4,5} |
| 4 | {1,3,4,5} |
| 5 | {1,3,4,5} |
SELECT 5
with recursive
nodes as (
select u.id, r.ref
from users u
cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
),
edges as (
select distinct n1.id as id1, n2.id as id2
from nodes n1
inner join nodes n2 on n1.ref = n2.ref
),
rcte as (
select id1, id2, array[id1] as visited from edges where id1 = id2
union all
select r.id1, e.id2, r.visited || e.id2
from rcte r
inner join edges e on e.id1 = r.id2
where e.id2 <> all(r.visited)
),
groups as (
select id1 as id, array_agg(distinct id2 order by id2) as ids
from rcte
group by id1
)
select g.ids,
array_agg(distinct u.username) as usernames,
array_agg(distinct u.ref1) as refs1,
case when g.ids is null then array_agg(distinct a.owner) else array_agg(distinct u.ref2) end as refs2,
array_agg(distinct a.id) as asset_ids,
count(distinct a.id) as asset_count
from assets a
left join users u on a.owner in (u.ref1, u.ref2)
left join groups g on g.id = u.id
group by g.ids
| ids | usernames | refs1 | refs2 | asset_ids | asset_count |
|---|---|---|---|---|---|
| {1,3,4,5} | {bobo,lolo,momo,popo} | {a,c} | {d,f} | {1,3,4,5,6,8,9,10} | 8 |
| {2} | {toto} | {b} | {e} | {2,7} | 2 |
| null | {NULL} | {NULL} | {z} | {11} | 1 |
SELECT 3