>Strangely enough doing an EXPLAIN on the two queries shows that using
>EXISTS would be faster than IN ... even though it isn't ..
A sad sidenote: I am stuck here with a similar IN/EXIST problem. One of our expensive queries contains NOT IN and IN as subqueries. As I was advised on this list, I tried to replace IN with EXISTS. When doing so for part of the query (omitting one of the IN subqueries) the IN and EXIST versions are both about the same speed in execution (about 30sec).
EXPLAIN tells me, that the EXIST version should be 15 times faster, which it is not. Caching is also not an issue here.
EXPLAIN also shows, that both queries want to perform a sequential scan on the outermost query part, instead of an index scan (where clause on the primary key). If I replace the innermost query by the results it gives (splitting the request in two requests), than the planner uses the index scan and is in fact much faster!
My next plan is to switch from 7.1.3 to 7.2, but that requires some planning, as the database is permamently used.
Regards
Jan