Forum OpenACS Q&A: prohibitive ACS permissioning slowness?

1: prohibitive ACS permissioning slowness?

Posted by Peter Marklund on 06/16/02 10:49 PM

I recently tried to add ACS permissioning to the folders in my Photo Archive application. Unfortunately, all SQL queries that I have tried so far to retrieve the sub-folders that the user has read permission on have been heineously slow. Here is an example of a query that I tried:

select path as full_path
  from par_folders pf1, acs_objects ao1
  where  ao1.object_id = pf1.folder_id
  and    ao1.context_id = '13667'
  and exists (select 1
              from all_object_party_privilege_map 
              where object_id = ao1.object_id
              and party_id = '2426'
              and privilege = 'read');

Here is the size of some of the tables and views involved:

marklundweb=# select count(*) from acs_objects;
 count 
-------
  9021
(1 row)

marklundweb=# select count(*) from parties;
 count 
-------
     6
(1 row)

marklundweb=# select count(*) from acs_privileges;
 count 
-------
    82
(1 row)

marklundweb=# select count(*) from all_object_party_privilege_map;
  count  
---------
 1455495
(1 row)

Is the ACS permissioning system still unscalable? Can my query somehow be optimized?

2: Response to prohibitive ACS permissioning slowness? (response to 1)

Posted by Michael Bluett on 06/16/02 11:05 PM

Shouldn't your query use numbers rather than strings when querying, i.e. 13667 rather than '13667'? [Not that I have OpenACS installed yet, just a SQL hunch.]

3: Response to prohibitive ACS permissioning slowness? (response to 1)

Posted by Stephen . on 06/17/02 12:58 AM

Use acs_permission.permission_p rather than the exists clause, you're only doing one permission check and this function is optimised for this.

4: Response to prohibitive ACS permissioning slowness? (response to 1)

Posted by Stephen . on 06/17/02 08:54 AM

Err sorry, your not just selecting one row, and I'm sure you started with the permission_p function anyway. It was the morning after the night before and I shouldn't have been typing...

I would expect that even with the function call overhead permission_p would still perform better for small numbers of rows returned. Did you notice a big difference? It would be good to work out some guide lines for when to use the simple permission_p and when to go with inline SQL, and how.

5: Response to prohibitive ACS permissioning slowness? (response to 1)

Posted by Andrew Grumet on 06/17/02 02:38 PM

After experimenting with some of the more familiar approaches to permission_p scaling (such as restricting the number of rows that it is run on and avoiding placing it in a WHERE clause), we've started to look at its internals. A proposed change is mentioned in this thread: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0004rw&topic_id=12&topic=OpenACS%204%2e0%20Design. The thread did well for a short while and then sort of petered out (no pun intended). We have yet to carefully test the proposed changes but you might want to take a look at them.

6: Response to prohibitive ACS permissioning slowness? (response to 1)

Posted by Peter Marklund on 06/24/02 09:22 PM

<p>
Thanks for your replies! Unfortunately I gave up on using ACS permissions. I don't have time right now to look into how I could optimize ACS permissions and judging by the problems other people have had with the API I doubt that I would have much luck... Yes, I tried using acs_permission_p and I believe that was the slowest approach. I didn't do any timing though, how do I do that with Postgres? The fastest query that I tried was to join with all_object_party_privilege_map (instead of using the sub-query in the posting above) but even that query was far too slow to be acceptable (I consider anything over 0.5 seconds to be too slow).
</p>

<p>
Instead of using ACS permissions I resorted to adding a public_p flag to my folders table. However, I then ran into the following query that was also slow:

<pre>
select path as full_path
from par_folders pf1, acs_objects ao1
where ao1.object_id = pf1.folder_id
and ao1.context_id = :parent_id
and (exists (select 1 from acs_objects ao2,
      par_folders pf2
      where pf2.folder_id = ao2.object_id
      and ao2.tree_sortkey between ao1.tree_sortkey
      and tree_right(ao1.tree_sortkey)
      and pf2.public_p = 't'
)
)
</pre>

Now I would like to know what index that will best speed up my query. If I can't get that query fast I will simply resort to replacing the tree sub-query with a simple "and public_p = 't'" clause. That would at last give me a query that doesn't take forever...
</p>