Re: [BUGS] SQL optimisation dead loop - Mailing list pgsql-bugs
From | Bruce Momjian |
---|---|
Subject | Re: [BUGS] SQL optimisation dead loop |
Date | |
Msg-id | 199903141819.NAA00729@candle.pha.pa.us Whole thread Raw |
List | pgsql-bugs |
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
pgsql-bugs by date: