Re: [BUGS] SQL optimisation dead loop
От | Bruce Momjian |
---|---|
Тема | Re: [BUGS] SQL optimisation dead loop |
Дата | |
Msg-id | 199807131906.PAA21752@candle.pha.pa.us обсуждение исходный текст |
Список | 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@candle.pha.pa.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 по дате отправления: