Thread: subselect and optimizer

I'm using PostgreSQL 6.3.2.

As reported in some messages ago PostgreSQL has problem with
"... where some_field in (select ..." type subqueries.
One of the solutions was to create indecies.
I created two indecies for character(9) fields key and newkey:
create index key_i on bik (key);
create index newkey_i on bik (newkey);
run two quiery explain:

bik=> explain select * from bik where key in (select newkey from bik where
bik=
'044531864');
NOTICE:  Apr 21 14:15:41:QUERY PLAN:

Seq Scan on bik  (cost=770.92 size=1373 width=113)
  SubPlan
    ->  Seq Scan on bik  (cost=770.92 size=1 width=12)

EXPLAIN
bik=> explain select * from bik where key = (select newkey from bik where
bik='
044531864');
NOTICE:  Apr 21 14:16:01:QUERY PLAN:

Index Scan on bik  (cost=2.05 size=1 width=113)
  InitPlan
    ->  Seq Scan on bik  (cost=770.92 size=1 width=12)

EXPLAIN

When I run first query it hang for a long time, at least 10 minutes
(I interrupted it) while second one completed in 1 second.
Table bik has about 13000 rows and 2.6M size.
It seems the problem is that in first queiry plan is "Seq Scan" while
in second is "Index Scan". How it can be fixed ?

with best regards,
Igor Sysoev

Igor Sysoev wrote:
>
> I'm using PostgreSQL 6.3.2.
>
> As reported in some messages ago PostgreSQL has problem with
> "... where some_field in (select ..." type subqueries.
> One of the solutions was to create indecies.
> I created two indecies for character(9) fields key and newkey:
> create index key_i on bik (key);
> create index newkey_i on bik (newkey);
> run two quiery explain:
>
> bik=> explain select * from bik where key in (select newkey from bik where
> bik='044531864');
> NOTICE:  Apr 21 14:15:41:QUERY PLAN:
>
> Seq Scan on bik  (cost=770.92 size=1373 width=113)
>   SubPlan
>     ->  Seq Scan on bik  (cost=770.92 size=1 width=12)
          ^^^
This is very strange. Index Scan should be used here.
I'll try to discover this...

BTW, IN is slow (currently :) - try to create 2-key index on bik (bik, newkey)
and rewrite your query as

select * from bik b1 where EXISTS (select newkey from bik where
bik = '....' and b1.key = newkey)

And let's know... (Note, that index on (newkey, bik) may be more useful
than on (bik, newkey) - it depends on your data).

Vadim