Forum OpenACS Q&A: prohibitive ACS permissioning slowness?
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?
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.
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).
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:
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,
where pf2.folder_id = ao2.object_id
and ao2.tree_sortkey between ao1.tree_sortkey
and pf2.public_p = 't'
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...