Forum OpenACS Q&A: Re: Strange query performance when using <b>UNION</b>
7: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/24/05 12:01 PM
Another interesting fact is that this query is fast on both the development and the release databases - it's only slow on the live database. The main difference between these systems is that live has a much large CC_USERS with over 36000 records. This CC_USERS view is quite slow - just doing a SELECT COUNT(*) from it takes 5 seconds on live. The STAFF table has 247 records on live and 122 on the release database. When STAFF and CC_USERS are joined it correctly drives off the staff_id.
So, it's almost as though using the UNION causes a full table scan of CC_USERS (even though the explain plan didn't show that). I'm going to go check that explain plan again.
Curiouser and curiouser!