This could not have possibly worked well under .LRN 2.0 ... did forums perhaps do something else rather than call that page in 2.0?
Anyway, this query exhibits all the badness of the original permissions queries that I sped up back for 4.6. I didn't realize this query existed.
Note the UNION ALL statements. PG doesn't optimize UNION ALL. Also notice all the references to "-2" etc ... that's "The public" (or registered users?) ...
The denormalized party-privilege map I added in 4.6 can be used to speed this up. Take a look at the current permissions view "acs_object_party_privilege_map" and its use of "Party_approved_member_map" for a hint as to how to tackle this.
Also ... the call to "acs_permission__permission_p(0, ptab.grantee_id, 'admin') = 'f'" in the where clause is a BIG NO NO for both Oracle and PG.
They each should read something like:
"not in (select 1 from acs_object_party_privilege_map where object_id = 0 and grantee_id = ptab.grantee_id and privilege = 'admin')"
for performance.
However I think the use of object 0 is bogus in this case as it's now the "unregistered visitor" not the system context root. That "0" should be replaced with the current value for the context root (see acs_magic_objects)
