Re: 7.4 dramatically slower than 7.3?

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