Performanceproblem bei Joins mit Views

Hi zusammen,
ich habe folgendes Performanceproblem mit Views und Joins mit der Postgre 8.2.4.

Ausgangslage :
Es existiert ein View, der ein ziemlich komplexes Datenmodell als Tabelle darstellt (Vererbung usw.) Die zugrunde liegende Tabelle hat ca. 14700 Einträge und wenn ich mir alle Einträge selektiere dauert das 31,5 Sekunden oder ca. 2.1 ms pro Datensatz, alles ok.

Select * from v_artikel

Wenn ich einen einzelnen Datensatz selektiere dauert das um die 15 ms. Wenn ich den Overhead wegrechne usw. ist das auch ok.

Select * from v_artikel where Artikelnr = 'XYZ'

Jetzt habe ich eine Tabelle mit Auftragspositionen, in der natürlich Artikel enthalten sind (auf der Artikelnr liegt natürlich ein Index). Meine Abfrage um die Positionen eines Auftrags zu selektieren sieht jetzt so aus:

Select * 
from    Auftragspos ap inner Join v_artikel a ON ap.artikelnr = a.artikelnr  
Where   AuftragsNr = 12345

Der Auftrag hat 14 Positionen und ich hätte jetzt erwartet, im schlechtesten Fall würde die Abfrage so 0,5 Sekunden laufen. In Wirklichkeit benötigt sie aber 26 Sekunden.
Meiner Ansicht nach liegt das Problem darin, dass der Optimizer erst das komplette Resultset des Views aufbaut, bevor er über den Join daran geht die Einträge aus v_artikel wieder rauszuschmeissen, die er nicht braucht.

Jetzt ändere ich das Statement folgendermaßen ab:

Select * 
from    Auftragspos ap inner Join v_artikel a ON ap.artikelnr = a.artikelnr  
Where   AuftragsNr = 12345 and    
a.artikelnr in (String mit den Artikelnummern)

oder konkret

Select * 
from    Auftragspos ap inner Join v_artikel a ON ap.artikelnr = a.artikelnr  
Where   AuftragsNr = 12345 and    
a.artikelnr in ('AAA,'BBB','CCC', ...)

Vorher selektiere ich alle Artikelnummern aus der Tabelle Auftragpos und baue diese zu einem String zusammen ('AAA,‘BBB’,‘CCC’, …)

Führt man dieses Statement aus, das genau das gleiche Ergebnis hat beträgt die Ausführungszeit nur noch 78 ms.
Anscheind reagiert hier der Optimizer und merkt von vorneherein, dass er nicht alle Datensätze von v_artikel benötigt, sondern nur die angegebenen.
Allerdings auch nur, wenn ich die Artikelnummern statisch ins Statement reinschreibe.

Folgendes Statement:

Select *  
from    Auftragspos ap inner join v_artikel a ON ap.Artikelnr = a.artikelnr  
Where   
  AuftragsNr = 12345 and    
  a.artikelnr in (Select distinct artikelnr from Auftragspos Where ka_nr = 12345 )

ist genauso langsam, wie die erste Version.

Die gefundene Lösung ist natürlich eine Möglichkeit, jedoch alles andere als elegant. Daher die Frage: gibt es hier eine Möglichkeit dem Optimizier “zu sagen” was er tun soll, oder sonst irgendwas, was ich bisher übersehen habe ?

Vielen Dank schon mal im Vorraus für die Hilfe.

Zeige uns mal ein EXPLAIN ANALYSE der problematischen Anbfrage.


Andreas

Hi,

das Explain wollte ich eigentlich vermeiden, da darin viel von dem zugrundeliegenden View hereikommt, was natürlich verwirrt.
Aber vermutlich geht es nicht ohne. :wink:

Hier die Analyse das Statements, das so langsam ist

Merge Join  (cost=174257.77..183373.67 rows=1 width=1303)
  Merge Cond: ((v_artikel_all.artikelnr)::text = inner.?column29?)
  ->  GroupAggregate  (cost=174217.26..183330.59 rows=200 width=1720)
        Filter: (max(as_aktlevel) = max(as_maxlevel))
        ->  Sort  (cost=174217.26..174484.38 rows=106851 width=1073)
              Sort Key: ak.artikelnr, a.artikelnr
              ->  Nested Loop  (cost=1.70..114524.90 rows=106851 width=1073)
                    ->  Nested Loop  (cost=0.00..1299.98 rows=17811 width=46)
                          Join Filter: ((a.art_schema)::text = (ars.as_name)::text)
                          ->  Seq Scan on artikelschema ars  (cost=0.00..1.01 rows=1 width=11)
                          ->  Seq Scan on artikel a  (cost=0.00..720.11 rows=17811 width=42)
                    ->  Bitmap Heap Scan on artikel a  (cost=1.70..5.76 rows=6 width=847)
                          Recheck Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))
                          ->  Bitmap Index Scan on artikel_pk  (cost=0.00..1.70 rows=6 width=0)
                                Index Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))
  ->  Sort  (cost=40.51..40.54 rows=11 width=309)
        Sort Key: (kap.artikelnr)::text
        ->  Bitmap Heap Scan on kap  (cost=4.34..40.32 rows=11 width=309)
              Recheck Cond: ((ka_nr)::text = '58742'::text)
              ->  Bitmap Index Scan on xkap_lieferext  (cost=0.00..4.34 rows=11 width=0)
                    Index Cond: ((ka_nr)::text = '58742'::text)

