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