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
