PostgreSQL Antipatterns: calcularea condițiilor în SQL

SQL nu este C++ și nici JavaScript. De aceea, calculul expresiilor logice se realizează diferit, iar aceasta nu este deloc același lucru:

UND fncondX() ȘI fncondY()

= fncondX() && fncondY()

În procesul de optimizare a planului de execuție a interogării PostgreSQL poate schimba în mod arbitrar ordinea condițiilor echivalente, să nu calculeze unele dintre ele pentru înregistrări individuale, să le asocieze cu condiția indicelui aplicat… Pe scurt, cel mai simplu este să consideri că nu poți gestiona ordinea în care vor fi (și vor fi chiar) calculate condițiile egale. Prin urmare, dacă totuși dorești să gestionezi prioritatea, trebuie să le faci structurale

inegale prin intermediul expresiilor condiționale operatorilor și Datele și lucrul cu acestea sunt fundamentul.

complexului nostru SBIS , de aceea este foarte important pentru noi ca operațiile asupra lor să fie realizate nu doar corect, ci și eficient. Să ne uităm la exemple concrete unde pot apărea erori în calcularea expresiilor și unde ar trebui să îmbunătățim eficiența acestora.Începător

#0: RTFM

Când ordinea de calcul este importantă, aceasta poate fi fixată prin intermediul construcției exemplu din documentație:

CASE . De exemplu, această metodă de a evita împărțirea la zero în propozițienu este de încredere: WHERE SELECT ... UND x > 0 ȘI y/x > 1.5;

O variantă sigură:

SELECT ... UND CASE CÂND x > 0 ATUNCI y/x > 1.5 ALTFEL fals END;

Construcția aplicată astfel

protejează expresia de optimizare, de aceea ar trebui utilizată doar când este necesar. . De exemplu, această metodă de a evita împărțirea la zero în propoziție ÎNCEPE IF cond(NEW.fld) ȘI EXISTĂ(SELECT ...) AȘA CĂ ... SFÂRȘIT IF; ÎNTOARCE NEW; SFÂRȘIT;

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

Totul pare bine, dar… Nimeni nu promite că imersia

nu va fi executată dacă prima condiție este falsă. Să corectăm folosind SELECT imbricate IF ÎNCEPE IF cond(NEW.fld) ATUNCI IF EXISTĂ(SELECT ...) ATUNCI ... SFÂRȘIT IF; SFÂRȘIT IF; ÎNTOARCE NEW; SFÂRȘIT;:

Acum să ne uităm atent — întreg corpul funcției declanșatoare s-a „învelit” în

. Asta înseamnă că nimic nu ne împiedică să extragem această condiție din procedură folosind ÎNCEPE IF cond(NEW.fld) ATUNCI IF EXISTĂ(SELECT ...) ATUNCI ... SFÂRȘIT IF; SFÂRȘIT IF; ÎNTOARCE NEW; SFÂRȘIT;-condiția ÎNCEPE IF EXISTĂ(SELECT ...) ATUNCI ... SFÂRȘIT IF; ÎNTOARCE NEW; SFÂRȘIT; ... CREAȚI TRIGGER ... CÂND cond(NEW.fld);Această abordare permite garantarea economisirii resurselor serverului când condiția este falsă.:

SELECT ... UND EXISTĂ(... A) SAU EXISTĂ(... B)

În cazul neplăcut, se poate întâmpla ca ambele

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

să fie „adevărate”, dar

ambele să se execute [START WITH …] CONNECT BY Dar dacă știm sigur că unul dintre ele este „adevărat” mult mai des (sau „fals” - pentru ambele se vor executa.

Dar dacă știm cu exactitate că unul dintre ele este „adevărat” mult mai des (sau „fals” – pentru ȘI-chain) — is it possible to somehow 'increase its priority' so that the second one doesn’t execute unnecessarily?

It turns out it is possible — the algorithmic approach is close to the topic of the article PostgreSQL Antipatterns: a rare record will reach the middle of the JOIN.

Let's just 'put both these conditions under CASE':

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

In this case, we did not define ELSE-value, meaning in the event of both conditions being false . De exemplu, această metodă de a evita împărțirea la zero în propoziție va returna NULL, which is interpreted as FALSE în WHERE-condition.

This example can be combined differently — according to taste and preference:

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

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

We spent two days analyzing the reasons for this 'strange' trigger activation — 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 imagine that all OLD-fields had a value NULL. What will this result in?

SELECT NULL  1 OR NULL  2;
-- NULL

From the perspective of condition execution NULL is equivalent FALSE, as mentioned above.

Soluție: use the operator IS DISTINCT FROM de la ROW-operator, comparing entire records at once:

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

Problem #2: different implementation of identical functionality

Let’s compare:

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

Why are there 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 code:

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

Oops… In fact, it turns out that if any of the first two conditions is true, the whole condition is reduced to TRUE, disregarding the inequalities. And that’s not at all what we wanted.

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 problems in #3 arose precisely because there were three conditions. But they can be replaced with one, using the mechanism coalesce ... IN:

coalesce(NEW."Document_"::text, '') IN ('', '"Set"', '"PayrollDocument"')

Așa vom NULL «prinde», și nu va fi nevoie de OR a ne complica cu paranteze.

În concluzie

Vom fixa ceea ce am realizat:

IF (
  coalesce(NEW."Document_"::text, '') IN ('', '"Pachet"', '"DocumentPeSalariu"') AND
  (
    OLD."DocumentOrganizațiaNoastră"
  , OLD."Șters"
  , OLD."Data"
  , OLD."Timp"
  , OLD."PersoanăCreată"
  ) IS DISTINCT FROM (
    NEW."DocumentOrganizațiaNoastră"
  , NEW."Șters"
  , NEW."Data"
  , NEW."Timp"
  , NEW."PersoanăCreată"
  )
) THEN ...

Și având în vedere că această funcție de declanșare poate fi aplicată doar în UPDATE-declanșatorul din cauza prezenței OLD/NEW în condiția de nivel superior, atunci această condiție poate fi transferată la ÎNCEPE IF EXISTĂ(SELECT ...) ATUNCI ... SFÂRȘIT IF; ÎNTOARCE NEW; SFÂRȘIT; ... CREAȚI TRIGGER ... CÂND cond(NEW.fld);-condiție, așa cum a fost arătat în #1…

Sursa: habr.com