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

Re: [BUGS] SQL optimisation dead loop

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: utesch(at)aut(dot)tu-freiberg(dot)de
Cc: hackers(at)postgreSQL(dot)org (PostgreSQL-development), patrick(at)dante(dot)urbanet(dot)ch
Subject: Re: [BUGS] SQL optimisation dead loop
Date: 1998-07-13 19:06:17
Message-ID: 199807131906.PAA21752@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
[I am sending this to the hackers list, and the GEQO author.]

Yikes.  This user has a query that causes the GEQO optimizer to really
go into orbit.  According to the user, it consumes 135MB of memory
before failing.

Any comments?

> On Mon, 13 Jul 1998, you wrote:
> >That is strange that GEQO is failing on this.  I have never heard of
> >this happening.  In fact, geqo was designed for large number of table
> >joins.
> >
> >Can you send me a reproducable case that I can test with?
> 
> My first mail was containing such a sample. If you loosed it, I've tried to 
> rewrote (I am at home) it at the end of this mail.
> 
> Here is the query:
> CREATE TABLE client (nom varchar not null, passwd varchar not null,
> peut_creer bool not null, peut_lire bool not null, peut_stat bool not null,
> est_admin bool, est_fournisseur bool not null, est_client bool not null,
> raison_social varchar, contact varchar, adresse varchar, telephone varchar,
> fax varchar, adr_facture varchar);
> 
> CREATE TABLE type (nom varchar not null, descr varchar not null);
> 
> CREATE TABLE offre (client oid, tipe oid, zone5 oid, dest5 oid, date_creation
> datetime, valide_depuis datetime, valide_jusqua datetime, fichier oid,
> commission float);
> 
> CREATE TABLE a_lut (offre oid, client oid, date_lecture datetime);
> 
> CREATE TABLE prix (offre oid, valeur float, nb_jours int, valide_de datetime,
> valide_a datetime);
> 
> CREATE TABLE zone5 (nom varchar, zone4 oid, prix float);
> CREATE TABLE zone4 (nom varchar, zone3 oid, prix float);
> CREATE TABLE zone3 (nom varchar, zone2 oid, prix float);
> CREATE TABLE zone2 (nom varchar, zone1 oid, prix float);
> CREATE TABLE zone1 (nom varchar, prix float);
> 
> CREATE TABLE dest5 (nom varchar, dest4 oid);
> CREATE TABLE dest4 (nom varchar, dest3 oid);
> CREATE TABLE dest3 (nom varchar, dest2 oid);
> CREATE TABLE dest2 (nom varchar, dest1 oid);
> CREATE TABLE dest1 (nom varchar);
> 
> SELECT offre.oid as offre_oid,offre.client as offre_client,
> offre.date_creation as offre_date_creation,
> offre.valide_depuis as offre_valide_depuis,
> offre.valide_jusqua as offre_valide_jusqua,
> offre.commission as offre_commission,
> type.oid as type_oid, type.nom as type_nom,
> dest5.oid as dest5_oid,dest5.nom as dest5_nom,dest4.oid as dest4_oid,
> dest4.nom as dest4_nom,dest3.oid as dest3_oid,dest3.nom as dest3_nom,
> dest2.oid as dest2_oid,dest2.nom as dest2_nom,dest1.oid as dest1_oid,
> dest1.nom as dest1_nom, zone5.oid as zone5_oid,zone5.nom as zone5_nom,
> zone4.oid as zone4_oid, zone4.nom as zone4_nom,zone3.oid as zone3_oid,
> zone3.nom as zone3_nom, zone2.oid as zone2_oid,zone2.nom as zone2_nom,
> zone1.oid as zone1_oid, zone1.nom as zone1_nom  FROM
> offre,type,dest5,dest4,dest3,dest2,dest1,zone5,zone4,zone3,zone2,zone1  
> WHERE offre.tipe=type.oid AND
> offre.dest5=dest5.oid AND  dest5.dest4=dest4.oid AND dest4.dest3=dest3.oid AND
> dest3.dest2=dest2.oid AND dest2.dest1=dest1.oid 
> offre.zone5=zone5.oid AND  zone5.zone4=zone4.oid AND zone4.zone3=zone3.oid AND
> zone3.zone2=zone2.oid AND zone2.zone1=zone1.oid 
> 
> BOOM!!!!
> 
> 
> best regards.
> --
>   -)                 Patrick Valsecchi   /\\  
>  _\_v    http://dante.urbanet.ch/~patrick/index.html
> 


-- 
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)

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-07-13 20:29:33
Subject: Sequence objects have no global currval operator?
Previous:From: Bruce MomjianDate: 1998-07-13 18:59:33
Subject: Re: Problems with your patch (Sorry).

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