🥇PostgreSQL-i antimustrid: seisukorra hindamine SQL-is

SQL ei ole C++ ega ka JavaScript. Seetõttu on loogiliste avaldiste hindamine erinev ja see pole üldse sama asi:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL-päringu täitmisplaani optimeerimisel võib samaväärseid tingimusi meelevaldselt "ümber korraldada"., ärge arvutage neist ühtegi üksikute kirjete jaoks, vaadake rakendatud indeksi tingimust ... Ühesõnaga, kõige lihtsam on eeldada, et ei saa hakkama nende järjekord (ja kas neid üldse arvutatakse) võrdne tingimused.

Seega, kui soovite ikkagi prioriteete hallata, peate seda struktuurselt muuta need tingimused ebavõrdseks tingimuslikuga väljendid и operaatorid.

Andmed ja nendega töötamine on aluseks meie VLSI kompleksist, seega on meie jaoks väga oluline, et nendega tehtavad toimingud ei toimuks mitte ainult õigesti, vaid ka tõhusalt. Vaatame konkreetseid näiteid, kus võib avaldiste arvutamisel vigu teha ja kus tasub nende efektiivsust parandada.

# 0: RTFM

Käivitamine näide dokumentatsioonist:

Kui hindamise järjekord on oluline, saab selle konstruktsiooniga fikseerida CASE. Näiteks nii, et vältida lauses nulliga jagamist WHERE ebausaldusväärne:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Ohutu valik:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Kasutatud konstruktsioon CASE kaitseb avaldist optimeerimise eest, seega tuleks seda kasutada ainult vajaduse korral.

#1: päästiku tingimus

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

Kõik tundub hea olevat, aga... Keegi ei luba, et investeeris SELECT ei täideta, kui esimene tingimus on vale. Parandage see koos pesastatud IF:

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

Vaatame nüüd hoolega – kogu päästikufunktsiooni korpus osutus sisse "mähituks". IF. Ja see tähendab, et miski ei takista meil seda tingimust protseduurist eemaldamast WHEN- tingimused:

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

Selline lähenemine võimaldab säästa serveriressursse garantiiga, kui tingimus on vale.

#2: VÕI/JA kett

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

Muidu võib saada, et mõlemad EXISTS see on tõsi, kuid mõlemad hukatakse.

Aga kui me teame kindlalt, et üks neist on "tõene" palju sagedamini (või "vale" - jaoks AND-ahelad) - kas on võimalik kuidagi "selle prioriteeti tõsta", et teist uuesti ei täidetaks?

Selgub, et see on võimalik – algoritmiline lähenemine on artikli teemale lähedane PostgreSQL-i antimustrid: haruldane kirje jõuab JOIN-i keskpaigani.

Lükkame need mõlemad tingimused lihtsalt CASE alla:

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

Sel juhul me ei määratlenud ELSE-väärtus, st kui mõlemad tingimused on valed CASE tuleb tagasi NULL, mida tõlgendatakse kui FALSE в WHERE- tingimused.

Seda näidet saab kombineerida muul viisil - maitsta ja värvida:

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

#3: kuidas [mitte] kirjutada tingimusi

Veetsime kaks päeva selle päästiku "kummalise" käivitamise põhjuste analüüsimiseks - vaatame, miks.

Allikas:

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

Probleem nr 1: ebavõrdsus ei arvesta NULL-i

Oletame, et kõik OLD-väljad olid olulised NULL. Mis juhtub?

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

Ja seda tingimuste väljatöötamise seisukohalt NULL samaväärne FALSE, nagu eelnevalt mainitud.

otsus: kasutage operaatorit IS DISTINCT FROM pärit ROW-operaator, mis võrdleb korraga terveid kirjeid:

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

Probleem number 2: sama funktsiooni erinev rakendamine

Võrrelda:

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

Miks on lisainvesteeringuid SELECT? Funktsioon to_regclass? Miks on teisiti...

Parandame:

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

Probleem nr 3: tõeväärtus

Vormindame allika:

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

Oeh ... Tegelikult selgus, et kahe esimese tingimuse tõesuse korral muutub kogu tingimus TRUE, arvestamata ebavõrdsust. Ja see pole üldse see, mida me tahtsime.

Parandame:

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

Ülesanne nr 4 (väike): keeruline VÕI tingimus ühe välja jaoks

Tegelikult oli meil nr 3 probleeme just seetõttu, et seal oli kolm tingimust. Kuid nende asemel saate mehhanismi kasutades hakkama ka ühega coalesce ... IN:

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

Nii ka meie NULL "saak" ja keeruline OR Sulgudega ei pea pabistama.

Kogusummas

Parandame selle, mis meil on:

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

Ja arvestades, et seda päästikufunktsiooni saab kasutada ainult UPDATEkohaloleku tõttu vallandada OLD/NEW ülemise taseme seisundis, siis saab selle tingimuse üldiselt välja võtta WHEN- seisukord nagu näidatud #1...

Allikas: www.habr.com

PostgreSQL-i antimustrid: seisukorra hindamine SQL-is