I have this query that is not performing well
explain analyse
WITH RECURSIVE
user_orgs AS (
select o.id from organisation_admins oa JOIN usr_organisation o ON o.id = oa.id where oa.user_id = '48767f12-90d4-4211-a4cb-fe860f00db2a' -- orgs where user is admin
UNION ALL
select uo.id from usr_organisation uo where uo.id in (
select toa.organisation_id from tenant_organisations toa JOIN tenant_admins ta ON toa.id = ta.id where ta.user_id = '48767f12-90d4-4211-a4cb-fe860f00db2a'
)
),
orgs_and_suborgs as (
SELECT o.id from usr_organisation o where o.id in (select * from user_orgs)
UNION ALL
SELECT sa.child_id FROM organisation_associations AS sa JOIN orgs_and_suborgs ON orgs_and_suborgs.id = sa.parent_id
) cycle id set is_cycle using path
select t.id, t.name, t.metadata, t.settings_id from organisation_teams ot LEFT JOIN usr_teams t ON ot.team_id = t.id where ot.id in (select * from orgs_and_suborgs where not is_cycle)
union all
select t.id, t.name, t.metadata, t.settings_id from team_user tu LEFT JOIN usr_teams t on tu.id = t.id where tu.user_id = '48767f12-90d4-4211-a4cb-fe860f00db2a' -- user direct child of team
union all
select t.id, t.name, t.metadata, t.settings_id from usr_teams t LEFT JOIN team_usergroups tug ON t.id = tug.id
where tug.user_group_id in ( -- user in a group that is child of team
WITH RECURSIVE c AS (
select u.id as id from usr_user_group u join user_group_user ug ON u.id = ug.id where ug.user_id = '48767f12-90d4-4211-a4cb-fe860f00db2a'
UNION ALL
SELECT sa.parent_id
FROM user_group_associations AS sa JOIN c ON c.id = sa.child_id
)
SELECT g.id FROM c JOIN usr_user_group g ON c.id = g.id
)
The analyse output gives the following:
Append (cost=1714.97..2103.39 rows=1040 width=64) (actual time=182.768..198.379 rows=12697 loops=1)
CTE orgs_and_suborgs
-> Recursive Union (cost=50.16..1380.77 rows=1996 width=49) (actual time=0.446..100.537 rows=12690 loops=1)
-> Nested Loop (cost=50.16..75.75 rows=6 width=49) (actual time=0.445..0.482 rows=6 loops=1)
-> HashAggregate (cost=49.87..49.93 rows=6 width=16) (actual time=0.432..0.444 rows=6 loops=1)
Group Key: o_1.id
Batches: 1 Memory Usage: 24kB
-> Append (cost=4.61..49.86 rows=6 width=16) (actual time=0.389..0.434 rows=6 loops=1)
-> Nested Loop (cost=4.61..43.31 rows=5 width=16) (actual time=0.009..0.011 rows=0 loops=1)
-> Bitmap Heap Scan on organisation_admins oa (cost=4.32..21.80 rows=5 width=16) (actual time=0.008..0.009 rows=0 loops=1)
Recheck Cond: (user_id = 'b0066b01-5e1c-498e-ac02-4dd686abad77'::uuid)
-> Bitmap Index Scan on organisation_admins_user_id_index (cost=0.00..4.32 rows=5 width=0) (actual time=0.006..0.007 rows=0 loops=1)
Index Cond: (user_id = 'b0066b01-5e1c-498e-ac02-4dd686abad77'::uuid)
-> Index Only Scan using usr_organisation_pk on usr_organisation o_1 (cost=0.29..4.30 rows=1 width=16) (never executed)
Index Cond: (id = oa.id)
Heap Fetches: 0
-> Nested Loop (cost=2.66..6.46 rows=1 width=16) (actual time=0.145..0.184 rows=6 loops=1)
-> HashAggregate (cost=2.38..2.39 rows=1 width=16) (actual time=0.104..0.110 rows=6 loops=1)
Group Key: toa.organisation_id
Batches: 1 Memory Usage: 24kB
-> Hash Join (cost=1.15..2.38 rows=1 width=16) (actual time=0.095..0.100 rows=6 loops=1)
Hash Cond: (toa.id = ta.id)
-> Seq Scan on tenant_organisations toa (cost=0.00..1.17 rows=17 width=32) (actual time=0.052..0.053 rows=17 loops=1)
-> Hash (cost=1.14..1.14 rows=1 width=16) (actual time=0.022..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tenant_admins ta (cost=0.00..1.14 rows=1 width=16) (actual time=0.008..0.010 rows=1 loops=1)
Filter: (user_id = 'b0066b01-5e1c-498e-ac02-4dd686abad77'::uuid)
Rows Removed by Filter: 10
-> Index Only Scan using usr_organisation_pk on usr_organisation uo (cost=0.29..4.07 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=6)
Index Cond: (id = toa.organisation_id)
Heap Fetches: 0
-> Index Only Scan using usr_organisation_pk on usr_organisation o (cost=0.29..4.30 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=6)
Index Cond: (id = o_1.id)
Heap Fetches: 0
-> Nested Loop (cost=0.29..126.51 rows=199 width=49) (actual time=0.031..10.189 rows=1409 loops=9)
-> WorkTable Scan on orgs_and_suborgs orgs_and_suborgs_1 (cost=0.00..1.20 rows=30 width=48) (actual time=0.001..0.582 rows=1410 loops=9)
Filter: (NOT is_cycle)
-> Index Only Scan using organisation_associations_pkey on organisation_associations sa (cost=0.29..4.01 rows=7 width=32) (actual time=0.003..0.003 rows=1 loops=12690)
Index Cond: (parent_id = orgs_and_suborgs_1.id)
Heap Fetches: 0
-> Hash Right Join (cost=334.19..667.58 rows=1038 width=64) (actual time=182.767..196.585 rows=12695 loops=1)
Hash Cond: (t.id = ot.team_id)
-> Seq Scan on usr_teams t (cost=0.00..273.64 rows=13164 width=64) (actual time=0.012..2.288 rows=13185 loops=1)
-> Hash (cost=321.22..321.22 rows=1038 width=16) (actual time=182.726..182.734 rows=12695 loops=1)
Buckets: 16384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 724kB
-> Hash Join (cost=46.91..321.22 rows=1038 width=16) (actual time=162.194..176.241 rows=12695 loops=1)
Hash Cond: (ot.id = orgs_and_suborgs.id)
-> Seq Scan on organisation_teams ot (cost=0.00..228.50 rows=13050 width=32) (actual time=0.010..3.729 rows=13185 loops=1)
-> Hash (cost=44.41..44.41 rows=200 width=16) (actual time=161.995..161.997 rows=12690 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 723kB
-> HashAggregate (cost=42.41..44.41 rows=200 width=16) (actual time=153.563..157.191 rows=12690 loops=1)
Group Key: orgs_and_suborgs.id
Batches: 1 Memory Usage: 1185kB
-> CTE Scan on orgs_and_suborgs (cost=0.00..39.92 rows=998 width=16) (actual time=0.452..139.310 rows=12690 loops=1)
Filter: (NOT is_cycle)
-> Nested Loop Left Join (cost=0.57..16.60 rows=1 width=64) (actual time=0.220..0.229 rows=2 loops=1)
-> Index Scan using team_user_user_id_index on team_user tu (cost=0.28..8.30 rows=1 width=16) (actual time=0.041..0.042 rows=2 loops=1)
Index Cond: (user_id = 'b0066b01-5e1c-498e-ac02-4dd686abad77'::uuid)
-> Index Scan using usr_teams_pk on usr_teams t_1 (cost=0.29..8.30 rows=1 width=64) (actual time=0.011..0.011 rows=1 loops=2)
Index Cond: (id = tu.id)
-> Nested Loop Semi Join (cost=13.11..22.84 rows=1 width=64) (actual time=0.008..0.013 rows=0 loops=1)
Join Filter: (tug.user_group_id = g.id)
-> Nested Loop (cost=0.29..8.30 rows=1 width=80) (actual time=0.007..0.008 rows=0 loops=1)
-> Seq Scan on team_usergroups tug (cost=0.00..0.00 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
-> Index Scan using usr_teams_pk on usr_teams t_2 (cost=0.29..8.30 rows=1 width=64) (never executed)
Index Cond: (id = tug.id)
-> Hash Join (cost=12.82..14.29 rows=11 width=16) (never executed)
Hash Cond: (g.id = c.id)
CTE c
-> Recursive Union (cost=8.30..12.46 rows=11 width=16) (never executed)
-> Hash Join (cost=8.30..9.64 rows=1 width=16) (never executed)
Hash Cond: (u.id = ug.id)
-> Seq Scan on usr_user_group u (cost=0.00..1.26 rows=26 width=16) (never executed)
-> Hash (cost=8.29..8.29 rows=1 width=16) (never executed)
-> Index Scan using user_group_user_user_id_index on user_group_user ug (cost=0.28..8.29 rows=1 width=16) (never executed)
Index Cond: (user_id = 'b0066b01-5e1c-498e-ac02-4dd686abad77'::uuid)
-> Hash Join (cost=0.01..0.26 rows=1 width=16) (never executed)
Hash Cond: (c_1.id = sa_1.child_id)
-> WorkTable Scan on c c_1 (cost=0.00..0.20 rows=10 width=16) (never executed)
-> Hash (cost=0.00..0.00 rows=1 width=32) (never executed)
-> Seq Scan on user_group_associations sa_1 (cost=0.00..0.00 rows=1 width=32) (never executed)
-> Seq Scan on usr_user_group g (cost=0.00..1.26 rows=26 width=16) (never executed)
-> Hash (cost=0.22..0.22 rows=11 width=16) (never executed)
-> CTE Scan on c (cost=0.00..0.22 rows=11 width=16) (never executed)
Planning Time: 7.577 ms
Execution Time: 203.030 ms
tenant_organisations has 2 indexes: (id, organisation_id) and (id)
tenant_admins: (id, user_id) and (user_id)
team_usergroups: (id, user_group_id)
user_group_user: (id, user_id)
user_group_associations: (parent_id, child_id)
