subselect and optimizer - Mailing list pgsql-hackers

From Igor Sysoev
Subject subselect and optimizer
Date
Msg-id 199804211048.OAA09082@ns.nitek.ru
Whole thread Raw
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Patrice Hédé
Date:
Subject: Re: [DOCS] FAQ organization
Next
From: The Hermit Hacker
Date:
Subject: Re: [HACKERS] LINUX_ELF