🥇PostgreSQL Antipatterns: Vyhodnocení stavu v SQL

SQL není C++ ani JavaScript. Proto je vyhodnocování logických výrazů odlišné a není to vůbec totéž:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Při optimalizaci plánu provádění dotazu PostgreSQL může ekvivalentní podmínky libovolně „přeuspořádat“., nevypočítávejte žádné z nich pro jednotlivé záznamy, odkazujte na stav použitého indexu ... Zkrátka nejjednodušší je předpokládat, že nezvládá pořadí, ve kterém budou (a zda se vůbec budou počítat) spravedlivý podmínky.

Proto, pokud stále chcete řídit prioritu, musíte strukturálně učinit tyto podmínky nerovnými s podmíněným výrazy и operátory.

Data a práce s nimi jsou základ našeho komplexu VLSI, proto je pro nás velmi důležité, aby operace na nich byly prováděny nejen správně, ale také efektivně. Podívejme se na konkrétní příklady, kde může dojít k chybám při vyhodnocování výrazů a kde stojí za to zlepšit jejich efektivitu.

#0: RTFM

Začíná příklad z dokumentace:

Když je pořadí hodnocení důležité, lze jej opravit pomocí konstruktu CASE. Například tímto způsobem se vyhnete dělení nulou ve větě WHERE nespolehlivý:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Bezpečná varianta:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Použitá konstrukce CASE chrání výraz před optimalizací, takže by se měl používat pouze v případě potřeby.

#1: spouštěcí podmínka

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

Všechno vypadá dobře, ale... Nikdo neslibuje, že investované SELECT nebude provedena, pokud je první podmínka nepravdivá. Opravte to pomocí vnořený IF:

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

Nyní se podívejme pozorně – celé tělo funkce spouště se ukázalo jako „zabalené“. IF. A to znamená, že nám nic nebrání tento stav z procedury odstranit WHEN-podmínky:

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

Tento přístup vám umožňuje ušetřit prostředky serveru se zárukou, pokud je podmínka nepravdivá.

#2: Řetěz OR/AND

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

Jinak lze získat, že obojí EXISTS bude to pravda, ale obojí bude vykonáno.

Ale pokud víme jistě, že jeden z nich je „pravdivý“ mnohem častěji (nebo „nepravdivý“ - pro AND-řetězce) - je možné nějak "zvýšit jeho prioritu", aby se druhý neprováděl znovu?

Ukazuje se, že je to možné - algoritmický přístup je blízký tématu článku PostgreSQL Antipatterns: Vzácný záznam dosáhne středu JOIN.

Pojďme jen "strčit pod CASE" obě tyto podmínky:

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

V tomto případě jsme nedefinovali ELSE-hodnota, to znamená, pokud jsou obě podmínky nepravdivé CASE vrátí se NULL, který se vykládá jako FALSE в WHERE- podmínky.

Tento příklad lze kombinovat jiným způsobem - podle chuti a barvy:

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

#3: jak [ne] psát podmínky

Strávili jsme dva dny analyzováním důvodů „podivného“ spuštění tohoto spouštěče – podívejme se proč.

Zdroj:

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 ...

Problém č. 1: Nerovnost nebere v úvahu NULL

Předpokládejme, že všechno OLD- na polích záleželo NULL. Co se bude dít?

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

A to z pohledu vypracování podmínek NULL ekvivalent FALSE, jak je zmíněno výše.

rozhodnutí: použijte operátor IS DISTINCT FROM z ROW-operátor, porovnávající celé záznamy najednou:

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

Problém číslo 2: odlišná implementace stejné funkcionality

Porovnat:

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

Proč jsou tam další investice SELECT? Funkce to_regclass? Proč je to jinak...

Pojďme opravit:

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

Problém č. 3: boolovská priorita

Zformátujeme zdroj:

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

Jejda... Ve skutečnosti se ukázalo, že v případě pravdivosti kterékoli z prvních dvou podmínek se celá podmínka změní na TRUE, bez ohledu na nerovnosti. A to vůbec není to, co jsme chtěli.

Pojďme opravit:

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

Problém č. 4 (malý): složitá podmínka NEBO pro jedno pole

Ve skutečnosti jsme měli problémy v č. 3 právě proto, že byly tři podmínky. Ale místo nich si vystačíte s jedním pomocí mechanismu coalesce ... IN:

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

My také NULL „chytit“ a složitě OR Nemusíte si lámat hlavu se závorkami.

Celkem

Opravme, co máme:

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

A vzhledem k tomu, že tuto spouštěcí funkci lze použít pouze v UPDATEspoušť kvůli přítomnosti OLD/NEW ve stavu vyšší úrovně, pak lze tento stav obecně odstranit WHEN- stav viz #1...

Zdroj: www.habr.com

PostgreSQL Antipatterns: Vyhodnocení podmínek v SQL