Get best of the week

PostgreSQL Antipatterns: Berechnung der Bedingungen in SQL

SQL ist weder C ++ noch JavaScript. Daher ist die Berechnung logischer Ausdrücke unterschiedlich, und dies ist nicht dasselbe:
WHERE fncondX() AND fncondY()
= fncondX() && fncondY()

Bei der Optimierung des Abfrageausführungsplans kann PostgreSQL die entsprechenden Bedingungen willkürlich neu anordnen , keine für einzelne Datensätze berechnen und sie der Bedingung des entsprechenden Index zuordnen. Kurz gesagt, es ist am einfachsten anzunehmen, dass Sie nicht steuern können, in welcher Reihenfolge Peer- Bedingungen werden berechnet (und ob überhaupt) .

Wenn Sie die Priorität dennoch steuern möchten, müssen Sie diese Bedingungen mithilfe von bedingten Ausdrücken und Operatoren strukturell ungleich machen .


Daten und die Arbeit mit ihnen sind die Grundlage unseres VLSI-Komplexes. Daher ist es für uns sehr wichtig, dass die Operationen an ihnen nicht nur korrekt, sondern auch effizient ausgeführt werden. Schauen wir uns spezifische Beispiele an, bei denen Ausdrucksberechnungsfehler gemacht werden können und bei denen es sich lohnt, ihre Effizienz zu verbessern.

# 0: RTFM


Startbeispiel aus der Dokumentation :

Wenn die Berechnungsreihenfolge wichtig ist, kann sie mithilfe der Konstruktion festgelegt werden CASE. Ein solcher Weg, um zu vermeiden, dass in einem Satz durch Null geteilt wird, ist beispielsweise WHEREunzuverlässig:

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

Sichere Option:

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

Die auf diese Weise verwendete Konstruktion CASEschützt den Ausdruck vor Optimierung und sollte daher nur bei Bedarf verwendet werden.

# 1: Bedingung im Auslöser


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

Es scheint, dass alles gut aussieht, aber ... Niemand verspricht, dass das beiliegende SELECTnicht ausgeführt wird, wenn die erste Bedingung falsch ist. Richtig mit verschachteltIF :

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

Schauen wir uns das jetzt genau an - der gesamte Körper der Triggerfunktion hat sich als "eingewickelt" herausgestellt IF. Und das bedeutet, dass uns nichts daran hindert, diese Bedingung mit der Bedingung -bedingung aus der Prozedur zu WHENentfernen :

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

Dieser Ansatz ermöglicht die garantierte Einsparung von Serverressourcen unter falschen Bedingungen.

# 2: ODER / UND-Kette


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

Andernfalls können Sie feststellen, dass beide EXISTS"wahr" sind, aber beide erfüllt sind .

Aber wenn wir sicher wissen, dass einer von ihnen viel häufiger „wahr“ ist (oder „falsch“ für die ANDKette), ist es dann möglich, „seine Priorität zu erhöhen“, damit der zweite nicht noch einmal ausgeführt wird?

Es stellt sich heraus, dass dies möglich ist - der Algorithmus-Ansatz kommt dem Thema des Artikels über PostgreSQL Antipatterns nahe: Ein seltener Datensatz erreicht die Mitte des JOIN .

Lassen Sie uns einfach beide Bedingungen unter "CASE" stellen:

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

In diesem Fall haben wir den ELSE-Wert nicht ermittelt. Wenn beide Bedingungen falsch sind , CASEwird er zurückgegeben NULL, was wie FALSEin der WHERE-bedingung interpretiert wird.

Dieses Beispiel kann auf andere Weise kombiniert werden - nach Geschmack und Farbe:

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

# 3: wie man Bedingungen [nicht] schreibt


Wir haben zwei Tage damit verbracht, die Gründe für den „seltsamen“ Auslöser dieses Auslösers zu analysieren - mal sehen, warum.

Quelle:

IF( NEW."_" is null or NEW."_" = (select '""'::regclass::oid) or NEW."_" = (select to_regclass('""')::oid)
     AND (   OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW."" ) ) THEN ...

Problem Nr. 1: Ungleichung berücksichtigt nicht NULL


Stellen Sie sich vor, dass alle OLDFelder wichtig sind NULL. Was wird passieren?

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

Und unter dem Gesichtspunkt der Ausarbeitung sind die Bedingungen NULLgleichwertig FALSE, wie oben erwähnt.

Lösung : Verwenden Sie den Operator IS DISTINCT FROMdes ROWOperators und vergleichen Sie ganze Datensätze auf einmal:

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

Problem Nummer 2: Unterschiedliche Implementierung derselben Funktionalität


Vergleichen Sie:

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

Warum gibt es zusätzliche Verschachtelungen SELECT? Was ist mit der Funktion to_regclass? Und auf verschiedene Weise, warum? ..

Fix:

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

Problem Nr. 3: Priorität für Bool-Operationen


Formatieren Sie die Quelle:

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

Hoppla ... Tatsächlich stellte sich heraus, dass im Fall der Wahrheit einer der ersten beiden Bedingungen die gesamte Bedingung TRUEohne Berücksichtigung von Ungleichheiten wird. Und das wollten wir überhaupt nicht.

Fix:

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

Aufgabe 4 (klein): komplexe ODER-Bedingung für ein Feld


Tatsächlich hatten wir in Nr. 3 Probleme, gerade weil es drei Bedingungen gab. Anstelle von ihnen können Sie jedoch einen mithilfe des Mechanismus ausführen coalesce ... IN:

coalesce(NEW."_"::text, '') IN ('', '""', '""')

Also NULL„fangen“ wir und müssen ORmit Klammern komplexieren nicht umzäunen.

Gesamt


Wir reparieren, was wir haben:

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

Und wenn wir berücksichtigen, dass diese UPDATETriggerfunktion aufgrund des Vorhandenseins OLD/NEWeiner obersten Ebene in der Bedingung nur im -trigger verwendet werden kann, kann diese Bedingung im Allgemeinen in die WHEN-bedingung versetzt werden, wie in # 1 gezeigt wurde ...

More articles:


All Articles