PostgreSQL Antipatterns: izračunavanja pogojev v SQL

SQL ni je C++, niti JavaScript. Zato se izračunavanje logičkih izraza događa drugačije, i ovo — nije isto:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

U procesu optimizacije plana izvršenja upita, PostgreSQL može slobodno „premestiti“ ekvivalentne uslove, ne izračunavati neke od njih za pojedine zapise, dodeliti ih uslovu primenjenog indeksa… Ukratko, najbolje je smatrati da unapred ne možete upravljati tim redosledom u kojem će se (i hoće li uopšte) izračunavati ravnopravni uslovi.

Zato, ako želite da upravljate prioritetom, treba strukturalno učiniti ove uslove nerazmjernim uz pomoć uslovnih izraza in operatora..

Podaci i rad sa njima — osnova našeg sistema SBIS, stoga nam je važno da operacije nad njima budu izvršene ne samo tačno, već i efikasno. Pogledajmo konkretne primere gde mogu da nastanu greške u izračunavanju izraza, i gde je potrebno poboljšati njihovu efikasnost.

#0: RTFM

Početni primer iz dokumentacije:

Kada je redosled izračunavanja važan, može se fiksirati pomoću konstrukcije CASE. Na primer, ovakav način izbegavanja deljenja sa nulom u izrazu WHERE nije siguran:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Sigurna varijanta:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Konstrukcija koja se tako primenjuje CASE štiti izraz od optimizacije, tako da je treba koristiti samo po potrebi.

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

POČNI
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  VRATI NEW;
KRAJ;

Sve izgleda dobro, ali… Niko ne garantuje da se ugnježdeni SELECT neće izvršiti kada je prvo stanje lažno. Ispravićemo pomoću ugneždenih IF:

POČNI
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  VRATI NEW;
KRAJ;

Sada hajde da pogledamo pažljivo — celo telo triggerske funkcije je „obavijeno“ u IF. A to znači da nas ništa ne sprečava da tu konstrukciju iznesemo iz procedure pomoću WHEN-uslova:

POČNI
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  VRATI NEW;
KRAJ;
...
KREIRAJ TRIGGER ...
  WHEN cond(NEW.fld);

Ovakav pristup omogućava da se garantovano štede resursi servera kada je uslov lažan.

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

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

U neprijatnoj situaciji, može se desiti da oba EXISTS budu „istiniti“, ali oba će se izvršiti..

Ali ako sigurno znamo da je jedan od njih „istinit“ mnogo češće (ili „lažan“ — za AND-chain) — is there a way to "increase its priority" so that the second one doesn't execute unnecessarily?

It turns out, it is possible — the algorithmic approach is relevant to the article's topic. PostgreSQL Antipatterns: A rare record will reach the middle of the JOIN..

Let's just "embed both conditions under CASE":

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

In this case, we haven't defined ELSE-value, meaning in the case of both conditions being false CASE will return NULL, which is interpreted as FALSE u WHERE-condition.

This example can be combined differently — to each their own:

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

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

We spent two days analyzing the reasons for this trigger's "strange" behavior — let's see why.

Source code:

IF( NEW."Document_" is null or NEW."Document_" = (select '"Set"'::regclass::oid) or NEW."Document_" = (select to_regclass('"PayrollDocument"')::oid)
     AND (   OLD."OurOrganizationDocument"  NEW."OurOrganizationDocument"
          OR OLD."Deleted"  NEW."Deleted"
          OR OLD."Date"  NEW."Date"
          OR OLD."Time"  NEW."Time"
          OR OLD."CreatedBy"  NEW."CreatedBy" ) ) THEN ...

Problem #1: inequality does not account for NULL

Let's assume all OLD-fields had a value NULL. What will happen?

SELECT NULL  1 OR NULL  2;
-- NULL

From the condition's perspective NULL it is equivalent FALSE, as mentioned above.

Solution: use the operator IS DISTINCT FROM od ROW-operator, comparing whole records at once:

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

Problem #2: different implementations of the same functionality

Let's compare:

NEW."Document_" = (select '"Set"'::regclass::oid)
NEW."Document_" = (select to_regclass('"PayrollDocument"')::oid)

Why these unnecessary nested SELECT? А функция to_regclass? А по-разному-то почему?..

Let's fix it:

NEW."Document_" = '"Set"'::regclass::oid
NEW."Document_" = '"PayrollDocument"'::regclass::oid

Problem #3: the priority of bool operations

Let's format the source:

{... IS NULL} OR
{... Set} OR
{... PayrollDocument} AND
( {... inequalities} )

Oops... Essentially, it turns out that if any of the first two conditions are true, the whole condition turns into TRUE, ignoring the inequalities. And that's not what we wanted at all.

Let's fix it:

(
  {... IS NULL} OR
  {... Set} OR
  {... PayrollDocument}
) AND
( {... inequalities} )

Problem #4 (small): complex OR-condition for a single field

In fact, the issues in #3 arose precisely because there were three conditions. But instead, one can manage with one, using the coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplate"')

Tako da ćemo NULL „uhvatiti“, i složenih ILI neće biti potrebno nasilno koristiti zagrade.

Ukupno

Zabeležimo šta smo dobili:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplate"') AND
  (
    OLD."DokumentNasOrganizacija"
  , OLD."Obrisan"
  , OLD."Datum"
  , OLD."Vreme"
  , OLD."LiceKreirano"
  ) IS DISTINCT FROM (
    NEW."DokumentNasOrganizacija"
  , NEW."Obrisan"
  , NEW."Datum"
  , NEW."Vreme"
  , NEW."LiceKreirano"
  )
) THEN ...

A ako uzmemo u obzir da se ova triggerska funkcija može primeniti samo u UPDATE-triggeru zbog postojanja OLD/NEW u uslovu najvišeg nivoa, to se stanje može uopšte izdvojiti u WHEN-uslov, kao što je pokazano u #1…

Izvor: habr.com