🥇Antywzorce PostgreSQL: ocena warunków w SQL

SQL to nie C++ ani JavaScript. Dlatego ocena wyrażeń logicznych jest inna, a to wcale nie jest to samo:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Podczas optymalizacji planu wykonania zapytania PostgreSQL może dowolnie „przestawiać” równoważne warunki, nie obliczaj żadnego z nich dla poszczególnych rekordów, zapoznaj się z warunkiem zastosowanego indeksu… Krótko mówiąc, najprościej założyć, że nie mogę zarządzać kolejność w jakiej będą (i czy w ogóle będą naliczane) równy warunki.

Dlatego jeśli nadal chcesz zarządzać priorytetami, musisz to zrobić strukturalnie uczynić te warunki nierównymi z warunkowym wyrażenia и operatorzy.

Dane i praca z nimi to podstawa naszego kompleksu VLSI, dlatego jest dla nas bardzo ważne, aby operacje na nich były wykonywane nie tylko poprawnie, ale również sprawnie. Przyjrzyjmy się konkretnym przykładom, gdzie można popełnić błędy w ocenie wyrażeń i gdzie warto poprawić ich efektywność.

#0: RTFM

Startowy przykład z dokumentacji:

Gdy kolejność oceny jest ważna, można ją ustalić za pomocą konstrukcji CASE. Na przykład w ten sposób, aby uniknąć dzielenia przez zero w zdaniu WHERE niewiarygodne:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Bezpieczna opcja:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Zastosowana konstrukcja CASE chroni wyrażenie przed optymalizacją, dlatego należy go używać tylko wtedy, gdy jest to konieczne.

# 1: warunek wyzwalania

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

Wszystko wydaje się wyglądać dobrze, ale... Nikt nie obiecuje, że zainwestował SELECT nie zostanie wykonane, jeśli pierwszy warunek jest fałszywy. Napraw to za pomocą zagnieżdżone IF:

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

Przyjrzyjmy się teraz uważnie - całe ciało funkcji wyzwalającej okazało się „zawinięte”. IF. A to oznacza, że nic nie stoi na przeszkodzie, aby usunąć ten warunek z procedury używającej WHEN-warunki:

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

Takie podejście pozwala zaoszczędzić zasoby serwera z gwarancją, jeśli warunek jest fałszywy.

#2: Łańcuch OR/AND

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

W przeciwnym razie można uzyskać jedno i drugie EXISTS będzie prawdą, ale oba zostaną wykonane.

Ale jeśli wiemy na pewno, że któraś z nich jest „prawdziwa” znacznie częściej (lub „fałszywa” – np AND-chains) - czy można jakoś "zwiększyć jego priorytet", aby drugi nie był wykonywany po raz kolejny?

Okazuje się, że jest to możliwe – podejście algorytmiczne jest bliskie tematowi artykułu Antywzorce PostgreSQL: Rzadki wpis dociera do środka JOIN.

Po prostu „wsuń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ść, to znaczy, jeśli oba warunki są fałszywe CASE wróci NULL, co jest interpretowane jako FALSE в WHERE- warunki.

Ten przykład można połączyć w inny sposób - do smaku i koloru:

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

#3: jak [nie] pisać warunków

Spędziliśmy dwa dni na analizie przyczyn „dziwnego” wyzwolenia tego wyzwalacza - zobaczmy dlaczego.

Źródło:

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: Nierówność nie uwzględnia NULL

Załóżmy, że wszystko OLD- liczyły się pola NULL. Co się stanie?

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

I z punktu widzenia wypracowania warunków NULL równowartość FALSE, jak wspomniano powyżej.

decyzja: użyj operatora IS DISTINCT FROM od ROW-operator, porównujący od razu całe rekordy:

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

Problem numer 2: inna implementacja tej samej funkcjonalności

Porównaj:

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

Dlaczego są dodatkowe inwestycje SELECT? Funkcja to_regclass? Dlaczego jest inaczej...

naprawmy:

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

Problem nr 3: pierwszeństwo bool

Sformatujmy źródło:

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

Ups... W rzeczywistości okazało się, że w przypadku prawdziwości któregokolwiek z dwóch pierwszych warunków, cały warunek zamienia się w TRUE, pomijając nierówności. A to wcale nie jest to, czego chcieliśmy.

naprawmy:

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

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

Właściwie mieliśmy problemy w nr 3 właśnie dlatego, że były trzy warunki. Ale zamiast nich możesz sobie poradzić z jednym, używając mechanizmu coalesce ... IN:

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

Tak jak my NULL „haczyk” i złożony OR Nie musisz męczyć się z nawiasami.

Razem

Naprawmy to, co mamy:

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

A biorąc pod uwagę, że ta funkcja wyzwalacza może być używana tylko w UPDATEwyzwalacz ze względu na obecność OLD/NEW w warunku wyższego poziomu, to warunek ten można ogólnie wyjąć WHEN- stan jak na zdjęciu nr 1...

Źródło: www.habr.com

Antywzorce PostgreSQL: ocena warunków w SQL