Why is checking that an admin exists so slow?
Well, one hint that there is a problem is the query plan generated for the seemingly innocuous query to do the check:
EXPLAIN SELECT 1 as admin_exists_p
FROM dual
WHERE EXISTS (SELECT 1
FROM all_object_party_privilege_map m, users u
WHERE m.object_id = 0
and m.party_id = u.user_id
and m.privilege = 'admin');'
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0)
InitPlan
-> Nested Loop (cost=78.26..9714.27 rows=70 width=74)
-> Nested Loop (cost=78.26..9501.38 rows=70 width=70)
-> Seq Scan on acs_privileges p2 (cost=0.00..3.56 rows=1 width=21)
-> Materialize (cost=6577.13..6577.13 rows=139 width=49)
-> Hash Join (cost=78.26..6577.13 rows=139 width=49)
-> Hash Join (cost=74.70..6570.79 rows=139 width=28)
-> Subquery Scan gpm (cost=0.00..5971.26 rows=104618 width=124)
-> Append (cost=0.00..5971.26 rows=104618 width=124)
-> Subquery Scan *SELECT* 1 (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan dual (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan *SELECT* 2 (cost=0.00..948.21 rows=33621 width=4)
-> Seq Scan on users (cost=0.00..948.21 rows=33621 width=4)
-> Subquery Scan *SELECT* 3 (cost=0.00..626.37 rows=33637 width=4)
-> Seq Scan on parties (cost=0.00..626.37 rows=33637 width=4)
-> Subquery Scan *SELECT* 4 (cost=1351.38..2201.48 rows=3736 width=124)
-> Hash Join (cost=1351.38..2201.48 rows=3736 width=124)
-> Seq Scan on membership_rels mr (cost=0.00..635.29 rows=33623 width=4)
-> Hash (cost=1342.03..1342.03 rows=3736 width=120)
-> Hash Join (cost=62.29..1342.03 rows=3736 width=120)
-> Seq Scan on group_element_index (cost=0.00..719.28 rows=33628 width=30)
-> Hash (cost=62.02..62.02 rows=108 width=90)
-> Nested Loop (cost=1.09..62.02 rows=108 width=90)
-> Hash Join (cost=1.09..7.26 rows=7 width=62)
-> Seq Scan on acs_object_types o2 (cost=0.00..5.39 rows=139 width=28)
-> Hash (cost=1.07..1.07 rows=7 width=34)
-> Seq Scan on rel_segments rs (cost=0.00..1.07 rows=7 width=34)
-> Seq Scan on acs_object_types o1 (cost=0.00..5.39 rows=139 width=28)
-> Subquery Scan *SELECT* 5 (cost=719.35..2195.19 rows=33623 width=16)
-> Hash Join (cost=719.35..2195.19 rows=33623 width=16)
-> Seq Scan on group_element_index (cost=0.00..803.35 rows=33628 width=12)
-> Hash (cost=635.29..635.29 rows=33623 width=4)
-> Seq Scan on membership_rels mr (cost=0.00..635.29 rows=33623 width=4)
-> Hash (cost=74.60..74.60 rows=41 width=20)
-> Nested Loop (cost=0.00..74.60 rows=41 width=20)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..19.40 rows=6 width=4)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..9.89 rows=2 width=16)
-> Hash (cost=3.25..3.25 rows=125 width=21)
-> Seq Scan on acs_privileges p1 (cost=0.00..3.25 rows=125 width=21)
SubPlan
-> Nested Loop (cost=0.00..21.00 rows=1 width=41)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..6.40 rows=2 width=10)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..6.40 rows=2 width=31)
-> Index Scan using users_pk on users u (cost=0.00..3.03 rows=1 width=4)
-> Subquery Scan dual (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Ouch! Using the acs_permissions_all view on the other hand is lightning fast. Looking at the query plan in this case it's pretty clear why:
EXPLAIN SELECT 1 as admin_exists_p
FROM dual
WHERE exists (SELECT 1
FROM acs_permissions_all m, users u
WHERE m.object_id = 0
and m.grantee_id = u.user_id
and m.privilege = 'admin');
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0)
InitPlan
-> Nested Loop (cost=0.00..77.58 rows=1 width=16)
-> Nested Loop (cost=0.00..74.55 rows=1 width=12)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..19.40 rows=6 width=4)
-> Index Scan using acs_permissions_pk on acs_permissions p (cost=0.00..9.89 rows=1 width=8)
-> Index Scan using users_pk on users u (cost=0.00..3.03 rows=1 width=4)
-> Subquery Scan dual (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Unfortunately it does not return permissions granted to users so this can return true if for example you granted admin to an "Admins" group but neglected to add any users as members of that group.
What's the right answer? I don't know. I think trying to do all the things we do with heirarchical permissions without denormalizing further is probably part of the problem. Also, I think there is more special cases in the views than is strictly necessary.