🥇PostgreSQL Antipatterns: Hodnotenie stavu v SQL

SQL nie je C++ ani JavaScript. Preto je hodnotenie logických výrazov odlišné a nie je to vôbec to isté:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Pri optimalizácii plánu vykonávania dotazu PostgreSQL môže ľubovoľne „preusporiadať“ ekvivalentné podmienky, nepočítajte žiadne z nich pre jednotlivé záznamy, odvolávajte sa na stav použitého indexu ... Skrátka, najjednoduchšie je predpokladať, že nezvláda poradie, v akom budú (a či sa vôbec budú počítať) rovný podmienky.

Preto, ak stále chcete riadiť prioritu, musíte štrukturálne urobiť tieto podmienky nerovnými s podmieneným výrazy и operátorov.

Dáta a práca s nimi je základ nášho komplexu VLSI, preto je pre nás veľmi dôležité, aby operácie na nich boli vykonávané nielen správne, ale aj efektívne. Pozrime sa na konkrétne príklady, kde sa môžu chyby pri vyhodnocovaní výrazov dopustiť a kde sa oplatí zlepšiť ich efektivitu.

#0: RTFM

Spustenie príklad z dokumentácie:

Keď je poradie hodnotenia dôležité, dá sa opraviť pomocou konštrukcie CASE. Napríklad takto sa vyhnete deleniu nulou vo vete WHERE nespoľahlivý:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Bezpečná možnosť:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Použitá konštrukcia CASE chráni výraz pred optimalizáciou, preto by sa mal používať iba v prípade potreby.

#1: spúšťacia podmienka

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

Zdá sa, že všetko vyzerá dobre, ale... Nikto nesľubuje, že investované SELECT sa nevykoná, ak je prvá podmienka nepravdivá. Opravte to pomocou vnorené IF:

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

Teraz sa pozrime pozorne - ukázalo sa, že celé telo funkcie spúšte je "zabalené". IF. A to znamená, že nám nič nebráni tento stav z procedúry odstrániť WHEN-podmienky:

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

Tento prístup vám umožňuje ušetriť prostriedky servera so zárukou, ak je podmienka nepravdivá.

#2: reťazec ALEBO/A

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

V opačnom prípade možno získať, že oboje EXISTS bude to pravda, ale obe budú popravené.

Ale ak s istotou vieme, že jeden z nich je „pravdivý“ oveľa častejšie (alebo „nepravdivý“ - napr AND-reťazce) - je možné nejako "zvýšiť jej prioritu", aby sa druhý nevykonával ešte raz?

Ukazuje sa, že je to možné - algoritmický prístup je blízky téme článku PostgreSQL Antipatterns: Zriedkavý záznam dosiahne stred JOINu.

Len "strčme pod CASE" obe tieto podmienky:

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

V tomto prípade sme nedefinovali ELSE-hodnota, to znamená, ak sú obe podmienky nepravdivé CASE vráti sa NULL, ktorý sa vykladá ako FALSE в WHERE- podmienky.

Tento príklad je možné kombinovať iným spôsobom - podľa chuti a farby:

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

#3: ako [ne] písať podmienky

Strávili sme dva dni analyzovaním dôvodov „zvláštneho“ spustenia tohto spúšťača – pozrime sa prečo.

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: Nerovnosť nezodpovedá NULL

Predpokladajme, že všetko OLD- na poliach záležalo NULL. Čo sa bude diať?

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

A to z pohľadu vypracovania podmienok NULL ekvivalent FALSE, ako je spomenuté vyššie.

rozhodnutie: použite operátor IS DISTINCT FROM od ROW-operátor, porovnávajúci celé záznamy naraz:

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

Problém číslo 2: rozdielna implementácia tej istej funkcionality

Porovnajme:

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

Prečo sú tu ďalšie investície SELECT? Funkcia to_regclass? Prečo je to inak...

Poďme opraviť:

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

Problém č. 3: boolovská priorita

Naformátujme zdroj:

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

Ups... V skutočnosti sa ukázalo, že v prípade pravdivosti ktorejkoľvek z prvých dvoch podmienok sa celá podmienka zmení na TRUE, bez ohľadu na nerovnosti. A to vôbec nie je to, čo sme chceli.

Poďme opraviť:

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

Problém č. 4 (malý): komplexná podmienka ALEBO pre jedno pole

V skutočnosti sme mali problémy s číslom 3 práve preto, že tam boli tri podmienky. Ale namiesto nich si vystačíte s jedným pomocou mechanizmu coalesce ... IN:

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

My tiež NULL „úlovok“ a komplex OR Nemusíte sa trápiť so zátvorkami.

Celkom

Opravme, čo máme:

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

A vzhľadom na to, že túto spúšťaciu funkciu možno použiť iba v UPDATEspúšťač v dôsledku prítomnosti OLD/NEW v stave vyššej úrovne, potom môže byť tento stav vo všeobecnosti odstránený WHEN- stav ako na 1...

Zdroj: hab.com

PostgreSQL Antipatterns: Hodnotenie stavu v SQL