aka who needs open_fts when you have google
I had a troublesome page that was taking 32 seconds to return 353 rows. it's a big query with unions, subselects, cases, coalesces, plpgsql calls, the lot. So I did a google search to try to find out why it seemd like one of my subselects was being performed for each row when it was clearly immutable within the transaction.
Google's first half page of results wisely pointed me to this OpenACS thread:
Scalability of acs_objects and acs_object_context_index
I was Feeling Lucky® today ;)
I followed the sage like advice from Don Baccus to replace a call to acs_permission__permission_p in my query with a subselect into acs_object_party_privilege_map like so:
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 my query immediately dropped from 44513 to 801 ms !!!
