Forum OpenACS Q&A: Re: Re: Re: Performance Problems - what is the bottleneck?

The hash join is killing you, it returns 82804 rows while PostgreSQL expected 198 rows. It looks you need a "VACUUM ANALYZE", if it doesn't help you need to configure PostgreSQL to gather more statistics while analyzing (it is a setting somewhere, read PostgreSQL docs).
Yes, I believe Daniel's right, good eye there.

Roc, what does the query:

select count(*)
from acs_object_party_privilege_map ppm
where ppm.object_id = '1228' and ppm.party_id = '24471989' and ppm.privilege = 'dotlrn_browse';

return?

Also .LRN and several OpenACS packages declare far too many custom privileges. I've mentioned this before and in fact have cleaned up a couple of packages. The hierarchy map is always short enough to be sequentially scanned. Cutting the number of privileges in the system in half would cut the cost of joining against the hierarchy table by half as well, on average.

Something folks should think about while working on new packages especially (please, don't define new versions of "read" or "write" when plain old "read" or "write" will work!)

This isn't the cause of your extremely slow query, rather I'm pointing out (again) that in those cases where permissions queries are reasonably fast, they could be made a couple of times faster by reducing the number of unnecessary custom privileges.