Verwenden einer einzigen SQL-korrelierten Unterabfrage, um zwei Spalten zu erhalten

Mein Problem wird durch die folgende Abfrage dargestellt:

SELECT b.row_id, bx, by, b.something, (SELECT ax FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_x, (SELECT ay FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_y FROM my_table b 

Ich verwende dieselbe Unterabfrageanweisung zweimal, um sowohl source_x als auch source_y . Deshalb frage ich mich, ob es möglich ist, es nur mit einer Unterabfrage zu tun?

Weil, sobald ich diese Abfrage auf meinen realen data (Millionen von Zeilen) ausgeführt habe, scheint es, nie zu enden und Stunden zu nehmen, wenn nicht Tage (meine Verbindung legte vor dem Ende auf).

Ich benutze PostgreSQL 8.4

Solutions Collecting From Web of "Verwenden einer einzigen SQL-korrelierten Unterabfrage, um zwei Spalten zu erhalten"

@DavidEG hat die beste Syntax für die Abfrage gepostet.

Ihr Problem besteht jedoch definitiv nicht nur in der Abfragetechnik . Ein JOIN anstelle von zwei Unterabfragen kann die Dinge höchstens um den Faktor zwei beschleunigen. Höchstwahrscheinlich weniger. Das erklärt nicht "Stunden". Selbst mit Millionen von Zeilen sollte ein ordentlich eingerichtetes PostgreSQL eine einfache Abfrage wie diese in Sekunden und nicht in Stunden beenden.

  • Das Erste, was auffällt, ist der Syntaxerrors in Ihrer Anfrage:

     ... WHERE a.row_id = (b.row_id - 1), a.something != 42 

AND oder OR wird hier benötigt, kein Komma.

  • Als nächstes sind Indizes zu prüfen. Wenn row_id nicht der Primärschlüssel ist, haben Sie möglicherweise keinen Index dafür. Für eine optimale performance dieser speziellen Abfrage erstellen Sie einen mehrspaltigen Index für (row_id, something) wie (row_id, something) :

     CREATE INDEX my_table_row_id_something_idx ON my_table (row_id, something) 
  • Wenn der Filter jedes Mal denselben Wert in something != 42 Sie stattdessen auch einen Teilindex für zusätzliche Beschleunigung verwenden:

     CREATE INDEX my_table_row_id_something_idx ON my_table (row_id) WHERE something != 42 

Dies wird nur dann einen wesentlichen Unterschied machen, wenn 42 ein gemeinsamer Wert ist oder something eine größere Spalte als nur eine Ganzzahl ist. (Ein Index mit zwei Ganzzahlen belegt normalerweise die gleiche Größe auf der Disc wie ein Index mit nur einem, aufgrund der dataausrichtung. Weitere Informationen zur dataausrichtung finden Sie hier .)

  • Wenn performance ein Problem ist, ist es immer eine gute Idee, Ihre Einstellungen zu überprüfen . Standardeinstellungen PostgreSQL ist in vielen Distributionen sehr minimal und nicht in der Lage, "Millionen von Zeilen" zu verarbeiten.

  • Abhängig von Ihrer aktuellen Version von PostgreSQL kann ein Upgrade auf die aktuelle Version 9.1 sehr hilfreich sein .

  • Letztendlich ist auch Hardware immer ein Faktor. Tuning und Optimierung können Sie nur soweit bringen.

Ich denke, dass Sie diesen Ansatz verwenden können:

 SELECT b.row_id , bx , by , b.something , ax , ay FROM my_table b left join my_table a on a.row_id = (b.row_id - 1) and a.something != 42 

altmodische Syntax:

 SELECT b.row_id, bx, by, b.something , ax AS source_x , ay AS source FROM my_table b ,my_table a WHERE a.row_id = b.row_id - 1 AND a.something != 42 ; 

Joinsyntax:

 SELECT b.row_id, bx, by, b.something , ax AS source_x , ay AS source FROM my_table b JOIN my_table a ON (a.row_id = b.row_id - 1) WHERE a.something != 42 ; 
 SELECT b.row_id, bx, by, b.something, ax, ay FROM my_table b LEFT JOIN ( SELECT row_id + 1, x, y FROM my_table WHERE something != 42 ) AS a ON a.row_id = b.row_id;