subselect and optimizer

From: "Igor Sysoev" <igor(at)nitek(dot)ru>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: subselect and optimizer
Date: 1998-04-21 10:45:15
Message-ID: 199804211048.OAA09082@ns.nitek.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Good 1998-04-21 11:19:03 Re: [HACKERS] Re: [QUESTIONS] Configuration problems in PostgreSQL 6.3.2 on Linux-ELF
Previous Message Michael Meskes 1998-04-21 10:34:25 Re: [HACKERS] shift/reduce problem with ecpg