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