PostgreSQL-Antipatterns: „Es darf nur eines geben!“

In SQL beschreiben Sie „was“ Sie erreichen möchten, nicht „wie“ es ausgeführt werden soll. Daher nimmt das Problem der Entwicklung von SQL-Abfragen im Stil „Wie man es hört, ist wie es geschrieben wird“ seinen Ehrenplatz ein Merkmale der Berechnung von Bedingungen in SQL.

Sehen wir uns heute anhand ganz einfacher Beispiele an, wozu das im Anwendungskontext führen kann GROUP/DISTINCT и LIMIT mit ihnen.

Nun, wenn Sie in der Anfrage geschrieben haben „Verbinde zuerst diese Zeichen und wirf dann alle Duplikate weg, Es sollte nur noch einer übrig sein für jeden Schlüssel kopieren“ - Genau so funktioniert es, auch wenn die Verbindung überhaupt nicht benötigt wurde.

Und manchmal hat man Glück und es „funktioniert einfach“, manchmal hat es einen unangenehmen Einfluss auf die Leistung und manchmal führt es zu Effekten, die aus Entwicklersicht völlig unerwartet sind.

Nun ja, 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 wäre klar, was sie wollten Wählen Sie Datensätze X aus, für die es Datensätze in Y gibt, die sich auf die erfüllte Bedingung beziehen. Habe eine Anfrage geschrieben über JOIN – habe mehrmals einige pk-Werte erhalten (genau wie viele passende Einträge in Y erschienen). Wie zu entfernen? Sicherlich DISTINCT!

Besonders „erfreulich“ ist es, wenn es zu jedem X-Datensatz mehrere Hundert zugehörige Y-Datensätze gibt und die Duplikate dann heldenhaft entfernt werden ...

Wie repariert man? Stellen Sie zunächst fest, dass das Problem geändert werden kann „Wählen Sie Datensätze X aus, für die in Y MINDESTENS EINER mit der erfüllten Bedingung verknüpft ist.“ – vom Y-Record selbst brauchen wir schließlich nichts.

Verschachtelt EXISTIERT

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 in EXISTS ausreicht, den ersten Eintrag zu finden, der auftaucht, ältere Versionen tun dies nicht. Deshalb gebe ich lieber immer an LIMIT 1 innen EXISTS.

SEITLICHE VERBINDUNG

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;

Die gleiche Option ermöglicht bei Bedarf die sofortige Rückgabe einiger Daten aus dem gefundenen zugehörigen Y-Datensatz. Eine ähnliche Option wird im Artikel besprochen „PostgreSQL-Antipatterns: Ein seltener Datensatz erreicht die Mitte eines JOIN“.

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

Ein zusätzlicher Vorteil solcher Abfragetransformationen ist die Möglichkeit, die Suche nach Datensätzen einfach 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 Anfrage und versuchen zu verstehen, was das DBMS tun soll:

• die Zeichen verbinden
• einzigartig von X.pk
• Wählen Sie aus den verbleibenden Einträgen einen aus

Also, was hast du bekommen? „Nur ein Eintrag“ von den Einzigartigen – und wenn wir dieses der Nicht-Einzigartigen nehmen, wird sich das Ergebnis irgendwie ändern? … „Und wenn es keinen Unterschied gibt, warum dann mehr bezahlen?“

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Und genau das gleiche Thema mit GROUP BY + LIMIT 1.

„Ich muss nur fragen“: implizite GRUPPE + LIMIT

Ähnliche Dinge passieren zu unterschiedlichen Zeitpunkten Nicht-Leerheitsprüfungen Zeichen oder CTEs im Verlauf der Anfrage:

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

Aggregatfunktionen (count/min/max/sum/...) werden auch ohne explizite Anweisungen erfolgreich auf dem gesamten Satz ausgeführt GROUP BY. Nur mit LIMIT Sie sind nicht sehr freundlich.

Der Entwickler kann denken „Wenn dort Datensätze vorhanden sind, dann brauche ich nicht mehr als LIMIT“. Aber tu das nicht! Denn für die Basis gilt:

• zählen, was sie wollen nach allen Aufzeichnungen
• Geben Sie so viele Zeilen, wie sie verlangen

Abhängig von den Zielbedingungen bietet es sich an, eine der folgenden Ersetzungen vorzunehmen:

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

„Wie viel in Gramm wiegen“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ein naiver Entwickler glaubt vielleicht ernsthaft, dass die Anfrage nicht mehr ausgeführt wird. Sobald wir 1 $ der ersten unterschiedlichen Werte finden, die auftauchen.

Irgendwann in der Zukunft könnte und wird dies dank eines neuen Knotens funktionieren Index Scan überspringen, an dessen Umsetzung derzeit gearbeitet wird, aber noch nicht.

Vorerst erst einmal Alle Datensätze werden abgerufen, sind einzigartig und nur von ihnen wird der angeforderte Betrag zurückerstattet. Es ist besonders traurig, wenn wir so etwas wollten $ 1 = 4, und es gibt Hunderttausende Datensätze in der Tabelle ...

Um nicht umsonst traurig zu sein, verwenden wir eine rekursive Abfrage „DISTINCT ist für die Armen“ aus dem PostgreSQL-Wiki:

Source: habr.com