PostgreSQL Antipatterns: „Es kann nur einen geben!“

In SQL beschreiben Sie, was Sie erhalten möchten, und nicht, wie es ausgeführt werden soll. Daher nimmt das Problem der Entwicklung von SQL-Abfragen im Stil von „wie es sich anhört, so wird es geschrieben“ seinen Platz ein, zusammen mit der Berechnung von Bedingungen in SQL..

Heute schauen wir uns an einfachen Beispielen an, zu was das im Kontext der Verwendung führen kann. GROUP/DISTINCT und LIMIT gemeinsam mit ihnen.

Wenn Sie in der Abfrage geschrieben haben, „verbinde zuerst diese Tabellen und werfe dann alle Duplikate weg, es soll nur ein Exemplar pro Schlüssel übrig bleiben“ — genau so wird es funktionieren, auch wenn die Verbindung nicht nötig war.

Und manchmal hat man Glück und es „funktioniert einfach“, manchmal wirkt es sich negativ auf die Leistung aus, und manchmal führt es zu absolut unerwarteten Effekten aus Sicht des Entwicklers.

Nun, vielleicht nicht so spektakulär, aber…

„Süßes Paar“: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Es ist klar, dass man solche Datensätze X auswählen wollte, für die es in Y Verknüpfungen gibt, die mit der ausgeführten Bedingung übereinstimmen.Sie haben die Anfrage über JOIN — haben mehrmals einige pk-Werte erhalten (genau so oft, wie passende Einträge in Y vorhanden waren). Wie kann man das beheben? Natürlich DISTINCT!

Besonders ärgerlich ist es, wenn für jeden X-Eintrag mehrere Hundert verwandte Y-Einträge gefunden werden, die dann heldenhaft Duplikate entfernt werden…

Wie kann man das beheben? Zunächst einmal sollte man erkennen, dass die Aufgabe modifiziert werden kann zu „solche X-Einträge auszuwählen, für die es in Y MINDESTENS EINEN verwandten Eintrag gibt, der den Bedingungen entspricht“ — denn aus dem Y-Eintrag benötigen wir nichts.

Verschachteltes EXISTS

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Einige Versionen von PostgreSQL verstehen, dass es ausreicht, den ersten gefundenen Eintrag in EXISTS zu finden, ältere tun das nicht. Deshalb bevorzuge ich es, immer LIMIT 1 innerhalb EXISTS.

LATERAL JOIN

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Diese Variante ermöglicht es auch, gleichzeitig einige Daten aus dem gefundenen verwandten Y-Eintrag zurückzugeben, falls erforderlich. Eine ähnliche Variante wurde in dem Artikel behandelt „PostgreSQL Antipatterns: Seltene Einträge erreichen das Mittelmaß bei JOIN“.

„Warum mehr bezahlen?“: DISTINCT [ON] + LIMIT 1

Ein weiterer Vorteil solcher Abfrageumwandlungen ist die Möglichkeit, die Auswahl von Datensätzen leicht einzuschränken, wenn nur einer oder mehrere davon benötigt werden, wie im folgenden Fall:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Jetzt lesen wir die Abfrage und versuchen zu verstehen, was der DBMS vorgeschlagen wird:

• wir verbinden die Tabellen
• wir machen sie eindeutig nach X.pk
• aus den verbleibenden Datensätzen wählen wir einen aus

Was haben wir also erhalten? „Irgendeinen Datensatz“ aus den einzigartigen – und wenn wir diesen einen aus den nicht eindeutigen nehmen, ändert sich das Ergebnis irgendwie? ‚Wenn es keinen Unterschied gibt, warum mehr bezahlen?‘

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- hier können passende Bedingungen eingefügt werden
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Das gleiche Thema gilt für GROUP BY + LIMIT 1.

„Ich muss nur fragen“: implizite GROUP + LIMIT

Ähnliche Dinge treten bei verschiedenen Überprüfungen der Nicht-Leerheit von Tabellen oder CTE während der Ausführung der Abfrage auf:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Aggregatfunktionen (count/min/max/sum/...) funktionieren erfolgreich auf dem gesamten Satz, sogar ohne ausdrückliche Angabe GROUP BY. Nur das mit LIMIT sind sie nicht sehr kompatibel.

Der Entwickler könnte denken Wenn es dort Einträge gibt, brauche ich nicht mehr als LIMIT. Aber das muss nicht sein! Denn für die Datenbank ist das:

• Berechne, was gewünscht ist für alle Einträge
• Gib so viele Zeilen zurück, wie angefragt

Je nach den Zielbedingungen ist hier eine der Ersetzungen angebracht:

• (count + LIMIT 1) = 0 findet man NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 findet man EXISTS(LIMIT 1)
• count >= N findet man (SELECT count(*) FROM (... LIMIT N))

„Wie viel in Gramm wiegt man“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ein naiver Entwickler könnte fälschlicherweise annehmen, dass die Ausführung der Anfrage stoppt, sobald wir die ersten $1 unterschiedlichen Werte gefunden haben.

In der Zukunft könnte das so funktionieren, dank eines neuen Knotens Index Skip Scan, dessen Implementierung derzeit erarbeitet wird, aber bisher — noch nicht.

Zunächst werden alle Einträge extrahiert, einzigartig gemacht, und nur daraus wird zurückgegeben, was angefordert wurde. Besonders traurig wird es, wenn wir etwas wie $1 = 4, aber in der Tabelle — Hunderttausende von Einträgen…

Um den Frust zu vermeiden, benutzen wir eine rekursive Abfrage „DISTINCT für Arme“ aus der PostgreSQL Wiki:

Quelle: habr.com