Thread

Re: Let's talk up 6.3

Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-03-30T00:37:09Z

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