Forum OpenACS Development: Re: Fixing a very slow query
I remember asking questions about optimising the permissions queries in OpenACS after seeing some very strange query times. It was Don who explained that 'vacuum analyse' in postgres collects stats and allows the planner to make much better decisions.
The combination of indices on the integer keys and multiple runs of vacuum analyse should result in a much improved execution plan.
Regarding "exists" vs. "in", you're probably remembering advice from long ago, as the PG people figured out how to implement "in" as a special kind of join-like operation some years ago, thus making it as efficient in PG as it had been in Oracle.
In the past "in" was always extremely slow.
"exists" should still be faster, though.