Re: IN vs EXIIST

Jan Weerts wrote:
>
> 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).

At least you get the same speed. In my case replacing IN with EXISTS
makes it about 25X slower!

> EXPLAIN tells me, that the EXIST version should be 15 times faster,
> which it is not. Caching is also not an issue here.

Yup, same as me. BTW how do I clear the cache?

> 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).

To test this I added an index on outer query search term and
lo-and-behold ...

Just like you I get a seq scan on the outer part for both but the IN
query does a seq scan on the inner query while the EXISTS uses an index
scan. The EXISTS is still just as slow though ..

> My next plan is to switch from 7.1.3 to 7.2, but that requires some
> planning, as the database is permamently used.

I'm on 7.2 so I don't know if that will help ;)

Jc