und hier die schnelle Version zum Vergleich

"Hash Join  (cost=773.46..809.49 rows=1 width=1303)"
"  Hash Cond: ((kap.artikelnr)::text = (v_artikelkopf_all.artikelnr)::text)"
"  ->  Bitmap Heap Scan on kap  (cost=4.34..40.32 rows=11 width=309)"
"        Recheck Cond: ((ka_nr)::text = '58742'::text)"
"        ->  Bitmap Index Scan on xkap_lieferext  (cost=0.00..4.34 rows=11 width=0)"
"              Index Cond: ((ka_nr)::text = '58742'::text)"
"        ->  Subquery Scan v_artikelkopf_all  (cost=747.07..768.07 rows=84 width=994)"
"              ->  GroupAggregate  (cost=747.07..767.23 rows=84 width=1720)"
"                    Filter: (max(as_aktlevel) = max(as_maxlevel))"
"                    ->  Sort  (cost=747.07..747.28 rows=84 width=1073)"
"                          ->  Nested Loop  (cost=82.13..744.39 rows=84 width=1073)"
"                                ->  Nested Loop  (cost=59.64..110.77 rows=14 width=46)"
"                                      ->  Seq Scan on artikelschema ars  (cost=0.00..1.01 rows=1 width=11)"
"                                      ->  Bitmap Heap Scan on artikel a  (cost=59.64..109.31 rows=14 width=42)"
"                                            Recheck Cond: ((artikelnr)::text = ANY (('{8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|35,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|36,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|37,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|38,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|39,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|40,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|41,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|42,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|43,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|44,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|45,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|46,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|47,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|48}'::character varying[])::text[]))"
"                                            ->  Bitmap Index Scan on artikel_pk  (cost=0.00..59.63 rows=14 width=0)"
"                                                  Index Cond: ((artikelnr)::text = ANY (('{8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|35,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|36,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|37,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|38,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|39,8002ALSA|ANTI.SCH.AST2.FP.SCHUERR|40,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|41,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|42,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|43,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|44,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|45,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|46,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|47,8020ALSA|ANTI.SCH.AST2.FP.SCHUERR|48}'::character varying[])::text[]))"
"                                ->  Bitmap Heap Scan on artikel a  (cost=22.50..44.66 rows=6 width=847)"
"                                      Recheck Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))"
"                                      ->  Bitmap Index Scan on artikel_pk  (cost=0.00..22.50 rows=6 width=0)"
"                                            Index Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))"

Gruss Jürgen

Ich wollte einklich ein EXPLAIN ANALYSE, da sieht man mehr.

Meine Vermutung: jede Menge tote Tupel, lange kein VACUUM gemacht. Im ersten Beitrag war die Rede von ca. 14.000 Rows, der Planner schätzt irgendwo was von 106851.

Andreas, knapp bei Zeit…

Hi,

