ProHoster > Blog > Verwaltung > PostgreSQL-Antipatterns: Bedingungsauswertung in SQL

PostgreSQL-Antipatterns: Bedingungsauswertung in SQL

SQL ist weder C++ noch JavaScript. Daher ist die Bewertung logischer Ausdrücke unterschiedlich, und das ist überhaupt nicht dasselbe:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Beim Optimieren des Ausführungsplans einer PostgreSQL-Abfrage kann die äquivalenten Bedingungen beliebig „neu anordnen“., berechnen Sie keine davon für einzelne Datensätze, sondern beziehen Sie sich auf die Bedingung des angewendeten Index ... Kurz gesagt, der einfachste Weg ist, davon auszugehen, dass Sie schaffe es nicht in welcher Reihenfolge sie vorliegen (und ob sie überhaupt berechnet werden) gleich Bedingungen

Wenn Sie dennoch Prioritäten verwalten möchten, müssen Sie dies strukturell tun machen diese Bedingungen ungleich mit bedingt Ausdrücke и Betreiber.

PostgreSQL-Antipatterns: Bedingungsauswertung in SQL
Daten und die Arbeit mit ihnen sind die Basis unseres VLSI-KomplexesDaher ist es für uns sehr wichtig, dass die Operationen an ihnen nicht nur korrekt, sondern auch effizient durchgeführt werden. Schauen wir uns konkrete Beispiele an, wo Fehler bei der Ausdrucksauswertung gemacht werden können und wo es sich lohnt, deren Effizienz zu verbessern.

#0: RTFM

Beginnend Beispiel aus der Dokumentation:

Wenn die Reihenfolge der Auswertung wichtig ist, kann sie mit dem Konstrukt festgelegt werden CASE. Auf diese Weise lässt sich beispielsweise die Division durch Null in einem Satz vermeiden WHERE unzuverlä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 Konstruktion verwendet CASE schützt den Ausdruck vor Optimierung und sollte daher nur bei Bedarf verwendet werden.

#1: Auslösebedingung

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

Alles scheint gut auszusehen, aber... Niemand verspricht, dass das investiert wird SELECT wird nicht ausgeführt, wenn die erste Bedingung falsch ist. Repariere es mit verschachtelt IF:

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

Schauen wir uns nun genau an – es stellte sich heraus, dass der gesamte Körper der Triggerfunktion „eingepackt“ war IF. Und das bedeutet, dass uns nichts daran hindert, diesen Zustand aus dem Verfahren zu entfernen WHEN-Bedingungen:

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

Mit diesem Ansatz können Sie Serverressourcen mit einer Garantie sparen, wenn die Bedingung falsch ist.

#2: ODER/UND-Kette

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

Andernfalls kann beides erreicht werden EXISTS wird wahr sein, aber beide werden ausgeführt.

Aber wenn wir sicher wissen, dass eine davon viel häufiger „wahr“ ist (oder „falsch“ – z AND-chains) - ist es möglich, die Priorität irgendwie zu erhöhen, damit die zweite nicht noch einmal ausgeführt wird?

Es stellt sich heraus, dass es möglich ist – der algorithmische Ansatz kommt dem Thema des Artikels nahe PostgreSQL-Antipatterns: Seltener Eintrag erreicht die Mitte eines JOIN.

Lassen Sie uns diese beiden Bedingungen einfach „unter CASE schieben“:

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

In diesem Fall haben wir es nicht definiert ELSE-Wert, das heißt, wenn beide Bedingungen falsch sind CASE wird zurückkehren NULL, was interpretiert wird als FALSE в WHERE- Bedingungen.

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

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

#3: Wie schreibt man Bedingungen nicht?

Wir haben zwei Tage damit verbracht, die Gründe für das „seltsame“ Auslösen 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: Ungleichheit ist nicht für NULL verantwortlich

Nehmen wir an, dass alles OLD-Felder waren wichtig NULL. Was wird passieren?

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

Und unter dem Gesichtspunkt der Ausarbeitung der Bedingungen NULL gleichwertig FALSE, wie oben erwähnt.

Lösung: Operator verwenden IS DISTINCT FROM aus ROW-Operator, der ganze Datensätze auf einmal vergleicht:

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

Problem Nummer 2: unterschiedliche Implementierung derselben Funktionalität

Vergleichen wir:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Warum gibt es zusätzliche Investitionen? SELECT? Eine Funktion to_regclass? Warum ist es anders...

Lassen Sie uns Folgendes beheben:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Problem Nr. 3: Bool-Priorität

Formatieren wir die Quelle:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Ups ... Tatsächlich stellte sich heraus, dass sich im Falle der Wahrheit einer der ersten beiden Bedingungen die gesamte Bedingung in verwandelt TRUE, ohne Berücksichtigung von Ungleichheiten. Und das ist überhaupt nicht das, was wir wollten.

Lassen Sie uns Folgendes beheben:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

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

Tatsächlich hatten wir in Nr. 3 gerade deshalb Probleme, weil es drei Bedingungen gab. Aber stattdessen können Sie mit dem Mechanismus auch mit einem auskommen coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Wir auch NULL „Fang“ und komplex OR Sie müssen sich nicht mit Klammern herumschlagen.

Insgesamt

Lassen Sie uns beheben, was wir haben:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Und da diese Triggerfunktion nur in verwendet werden kann UPDATEAuslöser aufgrund der Anwesenheit OLD/NEW Im oberen Zustand kann dieser Zustand im Allgemeinen herausgenommen werden WHEN-Zustand wie in #1 gezeigt...

Source: habr.com

Kommentar hinzufügen