Forum OpenACS Development: Re: Fixing a very slow query

Posted by Richard Hamilton on
I thought that the query planner was supposed to be clever enough to select the best method! I also have always been led to believe that 'exists' is superior to 'in' because the subquery need only return a boolean value rather that requiring a sequential search through the subquery output.

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.


Posted by Don Baccus on
Statistics-based optimization will always be fooled at times, unfortunately.

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.