PostgreSQL Antipatterns: SQLis tingimuste arvutamine

SQL ei ole C++ ega JavaScript. Seetõttu toimub loogiliste väljendite arvutamine teistmoodi, ja see pole sugugi sama asi:

KUS fncondX() JA fncondY()

= fncondX() && fncondY()

Küsimuse täitmisplaani optimeerimise protsessis võib PostgreSQL mugavalt „üles tõsta“ võrdväärseid tingimusi, mitte arvutada mõned neist eraldi kirjed, siduda kasutatava indeksi tingimustega... Ühesõnaga, lihtsaim on arvestada, et te ei saa jõuda kontrollima seda, millises järjekorras arvutatakse (ja kas nad arvutatakse üldse) võrdset tingimust.

Seetõttu, kui soovid siiski juhtida prioriteeti, tuleb struktuurselt muuta need tingimused ebaühtlaseks tingimuslike väljendite abil. ja Kuberneteses, mis võtavad täieliku kontrolli andmesalvestuslahenduste, näiteks Ceph, EdgeFS, Minio, Cassandra, CockroachDB, deploymente, haldamist ja automaatset taastamist..

Andmed ja töö nendega on meie kompleksse SBiS , seetõttu on meie jaoks väga oluline, et toimingud nendega toimuksid mitte ainult korrektselt, vaid ka efektiivselt. Vaatame konkreetseid näiteid, kus võib esineda väljendite arvutamisvigu ja kus oleks mõistlik parandada nende efektiivsust.Algus

#0: RTFM

Kui arvutamise järjekord on oluline, saab seda fikseerida konstruktsiooni CSIInlineVolume:

CASE abil. Näiteks, niisugune viis nulliga jagamise vältimiseks lausespole usaldusväärne: KUS SELECT ... KUS x > 0 JA y/x > 1.5;

Ohutu variant:

SELECT ... KUS CASE KUI x > 0 SIIS y/x > 1.5 MUUL juhul vale LÕPP;

Nii rakendatud konstruktsioon

kaitseb väljendit optimeerimise eest, seetõttu tuleks seda kasutada vaid vajadusel. abil. Näiteks, niisugune viis nulliga jagamise vältimiseks lauses ALGUS IF cond(NEW.fld) JA ON EXITS(SELECT ...) SIIS ... LÕPP IF; RETURN NEW; LÕPP;

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

Tundub, et kõik on hästi, kuid... Keegi ei lubanud, et sisemine

ei toimi, kui esimene tingimus on vale. Parandame seda SELECT sisemiste IF-idega ALGUS IF cond(NEW.fld) SIIS IF ON EXITS(SELECT ...) SIIS ... LÕPP IF; LÕPP IF; RETURN NEW; LÕPP;:

Nüüd vaatame hoolikalt — kogu trigerfonks on „ümber pakendatud”

. Ja see tähendab, et miski ei takista meil seda tingimust protseduurist välja viia põhjal ALGUS IF cond(NEW.fld) SIIS IF ON EXITS(SELECT ...) SIIS ... LÕPP IF; LÕPP IF; RETURN NEW; LÕPP;WHEN -tingimusALGUS IF ON EXITS(SELECT ...) SIIS ... LÕPP IF; RETURN NEW; LÕPP; ... Loo TRIGGER ... KUI cond(NEW.fld);:

Selline lähenemine võimaldab tagada serveri ressursside kokkuhoidu, kui tingimus on vale.

SELECT ... KUS ON EXIST(A) VÕI ON EXIST(B)

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

Ebameeldival juhul võib juhtuda, et mõlemad

on „tõesed“, aga EXISTS mõlemad käivitatakse. Kuid kui me teame kindlalt, et üks neist on „tõene“ palju sagedamini (või „vale“ -.

ahelas) — kas ei saa kuidagi „tõsta tema prioriteeti“, et teine ​​ei käivituks liigselt? JA-ahelites) — kas pole võimalik kuidagi «tema prioriteeti tõsta», et teist korda ei täidetaks?

Selgub, et see on võimalik – algoritmiline lähenemine on artikli teema lähedane PostgreSQL Antipatterns: harva esinev kirje jõuab JOINi keskosas.

Laseme lihtsalt „paneme CASE alla“ need kaks tingimust:

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

Käesoleval juhul ei ole me määratlenud ELSE-väärtusena, st juhul, kui mõlemad tingimused on vale abil. Näiteks, niisugune viis nulliga jagamise vältimiseks lauses tagastab NULL, mida tõlgendatakse kui FALSE ja KUS-tingimus.

Seda näidet saab kombineerida ka teistmoodi – maitse ja värvi järgi:

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

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

Pöördusime sellele „veidra“ käivituse põhjuste analüüsile kaks päeva – vaatame, miks.

Algne kood:

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."Uldar"  NEW."Uldar"
          OR OLD."Date"  NEW."Date"
          OR OLD."Time"  NEW."Time"
          OR OLD."LitsoSozdal"  NEW."LitsoSozdal" ) ) THEN ...

Probleem nr 1: eriolu ei arvesta NULL

Kujutage ette, et kõik OLD-väljad olid väärtusega NULL. Mida saame?

SELECT NULL  1 OR NULL  2;
-- NULL

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

Lahendus: kasutage operaatorit IS DISTINCT FROM alates ROW-operaator, võrreldes kohe tervikregistreid:

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

Probleem nr 2: sama funktsionaalsuse erinev rakendamine

Võrdleme:

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

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

Parandame:

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

Probleem nr 3: bool-operatsioonide prioriteet

Formaatime algse koodi:

{... IS NULL} OR
{... Komplekt} OR
{... DokumentPoZarplate} AND
( {... mittenarvud} )

Ups… Tegelikult selgus, et juhul, kui kehtib ükskõik milline kahest esimesest tingimusest, muutub kogu tingimus TÕSI, arvestamata mittenarvusi. See ei ole absoluutselt see, mida me soovisime.

Parandame:

(
  {... IS NULL} OR
  {... Komplekt} OR
  {... DokumentPoZarplate}
) AND
( {... mittenarvud} )

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

Olles, et probleem nr 3 ilmnes just selle tõttu, et tingimusi oli kolm. Kuid nende asemel võiks hakkama ainult ühega, kasutades mehhanismi coalesce ... IN:

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

Nii saame ka NULL „püüda”, ja keerulisi OR ilingust ei pea olema.

Kokkuvõttes

Kinnitan, mida oleme saavutanud:

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

Ja kui arvestada, et seda triggereerimist saab kasutada ainult UPDATE-triggeerimises sellise olemasolu tõttu OLD/NEW peamise tingimuse sees, siis selle tingimuse saab üldse välja tuua -tingimus-tingimusena, nagu näidatud #1…

Allikas: habr.com