Re: index not used with subselect in where clause ?

On Mon, 16 Apr 2001, Christian Fritze wrote:

> SELECT attr1 FROM table1 WHERE attr1 IN (<list of ints>)
> AND <more conditions>;
>
> where <list of ints> is entered explicitly (or generated by a
> program) everything works fine and fast using the index on attr1.
>
> But when I try doing a
>
> SELECT attr1 FROM table1 WHERE attr1 IN (<SUBSELECT returning list of ints>)
> AND <more conditions>;
>
> then the SELECT on table1 uses a sequential scan running 'endlessly'.

From the FAQ:

4.23) Why are my subqueries using IN so slow?

Currently, we join subqueries to outer queries by sequentially scanning
the result of the subquery for each row of the outer query. A workaround
is to replace IN with EXISTS:

SELECT *
FROM tab
WHERE col1 IN (SELECT col2 FROM TAB2)

to:
SELECT *
FROM tab
WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)

We hope to fix this limitation in a future release.