Yes, it gets much more interesting if you remove the other PL/SQL calls. If you have time could you just return the site node node_id and otherwise strip out all the PL/SQL calls with the acs_party_object_privilege_map version using "-1" so we can get an idea as to the cost of the perm check alone?
Using magic numbers in the code isn't really a good idea, for instance I just changed the security context object id from 0 to -4 in 4.6.1 and had to chase code that had hardcoded object 0 (fortunately not much had.) I needed object id 0 in order to provide a "real" party with party_id 0 for the "unregistered visitor" ([ad_conn user_id] == 0 case) so I could remove a UNION branch from the various views (all UNION branches are gone now, more important for PG than Oracle.)
PG doesn't have global variables and I'd hate to see us adopt an Oracle-only solution. With a subselect in your from clause you can probably grab the magic object id "properly" and end up with the same efficiency:
from (select acs.magic_object_id('the public') as party_id from dual) public
...
where party_id = public.party_id
while maintaining the abstraction.
File storage queries improved to a much greater extent when I moved to using the acs_party_object_privilege_map. What I've been seeing is more consistency using the view compared with calling permission_p in the where clause. Sometimes Oracle seems to get this bright idea that it should call permission_p() first on all rows before doing any other qualification on them and bang! you're out 'o luck.
Hmmm ... come to think of it, I think Oracle's calling acs.magic_object_id for every row in the acs_party_object_privilege_map before it weeds any out and that's why removing the call made such a HUGE difference!!! Yeah, I'm sure of it. BAD Oracle! NASTY Oracle! DON'T DO THAT, ORACLE!
Flattening the privilege hierarchy can be done, we've denormalized all this in the PG version. But ... getting rid of all those "foo_*" style privileges should make that unnecessary. Even CONNECT BY should be fast when there's only one or two things to connect!
I know you didn't want to focus on this query per se but real world examples like this are a big help when thinking about improving scalability.
