🥇PostgreSQL Antipatterns: izračun uvjeta u SQL

SQL nije C++ ni JavaScript. Stoga se ispunjenje logičkih izraza odvija drugačije i ovo nije isto:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Tijekom optimizacije plana izvršenja upita PostgreSQL može slobodno "premještati" ekvivalentne uvjete, ne izračunati neke od njih za pojedinačne zapise, pripisati ih uvjetu korištenog indeksa... Ukratko, najjednostavnije je smatrati da unaprijed ne možete upravljati tim redoslijedom u kojem će (i hoće li uopće) se izračunati ravnopravni uvjeti.

Zato, ako ipak želite upravljati prioritetom, trebate strukturno napraviti ove uvjete nejednakima uz pomoć uvjetnih izraza i operatora.

Podaci i rad s njima su osnova našeg kompleksa SBIS-a,stoga nam je vrlo važno da operacije s njima ne samo da se izvode ispravno, već i učinkovito. Pogledajmo konkretne primjere gdje mogu nastati greške u izračunu izraza, a gdje vrijedi poboljšati njihovu učinkovitost.

#0: RTFM

Početni primjer iz dokumentacije:

Kada je redoslijed izračunavanja važan, može se fiksirati pomoću konstrukcije CASE. Na primjer, ovakav način izbjegavanja dijeljenja s nulom u izrazu GDJE nije pouzdan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Sigurna opcija:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Konstrukcija primijenjena na ovaj način CASE štiti izraz od optimizacije, stoga je treba koristiti samo kada je to nužno.

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

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

Naizgled sve izgleda dobro, ali... Nitko ne obećava da će unutarnji SELECT biti izvršen ako je prvo uvjet lažan. Ispravimo pomoću unutarnjih IF:

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

Sada pažljivo pogledajmo — cijelo tijelo triggerske funkcije je bilo "obavijeno" u IF. A to znači da nam ništa ne smeta da iznesemo ovo uvjet iz procedure pomoću WHEN-uvjeta:

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

Ovakav pristup omogućava jamstvo uštede resursa servera kada je uvjet lažan.

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

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

U neugodnom slučaju, može se dogoditi da oba EXISTS budu "istinita", ali oba će se izvršiti.

Ali ako smo sigurni da je jedan od njih "istinita" mnogo češće (ili "lažna" — za AND-lanci) — je li moguće nekako "povećati njegov prioritet" da se drugi ne izvrši suvišno?

Ispostavlja se da je moguće — algoritamski pristup je blizak temi članka PostgreSQL Antipatterns: rijetka zapis će stići do sredine JOIN-a.

Hajde da jednostavno "stavimo pod CASE" oba ova uslova:

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

U ovom slučaju nismo odredili ELSE-vrijednost, tj. u slučaju neistine oba uslova CASE vrati NULL, što se tretira kao FALSE u GDJE-uslovima.

Ovaj primer se može kombinovati i drugačije — prema ukusu i boji:

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

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

Na analizu razloga za "čudno" aktiviranje ovog okidača potrošili smo dva dana — hajde da pogledamo zašto.

Izvorni kod:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplati"')::oid)
     AND (   OLD."DokumentNašaOrganizacija" <> NEW."DokumentNašaOrganizacija"
          OR OLD."Obrisan" <> NEW."Obrisan"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Vreme" <> NEW."Vreme"
          OR OLD."OsobaKojaJeNapravila" <> NEW."OsobaKojaJeNapravila" ) ) THEN ...

Problem br. 1: nejednakost ne uzima u obzir NULL

Zamislimo da su sva OLD-polja imala vrednost NULL. Šta bi se desilo?

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

A sa stanovišta izvršenja uslova NULL ekvivalentan FALSE, kao što je već spomenuto.

Rešenje: koristite operator IS DISTINCT FROM od ROW-operatora, upoređujući odmah cele zapise:

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

Problem br. 2: različita implementacija identičnog funkcionalnosti

Uporedimo:

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

Zašto ovde suvišne ugnježdene SELECT? А функция to_regclass? А по-разному-то почему?..

Ispravimo:

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

Problem br. 3: prioritet bool-operacija

Formatirajmo izvor:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarplati} AND
( {... nejednakosti} )

Ups… U stvari, ispostavilo se da u slučaju istinitosti bilo kojeg od prva dva uslova, ceo uslov se pretvara u TRUE, ne uzimajući u obzir nejednakosti. A to nije ono što smo želeli.

Ispravimo:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarplati}
) AND
( {... nejednakosti} )

Problem br. 4 (mali): komplikovano OR-uslov za jedno polje

U osnovi, problemi u br. 3 su se pojavili upravo zato što je bilo tri uslova. Umesto njih možemo se poslužiti jednim, pomoću mehanizma coalesce ... IN:

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

Tako ćemo NULL «uhvatiti», i složenih ILI s zagradama neće biti potrebno.

U konačnici

Fiksirajmo ono što smo dobili:

AKO (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplati"') I
  (
    OLD."DokumentNašaOrganizacija"
  , OLD."Obrisan"
  , OLD."Datum"
  , OLD."Vrijeme"
  , OLD."OsobaKojaJeKreirala"
  ) JE RAZLIČITO OD (
    NEW."DokumentNašaOrganizacija"
  , NEW."Obrisan"
  , NEW."Datum"
  , NEW."Vrijeme"
  , NEW."OsobaKojaJeKreirala"
  )
) TE ...

A ako uzmemo u obzir da se ova okidačka funkcija može primjenjivati samo u AZURIRAJ-okidaču zbog prisutnosti OLD/NEW u uvjetu najviše razine, onda se to uvjet može uopšte izdvojiti u WHEN-uvjet, kao što je prikazano u #1…

Izvor: habr.com