Re: [HACKERS] subselect and optimizer

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