PostgreSQL Antipatterns: obliczanie warunków w SQL

SQL to nie C++ ani JavaScript. Dlatego obliczanie wyrażeń logicznych odbywa się inaczej, a to zupełnie nie to samo:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

W procesie optymalizacji planu wykonania zapytania PostgreSQL może dowolnie "przemieszczać" równoważne warunki, nie wyliczając niektórych z nich dla poszczególnych rekordów, przypisując je do warunku stosowanego indeksu... Krótko mówiąc, najłatwiej uznać, że z góry nie możesz zarządzać tym, w jakiej kolejności będą (i czy w ogóle) obliczane równoprawne warunki.

Dlatego jeśli chcesz jednak zarządzać priorytetem, musisz strukturalnie uczynić te warunki nierównymi za pomocą wyrażeń warunkowych i operatorów.

Dane i praca z nimi to fundament naszego systemu SBiS, dlatego bardzo ważne jest, aby operacje na nich były wykonywane nie tylko poprawnie, ale też efektywnie. Przyjrzyjmy się konkretnym przykładom, gdzie mogą wystąpić błędy w obliczeniach wyrażeń, a gdzie warto poprawić ich wydajność.

#0: RTFM

Początkowy przykład z dokumentacji:

Kiedy kolejność obliczeń ma znaczenie, można ją ustalić za pomocą konstrukcji CASE. Na przykład, taki sposób uniknięcia dzielenia przez zero w klauzuli WHERE nie jest niezawodny:

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

Bezpieczna wersja:

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

Konstrukcja używana w ten sposób CASE chroni wyrażenie przed optymalizacją, dlatego należy jej używać tylko w razie potrzeby.

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

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

Wydaje się, że wszystko wygląda dobrze, ale... Nikt nie obiecuje, że zagnieżdżone SELECT nie zostanie wykonane w przypadku fałszywości pierwszego warunku. Poprawimy to za pomocą zagnieżdżonych IF:

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

Teraz przyjrzyjmy się dokładniej — całe ciało funkcji wyzwalacza okazało się "zainwestowane" w IF. A to oznacza, że nic nam nie przeszkadza, aby wydobyć to warunek z procedury za pomocą WHEN-warunku:

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

Takie podejście pozwala gwarantować oszczędność zasobów serwera w przypadku fałszywości warunku.

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

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

W nieprzyjemnym przypadku można uzyskać, że obie EXISTS będą "prawdziwe", ale obie się wykonają.

Ale jeśli na pewno wiemy, że jedna z nich jest "prawdziwa" znacznie częściej (lub "fałszywa" — dla AND-ciągów) — czy nie dałoby się jakoś „podnieść jego priorytet”, aby drugi nie był wykonywany niepotrzebnie?

Okazuje się, że można — algorytmiczne podejście jest zbliżone do tematu artykułu PostgreSQL Antipatterns: rzadki zapis dotrze do środka JOIN.

Po prostu „umieśćmy pod CASE” oba te warunki:

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

W tym przypadku nie zdefiniowaliśmy ELSE-wartości, czyli w przypadku fałszywości obu warunków CASE zwróci NULL, co interpretuje się jako FAŁSZ do WHERE-warunek.

Ten przykład można skombinować inaczej — według gustu:

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

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

Na analizę przyczyn „dziwnego” działania tego triggera poświęciliśmy dwa dni — przyjrzyjmy się, dlaczego.

Kod źródłowy:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarcie"')::oid)
     AND (   OLD."DokumentNaszOrganizacja" <> NEW."DokumentNaszOrganizacja"
          OR OLD."Usunięty" <> NEW."Usunięty"
          OR OLD."Data" <> NEW."Data"
          OR OLD."Czas" <> NEW."Czas"
          OR OLD."OsobaUtworzyła" <> NEW."OsobaUtworzyła" ) ) THEN ...

Problem nr 1: nierówność nie uwzględnia NULL

Załóżmy, że wszystkie OLD-pola miały wartość NULL. Co wyjdzie?

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

A z punktu widzenia przetwarzania warunków NULL jest równoważny FAŁSZ, jak wspomniano powyżej.

Rozwiązanie: użyj operatora IS DISTINCT FROM od ROW-operatora, porównując od razu całe rekordy:

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

Problem nr 2: różna realizacja tej samej funkcjonalności

Porównajmy:

NEW."Dokument_" = (select '"Komplet"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentPoZarcie"')::oid)

Dlaczego tu są zbędne zagnieżdżenia SELECT? А функция to_regclass? А по-разному-то почему?..

Poprawmy:

NEW."Dokument_" = '"Komplet"'::regclass::oid
NEW."Dokument_" = '"DokumentPoZarcie"'::regclass::oid

Problem nr 3: priorytet operacji boolowskich

Sformatujmy kod źródłowy:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarcie} AND
( {... nierówności} )

Ups… W rzeczywistości okazało się, że w przypadku prawdziwości dowolnego z dwóch pierwszych warunków, całe warunki zmieniają się w PRAWDA, bez uwzględnienia nierówności. A to zupełnie nie to, czego chcieliśmy.

Poprawmy:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarcie}
) AND
( {... nierówności} )

Problem nr 4 (mały): złożony warunek OR dla jednego pola

Właściwie problemy w nr 3 pojawiły się dokładnie dlatego, że mieliśmy trzy warunki. Ale można się obejść jednym, za pomocą mechanizmu coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Zestaw"', '"DokumentDoWynagrodzenia"')

Tak my i NULL „złapiemy”, i skomplikowanych OR z nawiasami nie będziemy musieli kombinować.

Podsumowując

Utrwalimy to, co uzyskaliśmy:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Zestaw"', '"DokumentDoWynagrodzenia"') AND
  (
    OLD."DokumentNaszaOrganizacja"
  , OLD."Usunięty"
  , OLD."Data"
  , OLD."Czas"
  , OLD."OsobaUtworzona"
  ) IS DISTINCT FROM (
    NEW."DokumentNaszaOrganizacja"
  , NEW."Usunięty"
  , NEW."Data"
  , NEW."Czas"
  , NEW."OsobaUtworzona"
  )
) THEN ...

A jeśli weźmiemy pod uwagę, że ta funkcja wyzwalająca może być stosowana tylko w UPDATE-wyzwalaczu ze względu na obecność OLD/NEW w górnym warunku, to ten warunek można całkowicie przenieść do WHEN-warunku, jak pokazano w #1…

Źródło: habr.com