Antipatterns PostgreSQL: evaluarea condițiilor în SQL

SQL nu este C++ și nu JavaScript. Prin urmare, calculul expresiilor logice are loc diferit și nu este deloc același lucru:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

În procesul de optimizare a planului de execuție a interogărilor PostgreSQL poate „rearanja” în mod arbitrar condiții echivalente, nu calcula unele dintre ele pentru înregistrările individuale, raportează-le la condițiile indicelui aplicat... Pe scurt, cel mai simplu mod este să presupui că nu pot controla în ce ordine vor (și dacă vor fi calculate) egal conditii.

Prin urmare, dacă tot doriți să gestionați prioritatea, trebuie să o structurați face aceste condiții inegale folosind condiționale expresii и operatori.

Datele și lucrul cu ele sunt baza complexul nostru VLSI, de aceea este foarte important pentru noi ca operațiunile asupra acestora să fie efectuate nu numai corect, ci și eficient. Să ne uităm la exemple specifice în care pot fi făcute erori în calcularea expresiilor și unde merită îmbunătățirea eficienței acestora.

#0: RTFM

Pornire exemplu din documentație:

Când ordinea evaluării este importantă, aceasta poate fi surprinsă folosind constructul CASE. De exemplu, aceasta este o modalitate de a evita împărțirea la zero într-o propoziție WHERE nesigur:

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

Opțiune sigură:

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

Designul folosit în acest fel CASE protejează expresia de optimizare, deci ar trebui să fie folosită numai atunci când este necesar.

#1: condiție de declanșare

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

Totul pare să arate bine, dar... Nimeni nu promite că investiția SELECT nu va fi executată dacă prima condiție este falsă. Să o reparăm cu cuibărit IF:

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

Acum să ne uităm cu atenție - întregul corp al funcției de declanșare este „înfășurat”. IF. Aceasta înseamnă că nimic nu ne împiedică să eliminăm această afecțiune din procedura de utilizare WHEN-conditii:

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

Această abordare este garantată pentru a economisi resursele serverului atunci când condiția este falsă.

#2: lanț SAU/ȘI

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

În caz contrar, poți ajunge cu ambele EXISTS va fi „adevărat”, dar ambele vor fi îndeplinite.

Dar dacă știm cu siguranță că unul dintre ele este „adevărat” mult mai des (sau „fals” - pentru AND-lanțuri) - este posibil să-i „măriți prioritatea” cumva pentru ca al doilea să nu fie executat din nou?

Se pare că este posibil - abordarea algoritmică este aproape de subiectul articolului Antipatterns PostgreSQL: o înregistrare rară va ajunge la mijlocul unui JOIN.

Să „împingem” ambele condiții în CASE:

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

În acest caz nu am definit ELSE-valoare, adică dacă ambele condiții sunt false CASE va reveni NULL, care este interpretat ca FALSE в WHERE-conditii.

Acest exemplu poate fi combinat în alte moduri - în funcție de gust și culoare:

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

#3: cum [nu] să scrieți condiții

Am petrecut două zile analizând motivele funcționării „ciudate” a acestui declanșator - să vedem de ce.

Sursă:

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

Problema #1: inegalitatea nu respectă NULL

Să ne imaginăm că totul OLD-câmpurile aveau sens NULL. Ce se va intampla?

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

Și din punctul de vedere al elaborării condițiilor NULL echivalent FALSE, așa cum sa menționat mai sus.

decizie: utilizați operator IS DISTINCT FROM din ROW-operator, comparând înregistrări întregi simultan:

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

Problema #2: implementări diferite ale aceleiași funcționalități

Să comparăm:

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

De ce există investiții suplimentare aici? SELECT? O functie to_regclass? De ce este diferit?...

Să reparăm:

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

Problema #3: prioritatea operațiunilor bool

Să formatăm sursa:

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

Hopa... De fapt, s-a dovedit că, dacă oricare dintre primele două condiții este adevărată, întreaga condiție se transformă în TRUE, fără a ține cont de inegalități. Și nu este deloc ceea ce ne-am dorit.

Să reparăm:

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

Problema #4 (mică): condiție SAU complexă pentru un câmp

De fapt, am avut probleme la nr. 3 tocmai pentru că erau trei condiții. Dar în loc de ele te poți descurca cu unul, folosind mecanismul coalesce ... IN:

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

Deci noi NULL „vom prinde”, și dificil OR Nu este nevoie să îngrădiți cu paranteze.

În total

Să înregistrăm ce avem:

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

Și dacă considerați că această funcție de declanșare poate fi folosită doar în UPDATE-declanșare din cauza disponibilității OLD/NEW în starea de nivel superior, atunci această condiție poate fi plasată în general WHEN- stare, așa cum se arată în #1...

Sursa: www.habr.com