Thread

Re: [BUGS] SQL optimisation dead loop

Bruce Momjian <maillist@candle.pha.pa.us> — 1999-03-14T18:19:27Z

Version 6.5, due to go beta soon, will fix this problem.  I have
overhauled the optimizer.

> Your name               : Patrick Valsecchi 
> Your email address      : patrick@dante.urbanet.ch
> 
> 
> System Configuration
> ---------------------
>   Architecture (example: Intel Pentium)         : Pentium or K6 ???
> 
>   Operating System (example: Linux 2.0.26 ELF)  : Linux 2.0.32 RedHat5.0
> 
>   PostgreSQL version (example: PostgreSQL-6.3.2)  : PostgreSQL-6.3.2
> 
>   Compiler used (example:  gcc 2.7.2)           : gcc version 2.7.2.3
> 
> 
> Please enter a FULL description of your problem:
> ------------------------------------------------
> 
> I am developing a big (15 tables) web application witch use Postgres.
> One of my queries is crashing badly postgres. It's consuming all the memory and die when it's full.
> 
> It seems to be the optimizer, since postgres is dieing even if I add an EXPLAIN before the query.
> 
> I have put in my tables the only data for having only one row as result of my query.
> 
> 
> Please describe a way to repeat the problem.   Please try to provide a
> concise reproducible example, if at all possible: 
> ----------------------------------------------------------------------
> 
> Here is the building commands:
> 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, a_lut oid, tipe oid, dest5 oid, zone5 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);
> CREATE TABLE zone4 (nom varchar, zone3 oid);
> CREATE TABLE zone3 (nom varchar, zone2 oid);
> CREATE TABLE zone2 (nom varchar, zone1 oid);
> CREATE TABLE zone1 (nom varchar);
> 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);
> 
> And here is the "query of death":
> SELECT offre.oid,offre.date_creation,offre.valide_depuis,offre.valide_jusqua,
>   offre.commission,offre.fichier,offre.client,type.oid,type.nom,type.descr,
>   dest5.oid,dest5.nom,dest4.oid,dest4.nom,dest3.oid,dest3.nom,dest2.oid,
>   dest2.nom,dest1.oid,dest1.nom,zone5.oid,zone5.nom,zone4.oid,zone4.nom,
>   zone3.oid,zone3.nom,zone2.oid,zone2.nom,zone1.oid,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 AND
>   offre.zone5=zone5.oid AND zone5.zone4=zone4.oid AND zone4.zone3=zone3.oid AND
>   zone3.zone2=zone2.oid AND zone2.zone1=zone1.oid
> 
> I know, it's a huge query, but it's under the 8192 bytes limit.
> 
> 
> 
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> 
> 
> 
> =============
> 
> Thanks for your help.
> 
> I'm not subscribed to the mailing list. Send your questions directly to me...
> 
> Best regards.
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026