Ahh ... see, the code's calling permission_p() rather than use the acs_object_party_privilege_map, which might look something like:
and exists (select 1
from acs_object_party_privilege_map m
where m.object_id = site_nodes.object_id
and m.party_id = acs.magic_object_id('whatever')
and m.privilege = 'read')
and perhaps even poking at the magic object map table directly avoiding the call to acs.magic_object_id. Personally I'm not the least bit offended by code that pokes into tables we know won't change if there's good reason, and testing with cc_users showed that calling acs.magic_object_id() can significantly slow down queries.
If you have time, could you try recasting the query first using acs_object_party_privilege_map and then poking into the magic object map directly, and then post the results here? It would be interesting to compare the three cases, the current query, then modified to use acs_object_party_privilege_map, then further modified to use the magic object map table.
I'm curious as to the large number of physical reads in the existing query ... if that holds steady for repeated runs then doesn't that indicate your buffer cache is too small? This is just a side issue, though, I would expect rewriting the query to speed things up considerably.
