ProHoster > Blog > Administrim > PostgreSQL Antipatterns: llogaritja e kushteve në SQL

PostgreSQL Antipatterns: llogaritja e kushteve në SQL

SQL nuk është C++ dhe as JavaScript. Prandaj, llogaritja e shprehjeve logjike ndodh ndryshe dhe kjo nuk është e njëjtë:

KU fncondX() DHE fncondY()

= fncondX() && fncondY()

GjatĂ« optimizimit tĂ« planit tĂ« ekzekutimit tĂ« pyetjes PostgreSQL mund tĂ« «ndĂ«rrojë» kushtet ekuivalente nĂ« mĂ«nyrĂ« arbitrare, tĂ« mos llogarisĂ« disa prej tyre pĂ«r regjistra tĂ« veçantĂ«, tĂ« lidhĂ« me kushtin e indeksit tĂ« aplikuar
 NĂ« pĂ«rgjithĂ«si, Ă«shtĂ« mĂ« e lehtĂ« tĂ« supozohet se ju paraprakisht nuk mund tĂ« menaxhoni rendin nĂ« tĂ« cilin do tĂ« llogariten (dhe a do tĂ« llogariten fare) kushtet e barabarta. Prandaj, nĂ«se dĂ«shironi tĂ« menaxhoni prioritetin, Ă«shtĂ« e nevojshme qĂ«

tĂ« bĂ«ni kĂ«to kushte jo tĂ« barabarta pĂ«rmes shprehjeve kushtore operatorĂ«ve dhe TĂ« dhĂ«nat dhe puna me to — janĂ« baza.

PostgreSQL Antipatterns: llogaritja e kushteve në SQL
e kompleksit tonë SBIS , prandaj është shumë e rëndësishme për ne që operacionet me to të kryhen jo vetëm saktësisht, por edhe efikasht. Le të shohim disa shembuj konkretë ku mund të ndodhin gabime në llogaritjen e shprehjeve, dhe ku është e nevojshme të përmirësojmë efikasitetin e tyre.Fillestar

#0: RTFM

Kur rendi i llogaritjes është i rëndësishëm, ai mund të fiksuar përmes strukturës shembuj nga dokumentacioni:

CASE . Për shembull, një mënyrë për të shmangur pjesëtimin me zero në një propozimnuk është e besueshme: KU SELECT ... KU x > 0 DHE y/x > 1.5;

Një variant i sigurt:

SELECT ... KU RASTI KUR x > 0 ATËHERË y/x > 1.5 PËRNDYSH E VËRTETË FALSE; END;

Struktura e aplikuar kështu

mbron shprehjen nga optimizimi, prandaj duhet ta pĂ«rdorni vetĂ«m kur Ă«shtĂ« e nevojshme. . PĂ«r shembull, njĂ« mĂ«nyrĂ« pĂ«r tĂ« shmangur pjesĂ«timin me zero nĂ« njĂ« propozim FILLIM NËSE cond(NEW.fld) DHE EKZISTON(SELECT ...) ATËHERË ... FUND NËSE; KTHIM NEW; FUND;

#1: ŃƒŃĐ»ĐŸĐČОД ĐČ Ń‚Ń€ĐžĐłĐłĐ”Ń€Đ”

Të gjitha duken mirë, por
 Askush nuk premton se

nĂ« nivelin e parĂ« kushti do tĂ« ekzekutohet vetĂ«m kur kushti i parĂ« Ă«shtĂ« i pavĂ«rtetĂ«. Ta pĂ«rmirĂ«sojmĂ« pĂ«rmes SELECT IF tĂ« thelluara FILLIM NËSE cond(NEW.fld) ATËHERË NËSE EKZISTON(SELECT ...) ATËHERË ... FUND NËSE; FUND NËSE; KTHIM NEW; FUND; Tani le tĂ« shohim me kujdes — e gjithĂ« trupi i funksionit tĂ« trigger-it u ‘ndĂ«rmjetĂ«sua’ nĂ«:

. Dhe kjo do të thotë se asgjë nuk na pengon ta nxjerrim këtë kusht nga procedura përmes

