Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #716: No index usage for "WHERE a IN ( SELECT ...)"
Date: 2002-07-19 12:27:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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)
    ->  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

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2002-07-19 13:49:43
Subject: Re: pg_ctl failure with older Bourne shells (use ${1:+"$@"})
Previous:From: pgsql-bugsDate: 2002-07-19 12:22:28
Subject: Bug #715: Too much memory consuming with postmaster

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group