Forum OpenACS Q&A: Response to OpenACS 4 production sites
Posted by
Hamilton Chua
on 03/10/02 01:18 AM
As requested, here are the two queries. Each query is followed by Explain Results.
Query :
select 1 as admin_exists_p from dual where exists (select 1 from acs_object_party_privilege_map m, users u where m.object_id = 0 and m.party_id = u.user_id and m.privilege = 'admin');
Explain Results:
NOTICE: QUERY PLAN:
Result (cost=0.00..0.00 rows=0 width=0)
InitPlan
-> Nested Loop (cost=811562.64..825535.26 rows=19 width=8)
-> Subquery Scan m (cost=811562.64..825497.55 rows=185799 width=100)
-> Unique (cost=811562.64..825497.55 rows=185799 width=100)
-> Sort (cost=811562.64..811562.64 rows=1857988 width=100)
-> Append (cost=8510.41..63616.72 rows=1857988 width=100)
-> Subquery Scan *SELECT* 1 (cost=8510.41..10142.16 rows=234019 width=68)
-> Merge Join (cost=8510.41..10142.16 rows=234019 width=68)
-> Nested Loop (cost=0.00..1486.94 rows=441 width=24)
-> Index Scan using acs_privileges_pk on acs_privileges p1 (cost=0.00..6.23 rows=21 width=12)
-> Seq Scan on acs_privileges p2 (cost=0.00..1.21 rows=21 width=12)
SubPlan
-> Nested Loop (cost=0.00..3.29 rows=1 width=36)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..1.64 rows=1 width=12)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..1.64 rows=1 width=24)
-> Sort (cost=8510.41..8510.41 rows=11144 width=44)
-> Hash Join (cost=1025.15..7574.53 rows=11144 width=44)
-> Seq Scan on acs_object_context_index (cost=0.00..1857.48 rows=116948 width=8)
-> Hash (cost=1022.36..1022.36 rows=1118 width=36)
-> Merge Join (cost=715.46..1022.36 rows=1118 width=36)
-> Index Scan using acs_permissions_grantee_idx on acs_permissions p (cost=0.00..224.89 rows=4702 width=20)
-> Sort (cost=715.46..715.46 rows=1859 width=16)
-> Hash Join (cost=253.93..614.52 rows=1859 width=16)
-> Seq Scan on membership_rels mr (cost=0.00..88.05 rows=4644 width=4)
-> Hash (cost=165.81..165.81 rows=6942 width=12)
-> Seq Scan on group_element_index (cost=0.00..165.81 rows=6942 width=12)
-> Subquery Scan *SELECT* 2 (cost=10920.40..12501.40 rows=148755 width=100)
-> Merge Join (cost=10920.40..12501.40 rows=148755 width=100)
-> Nested Loop (cost=0.00..1486.94 rows=441 width=24)
-> Index Scan using acs_privileges_pk on acs_privileges p1 (cost=0.00..6.23 rows=21 width=12)
-> Seq Scan on acs_privileges p2 (cost=0.00..1.21 rows=21 width=12)
SubPlan
-> Nested Loop (cost=0.00..3.29 rows=1 width=36)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..1.64 rows=1 width=12)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..1.64
-> Sort (cost=10920.40..10920.40 rows=7084 width=76)
-> Hash Join (cost=3748.45..10292.74 rows=7084 width=76)
-> Seq Scan on acs_object_context_index (cost=0.00..1857.48 rows=116948 width=8)
-> Hash (cost=3746.67..3746.67 rows=711 width=68)
-> Hash Join (cost=296.97..3746.67 rows=711 width=68)
-> Hash Join (cost=99.66..3443.33 rows=1182 width=48)
-> Nested Loop (cost=0.00..2998.79 rows=4413 width=44)
-> Seq Scan on rel_segments rs (cost=0.00..1.04 rows=4 width=20)
-> Index Scan using group_elem_idx_group_idx on group_element_index (cost=0.00..113.85 rows=235 width=24)
SubPlan
-> Nested Loop (cost=0.00..5.39 rows=1 width=36)
-> Index Scan using acs_object_types_pk on acs_object_types o1 (cost=0.00..2.01 rows=1 width=12)
-> Seq Scan on acs_object_types o2 (cost=0.00..2.50 rows=50 width=24)
-> Hash (cost=88.05..88.05 rows=4644 width=4)
-> Seq Scan on membership_rels mr (cost=0.00..88.05 rows=4644 width=4)
-> Hash (cost=79.02..79.02 rows=4702 width=20)
-> Seq Scan on acs_permissions p (cost=0.00..79.02 rows=4702 width=20)
-> Subquery Scan *SELECT* 3 (cost=10942.73..13020.91 rows=984035 width=52)
-> Merge Join (cost=10942.73..13020.91 rows=984035 width=52)
-> Nested Loop (cost=0.00..1486.94 rows=441 width=24)
-> Index Scan using acs_privileges_pk on acs_privileges p1 (cost=0.00..6.23 rows=21 width=12)
-> Seq Scan on acs_privileges p2 (cost=0.00..1.21 rows=21 width=12)
SubPlan
-> Nested Loop (cost=0.00..3.29 rows=1 width=36)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..1.64 rows=1 width=12)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..1.64 rows=1 width=24)
-> Sort (cost=10942.73..10942.73 rows=46859 width=28)
-> Hash Join (cost=90.77..6684.95 rows=46859 width=28)
-> Seq Scan on acs_object_context_index (cost=0.00..1857.48 rows=116948 width=8)
-> Hash (cost=79.02..79.02 rows=4702 width=20)
-> Seq Scan on acs_permissions p (cost=0.00..79.02 rows=4702 width=20)
-> Subquery Scan *SELECT* 4 (cost=0.00..25059.59 rows=490971 width=52)
-> Nested Loop (cost=0.00..25059.59 rows=490971 width=52)
-> Nested Loop (cost=0.00..2892.65 rows=209 width=48)
-> Nested Loop (cost=0.00..2189.97 rows=10 width=36)
-> Nested Loop (cost=0.00..3.49 rows=1 width=28)
-> Index Scan using acs_permissions_grantee_idx on acs_permissions p (cost=0.00..2.01 rows=1 width=16)
-> Seq Scan on acs_privileges p1 (cost=0.00..1.21 rows=21 width=12)
-> Index Scan using acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (cost=0.00..2129.66 rows=4546 width=8)
-> Seq Scan on acs_privileges p2 (cost=0.00..1.21 rows=21 width=12)
SubPlan
-> Nested Loop (cost=0.00..3.29 rows=1 width=36)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..1.64 rows=1 width=12)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..1.64 rows=1 width=24)
-> Seq Scan on users u (cost=0.00..82.46 rows=2346 width=4)
-> Subquery Scan *SELECT* 5 (cost=0.00..2892.65 rows=209 width=48)
-> Nested Loop (cost=0.00..2892.65 rows=209 width=48)
-> Nested Loop (cost=0.00..2189.97 rows=10 width=36)
-> Nested Loop (cost=0.00..3.49 rows=1 width=28)
-> Index Scan using acs_permissions_grantee_idx on acs_permissions p (cost=0.00..2.01 rows=1 width=16)
-> Seq Scan on acs_privileges p1 (cost=0.00..1.21 rows=21 width=12)
-> Index Scan using acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (cost=0.00..2129.66 rows=4546 width=8)
-> Seq Scan on acs_privileges p2 (cost=0.00..1.21 rows=21 width=12)
SubPlan
-> Nested Loop (cost=0.00..3.29 rows=1 width=36)
-> Seq Scan on acs_privilege_hierarchy_index h1 (cost=0.00..1.64 rows=1 width=12)
-> Seq Scan on acs_privilege_hierarchy_index h2 (cost=0.00..1.64 rows=1 width=24)
-> Index Scan using users_pk on users u (cost=0.00..2.02 rows=1 width=4)
-> Subquery Scan dual (cost=0.00..0.00 rows=0 width=0)
-> Result (cost=0.00..0.00 rows=0 width=0)
Query :
select site_node__url(n.node_id) as url, n.node_id, n.directory_p,
n.pattern_p, n.object_id, o.object_type, n.package_key, n.package_id
from acs_objects o left outer join
(select n.node_id, n.directory_p, n.pattern_p, n.object_id,
p.package_key, p.package_id
from site_nodes n, apm_packages p
where n.object_id = p.package_id) n
using (object_id)
Explain Results:
NOTICE: QUERY PLAN:
Hash Join (cost=4.09..4210.72 rows=45459 width=42)
-> Seq Scan on acs_objects o (cost=0.00..1251.59 rows=45459 width=16)
-> Hash (cost=4.05..4.05 rows=16 width=26)
-> Subquery Scan n (cost=1.20..4.05 rows=16 width=26)
-> Hash Join (cost=1.20..4.05 rows=16 width=26)
-> Seq Scan on apm_packages p (cost=0.00..1.33 rows=33 width=16)
-> Hash (cost=1.16..1.16 rows=16 width=10)
-> Seq Scan on site_nodes n (cost=0.00..1.16 rows=16 width=10)
