Forum OpenACS Q&A: Response to prohibitive ACS permissioning slowness?

Collapse
Posted by Peter Marklund on
<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>