Verständnis korrekt?

Hi ich hab nochmal eine Fragen an die Experten und habe dazu folgende Szenario:

RT = Referenztabelle eine Art Kategoriesystem
DT = Datentabelle (mit mehreren Millionendatensätze)

RT1 <- 1:n -> RT2 <- 1:n -> RT3 <- 1:n -> RT4 <- 1:n -> DT

Jetzt gibt es 2 Möglichkeiten meiner Meinung nach:

Variante 1)

  • In der DT wird nur die RT4 ID gespeichert
  • Unique-Constrain über RT4 ID + Felder der Datentabellen für Eindeutigkeit

Variabnte 2)

  • Ich speichere alle RT IDs in der DT
  • Unique-Constrain über RT1 ID + RT2 ID + RT3 ID + RT4 ID + Felder der Datentabellen für Eindeutigkeit

Daten werden wie folgt abgefragt.

  • RT1 und RT2 werden per Einfachauswahl gewählt
  • RT3 und RT4 werden per Mehrfachauswahl kombiniert
  • RT1-4 stellen einen Baum dar, der auch komplett durch selektiert wird.

Das bedeutet für mich:

Variante 1)
Ich selektiere auf der Datentabelle mit ganz vielen RT4 Ids (bei Mehrfach auswahl). Heißt für mich eine Rangeabfrage auf erster Spalte im Index.

Variante 2)
Ich selektiere über alle RT Ids, wobei im Index RT1+2 fest wären und und ab RT3 eine Rangeabfrage startet. Außerdem hätte ich eine Redundanz auf der Datentabelle. (RT1-3)

Meine Frage, sind meine Annahmen richtig oder habe ich etwas übersehen. Und bei Variante 2; Ist es so das dann ein Range über RT3 + 4 gemacht wird, oder ist es wie bei MySQL das der Index nur bis Spalte 3 (RT3) genutzt wird und die RT4 Id über normale Sucheefolgt ohne Index?

Ich hoffe ich hab mich verständlich ausgedrückt.

LG

Nein.

Falls die Frage ist, ob PG pro Table/Query mehr als einen Index nutzen kann: ja.

Den Rest Deines Postings hab ich, sorry, nicht verstanden. Vielleicht zeigst Du einfach mal konkrete Tabellendefinitionen, Abfragen und Explain’s.


Andreas

Hallo,

habe mal eine Testdb aufgesetzt. Hier erstmal das SQL File

Variante 1 - Abfrage 1

EXPLAIN SELECT * FROM daten_variante_1 WHERE rt4_id = 2;
"Index Scan using daten_variante_1_rt4_id_key on daten_variante_1  (cost=0.00..8.27 rows=1 width=344)"
"  Index Cond: (rt4_id = 2)"

Für mich bedeutet das er konnt den Index nutzen und zwar Spalte: rs4_id

Variante 1 - Abfrage 2

EXPLAIN SELECT * FROM daten_variante_1 WHERE rt4_id IN (1,2);
"Seq Scan on daten_variante_1  (cost=0.00..12.62 rows=2 width=344)"
"  Filter: (rt4_id = ANY ('{1,2}'::integer[]))"

Hier sieht es nach einem Tablescan aus. Also ohne Index

Variante 2

EXPLAIN SELECT * FROM daten_variante_2 WHERE rt1_id = 1 AND rt2_id = 2 AND rt3_id IN (1,2) AND rt4_id IN (1,2);
"Index Scan using daten_variante_2_rt1_id_key on daten_variante_2  (cost=0.00..8.28 rows=1 width=356)"
"  Index Cond: ((rt1_id = 1) AND (rt2_id = 2))"
"  Filter: ((rt3_id = ANY ('{1,2}'::integer[])) AND (rt4_id = ANY ('{1,2}'::integer[])))"

Hier wird der Index mit den 2 Spalten rt1_id und rt2_id.

Für mich stellt sich die Frage, welcher Weg besser ist.
Eigentlich dachte ich das Postgresql besser ist als MySQL im Bezug auf den Index.
Aber nach diesen Explains siehts für mich so aus, als könne er auch den Index auch nicht nutzen wie MySQL wenn ich mit IN Abfragen arbeite.
Und das würde mir bei mehreren millionen Datensätze um die Ohren fliegen. Ist also doch die Variante 2 die bessere?

Der Index wird genutzt, wenn er hilft. Bei nur 2-3 Datensätzen HILFT ER NICHT, da sowieso die 8KByte-Page, auf der die komplette Tabelle ist, geladen werden muß. Würde PG erst noch den Index lesen wollen, wäre dies ein Plattenzugriff mehr und es würde länger dauern.

Also, um halbwegs das vergleichen zu können, muß Du die Tabellen schon mit reichlich Daten befüllen und ein Analyse laufen lassen, sonst ist das sinnfrei.

Abgesehen davon:

  • welche Version?
  • große Listen mit IN - Werten besser als JOIN machen
  • dein Datenmodell skaliert nicht
  • ab 8.4 kann PG rekursive Abfragen, ideal für Baumstrukturen. Ob dies im Einzelfall schneller als eine Lösung ist, müßte man testen.


    Andreas

Der Index wird genutzt, wenn er hilft. Bei nur 2-3 Datensätzen HILFT ER NICHT, da sowieso die 8KByte-Page, auf der die komplette Tabelle ist, geladen werden muß. Würde PG erst noch den Index lesen wollen, wäre dies ein Plattenzugriff mehr und es würde länger dauern.

Stimmt daran hab ich gar nicht mehr gedacht :slight_smile: Ich wusste irgend was muss ich vergessen haben :smiley: Ich konnte mir auch nicht vorstellen, dass er gar keinen Index nutzt.

Zu deinen Fragen:

  • welche Version?
    Verwendet wird 8.4
  • große Listen mit IN - Werten besser als JOIN machen
    Ich erhalten eine Liste mit allen IDs aus RT4. Den Left JOIN würde ich bei Variante 2 machen um die ids RT1-3 zu erhalten, wobei RT4 eindeutig alleine ist.
  • dein Datenmodell skaliert nicht
    was meinst du damit genau? die Datentabelle?
  • ab 8.4 kann PG rekursive Abfragen, ideal für Baumstrukturen. Ob dies im Einzelfall schneller als eine Lösung ist, müßte man testen.
    Das müsste dann auch mein ORM können, außerdem ist die Tabelle fast statisch und wird nur hin und wieder mit einem externen System abgeglichen.
    Die Tabellen erhalten auch nen extra Schlüsselfeld für den Abgleich mit dem externen System, sowie einer Sortierung.

Generell ist später noch abgedacht eine Partionierung zu machen, da die Daten Zeitabhängig sind.

Nun, Dein Baum geht nur bis 4 Ebenen, sollte eines Tages eine 5. dazukommen, mußt Du Tabellen und SQL ändern.

Andreas

Nun, Dein Baum geht nur bis 4 Ebenen, sollte eines Tages eine 5. dazukommen, mußt Du Tabellen und SQL ändern.

Achso, die Tabellen erhalten für unterschiedliche ebenen noch unterschiedliche Informationen. und sollte es dazu kommen muss sowieso die hält umgebaut werden. dann ist das halt so. also hier ist das bewusst gemacht worden.

Kann Dein ORM kein “normales” SQL?

Kann Dein ORM kein “normales” SQL?

Doch aber wenn ich direkt SQL nutze brauch ich kein ORM. Ich kann zwar Anfragen auch selber stellen, was ich bei komplexeren mache, hier macht es einfach keinen Sinn. Da hängen da noch mehr Sachen hinten dran.