Reihenfolge von Joins selbst bestimmen?

Hallo zusammen,

nachdem eines meiner kleineren Projekte nun schon seit ca.1,5 Jahren Produktionsdaten sammelt, werden die zugehörigen Abfragen inzwischen wirklich unerträglich langsam (bis zu 8 Minuten bei 6 Millionen Einträgen). Ich werde also nicht umhinkommen, die Abfragen und das Layout zu analysieren.

Mein Layout ist dabei einigermaßen normalisiert - und dabei liegt wahrscheinlich schon der Hase im Pfeffer. Eigentlich gibt es nämlich nur 2 Tabellen die wirklich die Produktionsdaten aufnehmen, alle restlichen Tabellen enthalten sozusagen statische Daten wie Fehler (Id, Name, Beschreibung, Code), eine Bauteilbeschreibung(Name, Beschreibung…) usw. Dadurch kommen aber durchaus einige Tabellen zusammen. Um nun nachher eine Auswertung fahren zu können, muss ich die ganzen Tabellen wieder miteinander joinen, z.B. um die ganzen Informationen wie Namen etc. anzeigen zu können. Wirklich größer sind eigentlich nur die beiden Tabellen, die die Produktionsdaten aufnehmen, die anderen bestehen maximal aus einigen hundert Einträgen. Mein erster Ansatz wäre die Vermutung, dass die Reihenfolge der Joins nicht stimmt und Postgres schon recht früh die großen Tables mit ‘reinjoint’. Wünschenswert wäre es aus meiner Sicht aber, das erst die kleineren Tables (dort wo möglich) gejoint werden und erst zum Schluß die 2 großen Joins erfolgen. Nach all dem Geschwafel aber vielleicht mal meine eigentlich Frage:

Ist es möglich, die Ausführungsreihenfolge der Joins vorzugeben und wenn ja wie?

Vielen Dank im Voraus,
Bierstuebl

Was sagt denn ein EXPLAIN ANALYSE?

Andreas

Hallo Andreas,

tja, das versuche ich jetzt dann zusammen zu basteln - das Problem ist, dass die Abfragen teils durchaus komplexer sind und auch noch mit stored procedures arbeiten. Da ich mich bisher noch nicht mir der Performance-Analyse unter Postgres beschäftigt habe, ist das wohl die nächste Aufgabe.

Was mich im Moment irritiert, ist dass mir ein explain analyze auf einen absolut simplen Select wie:

explain analyze select count(*) from pnascan where date_scan1 > ‘01.05.2007’

folgendes liefert:

Aggregate (cost=41237.75…41237.75 rows=1 width=0) (actual time=3077.079…3077.079 rows=1 loops=1)
-> Seq Scan on pnascan (cost=0.00…37166.13 rows=1628647 width=0) (actual time=0.036…2267.814 rows=1626763 loops=1)
Filter: (date_scan1 > ‘2007-01-05 00:00:00’::timestamp without time zone)
Total runtime: 3077.157 ms

Auf dem Feld pnascan ist allerdings ein Index drauf - der soweit ich das jetzt lesen kann ja gar nicht benutzt wird, oder?
Ich seh schon, das wird ne Menge Arbeit…

Ja.

Suchst Du nach dem 1. Mai oder dem 5. Januar?

Probier mal, statt “where date_scan1 > ‘01.05.2007’” ein “where date_scan1::date > ‘01.05.2007’” zu machen. Btw.: Du erwartest einen implizieten CAST von TEXT nach DATE. Womöglich wird u.a. das mit 8.3 nicht mehr laufen. Abhilfe: “…> ‘01-05.2007’::date”, also explizit casten.

Wieviel Zeilen hat die Tabelle? Das Select liefert ja ca. 1,6 Millionen Zeilen. Damit ein Index greift, müßten da signifikant mehr Datensätze sein UND das Select dürfte nur einen kleinen Teil selektieren.

Außerdem: Dein Datumsformat ist u.U. schlecht gewählt, bei mir zumindest beobachte ich dies:

scholl=*# explain analyse select count(1) from lief_kopf where ts > '01.10.2007'::date;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1597.59..1597.60 rows=1 width=0) (actual time=79.652..79.654 rows=1 loops=1)
   ->  Seq Scan on lief_kopf  (cost=0.00..1568.49 rows=11638 width=0) (actual time=0.021..56.250 rows=11590 loops=1)
         Filter: (ts > '2007-01-10'::date)
 Total runtime: 79.786 ms
(4 rows)

scholl=*# explain analyse select count(1) from lief_kopf where ts > '2007-10-01'::date;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1548.72..1548.73 rows=1 width=0) (actual time=35.649..35.651 rows=1 loops=1)
   ->  Bitmap Heap Scan on lief_kopf  (cost=45.68..1535.38 rows=5336 width=0) (actual time=9.164..24.936 rows=5453 loops=1)
         Recheck Cond: (ts > '2007-10-01'::date)
         ->  Bitmap Index Scan on idx_lief_ts  (cost=0.00..45.68 rows=5336 width=0) (actual time=8.875..8.875 rows=9974 loops=1)
               Index Cond: (ts > '2007-10-01'::date)
 Total runtime: 35.798 ms
(6 rows)

Möglicherweise hast Du aber passende DATE_STYLE-Parameter, damit das nicht passiert. Was für ein Datentype ist date_scan1?

Andreas

Hallo Andreas,

danke erstmal für die schnellen Antworten und den Hinweis mit dem casten :slight_smile:

date_scan1 ist übrigens vom Typ Timestamp (ohne Timezone), ist der Zeitstempel, wann ein Bauteil zum ersten Mal geprüft wurde. Ich schreibe alle Zeiten immer als UTC in die Datenbank, um so tolle Probleme wie Sommer/Winterzeit zu vermeiden.

Weiterhin hast du recht, dass ich eigentlich ab dem 1. Mai suchen wollte, das ganze per default aber wohl amerikanisches Datumsformat ist und ich somit ab dem 5. Januar gesucht habe - und somit eigentlich die ganze Tabelle selektiert habe :slight_smile: Suche ich z.B. ab 01.01.2008 (schön unverfänglich :-)) wird der Index benutzt.

Soweit ich das sehen kann, bist du aber nach deiner Erklärung selbst darauf reingefallen, da du einmal nach 01.10.2007 und dann nach 2007-10-01 suchst und damit einmal nach dem 10. Januar (Index wird nicht benutzt) und dann nach dem 1. Oktober (Index wird benutzt) suchst. Damit bekommst du unterschiedliche Ergebnismengen und damit auch unterschiedliche Zeiten :slight_smile: Oder wolltest du mir da zeigen, dass die beiden Datumsformate unterschiedliche Bedeutung haben? Schneller ist nämlich keine der beiden Varianten :slight_smile:

Insgesamt lag der Fehler natürlich bei mir. War nur mal ein erster Schuß direkt eingehackt - die Anfragen der Anwendung (in C# geschrieben) werden per npgsql-Treiber als prepared statement ausgeführt und solche Dinge wie Typecasts etc. sind damit halt schon implizit enthalten. Hab mich wie gesagt schon lange nicht mehr mit der Anwendung und Postgres beschäftigt und muss mich deshalb erst wieder reindenken.

Meine momentane Schwierigkeit besteht also darin, auch mal die Stored Procedures (in PL/pgSQL) irgendwie auf Peformance zu testen (und irgendwie zu debuggen). Allerdings hilft mir da bisher ein Explain Analyze (es kommt nur die Gesamtlaufzeit) nicht wirklich weiter und wie man (falls Möglich) eine Stored Procedure debuggt konnte ich bisher auch noch nicht rausfinden (über was besseres als Raise Notice um ein paar Meldungen auszugeben bin ich bisher noch nicht gestolpert). Auch das ist natürlich nur ein kleines Puzzlestückchen - warum müssen so Datenbanksachen immer so mords kompliziert sein :slight_smile: ?

Du kannst solche Probleme vermeiden, wenn Du timestamptz verwendest.

Weiterhin hast du recht, dass ich eigentlich ab dem 1. Mai suchen wollte, das ganze per default aber wohl amerikanisches Datumsformat ist und ich somit ab dem 5. Januar gesucht habe - und somit eigentlich die ganze Tabelle selektiert habe :slight_smile: Suche ich z.B. ab 01.01.2008 (schön unverfänglich :-)) wird der Index benutzt.

Okay.

Soweit ich das sehen kann, bist du aber nach deiner Erklärung selbst darauf reingefallen, da du einmal nach 01.10.2007 und dann nach 2007-10-01 suchst und damit einmal nach dem 10. Januar (Index wird nicht benutzt) und dann nach dem 1. Oktober (Index wird benutzt) suchst. Damit bekommst du unterschiedliche Ergebnismengen und damit auch unterschiedliche Zeiten :slight_smile: Oder wolltest du mir da zeigen, dass die beiden Datumsformate unterschiedliche Bedeutung haben? Schneller ist nämlich keine der beiden Varianten :slight_smile:

Mit fiel nur auf, daß das schiefgehen kann. Kann passieren, man muß halt aufpassen bei solchen Dingen. Sinnvoll ist da wohl auch to_date(), um exakt festzulegen, was was sein soll.


Meine momentane Schwierigkeit besteht also darin, auch mal die Stored Procedures (in PL/pgSQL) irgendwie auf Peformance zu testen (und irgendwie zu debuggen). Allerdings hilft mir da bisher ein Explain Analyze (es kommt nur die Gesamtlaufzeit) nicht wirklich weiter und wie man (falls Möglich) eine Stored Procedure debuggt konnte ich bisher auch noch nicht rausfinden (über was besseres als Raise Notice um ein paar Meldungen auszugeben bin ich bisher noch nicht gestolpert). Auch das ist natürlich nur ein kleines Puzzlestückchen - warum müssen so Datenbanksachen immer so mords kompliziert sein :slight_smile: ?

Nun ja. Praktisch kann da auch timeofday() sein, weil dies nicht die Startzeit der TX anzeigt, sondern auch innerhalb einer function immer die korrekte Zeit liefert, im Gegensatz zu now().

Andreas

Du kannst solche Probleme vermeiden, wenn Du timestamptz verwendest.

Ehrlich gesagt, habe ich das damals nicht so richtig begriffen wie timestamptz anzuwenden ist - deshalb macht meine Anwendung einfach eine Umwandlung von der lokalen Zeitzone des Rechners in UTC und wieder zurück - auf diese Weise kann die Anwendung ohne Änderung sowohl in den USA als auch in Deutschland eingesetzt werden. In den meisten Fällen hab ich sogar den Unix-Timestamp zur Verfügung - aber wie wandle ich den korrekt, dass ich den einem timestamptz in postgres zuweisen kann?

Grüße,
Bierstuebl



test=*# select now();
              now
-------------------------------
 2008-02-12 11:56:05.821019+01
(1 row)

test=*# select now() at time zone 'PST';
          timezone
----------------------------
 2008-02-12 02:56:05.821019
(1 row)

sorry, hab grad keine zeit weiter.

Andreas