PostgreSQL Antipatterns: výpočty podmienok v SQL

SQL nie je C++ ani JavaScript. Preto sa výpočty logických výrazov uskutočňujú inak a toto nie je to isté:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Počas optimalizácie plánu vykonávania dopytu PostgreSQL môže svojvoľne "premiestňovať" ekvivalentné podmienky, nevyhodnocovať niektoré z nich pre jednotlivé záznamy, priradiť ich k podmienke použitého indexu... Skrátka, najjednoduchšie je považovať za to, že vopred nemôžete ovládať poradie, v akom budú (a budú vôbec) vyhodnocované rovnocenné podmienky.

Preto ak sa chcete s prímom poradia hrať, musíte štrukturálne urobiť tieto podmienky nerovnými s pomocou podmienkových výrazov a operátorov.

Údaje a práca s nimi sú základom nášho komplexu SBIS, preto je pre nás veľmi dôležité, aby operácie s nimi prebiehali nielen správne, ale aj efektívne. Pozrime sa na konkrétne príklady, kde môžu vzniknúť chyby pri vyhodnocovaní výrazov a kde je potrebné zlepšiť ich efektivitu.

#0: RTFM

Štartovací príklad z dokumentácie:

Keď je poradie vyhodnocovania dôležité, môže sa fixovať pomocou konštrukcie CASE. Napríklad tento spôsob, ako sa vyhnúť deleniu nulou v podmienke WHERE nie je spoľahlivý:

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;

Konštrukcia používaná takto CASE chráni výraz pred optimalizáciou, preto ju treba používať len v prípade potreby.

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

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

Na prvý pohľad to vyzerá dobre, ale... Nikto nezaručuje, že vnorený SELECT nebude vykonaný pri nepravdivosti prvej podmienky. Opravené pomocou vnorených IF:

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

Teraz sa pozrime pozornejšie — celé telo spúšťacej funkcie je "zabalené" v IF. A to znamená, že nič nám nebráni vyňať túto podmienku z procedúry pomocou WHEN-podmienky:

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

Takýto prístup zaručuje úsporu serverových zdrojov pri nepravdivosti podmienky.

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

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

V nepríjemnom prípade sa môže stať, že obidva EXISTS budú "pravdivé", ale obidva sa vykonajú.

Ale ak presne vieme, že jeden z nich je "pravdivý" oveľa častejšie (alebo "nepravdivý" — pre A-reťazce) — nie je možné akosi "zvýšiť jeho prioritu", aby sa druhý nevykonával zbytočne?

Ukazuje sa, že to je možné — algoritmický prístup je blízky téme článku PostgreSQL Antipatterns: zriedkavý záznam sa dostane do stredu JOIN.

Poďme jednoducho „schovať pod CASE“ oba tieto podmienky:

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

V tomto prípade sme nedefinovali ELSE-hodnota, teda v prípade nepravdivosti oboch podmienok CASE vrátí NULL, čo sa vykladá ako FALSE v WHERE-podmienka.

Tento príklad je možné skombinovať aj inak — podľa chuti:

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

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

Na rozbor príčin „čudného“ spustenia tohto triggera sme strávili dva dni — poďme sa pozrieť, prečo.

Zdrojový kód:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplate"')::oid)
     AND (   OLD."DokumentNasaOrganizacia"  NEW."DokumentNasaOrganizacia"
          OR OLD."Odstraneny"  NEW."Odstraneny"
          OR OLD."Dátum"  NEW."Dátum"
          OR OLD."Čas"  NEW."Čas"
          OR OLD."OsobaVytvorila"  NEW."OsobaVytvorila" ) ) THEN ...

Problém č. 1: nerovnosť nezohľadňuje NULL

Predpokladajme, že všetky OLD-polia mali hodnotu NULL. Čo sa stane?

SELECT NULL  1 OR NULL  2;
-- NULL

A z hľadiska spracovania podmienky NULL je ekvivalentný FALSE, ako už bolo spomenuté vyššie.

Riešenie: použite operátor IS DISTINCT FROM od ROW-operátor, porovnávajúc priamo celé záznamy:

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

Problém č. 2: rôzna implementácia rovnakého funkčného využitia

Porovnajme:

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

Prečo sú tu zbytočné vnorené SELECT? А функция to_regclass? А по-разному-то почему?..

Opravené:

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

Problém č. 3: priorita bool-operácií

Naformátujme zdrojový kód:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarplate} AND
( {... nerovnosti} )

Ups… V skutočnosti sa ukazuje, že ak bude ktorékoľvek z prvých dvoch podmienok pravdivé, celé podmienka sa premení na TRUE, bez ohľadu na nerovnosti. A to nie je presne to, čo sme chceli.

Opravené:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarplate}
) AND
( {... nerovnosti} )

Problém č. 4 (malý): zložitá OR-podmienka pre jedno pole

Vlastne, problémy v č. 3 nastali práve preto, že podmienok bolo tri. Ale namiesto nich sa dá obísť s jedným, pomocou mechanizmu coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplate"')

Takto zachytíme a nevzniknú zložité NULL s závorkami. OR s závorkami nebude treba robiť.

Spolu

Zaznamenajme, čo sme dosiahli:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPodľaMzdy"') AND
  (
    OLD."DokumentNašaOrganizácia"
  , OLD."Odstránené"
  , OLD."Dátum"
  , OLD."Čas"
  , OLD."OsobaVytvoril"
  ) IS DISTINCT FROM (
    NEW."DokumentNašaOrganizácia"
  , NEW."Odstránené"
  , NEW."Dátum"
  , NEW."Čas"
  , NEW."OsobaVytvoril"
  )
) THEN ...

A ak zohľadníme, že túto spúšťaciu funkciu je možné aplikovať len v UPDATE-spúšti kvôli prítomnosti OLD/NEW v podmienke najvyššej úrovne, tak túto podmienku môžeme úplne presunúť do WHEN-podmienky, ako bolo ukázané v #1…

Zdroj: habr.com