Re: 7.4 dramatically slower than 7.3? - Mailing list pgsql-novice

Tom Lane wrote:

> You're showing actual time of 18 msec vs 34 msec in 7.3, which doesn't
> work out to 40 times slower on my calculator.  If there's a problem here
> you're not showing it.

Sorry for the confusion with 40 times slower I meant the execution of my app. due to this 1 query.

To clarify I modified the query:

7.3 with LEFT OUTER JOIN:
-------------------------------------
$ time psql test2 -f query.sql
  ?column?
----------

(1 row)


real    0m0.079s
user    0m0.000s
sys     0m0.010s
-------------------------------------

7.4. with LEFT OUTER JOIN:
-------------------------------------
$ time psql test2 -f query.sql
  ?column?
----------

(1 Zeile)


real    0m3.256s
user    0m0.038s
sys     0m0.026s

3.256 / 0.079 = 41 (That's what I meant)

7.3 without LEFT OUTER JOIN
---------------------------------------
$ time psql test2 -f query.sql
  ?column?
----------

(1 row)


real    0m0.072s = same as with LEFT OUTER JOIN
user    0m0.000s
sys     0m0.020s
---------------------------------------

7.4 without LEFT OUTER JOIN
$ time psql test2 -f query.sql
  ?column?
----------

(1 Zeile)


real    0m0.149s
user    0m0.035s
sys     0m0.020s
----------------------------------------



The query was (with LEFT OUTER JOIN):
SELECT
         NULL
FROM
                   objekt          o

   JOIN ( SELECT id_objekt, id2_objekt                   FROM objekt_objekt JOIN meta USING (
id_meta ) WHERE id_meta = 91 ) vater  ON (  vater.id_objekt = o.id_objekt )
   JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta USING (
id_meta ) WHERE id_meta = 110 ) Kapsel ON ( kapsel.id_objekt = o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=84 ) anzeige ON ( anzeige.id_objekt=o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_datum, datum_fld FROM datum JOIN meta USING ( id_meta )
WHERE id_meta=73 ) anzeige_datum_display_von ON (
anzeige_datum_display_von.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_datum, datum_fld FROM datum JOIN meta USING ( id_meta )
WHERE id_meta=74 ) anzeige_datum_display_bis ON (
anzeige_datum_display_bis.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_tf, tf FROM tf JOIN meta USING ( id_meta ) WHERE
id_meta=75 ) anzeige_enabled ON ( anzeige_enabled.id_objekt=anzeige.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=35 ) headline ON ( headline.id_objekt=o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=72 ) headline_CSS ON ( headline_CSS.id_objekt=headline.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=16 ) headline_CSS_regeln ON ( headline_CSS_regeln.id_objekt=headline_CSS.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=144 ) headline_CSS_hover ON ( headline_CSS_hover.id_objekt=headline_CSS.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=71 ) headline_txt ON ( headline_txt.id_objekt=headline.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=30 ) fliesstext ON ( fliesstext.id_objekt=o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_objekt_objekt, id2_objekt FROM objekt_objekt JOIN meta
USING ( id_meta ) WHERE id_meta=72 ) fliesstext_CSS ON (
fliesstext_CSS.id_objekt=fliesstext.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=16 ) fliesstext_CSS_regeln ON ( fliesstext_CSS_regeln.id_objekt=fliesstext_CSS.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=144 ) fliesstext_CSS_hover ON ( fliesstext_CSS_hover.id_objekt=fliesstext_CSS.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_txt, txt FROM txt JOIN meta USING ( id_meta ) WHERE
id_meta=71 ) fliesstext_txt ON ( fliesstext_txt.id_objekt=fliesstext.id2_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_tf, tf FROM tf JOIN meta USING ( id_meta ) WHERE
id_meta=34 ) html_translate ON ( html_translate.id_objekt=o.id_objekt )
   LEFT OUTER JOIN ( SELECT id_objekt, id_flt, flt FROM flt JOIN meta USING ( id_meta ) WHERE
id_meta=33 ) pos_y ON ( pos_y.id_objekt=o.id_objekt )
WHERE
       o.id_objekt=26377;


Again, sorry for the confusion, I hope now I made it a bit clearer.

TFYH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de