ich schrieb in meinem ersten Beitrag, dass das Ergebnis des Views 14000 Datensätze umfasst. Die zugrundeliegenden Tabellen haben wesentlich mehr Einträge, die in dem View zusammengefasst werden.
Deswegen auch der Hinweis, dass das Ding recht komplex ist. Vacuum lief gestern und seitdem hat sich nichts an der Datenbank geändert, weil es eine Testdatenbank ist. Also keinen toten Tupel o.ä.

Hier nun der gewünschte Explain Analyse des langsamen Statements:

Merge Join  (cost=174257.77..183373.67 rows=1 width=1303) (actual time=26971.281..27062.924 rows=14 loops=1)
  Merge Cond: ((v_artikelkopf_all.artikelnr)::text = inner.?column29?)
  ->  GroupAggregate  (cost=174217.26..183330.59 rows=200 width=1720) (actual time=4745.874..27018.384 rows=11651 loops=1)
        Filter: (max(as_aktlevel) = max(as_maxlevel))
        ->  Sort  (cost=174217.26..174484.38 rows=106851 width=1073) (actual time=4740.716..4825.264 rows=39247 loops=1)
              Sort Key: ak.artikelnr, a.artikelnr
              ->  Nested Loop  (cost=1.70..114524.90 rows=106851 width=1073) (actual time=1.697..3703.803 rows=49647 loops=1)
                    ->  Nested Loop  (cost=0.00..1299.98 rows=17811 width=46) (actual time=1.384..423.650 rows=17811 loops=1)
                          Join Filter: ((a.art_schema)::text = (ars.as_name)::text)
                          ->  Seq Scan on artikelschema ars  (cost=0.00..1.01 rows=1 width=11) (actual time=0.013..0.016 rows=1 loops=1)
                          ->  Seq Scan on artikel a  (cost=0.00..720.11 rows=17811 width=42) (actual time=0.021..43.078 rows=17811 loops=1)
                    ->  Bitmap Heap Scan on artikel a  (cost=1.70..5.76 rows=6 width=847) (actual time=0.119..0.126 rows=3 loops=17811)
                          Recheck Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))
                          ->  Bitmap Index Scan on artikel_pk  (cost=0.00..1.70 rows=6 width=0) (actual time=0.111..0.111 rows=3 loops=17811)
                                Index Cond: ((a.artikelnr)::text = ANY ((ARRAY[ak.key1, ((((ak.key1)::text || '|'::text) || (ak.key2)::text))::character varying, ((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text))::character varying, ((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text))::character varying, ((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text))::character varying, ((((((((((((ak.key1)::text || '|'::text) || (ak.key2)::text) || '|'::text) || (ak.key3)::text) || '|'::text) || (ak.key4)::text) || '|'::text) || (ak.key5)::text) || '|'::text) || (ak.key6)::text))::character varying])::text[]))
  ->  Sort  (cost=40.51..40.54 rows=11 width=309) (actual time=0.282..0.311 rows=14 loops=1)
        Sort Key: (kap.artikelnr)::text
        ->  Bitmap Heap Scan on kap  (cost=4.34..40.32 rows=11 width=309) (actual time=0.082..0.141 rows=14 loops=1)
              Recheck Cond: ((ka_nr)::text = '58742'::text)
              ->  Bitmap Index Scan on xkap_lieferext  (cost=0.00..4.34 rows=11 width=0) (actual time=0.053..0.053 rows=14 loops=1)
                    Index Cond: ((ka_nr)::text = '58742'::text)
Total runtime: 27123.107 ms

Trotzdem noch mal danke für die Hilfe, auch wenn die Antwort etwas genervt klang.

Grüss Jürgen

Okay.

Trotzdem noch mal danke für die Hilfe, auch wenn die Antwort etwas genervt klang.

Grüss Jürgen

Nö, no problem. Mir fällt nur grad nix hilfreiches ein, außer daß Du das vielleicht mal in die [Performance] - Mailingliste kippen könntest (gleich mit dem SELECT und dem EXPLAIN ANALYSE). Vielleicht erbarmt sich ja tgl alias Tom Lane…

Sieht halt wirklich so aus, als ob der Planner einen suboptimalen Plan findet, und daher stehen die Chancen gut, daß Tom drauf reagiert.

Andreas