Richtige Indexierung bei mehreren Joins

Hallo

Ich habe eine Abfrage der folgenden Form vor mir. Es handelt sich dabei um eelativ grosse Datenmengen, so dass ich mir aus der Indexierung einigen Geschwindigkeitsgewinn erhoffe.

Wie sieht die richtige Indexierung für folgenden (hypothetischen) Fall aus:

Select a.Id, b.kunde, sum(c.betrag) from Produkte a
Left Join kunden b
On a.id = b.id
Left Join betraege c
On a.id = c.id
Where a.groesse>10
Group by a.Id, b.kunde, sum(c.betrag)

Danke und Grüsse
Michael

Sind davon Spalten als PK definiert? Dies erzeugt einen impl. Index.

Indexe brauchst Du da, wo diese

  • Bestandteil der JOIN-Conditions sind
  • Bestandteil der WHERE-Condition
  • bei ORDER BY auftauchen

Mal ganz grob. Ob Indexe nützlich sind hängt aber auch von der Anzahl Datensätze ab und davon, wie selektiv diese sind. EXPLAIN (ANALYSE) verrät Dir mehr.
Im übrigen gibt es neben ‘normalen’ Indexen auch z.B. partielle und funktionale Indexe. Falls z.B. Tabelle a zig Millionen Datensätze hat, davon aber nur eine Handvoll mit a.groesse > 10 sind, dann wäre ein partieller Index an dieser Stelle sehr praktisch. Andererseits brauchen Indexe Platz (auf Platte UND im RAM) und Zeit bei schreibenden Operationen, um diesen zu aktualisieren. Also von daher Indexe nicht mit der Gieskanne verteilen.

Hi,

Besten Dank. Also wenn ich dich richtig verstehe würde man hier indizes auf a.id, b.id, c.id (wegen Join Condition), a.grösse (where) und c.betrag als auch b.kunde (group by) machen.
Sollte einer von denen z.B. a.id schon als pk definiert sein ist darauf implizit schon ein index vorhanden und der (zusätzliche) Index darauf muss nicht mehr gemacht werden. Partielle Indizes/ Platz etc. mal aussen vor. Verstehe ich das richtig?

  1. Falls ja, macht man die Indizes jeweils einzeln auf b.id und b. Kunde oder als gemeinsamen Index?
  2. inwiefern hilft mir hier EXPLAIN weiter? Im Sinne von Indizes setzen und wieder entfernen und dann deren Costs vergleichen --> wenns hilft Index setzen sonst sein lassen. Oder kann man aus dem EXPLAIN auch direkt auslesen welche Indizes helfen würden? Wie liesse sich dies möglicherweise an diesem Beispiel
    zeigen?

Besten Dank und Gruss
Michael

b.kunde wäre unnötig. IMHO.

Sollte einer von denen z.B. a.id schon als pk definiert sein ist darauf implizit schon ein index vorhanden und der (zusätzliche) Index darauf muss nicht mehr gemacht werden. Partielle Indizes/ Platz etc. mal aussen vor. Verstehe ich das richtig?

ja.

  1. Falls ja, macht man die Indizes jeweils einzeln auf b.id und b. Kunde oder als gemeinsamen Index?

Kombinierte Indexe können sehr effektiv sein, wenn die Abfrage paßt. Wenn aber der Index über (a,b) geht und im WHERE nur b auftaucht ist der Index wertlos.
Einzelne Indexe auf den Spalten können aber von PG via Bitmap Index Scan kombiniert werden - geht iirc seit 8.1 und hat damals echt viel gebracht.

  1. inwiefern hilft mir hier EXPLAIN weiter? Im Sinne von Indizes setzen und wieder entfernen und dann deren Costs vergleichen → wenns hilft Index setzen sonst sein lassen. Oder kann man aus dem EXPLAIN auch direkt auslesen welche Indizes helfen würden? Wie liesse sich dies möglicherweise an diesem Beispiel
    zeigen?

Besten Dank und Gruss
Michael

Ja, Kosten vergleichen. Man kann auch mit set enable_indexscan = [on|off] (als Beispiel, es gibt weiterer solcher Parameter) spielen, das gilt innerhalb der Session und geht schneller als Indexe anzulegen / zu löschen. Achten sollte man bei EXPLAIN auf SEQ SCAN - Ausgaben. Aber nicht jeder seq. Scan ist per se schlecht.

Tja, das ist ein weites Feld. Falls du da Interesse hast: http://2013.pgconf.de/ , die Anmeldung da geht bald los (pssst… Insiderwissen…)