Forum OpenACS Development: Re: OpenACS Permissions System - Performance Improvement Work

re different results: I did not get the same relative timings. Also I have some trouble understanding the times you get just because we are getting things that are 100x faster on similiar data and 50x faster on openacs with about 10-100x as much data (

In 7.4 geqo_threshold is used for two things, one is the threshold for the geqo optimizer but the other is whether or not to flatten the tables in a subquery or view when optimizing (in pg 8 that semi-secret function was broken out into an explicit parameter). And yes, it should not matter if there are only four tables in the query.

Your faster query does not return the correct answer it seems (member -1 does not show up in the rh_group_struct_view):

openacs.org=# select * from acs_permissions where 
privilege in (select descendant from acs_privilege_descendant_map where privilege='read') 
and object_id in (select ancestor_id from acs_object_context_index where object_id=179199) 
and grantee_id in (select group_id from rh_group_struct_view where element_id=-1);
 object_id | grantee_id | privilege
-----------+------------+-----------
(0 rows)

openacs.org=# select * from acs_object_party_privilege_map ppm where 
ppm.party_id=-1 and ppm.privilege='read' and ppm.object_id=179199;
 object_id | privilege | party_id
-----------+-----------+----------
    179199 | read      |       -1
(1 row)
And for reference here is the plan on openacs.org for a check for admin for public on a site node (which returns false):
openacs.org=# explain analyze select exists (select * from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=179199);
                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=119.70..119.71 rows=1 width=0) (actual time=1.332..1.333 rows=1 loops=1)
   InitPlan
     ->  Hash Join  (cost=15.82..119.70 rows=2 width=26) (actual time=1.322..1.322 rows=0 loops=1)
           Hash Cond: ("outer".grantee_id = "inner".party_id)
           ->  Hash Join  (cost=3.61..107.33 rows=11 width=26) (actual time=0.373..1.159 rows=30 loops=1)
                 Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                 ->  Nested Loop  (cost=0.00..102.88 rows=74 width=17) (actual time=0.070..0.542 rows=38 loops=1)
                       ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..22.95 rows=7 width=8) (actual time=0.031..0.054 rows=5 loops=1)
                             Index Cond: (object_id = 179199)
                       ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..11.39 rows=3 width=17) (actual time=0.017..0.053 rows=8 loops=5)
                             Index Cond: ("outer".ancestor_id = p.object_id)
                 ->  Hash  (cost=3.60..3.60 rows=3 width=31) (actual time=0.247..0.247 rows=0 loops=1)
                       ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..3.60 rows=3 width=31) (actual time=0.074..0.240 rows=1 loops=1)
                             Filter: ((descendant)::text = 'admin'::text)
           ->  Hash  (cost=12.20..12.20 rows=4 width=8) (actual time=0.061..0.061 rows=0 loops=1)
                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..12.20 rows=4 width=8) (actual time=0.047..0.053 rows=1 loops=1)
                       Index Cond: (member_id = -1)
 Total runtime: 1.582 ms
I think given this is <2ms for 8600 users and 140k objects I don't think it needs to be fixed. The plan produced here is quite a bit different than what you get so I think either you need to run vacuumdb -f -z YOURDB or your postgres conf is seriously out of whack.