PostgreSQL-Antipatterns: Berechnung von Bedingungen in SQL

SQL ist nicht C++ und auch nicht JavaScript. Daher erfolgt die Berechnung logischer Ausdrücke anders, und das ist definitiv nicht dasselbe:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Im Prozess der Optimierung des Abfrageausführungsplans kann PostgreSQL äquivalente Bedingungen beliebig "anordnen" , bestimmte davon für separate Datensätze nicht berechnen und sie dem angewendeten Index zuordnen... Zusammenfassend lässt sich sagen, dass Sie im Vorausnicht steuern können , in welcher Reihenfolge die (und ob überhaupt) gleichberechtigten Bedingungen berechnet werden.

Wenn Sie also den Prioritäten dennoch nachgehen möchten, müssen Sie strukturell diese Bedingungen ungleich machen durch die Verwendung von Ausdrücken und Operatoren.

Daten und deren Verarbeitung sind das Herzstück unserer SBIS-Lösung, daher ist es uns sehr wichtig, dass die Operationen nicht nur korrekt, sondern auch effizient ausgeführt werden. Lassen Sie uns anhand konkreter Beispiele betrachten, wo Berechnungsfehler auftreten können und wo die Effizienz verbessert werden sollte., deshalb ist es für uns von großer Bedeutung, dass die Operationen nicht nur korrekt, sondern auch effizient ausgeführt werden. Lassen Sie uns konkrete Beispiele betrachten, in denen Berechnungsfehler auftreten können und wo die Effizienz verbessert werden sollte.

#0: RTFM

Startbeispiel aus der Dokumentation:

Wenn die Reihenfolge der Berechnung wichtig ist, kann sie mit einer Konstruktion fixiert werden. CASE. Zum Beispiel ist es eine Methode, um eine Division durch null in einer Anweisung zu vermeiden. WHERE zuverlässig:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Eine sichere Option:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Die verwendete Konstruktion CASE schützt den Ausdruck vor Optimierung, daher sollte sie nur bei Bedarf verwendet werden.

#1: условие в триггере

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Es sieht alles gut aus, aber… Niemand garantiert, dass das verschachtelte SELECT nicht ausgeführt wird, wenn die erste Bedingung falsch ist. Lassen Sie uns das mit Hilfe von verschachtelten IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Jetzt schauen wir genau hin – der gesamte Körper der Triggerfunktion ist "eingewickelt" in IF. Das bedeutet, dass uns nichts hindert, diese Bedingung aus der Prozedur mit Hilfe von WHEN-Bedingungen:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Dieser Ansatz ermöglicht es, Ressourcen des Servers garantiert zu sparen, wenn die Bedingung falsch ist.

#2: OR/AND-цепочка

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Im ungünstigsten Fall kann es sein, dass beide EXISTS wahr sind, aber beide ausgeführt werden.

Aber wenn wir genau wissen, dass einer von ihnen viel häufiger "wahr" ist (oder "falsch" – für AND-Ketten) — gibt es keine Möglichkeit, «ihn zu priorisieren», damit die zweite Bedingung nicht unnötig oft ausgeführt wird?

Es stellt sich heraus, dass es möglich ist — der algorithmische Ansatz steht im Zusammenhang mit dem Thema des Artikels. PostgreSQL Antipatterns: Eine seltene Zeile wird die Mitte des JOINs erreichen..

Lassen Sie uns einfach beide Bedingungen unter CASE einfügen:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

In diesem Fall haben wir nicht definiert. ELSE-Wert, das heißt im Fall der Falschheit beider Bedingungen. CASE gibt zurück NULL, was als FALSE in WHERE-Bedingung interpretiert wird.

Dieses Beispiel kann auch anders kombiniert werden — je nach Geschmack:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: как [не] надо писать условия

Wir haben zwei Tage damit verbracht, die Gründe für das «seltsame» Auslösen dieses Triggers zu untersuchen — schauen wir uns an, warum.

Quellcode:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplett"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentZurLohnabrechnung"')::oid)
     AND (   OLD."DokumentUnsereOrganisation" <> NEW."DokumentUnsereOrganisation"
          OR OLD."Gelöscht" <> NEW."Gelöscht"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Zeit" <> NEW."Zeit"
          OR OLD."Ersteller" <> NEW."Ersteller" ) ) THEN ...

Problem Nr. 1: Ungleichheit berücksichtigt NULL nicht.

Stellen wir uns vor, dass alle OLD-Felder den Wert NULLhatten. Was wird daraus?

WÄHLEN SIE NULL  1 ODER NULL  2;
-- NULL

Und aus der Sicht der Bedingungserfüllung NULL entspricht FALSE, wie oben erwähnt.

Lösung: Verwenden Sie den Operator IS DISTINCT FROM ab ROWOperator, indem Sie gleichzeitig ganze Datensätze vergleichen:

WÄHLEN (NULL, NULL) IST VERSCHIEDEN VON (1, 2);
-- WAHR

Problem Nr. 2: Unterschiedliche Implementierung derselben Funktionalität

Vergleichen wir:

NEW."Dokument_" = (select '"Komplett"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentNachLöhnen"')::oid)

Warum hier überflüssige verschachtelte SELECT? А функция to_regclass? А по-разному-то почему?..

Lassen Sie uns das korrigieren:

NEW."Dokument_" = '"Komplett"'::regclass::oid
NEW."Dokument_" = '"DokumentNachLöhnen"'::regclass::oid

Problem Nr. 3: Priorität von booleschen Operationen

Lassen Sie uns den Ursprung formatieren:

{... IST NULL} ODER
{... Komplett} ODER
{... DokumentNachLöhnen} UND
( {... Ungleichheiten} )

Ups... Tatsächlich stellte sich heraus, dass im Falle der Wahrhaftigkeit einer der beiden ersten Bedingungen, die gesamte Bedingung in TRUE, ohne die Ungleichheiten zu berücksichtigen. Und das ist ganz und gar nicht das, was wir wollten.

Lassen Sie uns das korrigieren:

(
  {... IST NULL} ODER
  {... Komplett} ODER
  {... DokumentNachLöhnen}
) UND
( {... Ungleichheiten} )

Problem Nr. 4 (klein): Komplexe ODER-Bedingung für ein Feld

Eigentlich sind die Probleme in Nr. 3 genau deshalb aufgetreten, weil es drei Bedingungen gab. Stattdessen kann man mit einem auskommen, durch den Mechanismus coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplett"', '"DokumentZurLohnabrechnung"')

So fangen wir NULL und komplizierte OR mit Klammern brauchen wir nicht zu arbeiten.

Gesamt

Lassen Sie uns festhalten, was wir erreicht haben:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplett"', '"DokumentZurLohnabrechnung"') AND
  (
    OLD."DokumentUnsereOrganisation"
  , OLD."Entfernt"
  , OLD."Datum"
  , OLD."Uhrzeit"
  , OLD."ErstelltVon"
  ) IS DISTINCT FROM (
    NEW."DokumentUnsereOrganisation"
  , NEW."Entfernt"
  , NEW."Datum"
  , NEW."Uhrzeit"
  , NEW."ErstelltVon"
  )
) THEN ...

Und wenn man bedenkt, dass diese Trigger-Funktion nur in einem UPDATE-Trigger aufgrund der Existenz OLD/NEW in der oberen Bedingung vorkommt, kann man diese Bedingung sogar auslagern WHEN-Bedingung, wie in #1 gezeigt...

Quelle: habr.com