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

Janko Richter (j.richter@wallstreet-develop.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
situationsthis bug (?) makes the queries extremly slow.
 

Regards , Janko Richter

Sample Code


No file was uploaded with this report