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.