NË Tani le tĂ« shohim me kujdes — e gjithĂ« trupi i funksionit tĂ« trigger-it u ‘ndĂ«rmjetĂ«sua’ nĂ«-kushtit FILLIM NËSE EKZISTON(SELECT ...) ATËHERË ... FUND NËSE; KTHIM NEW; FUND; ... KRIJO TRIGGER ... NËSE cond(NEW.fld);Ky qasje garanton njĂ« kursim tĂ« garantuar tĂ« burimeve tĂ« serverit nĂ« rast tĂ« pavĂ«rtetĂ«sisĂ« sĂ« kushteve.:

SELECT ... KU EKZISTON(... A) OSE EKZISTON(... B)

Në rastin e pakëndshëm mund të ndodhë që të dy

#2: OR/AND-Ń†Đ”ĐżĐŸŃ‡ĐșĐ°

do të jenë "të vërteta", por

tĂ« dy dhe do tĂ« ekzekutohen EKZISTON Por nĂ«se ne e dimĂ« saktĂ«sisht se njĂ« prej tyre Ă«shtĂ« "tĂ« vĂ«rtetĂ«" shumĂ« mĂ« shpesh (ose "tĂ« pavĂ«rtetat" — pĂ«r tĂ« dy.

tĂ« dy do tĂ« zbatohen DHE-chains) — can't we somehow 'increase its priority' so that the second doesn't execute unnecessarily?

It turns out we can — the algorithmic approach is close to the topic of the article. PostgreSQL Antipatterns: a rare entry might reach the middle of the JOIN..

Let's just 'put both of these 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 case both conditions are false. . Për shembull, një mënyrë për të shmangur pjesëtimin me zero në një propozim will return NULL, which is interpreted as FALSE në KU-condition.

This example can also 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 the 'strange' trigger firing — let’s see why.

Source:

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

Problem №1: the inequality doesn’t account for NULL

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

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

And from the perspective of condition processing, NULL is equivalent FALSE, as mentioned earlier.

Zgjidhja: use the operator IS DISTINCT FROM nga ROW-operator, comparing entire 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('"DocumentBySalary"')::oid)

Why are there unnecessary nested SELECT? А Ń„ŃƒĐœĐșцоя to_regclass? А ĐżĐŸ-Ń€Đ°Đ·ĐœĐŸĐŒŃƒ-Ń‚ĐŸ ĐżĐŸŃ‡Đ”ĐŒŃƒ?..

Let's fix it:

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

Problem №3: the precedence of bool operations

Let’s format the source:

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

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

Let's fix it:

(
  {... IS NULL} OR
  {... Set} OR
  {... DocumentBySalary}
) 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 instead, we could manage with one using the mechanism coalesce ... IN:

coalesce(NEW."Dokumenti_"::text, '') IN ('', '"Kombinim"', '"DokumentiPĂ«rPaguara"')

Kështu ne edhe NULL «do të kapim», dhe të komplikuar OSE nuk do të duhet të sillemi me paranteza.

Përveç kësaj

Le të konfirmojmë atë që kemi arritur:

NĂ«se (
  coalesce(NEW."Dokumenti_"::text, '') IN ('', '"Kombinim"', '"DokumentiPĂ«rPaguara"') DHE
  (
    OLD."DokumentiOrganizataJonë"
  , OLD."Fshirë"
  , OLD."Data"
  , OLD."Koha"
  , OLD."Krijuesi"
  ) ËSHTË E DALLUAR NGA (
    NEW."DokumentiOrganizataJonë"
  , NEW."Fshirë"
  , NEW."Data"
  , NEW."Koha"
  , NEW."Krijuesi"
  )
) ATËHERË ...

Dhe nĂ«se e kemi parasysh qĂ« kjo funksion trigger mund tĂ« aplikojĂ« vetĂ«m nĂ« UPDATE-trigger pĂ«r shkak tĂ« pranisĂ« sĂ« OLD/NEW nĂ« kushtin mĂ« tĂ« lartĂ«, atĂ«herĂ« ky kusht mund tĂ« nxirret nĂ« FILLIM NËSE EKZISTON(SELECT ...) ATËHERË ... FUND NËSE; KTHIM NEW; FUND; ... KRIJO TRIGGER ... NËSE cond(NEW.fld);-kusht, siç u tregua nĂ« #1


Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster