PostgreSQL antipattern'id: SQL-is tingimuste arvutamine

SQL ei ole C++ ega JavaScript. Seetõttu toimub loogiliste avalduste arvutamine teisiti ning see — ei ole absoluutselt sama:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Küsimuse täitmisplaani optimeerimise käigus võib PostgreSQL ilma igasuguse tõendita "ümber paigutada" ekvivalentseid tingimusi, mitte arvutada mõningaid neist üksikute kirje jaoks, seostada neid rakendatud indeksi tingimusega... Ühesõnaga, kõige lihtsam on arvata, et te ei saa kasutada seda, millises järjekorras arvutatakse (ja kas neid üldse arvutatakse) võrdväärsed tingimused.

Seetõttu, kui soovite siiski juhtida prioriteeti, peate struktuurselt tegema need tingimused ebavõrdseks tingimuste avaldistega ja operaatoreid.

Andmed ja nende kasutamine on meie SBIS-i kompleksi alus , seega on meie jaoks väga oluline, et nende üle toimingud toimuksid mitte ainult korrektselt, vaid ka tõhusalt. Vaadakem konkreetseid näiteid, kus võib esineda arvutusvigu avaldustes ja kus tuleks nende efektiivsust parandada., seetõttu on meie jaoks väga tähtis, et nende üle teostatavad toimingud toimuksid mitte ainult õigesti, vaid ka tõhusalt. Vaadakem konkreetseid näiteid, kus võib esineda arvestusvigu, ja kus tasuks nende tõhusust parandada.

#0: RTFM

Algus dokumendi näide:

Kui arvutamise järjekord on oluline, saab seda fikseerida konstruktsiooniga CASE.Näiteks, see meetod, et vältida jagamist nulliga lauses WHERE ebamugav:

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

Turvaline valik:

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

Rakendatav ehitus CASE. kaitseb väljendit optimeerimise eest, seetõttu tuleb seda kasutada ainult vajadusel.

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

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

Kuna kõik näeb välja hästi, aga... Keegi ei garanteeri, et seesmine SELECT ei käivitu, kui esimene tingimus on vale. Parandame seda sisemistega IF:

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

Vaadakem nüüd tähelepanelikult — kogu tõukejõufunktsiooni keha on «mähitud» IF. Ja see tähendab, et miski ei takista meil selle tingimuse välja viimist protseduurist WHEN-tingimuse:

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

Selline lähenemine garanteerib serveri ressursside kokkuhoiu, kui tingimus on vale.

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

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

Ebamugaval juhul võib juhtuda, et mõlemad EXISTS on «tõelised», kuid mõlemad töötavad.

Aga kui me täpselt teame, et üks neist on «tõeliseks» palju sagedamini (või «vale» — jaoks AND-ahel) — kas ei võiks kuidagi «tõstva tema prioriteeti», et teine ei käivituks üleliia?

Selgub, et see on võimalik — algoritmiline lähenemine on artikli teema lähedal PostgreSQL antipatternid: haruldane kirje jõuab JOIN-i keskmesse.

Lihtsalt „paneme CASE alla“ need kaks tingimust:

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

Antud juhul me ei määratlenud ELSE-mõisted, milles mõlema tingimuse vale korral CASE. tagastab NULL, mis tõlgendatakse kui FALSE ühes WHERE-tingimus.

Seda näidet saab kombineerida ka teisiti — vastavalt maitsele:

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

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

Kulutasime kahe päeva jagu selle trigeri „veidra“ käivitamise põhjuste uurimisele — vaatame, miks.

Algallikas:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplekt"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplate"')::oid)
     AND (   OLD."DokumentNashaOrganizatsiya" <> NEW."DokumentNashaOrganizatsiya"
          OR OLD."Udalin" <> NEW."Udalin"
          OR OLD."Data" <> NEW."Data"
          OR OLD."Vremya" <> NEW."Vremya"
          OR OLD."LitsoSozdal" <> NEW."LitsoSozdal" ) ) THEN ...

Probleem nr 1: erinevus ei arvesta NULL-i

Kujuta ette, et kõik OLD-väljad olid väärtusega NULL. Mis juhtub?

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

Ja tingimuse töötlemise vaatenurgast NULL on ekvivalentne FALSE, nagu eespool mainitud.

Lahendus: kasutage operaatorit IS DISTINCT FROM alates ROW-operaatorit, võrreldes kohe terveid kirjeid:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TÕENE

Probleem nr 2: erinev sama funktsionaalsuse rakendamine

Võrdleme:

NEW."Dokument_" = (select '"Komplekt"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentPoZarplate"')::oid)

Miks siin liigselt sisemised SELECT? А функция to_regclass? А по-разному-то почему?..

Parandame:

NEW."Dokument_" = '"Komplekt"'::regclass::oid
NEW."Dokument_" = '"DokumentPoZarplate"'::regclass::oid

Probleem nr 3: bool-operatsioonide prioriteet

Formateerime lähtekoodi:

{... IS NULL} OR
{... Komplekt} OR
{... DokumentPoZarplate} AND
( {... ebaühtlustused} )

Oops… Tegelikult selgus, et kui üks kahest esimesest tingimusest on tõene, siis muutub kogu tingimus TRUE, arvestamata ebaühtlustusi. Ja see pole sugugi see, mida soovisime.

Parandame:

(
  {... IS NULL} OR
  {... Komplekt} OR
  {... DokumentPoZarplate}
) AND
( {... ebaühtlustused} )

Probleem nr 4 (väike): keeruline OR-tingimus ühe välja jaoks

Probleemid nr 3 tekkisid just seetõttu, et tingimusi oli kolm. Kuid nende asemel saab hakkama ühega, kasutades mehhanismi coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplekt"', '"DokumentPoZarplate"')

Nii me NULL «püüame», ja keerulisi OR koolonitega ei pea ehitama.

Kokku

Kinnitage, mida me saavutasime:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplekt"', '"DokumentPalgaKohta"') AND
  (
    OLD."DokumentMeieOrganisatsioon"
  , OLD."Kustutatud"
  , OLD."Kuupäev"
  , OLD."Aeg"
  , OLD."IsikLoonud"
  ) IS DISTINCT FROM (
    NEW."DokumentMeieOrganisatsioon"
  , NEW."Kustutatud"
  , NEW."Kuupäev"
  , NEW."Aeg"
  , NEW."IsikLoonud"
  )
) THEN ...

Ja kui arvestada, et seda käivitusfunktsiooni saab kasutada ainult KUUDA-käivitusest, kuna on olemas OLD/NEW ülemise taseme tingimuses, siis selle tingimuse võib üldse välja viia WHEN-tingimusse, nagu näidatud #1…

Allikas: habr.com