From: | "Vadim B(dot) Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su> |
---|---|
To: | Igor Sysoev <igor(at)nitek(dot)ru> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] subselect and optimizer |
Date: | 1998-04-22 06:00:44 |
Message-ID: | 353D878B.BD3F3A02@sable.krasnoyarsk.su |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Sysoev | 1998-04-22 06:35:09 | Re: [HACKERS] subselect and optimizer |
Previous Message | Vadim B. Mikheev | 1998-04-22 05:51:15 | Re: [QUESTIONS] How to use memory instead of hd? |