🥇Mga Antipattern sa PostgreSQL: pagkalkula sa mga kondisyon sa SQL

Ang SQL dili C++, ug dili JavaScript. Busa, ang kalkulasyon sa lohikal nga mga ekspresyon mahitabo sa lain-laing mga, ug kini mao ang dili sa tanan nga sama nga butang:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Sa proseso sa pag-optimize sa PostgreSQL query execution plan mahimong arbitraryong "pag-usab" sa katumbas nga mga kondisyon, ayaw kuwentaha ang pipila niini para sa tagsa-tagsa nga mga rekord, i-relate kini sa mga kondisyon sa gi-apply nga indeks... Sa laktod, ang pinakasayon nga paagi mao ang paghunahuna nga ikaw dili makakontrol sa unsa nga pagkasunod-sunod nga ilang buhaton (ug kung kini kwentahon ba) managsama mga kondisyon.

Busa, kung gusto nimo nga madumala ang prayoridad, kinahanglan nimo nga i-struktura kini paghimo niini nga mga kondisyon nga dili patas gamit ang mga kondisyon mga ekspresyon и mga operator.

Ang datos ug pagtrabaho uban kanila mao ang sukaranan among VLSI complex, busa hinungdanon kaayo alang kanamo nga ang mga operasyon sa kanila gihimo dili lamang sa husto, apan epektibo usab. Atong tan-awon ang piho nga mga pananglitan diin ang mga sayup sa pagkalkulo sa mga ekspresyon mahimo’g, ug kung diin kini takus nga pauswagon ang ilang kahusayan.

#0: RTFM

Nagsugod pananglitan gikan sa dokumentasyon:

Kung importante ang han-ay sa ebalwasyon, mahimo kining makuha gamit ang construct CASE. Pananglitan, kini usa ka paagi aron malikayan ang pagkabahin sa zero sa usa ka sentence WHERE dili kasaligan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Luwas nga kapilian:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Ang disenyo nga gigamit niini nga paagi CASE nanalipod sa ekspresyon gikan sa pag-optimize, mao nga kini kinahanglan nga gamiton lamang kung gikinahanglan.

#1: kahimtang sa pag-trigger

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

Ang tanan daw maayo tan-awon, apan ... Walay usa nga nagsaad nga ang pagpamuhunan SELECT dili ipatuman kung ang unang kondisyon dili tinuod. Ayuhon nato ni nagsalag IF:

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

Karon atong tan-awon pag-ayo - ang tibuok lawas sa trigger function kay "naputos" sa IF. Kini nagpasabot nga walay makapugong kanato sa pagtangtang niini nga kondisyon gikan sa pamaagi sa paggamit WHEN- kondisyon:

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

Kini nga pamaagi gigarantiyahan nga makatipig sa mga kapanguhaan sa server kung sayup ang kondisyon.

#2: O/UG kadena

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

Kung dili, mahimo nimong matapos ang duha EXISTS mahimong "tinuod", apan ang duha matuman.

Apan kung nahibal-an naton nga ang usa kanila "tinuod" mas kanunay (o "bakak" - kay AND-chains) - posible ba nga sa usa ka paagi "dugangan ang iyang prayoridad" aron ang ikaduha dili mapatay pag-usab?

Kini nahimo nga posible - ang algorithmic nga pamaagi duol sa hilisgutan sa artikulo Mga Antipattern sa PostgreSQL: usa ka talagsaon nga rekord ang moabot sa tunga-tunga sa usa ka JOIN.

Ato lang "i-shove" ang duha niining mga kondisyon ubos sa CASE:

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

Niini nga kaso wala namo gipasabut ELSE-value, nga mao, kon ang duha ka mga kondisyon mga bakak CASE mobalik NULL, nga gihubad ingon FALSE в WHERE- kondisyon.

Kini nga pananglitan mahimong ikombinar sa ubang mga paagi - depende sa lami ug kolor:

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

#3: unsaon [dili] pagsulat sa mga kondisyon

Gigugol namo ang duha ka adlaw sa pag-analisar sa mga hinungdan sa "katingad-an" nga operasyon niini nga gatilyo - tan-awon nato kung ngano.

Tinubdan:

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

Problema #1: ang dili pagkakapareho wala magtahod sa NULL

Atong hunahunaon nga ang tanan OLD-Ang mga uma adunay kahulogan NULL. Unsay mahitabo?

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

Ug gikan sa punto sa panglantaw sa pagtrabaho sa mga kondisyon NULL katumbas FALSE, ingon sa gihisgotan sa ibabaw.

desisyon: gamit ang operator IS DISTINCT FROM gikan sa ROW-operator, pagtandi sa tibuok nga mga rekord sa usa ka higayon:

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

Problema #2: lainlain nga pagpatuman sa parehas nga gamit

Itandi ang:

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

Ngano nga adunay dugang nga puhunan dinhi? SELECT? Usa ka function to_regclass? Nganong lahi man?..

Ayuhon nato:

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

Problema #3: prayoridad sa mga operasyon sa bool

Atong i-format ang tinubdan:

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

Oops... Sa tinuud, nahimo nga kung ang bisan kinsa sa una nga duha nga mga kondisyon tinuod, ang tibuuk nga kondisyon mahimong TRUE, nga walay pagtagad sa mga dili managsama. Ug dili gyud kini ang among gusto.

Ayuhon nato:

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

Problema #4 (gamay): komplikado O kondisyon para sa usa ka field

Actually, naa mi problema sa No. 3 kay naay tulo ka kondisyon. Apan imbis nga sila mahimo nimong makuha pinaagi sa usa, gamit ang mekanismo coalesce ... IN:

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

Busa kami NULL "atong dakpon", ug lisud OR Dili kinahanglan nga koral nga adunay mga bracket.

Total

Atong irekord ang atong nakuha:

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

Ug kung imong hunahunaon nga kini nga trigger function magamit ra sa UPDATE-trigger tungod sa pagkaanaa OLD/NEW sa taas nga lebel nga kahimtang, nan kini nga kahimtang kasagarang ibutang sa WHEN-kondisyon, ingon sa gipakita sa #1...

Source: www.habr.com

Mga Antipattern sa PostgreSQL: Pag-evaluate sa mga Kondisyon sa SQL