From: | "Vadim B(dot) Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su> |
---|---|
To: | raines(at)SLAC(dot)Stanford(dot)EDU |
Cc: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>, hackers(at)postgreSQL(dot)org |
Subject: | Re: Let's talk up 6.3 |
Date: | 1998-03-30 00:37:09 |
Message-ID: | 351EE935.655460A7@sable.krasnoyarsk.su |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Paul Raines wrote:
>
> I have made no indices yet. And these are the only two tables
> in the database (beside the system ones).
>
> bbrmdc=> explain verbose select distinct runtype from mdc1_runs where runnum in
> bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');
>
> Unique (cost=686.02 size=0 width=0)
> -> Sort (cost=686.02 size=0 width=0)
> -> Seq Scan on mdc1_runs (cost=686.02 size=1455 width=12)
> SubPlan
> -> Seq Scan on mdc1_simu (cost=733.02 size=1 width=12)
>
Current implementation of IN is very simple. As you see from EXPLAIN
for each row from mdc1_runs server performes SeqScan on mdc1_simu.
Try to create index on mdc1_simu (version) and let's know about results.
Also, you could create index on mdc1_simu (version, runnum) and re-write
your query as
select distinct runtype from mdc1_runs where
EXISTS (select * from mdc1_runs where version = '...' and
runnum = mdc1_runs.runnum);
- this can be faster.
In the future, subselects in FROM-clause will be implemented and
'IN' and others 'Op ANY' will be handled in this new way.
Vadim
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim B. Mikheev | 1998-03-30 02:38:08 | Re: [HACKERS] Optimizer fails? |
Previous Message | Gerhard Reithofer | 1998-03-29 22:07:28 | Re: [HACKERS] pgindent on odbc |