Bug #598: optimizer: convert 'IN' to join

piers haken (piersh(at)friskit(dot)com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
optimizer: convert 'IN' to join

Long Description
the optimizer should do better than a sequential scan with statements like:

SELECT * FROM t1 WHERE t1.index IN (SELECT t2.index FROM t2);

this gives:
Seq Scan on t1
SubPlan
-> Seq scan on t2

this is equivalent to (and should be transformed to)

SELECT t1.* FROM t1, t2 WHERE t1.index = t2.index;

which gives the much faster:
Nested Loop
-> Seq Scan on t1
-> Index Scan using t2_pkey on t2

FYI: SQL Server generates a hash table from t1 and probes it with pkey values of t2 read from a NON-primary index. For SQL Server, scanning a non-primary key takes fewer disk reads than scanning a primary key.

if you add a condition to the subquery on a non-unique column:

SELECT * FROM t1 WHERE t1.index IN (SELECT t2.index FROM t2 WHERE t2.value='something');
or
SELECT t1.* FROM t1, t2 WHERE t1.index=t2.index AND t2.value='something';

you also get two different plans:

Seq Scan on t1
SubPlan
-> Materialize
-> Index Scan using ix_t2_value on t2

as opposed to

Hash Join
-> Seq Scan on t1
-> Hash
-> Index Scan using ix_t2_value on t2

Sample Code

No file was uploaded with this report