👍🏿 🎅🏼 🧗🏿 PostgreSQL Antipatterns: "Es sollte nur noch eines übrig sein!" 🧗 💆 🤸

In SQL beschreiben Sie "was" Sie erhalten möchten, nicht "wie" es getan werden soll. Daher tritt das Problem der Entwicklung von SQL-Abfragen im Stil "Wie Sie es hören, während Sie schreiben" an die Stelle der Ehre, zusammen mit den Besonderheiten der Berechnung von Bedingungen in SQL .

Lassen Sie uns heute anhand äußerst einfacher Beispiele sehen, wozu dies im Zusammenhang mit der Verwendung GROUP/DISTINCTund LIMITzusammen mit ihnen führen kann.

Wenn Sie nun in der Anfrage geschrieben haben "Verbinden Sie zuerst diese Platten und verwerfen Sie dann alle Duplikate, sollte es nur eine Kopie für jeden Schlüssel geben" - 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 Effekt auf die Leistung und manchmal gibt es aus Sicht des Entwicklers absolut unerwartete Effekte.

Na 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;

Wie wäre es klar sein , dass wir wollten solche Aufzeichnungen X, für die Y hat die im Zusammenhang mit erfüllter Bedingung auswählen . Wir haben eine Anfrage durchgeschrieben JOIN- wir haben einige pk-Werte mehrmals erhalten (genau wie viele übereinstimmende Datensätze in Y sich herausstellten). Wie zu entfernen? Natürlich DISTINCT!

Es ist besonders "glücklich", wenn für jeden X-Datensatz mehrere hundert verknüpfte Y-Datensätze vorhanden sind und dann Duplikate heldenhaft entfernt werden ...

Wie kann man das beheben? Stellen Sie zunächst fest , dass die Aufgabe so geändert werden kann, dass „solche Datensätze X ausgewählt werden, für die Y MINDESTENS EINEN mit einer laufenden Bedingung verknüpft hat“ - schließlich benötigen wir nichts aus dem Y-Datensatz selbst.

Verschachtelte EXISTEN

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

Einige PostgreSQL-Versionen verstehen, dass es ausreicht, den ersten verfügbaren Datensatz in EXISTS zu finden, ältere nicht. Deshalb ziehe ich es vor, immer LIMIT 1innen anzugeben 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;

Mit derselben Option können bei Bedarf gleichzeitig einige Daten aus dem gefundenen verknüpften Y-Datensatz sofort zurückgegeben werden. Eine ähnliche Option wird im Artikel "PostgreSQL Antipatterns: Ein seltener Eintrag wird in die Mitte eines JOIN fliegen" erläutert .

"Warum mehr bezahlen": DISTINCT [ON] + LIMIT 1

Ein zusätzlicher Vorteil solcher Abfragetransformationen ist die Möglichkeit, die Iteration auf einfache Weise auf Datensätze zu beschränken, wenn nur eine oder mehrere von ihnen 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 vorschlägt:

Wir verbinden die Platten
durch X.pk einzigartig
Wählen Sie einen aus den verbleibenden Datensätzen aus

Das heißt, hast du was? "Ein Datensatz" der einzigartigen - und wenn Sie diesen einen der nicht eindeutigen nehmen, wird sich das Ergebnis irgendwie ändern? .. "Und wenn es keinen Unterschied gibt, warum 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 frage nur": implizite GROUP + LIMIT

Ähnliche Dinge treten bei verschiedenen Überprüfungen auf Nichtleere der Platte oder des CTE während der Ausführung der Anforderung auf:

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

Aggregierte Funktionen ( count/min/max/sum/...) werden auch ohne explizite Angabe erfolgreich für den gesamten Satz ausgeführt GROUP BY. Nur sind LIMITsie nicht sehr freundlich mit ihnen.

Der Entwickler kann denken „ wenn es Aufzeichnungen gibt, dann muss ich nicht mehr LIMIT“ . Aber nicht! Denn für die Basis ist es:

Zählen Sie, was Sie wollen, auf allen Datensätzen
Geben Sie so viele Zeilen an, wie Sie fragen

Abhängig von den Zielbedingungen ist es angebracht, hier eine der Substitutionen 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 hängen": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ein naiver Entwickler kann ehrlich denken, dass die Abfrage beendet wird, sobald wir die ersten verschiedenen $ 1-Werte finden, die auftauchen .

Irgendwann in der Zukunft kann und wird dies dank des neuen Index Skip Scan- Knotens funktionieren , an dessen Implementierung derzeit gearbeitet wird, aber noch nicht.

Bisher werden zunächst alle Datensätze abgerufen , eindeutig gemacht und nur von ihnen zurückgegeben, wie viele angefordert wurden. Es ist besonders traurig, wenn wir so etwas wie $ 1 = 4 wollten und es Hunderttausende von Datensätzen in der Tabelle gibt ...

Um nicht umsonst traurig zu sein, werden wir die rekursive Abfrage "DISTINCT for the Poor" aus dem PostgreSQL-Wiki verwenden :

PostgreSQL Antipatterns: "Es sollte nur noch eines übrig sein!"