🥇PostgreSQL Antipatterns: Qiimaynta Xaalada SQL

SQL ma aha C++, mana aha JavaScript. Sidaa darteed, qiimeynta tibaaxaha macquulka ah way ka duwan yihiin, tanina maaha wax la mid ah:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Iyadoo la wanaajinayo qorshaha fulinta weydiinta PostgreSQL si sabab la'aan ah ayuu dib u habayn karaa shuruudaha u dhigma, ha u xisaabin mid ka mid ah diiwaannada gaarka ah, tixraac xaaladda tusaha la dabaqay ... Marka la soo koobo, habka ugu fudud ayaa ah inaad u qaadato inaad adigu ma maamuli karo sida ay u kala horreeyaan (iyo in la xisaabin doono dhammaan) siman shuruudaha.

Sidaa darteed, haddii aad weli rabto inaad maamusho mudnaanta, waxaad u baahan tahay qaab dhismeed xaaladahan ka dhig kuwo aan sinnayn shardi leh tibaaxaha и hawlwadeennada.

Xogta iyo la shaqayntooda ayaa saldhig u ah Dhismahayaga VLSI, markaa aad bay muhiim noogu tahay in qalliinnada iyaga lagu sameeyo aan loo fulin si sax ah oo keliya, laakiin sidoo kale si hufan. Aynu eegno tusaalooyin la taaban karo oo khaladaad xagga qiimaynta odhaahda lagu samayn karo, iyo meelaha ay mudan tahay in kor loo qaado waxtarkooda.

#0: RTFM

Bilawga tusaale ka dukumentiyada:

Marka nidaamka qiimeyntu uu muhiim yahay, waxaa lagu hagaajin karaa dhismaha CASE. Tusaale ahaan, habkan si looga fogaado kala qaybinta eber jumlad ahaan WHERE aan la isku halayn karin:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Xulasho nabdoon:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Dhismaha la isticmaalay CASE waxay ka ilaalisaa muujinta hagaajinta, markaa waa in la isticmaalo oo kaliya marka loo baahdo.

#1: xaalada kicinta

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

Wax walba waxay u muuqdaan inay si fiican u muuqdaan, laakiin ... Qofna ma ballanqaadayo in maalgashadayaasha SELECT lama fulin doono haddii shuruudda koowaad ay been tahay. Ku hagaaji buul ku yaal IF:

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

Hadda aan si taxadar leh u eegno - dhammaan jirka oo dhan ee shaqada kicinta ayaa u soo baxday in ay "duubtay" gudaha IF. Tani waxay ka dhigan tahay in aysan jirin wax naga horjoogsanaya inaan ka saarno xaaladdan habka isticmaalka WHEN-shuruudaha:

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

Habkani wuxuu kuu ogolaanayaa inaad kaydiso agabka serverka oo leh dammaanad haddii xaaladdu been tahay.

#2: AMA/iyo silsilad

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

Haddii kale, waxaa la heli karaa in labadaba EXISTS run noqon doontaa, laakiin labadaba waa la fulin doonaa.

Laakiin haddii aan si hubaal ah u ogaano in mid ka mid ah uu yahay "run" marar badan (ama "been" - waayo AND-Silsilado) - Suurtagal ma tahay in si uun "loo kordhiyo mudnaanta" si aan kan labaad mar kale loo fulin?

Waxay soo baxday in ay suurtagal tahay - habka algorithmically wuxuu ku dhow yahay mawduuca maqaalka PostgreSQL Antipatterns: Gelitaan naadir ah waxay gaartaa bartamaha ku biir.

Aynu kaliya "ku xoorno CASE" labadan xaaladood:

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

Xaaladdan, ma aynaan qeexin ELSE-qiimaha, taas oo ah, haddii labada shuruudood ay been yihiin CASE soo laaban doona NULL, oo loo fasirtay FALSE в WHERE- shuruudaha.

Tusaalahan waxaa lagu dari karaa si kale - dhadhan iyo midab:

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

#3: sida [aan] loo qorin shuruudaha

Waxaan ku qaadanay laba maalmood falanqaynta sababaha "wax yaabka leh" ee kicinta kiciyaan - aynu aragno sababta.

Xigasho:

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

Dhibaatada #1: Sinnaan la'aanta kuma xisaabtanto NULL

Aynu ka soo qaadno in wax walba OLD-beeraha muhiim NULL. Maxaa dhici doona?

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

Iyo marka laga eego dhinaca ka shaqeynta shuruudaha NULL u dhiganta FALSE, sida kor ku xusan.

go'aanka: isticmaal hawlwadeen IS DISTINCT FROM ka ROW- hawlwadeen, isbarbar dhigaya dhammaan diiwaanada hal mar:

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

Dhibaatada lambarka 2: hirgelinta kala duwan ee isla shaqeynta

Isbarbardhig:

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

Maxay u jiraan maalgashi dheeri ah SELECT? Shaqo to_regclass? Maxay uga duwan tahay...

Aan hagaajino:

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

Dhibaatada # 3: ka horeynta bool

Aynu qaabaynno isha:

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

Oops ... Dhab ahaantii, waxay soo baxday in kiiska runta mid ka mid ah labada shuruudood ee hore, xaaladda oo dhan waxay isu beddeshaa. TRUE, iyadoo la tixgelinayo sinnaan la'aanta. Taasna gabi ahaanba ma aha waxaan rabnay.

Aan hagaajino:

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

Dhibaatada #4 (yar): adag AMA xaalad hal goob

Dhab ahaantii, waxaan dhibaato kala kulanay lambarka 3 si sax ah sababtoo ah waxaa jiray saddex xaaladood. Laakiin halkii iyaga, waxaad ku heli kartaa mid, adoo isticmaalaya habka coalesce ... IN:

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

Anaguna sidoo kale NULL "qabso", oo adag OR Uma baahnid inaad ku buuqdo qawlka.

Wadarta

Aan hagaajino waxa aan helnay:

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

Oo marka la eego in shaqada kicinta kaliya loo isticmaali karo gudaha UPDATEkiciya sababtoo ah joogitaanka OLD/NEW xaalada heerka sare, markaa xaaladan guud ahaan waa la soo saari karaa WHEN-xaalad sida ku cad #1...

Source: www.habr.com

PostgreSQL Antipatterns: Qiimaynta Xaaladda ee SQL