Hi Don,
just tested your modified query.
Changing the WHERE-clause from
acs_permission__permission_p(0, ptab.grantee_id, 'admin') = 'f'
to
not exists (select 1
from acs_object_party_privilege_map m
where m.object_id = acs__magic_object_id('security_context_root')
and m.party_id = ptab.grantee_id
and m.privilege = 'admin')
improved the query that
1. it is returning results again, without having me to remove that subquery
2. I took 17265 ms for the query to suceed
I assume that it will be even faster on the productive instance.
More info on that coming soon.
Thank you very much,
Nima