ProHoster > Blog > Administration > PostgreSQL Antipatterns: Berechnung von Bedingungen in SQL

PostgreSQL Antipatterns: Berechnung von Bedingungen in SQL

SQL ist nicht C++ und nicht JavaScript. Daher erfolgt die Auswertung logischer Ausdrücke anders, und das hier ist ganz und gar nicht dasselbe:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Bei der Optimierung des Ausführungsplans einer Anfrage kann PostgreSQL äquivalente Bedingungen beliebig "verschieben", einige von ihnen für bestimmte Datensätze nicht auswerten, sie dem verwendeten Index zuordnen… Kurz gesagt, am einfachsten ist es, zu sagen, dass Sie im Voraus nicht steuern können in welcher Reihenfolge die Bedingungen (und ob sie überhaupt) ausgewertet werden gleichberechtigt sind.

Wenn Sie also dennoch die Priorität steuern möchten, müssen Sie strukturell diese Bedingungen ungleich machen durch bedingte Ausdrücke. und von Operatoren.

PostgreSQL Antipatterns: Berechnung von Bedingungen in SQL
Daten und die Arbeit mit ihnen sind die Grundlage unseres SBIS-Komplexes,deshalb ist es uns sehr wichtig, dass die Operationen damit nicht nur korrekt, sondern auch effizient durchgeführt werden. Lassen Sie uns konkrete Beispiele betrachten, in denen Fehler bei der Auswertung von Ausdrücken auftreten können und wo ihre Effizienz verbessert werden sollte.

#0: RTFM

Startwert Beispiel aus der Dokumentation:

Wenn die Reihenfolge der Auswertung wichtig ist, kann sie mit der Konstruktion CASEfixiert werden. Zum Beispiel, um eine Division durch null in der Bedingung zu vermeiden, WHERE ist unsicher:

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

Sichere Variante:

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

Eine so angewendete Konstruktion CASE schützt den Ausdruck vor der Optimierung, daher sollte sie nur bei Bedarf verwendet werden.

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

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

Im Grunde sieht alles gut aus, aber… Niemand garantiert, dass die verschachtelte SELECT nicht ausgeführt wird, wenn die erste Bedingung falsch ist. Korrigieren wir das mit verschachtelten IF:

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

Schauen wir nun genauer hin — der gesamte Körper der Triggerfunktion ist in IFeingekapselt. Das bedeutet, dass uns nichts davon abhält, diese Bedingung 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 eine garantierte Einsparung von Serverressourcen bei Falschheit der Bedingung.

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

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

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

Wenn wir jedoch genau wissen, dass einer von ihnen viel häufiger „wahr“ ist (oder „falsch“ — für AND-Ketten) — kann man nicht irgendwie «seine Priorität erhöhen», damit die zweite nicht unnötig ausgeführt wird?

Es stellt sich heraus, dass es möglich ist — der algorithmische Ansatz ist nah am Thema des Artikels. PostgreSQL-Antipatterns: Eine seltene Aufzeichnung wird bis zur Mitte des JOINs gelangen..

Lass uns einfach beide Bedingungen «unter CASE» stecken:

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

In diesem Fall haben wir nicht definiert ELSE-Wert, also im Falle der Falschheit beider Bedingungen. CASE gibt zurück NULL, was als interpretiert wird FALSCH in WHERE-Bedingung.

Dieses Beispiel kann auch anders kombiniert werden — 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 analysieren — schauen wir uns an, warum.

Quelltext:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplett"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentNachGehaltsabrechnung"')::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."ErstelltVon" >< NEW."ErstelltVon" ) ) THEN ...

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

Stellen wir uns vor, dass alle OLD-Felder den Wert NULLhatten. Was würde passieren?

SELECT NULL >< 1 OR NULL >< 2;
-- NULL

Und aus der Sicht der Bedingungsauswertung NULL ist gleichbedeutend FALSCH, wie oben erwähnt.

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

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Problem Nr. 2: Verschiedene Implementierungen derselben Funktionalität.

Vergleichen wir:

NEW."Dokument_" = (select '"Komplett"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentNachGehaltsabrechnung"')::oid)

Warum sind hier unnötige verschachtelte SELECT? А функция to_regclass? А по-разному-то почему?..

Lassen Sie uns das beheben:

NEW."Dokument_" = '"Komplett"'::regclass::oid
NEW."Dokument_" = '"DokumentNachGehaltsabrechnung"'::regclass::oid

Problem Nr. 3: Priorität boolescher Operationen.

Formatieren wir den Quelltext:

{... IS NULL} OR
{... Komplett} OR
{... DokumentNachGehaltsabrechnung} AND
( {... Ungleichheiten} )

Ups... Tatsächlich ist es so ausgegangen, dass im Falle der Wahrheit einer der beiden ersten Bedingungen, die gesamte Bedingung sich in WAHR, ohne die Ungleichheiten zu berücksichtigen. Und das ist ganz und gar nicht das, was wir wollten.

Lassen Sie uns das beheben:

(
  {... IS NULL} OR
  {... Komplett} OR
  {... DokumentNachGehaltsabrechnung}
) AND
( {... Ungleichheiten} )

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

Genau genommen haben wir das Problem in Nr. 3 genau deshalb, weil es drei Bedingungen gab. Aber stattdessen könnte man mit einem auskommen, indem man den Mechanismus coalesce ... IN:

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

So fangen wir NULL «ein», und komplexe ODER mit Klammern müssen wir uns nicht herumschlagen.

Insgesamt

Lass uns festhalten, was wir erreicht haben:

WENN (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplett"', '"DokumentZurLohnabrechnung"') UND
  (
    OLD."DokumentUnsereOrganisation"
  , OLD."Gelöscht"
  , OLD."Datum"
  , OLD."Zeit"
  , OLD."ErstelltVon"
  ) IST VERSCHIEDEN VON (
    NEW."DokumentUnsereOrganisation"
  , NEW."Gelöscht"
  , NEW."Datum"
  , NEW."Zeit"
  , NEW."ErstelltVon"
  )
) DANN ...

Wenn man bedenkt, dass diese Trigger-Funktion nur in UPDATE-Triggern aufgrund von OLD/NEW in der obersten Bedingung angewendet werden kann, dann kann man diese Bedingung ganz in ein WHEN-Bedingung auslagern, wie in #1 gezeigt...

Quelle: habr.com

60GB SSD 8Gb DDR4