Datenbankabfrage beschleunigen

[xaromz]

Hallo,

ich versuche gerade, eine Suchfunktion über eine Datenbank zu implementieren. Die Daten sind hierarchisch über mehrere Tabellen geordnet. Ich möchte nun je nach Hierarchiestufe meine Abfrage starten. Die Datenbanken sind (vereinfacht so aufgebaut):

Code:

Tabelle a (6 Elemente):
id (primary key)

Tabelle b (200 Elemente):
id (primary key)
refid (foreign key auf Tabelle a)

Tabelle c (600.000 Elemente):
id (primary key)
refid (foreign key auf Tabelle b)

Tabelle d (10.000.000 Elemente):
id (primary key)
refid (foreign key auf Tabelle c)
data (Daten, die ich haben will)

Wenn ich nun alle Daten haben möchte, die unter einem Element in Tabelle c sind, geht das ja ganz einfach:

Code:

SELECT * FROM d WHERE refid=XX

Um alle Daten unter einem Element aus a zu bekommen, verwende ich momentan zwei Joins:

Code:

SELECT * FROM d LEFT JOIN c ON d.refid = c.id LEFT JOIN b ON c.refid = b.id WHERE b.id = XX

Diese Abfrage dauert aber sehr lange (bis zu 15 Minuten), sind ja auch zwei Joins über viele Daten.

Lässt sich diese Abfrage beschleunigen, und wenn ja, wie?

Gruß
xaromz

[mkinzler]

Warum left (outer) joins? Inner joins sollten ausreichen sein

[xaromz]

Hallo,

hast recht, inner joins sind besser. Die Abfragedauer ändert sich aber dadurch nicht.

Gruß
xaromz

[mkinzler]

Benötigst du wirklich alle 10Mio Einträge von d?
Man könnte es auch einmal mit Subselects versuchen?

[Bernhard Geyer]

Wieviel Ergebnisdatensätze kommen raus? Wie "breit" ist ein datensatz in bytes? Wie gut ist die Netzwerkanbindung? Wieviel RAM kann sich der DB-Server genehmigen?

[jobo]

Du schreibst, Du brauchst Daten aus Tabelle D.
Wozu sind dann die anderen Tabellen nötig?


Es gibt ungefähr 3 mögliche Varianten

1) Tabellen a - c enthalten keine Suchkriterien
> nutzlos, um die Suche zu beschleunigen
> Suche ohne Joins direkt auf Tabelle D
2) Tabelle a - c enthalten Suchkriterien
> Je nach Bedarf (welche Tabelle enthält welche Suchkriterien) verschiedene Joins verwenden
3) Ich hab das mit den Hirarchien nicht verstanden
> Du könntest das noch mal richtig erklären

In jedem Fall ist bei diesen Datenmengen eine sehr gute Indizierung angeraten.
Sowohl für die Suchfelder als auch für die Join Felder (Primary & Foreign Keys)

Wenn es bei Postgesql auch sowas wie Statistiken gibt, sollten die passen,
also aktuell sein oder justiert.

Den Ausführungsplan anzeigen lassen und die Indizierung entsprechend anpassen.

[xaromz]

Hallo,

danke für die Antworten, auch wenn es eher Fragen sind.

@Bernhard: Die Größe der Daten ist erst mal egal, ich besorge mir im ersten Schritt nur die Ids der gefundenen Datensätze. Netzanbindung ist somit auch egal, da die übertragenen Datenmengen relativ klein sind. Bei meinem aktuellen Text kommen bei einer Suchdauer von ~45s neun Datensätze raus. Wieviel RAM der Server nutzt kann ich nicht sagen, da hab' ich aber auch keinen Einfluss drauf.

@jobo:
Die Tabellen a-c enthalten eigentlich nur Strukturdaten als Baumknoten, also: in Tabelle b sind Elemente, die Unterelemente von a sind, und c wiederum enthält Unterelemente von b. In d sind dann die Blätter des Baums.
Ich möchte jetzt von jeder Gabelung aus die Möglichkeit haben, alle Blätter, die irgendwo an diesem Ast hängen, zu finden. Vielleicht ist auch eine Grafik besser:

Code:

A1
|-B1
| |-C1
| | |-D1
| | |-D2
| |
| |-C2
| | |-D3
| |
| |-C3
|   |-D4
|
|-B2
   |-C4
     |-D5

A2
|-B3
  |-C5
    |-D6
...

Wenn ich also von A1 aus suche, sollen D1-D5 gefunden werden, von B1 D1-D4 und von C1 D1-D2. Ich hoffe, das ist jetzt klar geworden.

Zu den Indices: Ich dachte, Primary Key und Foreign Key sind bereits Indices, oder ist es sinnvoll, da nochmal extra einen Index zu erstellen?

[mkinzler]

Wenn keine weiteren Einschränkungen (where) gibt sollte der Index reichen ( ich gehen davon aus, dass PosGreSQL für die Schlüssel einen Index anlegt)

Diese scheinen aber bei Joins nicht verwendet zu werden

Zitat von http://sql-info.de/postgresql/FAQ_german.html:

Indexe werden normalerweise nicht in ORDER BY-Abfrage oder in JOINs verwendet. Ein sequentieller Scan mit anschließendem explizitem Sortiervorgang ist normalerweise schneller als ein Index-Scan einer großen Tabelle. Jedoch wird bei einer Abfrage, in der LIMIT zusammen mit ORDER BY verwendet wird, oftmals ein Index verwendet, da nur ein kleiner Abschnitt der Tabelle zurückgeliefert wird.

Zitat:

Sollte es danach aussehen, also ob der Optimierer irrtümlich einen sequentiellen Scan ausführt, führen Sie SET enable_seqscan TO 'off' aus und prüfen Sie, ob die Indexabfrage dadurch scheller geworden ist.

[jobo]

Ok, nun hab ich es auch verstanden.

Code:

select d.* from 
  a, b, c, d
where
      a.id = b.parent
  and b.id = c.parent
  and c.id = d.parent
  and c.id = :level3
  and b.id = :level2
  and a.id = :level1

Ist das die Variante, die 45 Sekunden braucht?

'Primary' und 'Foreign Key' ist erstmal eine logische Definition. Das kann es auch ohne Index geben. Mehrfachindizierung macht m.E. erstmal keinen Sinn.
Es muss nicht unbedingt jeweiles ein Index dazu da sein. Obwohl bei einem Primärschlüssel ja i.D.R automatisch ein Index erzeugt wird.
Du solltest aber sicherstellen, dass die Indizierung auf den Joinfeldern vorhanden ist.

Ansonsten liefert eine bloße Level 1 Einschränkung ca 1,5 Mio Datensätze im Schnitt! Das ist unabhängig von der Breite von D eine relevante Größenordnung, so groß, dass sie zumindest höchstens technisch zu gebrauchen ist. Und es ist dann sehr wohl fraglich, ob Netz und Client das verkraften.

Ich empfehle Dir ein Test SQL (sowas wie oben) mit
"explain analyze" zu bearbeiten und Indizierung bzw. Parametrierung und Aufbau des Selects damit solange zu untersuchen und ändern, bis Du zufrieden bist oder nichts mehr rauszuholen ist.