I have this query that is not performing well

    explain analyse
    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

    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'
    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)

  • 1
    Less than 1 ms is a performance issue?
    – jjanes
    Aug 19 at 2:19
  • Sorry, used wrong userId for the demo. The numbers grow to: Append (cost=1714.97..2103.39 rows=1040 width=64) (actual time=182.768..198.379 rows=12697 loops=1) - 200ms when using a slightly different configured users. I have updated the output
    – Alex Tbk
    Aug 19 at 4:50
  • If 200ms is considered "not performing well", what is an acceptable level of performance that you are targeting? Aug 20 at 0:58
  • There is no exact goal, I rather want to understand if there are obvious issues - like missing indexes or issues with the query itself that can / could be optimized. Especially I would like to understand why there are SEQ scans that I would assume are slower than index scans
    – Alex Tbk
    Aug 20 at 8:25
  • I don't think we will get very far without a schema with example data we can use to reproduce the situation. The time is not very concentrated into just on place, so there probably is not just one fix like by adding an index. your seq scans are very fast (although some might be fast only because they are never actually run). A top-down redesign could only be feasible if we knew what the query was supposed to achieve or why you want to run it. For complex queries, you can't reverse engineer the purpose very well just by looking at the SQL, it needs some commentary.
    – jjanes
    Aug 21 at 16:00


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.