🥇PostgreSQL antipatterns: sąlygų skaičiavimas SQL

SQL nėra C++ ir ne JavaScript. Todėl loginių išraiškų apskaičiavimas vyksta skirtingai, ir tai visai nėra tas pats dalykas:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Optimizuojamas PostgreSQL užklausos vykdymo planas gali savavališkai „pertvarkyti“ lygiavertes sąlygas, kai kurių neskaičiuokite atskiriems įrašams, susiekite su taikomo indekso sąlygomis... Trumpai tariant, lengviausia manyti, kad jūs negali kontroliuoti kokia tvarka jie bus (ir ar jie apskritai bus skaičiuojami) lygus sąlygos.

Todėl, jei vis tiek norite valdyti prioritetą, turite jį struktūrizuoti padaryti šias sąlygas nevienodas naudojant sąlyginius išraiškos и operatoriai.

Duomenys ir darbas su jais yra pagrindas mūsų VLSI kompleksas, todėl mums labai svarbu, kad operacijos su jais būtų atliekamos ne tik teisingai, bet ir efektyviai. Pažvelkime į konkrečius pavyzdžius, kur gali būti klaidų skaičiuojant išraiškas, ir kur verta pagerinti jų efektyvumą.

#0: RTFM

Pradedama pavyzdys iš dokumentacijos:

Kai vertinimo tvarka yra svarbi, ją galima užfiksuoti naudojant konstrukciją CASE. Pavyzdžiui, tai yra būdas išvengti sakinio padalijimo iš nulio WHERE nepatikimas:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Saugus variantas:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Tokiu būdu naudojamas dizainas CASE apsaugo išraišką nuo optimizavimo, todėl ji turėtų būti naudojama tik tada, kai reikia.

#1: paleidimo sąlyga

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

Atrodo, kad viskas gerai, bet... To investicijos niekas nežada SELECT nebus vykdomas, jei pirmoji sąlyga yra klaidinga. Pataisykime su įdėtas IF:

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

Dabar pažiūrėkime atidžiai - visas paleidimo funkcijos korpusas yra „apvyniotas“. IF. Tai reiškia, kad niekas netrukdo mums pašalinti šios sąlygos iš procedūros naudojant WHEN-sąlygos:

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

Šis metodas garantuoja serverio išteklių taupymą, kai sąlyga klaidinga.

#2: ARBA/IR grandinė

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

Priešingu atveju galite susidurti su abiem EXISTS bus „tiesa“, bet abu išsipildys.

Bet jei tikrai žinome, kad vienas iš jų yra „tiesa“ daug dažniau (arba „klaidinga“ - už AND-grandinės) - ar galima kažkaip „padidinti jo prioritetą“, kad antrasis nebūtų vykdomas dar kartą?

Pasirodo, tai įmanoma – algoritminis požiūris artimas straipsnio temai PostgreSQL antipatterns: retas įrašas pasieks JOIN vidurį.

Tiesiog „įstumkime“ abi šias sąlygas į CASE:

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

Šiuo atveju mes neapibrėžėme ELSE-reikšmė, tai yra, jei abi sąlygos yra klaidingos CASE grįš NULL, kuris interpretuojamas kaip FALSE в WHERE-sąlygos.

Šis pavyzdys gali būti derinamas kitais būdais - priklausomai nuo skonio ir spalvos:

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

#3: kaip [ne] rašyti sąlygas

Mes praleidome dvi dienas analizuodami „keistos“ šio trigerio veikimo priežastis - pažiūrėkime, kodėl.

Šaltinis:

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

1 problema: nelygybė nepaiso NULL

Įsivaizduokime, kad viskas OLD-laukai turėjo prasmę NULL. Kas nutiks?

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

Ir sąlygų sudarymo požiūriu NULL lygiavertis FALSE, kaip paminėta aukščiau.

sprendimas: naudokite operatorių IS DISTINCT FROM nuo ROW-operatorius, lyginantis visus įrašus vienu metu:

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

2 problema: skirtingi tos pačios funkcijos įgyvendinimai

Palyginti:

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

Kodėl čia papildomos investicijos? SELECT? Funkcija to_regclass? Kodėl kitaip?..

Pataisykime:

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

3 problema: bool operacijų prioritetas

Suformatuokime šaltinį:

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

Oi... Tiesą sakant, paaiškėjo, kad jei kuri nors iš pirmųjų dviejų sąlygų yra teisinga, visa sąlyga virsta TRUE, neatsižvelgiant į nelygybes. Ir tai visai ne tai, ko norėjome.

Pataisykime:

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

4 problema (maža): sudėtinga ARBA sąlyga vienam laukui

Tiesą sakant, mes turėjome problemų Nr. 3 būtent dėl to, kad buvo trys sąlygos. Tačiau vietoj jų galite apsieiti su vienu, naudodami mechanizmą coalesce ... IN:

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

Taigi mes NULL „pagausime“, ir sunku OR Nereikia tvoros su kronšteinais.

Iš viso

Įrašykime, ką gavome:

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

Ir jei manote, kad šią paleidimo funkciją galima naudoti tik UPDATE- suaktyvinti dėl prieinamumo OLD/NEW aukštesnio lygio būsenoje, tada ši sąlyga paprastai gali būti įtraukta WHEN-Būklė, kaip parodyta Nr.1...

Šaltinis: www.habr.com

PostgreSQL antipatterns: sąlygų vertinimas SQL