Performance-Probleme mit 8.1

Hi,

folgendes SQL-Statement bereitet mir erhebliche Kopfschmerzen. Unter Version 7.2 benötigt die Ausführung 5 sec unter 8.1 mehr als 2 min.
Ursache sind so weit ich dies erkennen kann die letzten drei Joins im Statement, aber wie kann ich dies umformulieren, das es unter 8.1 wieder in einer akzeptablen Antwortzeit läuft.

###########
SELECT SUBSTRING(T1.FA_MODELLCODE, 1, 3) AS MG_GRUPPENNUMMER, MG_BEZEICHNUNG, COUNT() AS ANZAHL,
SUM( COALESCE(P2009T511.HF_RUECKNAHMEPREIS, P2009T502.BK_RECHNUNGSSUMME, T1.FA_CIF) ) AS PREIS,
( CURRENT_DATE - DATE( COALESCE(p2009t511.hf_ruecknahmedatum, p2009t502.bk_rechnungsdatum, t1.create_date) ) - 1 ) / 30 AS MONATE
FROM P2009T509 t1
LEFT JOIN P2009T215 ON SUBSTRING(T1.FA_MODELLCODE, 1, 3) = P2009T215.MG_GRUPPENNUMMER
INNER JOIN P2009T504 ON P2009T504.FA_ID = T1.FA_ID AND P2009T504.KO_ART = ‘0’
LEFT JOIN ( SELECT Y510.
FROM P2009T510 Y510 INNER JOIN ( SELECT MAX(T510.FK_ID) AS FK_ID, T510.FA_ID FROM P2009T510 T510 GROUP BY T510.FA_ID ) Z510 ON Y510.FA_ID = Z510.FA_ID AND Y510.FK_ID = Z510.FK_ID ) P2009T510 ON P2009T510.FA_ID = T1.FA_ID
LEFT JOIN ( SELECT Y502.* FROM P2009T502 Y502 INNER JOIN ( SELECT MAX(T502.BK_ID) AS BK_ID, T502.FA_ID FROM P2009T502 T502 GROUP BY T502.FA_ID ) Z502 ON Y502.FA_ID = Z502.FA_ID AND Y502.BK_ID = Z502.BK_ID ) P2009T502 ON P2009T502.FA_ID = T1.FA_ID
LEFT JOIN ( SELECT Y511.* FROM P2009T511 Y511 INNER JOIN ( SELECT MAX(T511.HF_ID) AS HF_ID, T511.FA_ID FROM P2009T511 T511 GROUP BY T511.FA_ID ) Z511 ON Y511.FA_ID = Z511.FA_ID AND Y511.HF_ID = Z511.HF_ID ) P2009T511 ON P2009T511.FA_ID = T1.FA_ID
WHERE t1.fa_status <> ‘BuyBack’ AND t1.fa_status <> ‘Verkauft’
AND (P2009T510.fk_status IS null OR P2009T510.fk_status <> ‘0’)
AND P2009T502.bk_id is null
##########
Jede Hilfe ist recht!!!

Im voraus vielen Dank
Kurt

Kannst du das ganze mal mit einem EXPLAIN dafür ausführen (und vielleicht alles in code Tags, siehe Button über dem Eingabefeld.

Danke

Sorry zum einen das SQL-Statemnet war nicht vollstaändig, hier also noch einmal:

#############################
SELECT MG_GRUPPENNUMMER, 
MG_BEZEICHNUNG, COUNT(*) AS ANZAHL, 
SUM( COALESCE(P2009T511.HF_RUECKNAHMEPREIS, P2009T502.BK_RECHNUNGSSUMME, T1.FA_CIF) ) AS PREIS, 
( CURRENT_DATE - DATE( COALESCE(p2009t511.hf_ruecknahmedatum, p2009t502.bk_rechnungsdatum, t1.create_date) ) - 1 ) / 30 AS MONATE 
FROM P2009T509 t1 
LEFT JOIN P2009T215 ON SUBSTRING(T1.FA_MODELLCODE, 1, 3) = P2009T215.MG_GRUPPENNUMMER 
INNER JOIN P2009T504 ON P2009T504.FA_ID = T1.FA_ID AND P2009T504.KO_ART = '0' 
LEFT JOIN ( SELECT Y510.* FROM P2009T510 Y510 INNER JOIN ( SELECT MAX(T510.FK_ID) AS FK_ID, T510.FA_ID FROM P2009T510 T510 GROUP BY T510.FA_ID ) Z510 ON Y510.FA_ID = Z510.FA_ID AND Y510.FK_ID = Z510.FK_ID ) P2009T510 ON P2009T510.FA_ID = T1.FA_ID 
LEFT JOIN ( SELECT Y502.* FROM P2009T502 Y502 INNER JOIN ( SELECT MAX(T502.BK_ID) AS BK_ID, T502.FA_ID FROM P2009T502 T502 GROUP BY T502.FA_ID ) Z502 ON Y502.FA_ID = Z502.FA_ID AND Y502.BK_ID = Z502.BK_ID ) P2009T502 ON P2009T502.FA_ID = T1.FA_ID 
LEFT JOIN ( SELECT Y511.* FROM P2009T511 Y511 INNER JOIN ( SELECT MAX(T511.HF_ID) AS HF_ID, T511.FA_ID FROM P2009T511 T511 GROUP BY T511.FA_ID ) Z511 ON Y511.FA_ID = Z511.FA_ID AND Y511.HF_ID = Z511.HF_ID ) P2009T511 ON P2009T511.FA_ID = T1.FA_ID 
WHERE t1.fa_status <> 'BuyBack' AND t1.fa_status <> 'Verkauft' 
AND (P2009T510.fk_status IS null OR P2009T510.fk_status <> '0') 
AND P2009T502.bk_id is null 
group by MG_GRUPPENNUMMER, MG_BEZEICHNUNG, P2009T511.HF_RUECKNAHMEPREIS, P2009T502.BK_RECHNUNGSSUMME, T1.FA_CIF, p2009t511.hf_ruecknahmedatum, p2009t502.bk_rechnungsdatum, t1.create_date
#############################

So und hier die Informationen nach der Zerlegung:
#############################
GroupAggregate  (cost=3787.12..3789.91 rows=62 width=113)
  ->  Sort  (cost=3787.12..3787.28 rows=62 width=113)
        Sort Key: p2009t215.mg_gruppennummer, p2009t215.mg_bezeichnung, y511.hf_ruecknahmepreis, y502.bk_rechnungssumme, t1.fa_cif, y511.hf_ruecknahmedatum, y502.bk_rechnungsdatum, t1.create_date
        ->  Hash Left Join  (cost=2563.64..3785.28 rows=62 width=113)
              Hash Cond: ("outer".fa_id = "inner".fa_id)
              ->  Hash Left Join  (cost=2558.69..3780.01 rows=62 width=96)
                    Hash Cond: ("outer".fa_id = "inner".fa_id)
                    Filter: ("inner".bk_id IS NULL)
                    ->  Hash Left Join  (cost=2207.83..3428.83 rows=62 width=75)
                          Hash Cond: ("outer".fa_id = "inner".fa_id)
                          Filter: (("inner".fk_status IS NULL) OR ("inner".fk_status <> '0'::bpchar))
                          ->  Hash Join  (cost=214.80..1435.47 rows=62 width=75)
                                Hash Cond: ("outer".fa_id = "inner".fa_id)
                                ->  Hash Left Join  (cost=1.12..948.16 rows=13651 width=75)
                                      Hash Cond: ("substring"(("outer".fa_modellcode)::text, 1, 3) = ("inner".mg_gruppennummer)::text)
                                      ->  Seq Scan on p2009t509 t1  (cost=0.00..837.82 rows=13651 width=43)
                                            Filter: (((fa_status)::text <> 'BuyBack'::text) AND ((fa_status)::text <> 'Verkauft'::text))
                                      ->  Hash  (cost=1.10..1.10 rows=10 width=50)
                                            ->  Seq Scan on p2009t215  (cost=0.00..1.10 rows=10 width=50)
                                ->  Hash  (cost=213.52..213.52 rows=62 width=4)
                                      ->  Index Scan using p2009t504_x3 on p2009t504  (cost=0.00..213.52 rows=62 width=4)
                                            Index Cond: (ko_art = '0'::bpchar)
                          ->  Hash  (cost=1993.04..1993.04 rows=1 width=9)
                                ->  Nested Loop  (cost=785.46..1993.04 rows=1 width=9)
                                      Join Filter: ("inner".fa_id = "outer".fa_id)
                                      ->  Subquery Scan z510  (cost=785.46..787.96 rows=200 width=8)
                                            ->  HashAggregate  (cost=785.46..785.96 rows=200 width=8)
                                                  ->  Seq Scan on p2009t510 t510  (cost=0.00..738.97 rows=9297 width=8)
                                      ->  Index Scan using "T510_fk_id" on p2009t510 y510  (cost=0.00..6.01 rows=1 width=13)
                                            Index Cond: (y510.fk_id = "outer".fk_id)
                    ->  Hash  (cost=350.85..350.85 rows=2 width=29)
                          ->  Hash Join  (cost=300.04..350.85 rows=2 width=29)
                                Hash Cond: (("outer".fa_id = "inner".fa_id) AND ("outer".bk_id = "inner".bk_id))
                                ->  Subquery Scan z502  (cost=150.02..152.52 rows=200 width=8)
                                      ->  HashAggregate  (cost=150.02..150.52 rows=200 width=8)
                                            ->  Seq Scan on p2009t502 t502  (cost=0.00..128.68 rows=4268 width=8)
                                ->  Hash  (cost=128.68..128.68 rows=4268 width=29)
                                      ->  Seq Scan on p2009t502 y502  (cost=0.00..128.68 rows=4268 width=29)
              ->  Hash  (cost=4.95..4.95 rows=1 width=25)
                    ->  Hash Join  (cost=2.82..4.95 rows=1 width=25)
                          Hash Cond: (("outer".fa_id = "inner".fa_id) AND ("outer".hf_id = "inner".hf_id))
                          ->  Seq Scan on p2009t511 y511  (cost=0.00..1.56 rows=56 width=29)
                          ->  Hash  (cost=2.54..2.54 rows=56 width=8)
                                ->  Subquery Scan z511  (cost=1.84..2.54 rows=56 width=8)
                                      ->  HashAggregate  (cost=1.84..1.98 rows=56 width=8)
                                            ->  Seq Scan on p2009t511 t511  (cost=0.00..1.56 rows=56 width=8)
#############################

War es dass, was benötigt wurde?

Vielen Dank im voraus
Kurt

Och nöö, pack das bitte mal in Code oder Quote Tags, so das man sowohl im SQL Befehl wie auch in der Explain Ausgabe die Einrückungen am Anfang sieht.
Ich hab keine Lust, mir das selber im Kopf vorzustellen, wie weit ich etwas einrücken muss.

Danke

erledigt O:-) aber ich muss da mal was machen. das ist einfach net so optimal…

cu tb.

WHERE t1.fa_status <> ‘BuyBack’ AND t1.fa_status <> ‘Verkauft’

Die Werte 'BuyBack* und ‘Verkauft’ gehören normalisiert, sprich, in Integer umgewandelt. Danach kannst du auf die Index Spalten nen Index legen und sauber joinen. Das spart ausserdem Performance, weil du nicht immer Stringvergleiche durchführen musst.


AND (P2009T510.fk_status IS null OR P2009T510.fk_status <> ‘0’)

Wieso kann man hier NULL und 0 angeben?
Kannst du die Datenbank so hinbekommen, das du vielleicht nur Werte speicherst, NULL ganz verbietest und 0 als Default Value angibst?

Ist fk_status udn fa_status ein Integer oder ein Textfeld?

Für alle Spalten, die in WHERE Bedingungen und Joins vorkommen, würde ich prüfen, ob es sich lohnt, einen Index anzulegen.


Alles in allem schaut diese Datenbank nicht sehr normalisiert für mich aus, so das sich daraus schon eine Menge Probleme ableiten lassen.

Was genau ist das für ein Projekt oder System?


Andreas

Hi,

ich habe leider keinen Einfluß auf das Datenmodell, muß nur Auswertungen generieren.
Zur Zeit sind die Auswertungen in Java geschrieben, soll aber nun durch einen Reportgenerator ersetzt werden.
Die Anwendung selbst ist eine Webapplikation, und ist erstmal nicht änderbar.
Was ist mit den eigentlichen Performance-unterschieden zwischen 7 und 8 ?

Die große Antwort ist: Depends

Normal sollte es schneller sein, aber wenn man Ecken findet, die langsamer geworden sind, wäre es sicherlich interessant, herauszufinden, warum.

Das Datenmodell ist schon mal nicht sehr sauber und wird dich/euch sicherlich einen gutteil Performance kosten. Vielleicht ist 8.x hier auch etwas pingeliger …

Kannst du Indicies anlegen? Kannst du Teile deines Queries (speziell jene, die als Seq-Scan gesucht werden) mit einem Index prüfen, ob die schneller werden?

Hi,

ja Indexe kann ich anlegen, aber hat bisher nichts gebracht.
Ich habe die Left Join (select … Teile im SQL-Statement schon mal versucht umzuformulieren, aber die Ergebnissmenge stimmte dann nicht. Diese Bremsen eindeutig die Version 8 aus.

Danke
Kurt

Hmm ok, jetzt bist du wohl an einem Punkt, wo man testen und probieren muss. Das geht nur noch schwer, wenn man nicht selber an der Datenbank sitzt, also können wir dir bei deiner zugegebenermassen komplizierten Anfrage nicht wirklich helfen.

Vielleicht ist es bloss ein kleines Detail, vielleicht ist es etwas größeres … habt ihr keinen Spezialisten für so etwas im Haus oder vor Ort?

Keine Spezialisten für PostgreSQL im Haus oder bekannt, deshalb habe ich mich ja hier ans Forum gewandt.

Danke
Kurt

Na wie gesagt, die Angelegenheit ist wahrscheinlich zu komplex, als das man sie ohne eine Testumgebung lösen könnte.

Schaut euch mal bei euch in der Stadt um, vielleicht findet sich jemand, den ihr da ranlassen könnt.