Bug #716: No index usage for "WHERE a IN ( SELECT ...)"

Janko Richter (j(dot)richter(at)wallstreet-develop(dot)de) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
No index usage for "WHERE a IN ( SELECT ...)"

Long Description
I have created a table "testtab" with an unique indexed (smallint) column "a" and 10.000 records. When I do
"EXPLAIN SELECT * FROM testtab WHERE a IN ( 12::smallint )"
Postgresql 7.2.1 gives :

Index Scan using uhu2idx on testtab (cost=0.00..3.01 rows=1 width=2)

But when I'm using a subquery i.e.:
"EXPLAIN SELECT * FROM testtab WHERE a IN ( SELECT 12::smallint )"

PG gives:

Seq Scan on testtab (cost=0.00..275.00 rows=5000 width=2)
SubPlan
-> Materialize (cost=0.01..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)

The subquery is an example for a subquery result set. With "real" subquerys PG does a seq scan too.Of course, in some situations this bug (?) makes the queries extremly slow.

Regards , Janko Richter

Sample Code

No file was uploaded with this report