Re: [BUGS] SQL optimisation dead loop

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(at)dante(dot)urbanet(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026