Forum OpenACS Development: Re: acs-kernel acs_permission__permission_p performance

Collapse
Posted by Don Baccus on
I have noticed poor plans resulting from PG 7.4 in several cases where performance seemed better in earlier versions.

In particular the "exists" version of the query should NEVER EVER be slower than the "count(*)" version. "exists" can stop when it sees the first row returned from the query. The "count(*)" version has to count EVERY ROW.

So something's screwed up.

And it bums me out, I put a great deal of effort into optimizing these queries back in the 7.1 or 7.2 days and having version flip-flops in terms of performance as we seem to be seeing is a huge hassle. How can we support both 7.3 and 7.4 when the planner gives us vastly different (shittier) query plans for 7.4? And if 8.0 flips it back ... what can we depend on?

I would be interested in the query plans (EXPLAIN) for the two queries on your system.