Skip site navigation (1) Skip section navigation (2)

Re: Let's talk up 6.3

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: raines(at)SLAC(dot)Stanford(dot)EDU
Cc: vadim(at)sable(dot)krasnoyarsk(dot)su, hackers(at)postgresql(dot)org (PostgreSQL-development)
Subject: Re: Let's talk up 6.3
Date: 1998-03-30 05:16:16
Message-ID: 199803300516.AAA02693@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
> 
> I have made no indices yet.  And these are the only two tables
> in the database (beside the system ones).

No indexes.  No wonder it takes so long.  Put an index on
mdc1_runs.runnum and mdc1_simu.version, and see how fast it is.  Did
Oracle manage to do this quickly without the indexes?

Having it crash is certainly not an acceptable outcome, but I am sure
indexes will fix the problem.

Now, the fact that it runs quickly as separate queries, even without the
indexes, but takes a long time with the indexes, I think is
understandable.  Think of a join of two tables.  You can do through each
quickly, but if you join two non-indexed fields, it will take quite some
time.  I think our subselect code is doing just that.  We designed it
that way to give good performance for the majority of subselects,
including correlated ones.


> 
> bbrmdc=> explain verbose select distinct runtype from mdc1_runs where runnum in
> bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');
> NOTICE:  QUERY PLAN:
>  
> 
> 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)
>  


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Responses

pgsql-hackers by date

Next:From: Thomas G. LockhartDate: 1998-03-30 06:32:49
Subject: Re: [HACKERS] Modules
Previous:From: Vadim B. MikheevDate: 1998-03-30 03:19:13
Subject: Re: [HACKERS] Reminder: Indices are not used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group