ProHoster > Блог > Administrado > PostgreSQL Antipatterns: Kondiĉa Taksado en SQL

PostgreSQL Antipatterns: Kondiĉa Taksado en SQL

SQL ne estas C++, nek JavaScript. Tial, la taksado de logikaj esprimoj estas malsama, kaj ĉi tio tute ne estas la sama afero:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Optimumante la ekzekutplanon de PostgreSQL-demando povas arbitre "rearanĝi" la ekvivalentajn kondiĉojn, ne kalkulu iun el ili por individuaj registroj, referu al la kondiĉo de la aplikata indekso ... Mallonge, la plej facila maniero estas supozi, ke vi ne povas administri la ordo en kiu ili estos (kaj ĉu ili estos entute kalkulitaj) egala kondiĉoj.

Tial, se vi ankoraŭ volas administri prioritaton, vi devas strukture fari ĉi tiujn kondiĉojn neegalaj kun kondicionalo esprimoj и telefonistoj.

PostgreSQL Antipatterns: Kondiĉa Taksado en SQL
Datumoj kaj labori kun ili estas la bazo de nia VLSI-komplekso, do estas tre grave por ni, ke operacioj sur ili estas faritaj ne nur ĝuste, sed ankaŭ efike. Ni rigardu konkretajn ekzemplojn, kie eraroj en esprima taksado povas esti faritaj, kaj kie indas plibonigi ilian efikecon.

#0: RTFM

Komencante ekzemplo de dokumentado:

Kiam la ordo de taksado estas grava, ĝi povas esti fiksita per la konstruo CASE. Ekzemple, tiamaniere eviti dividon per nulo en frazo WHERE nefidinda:

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

Sekura opcio:

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

La konstruo uzata CASE protektas la esprimon de optimumigo, do ĝi devus esti uzata nur kiam necese.

#1: ellasilo kondiĉo

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

Ĉio ŝajnas aspekti bone, sed... Neniu promesas, ke la investis SELECT ne estos ekzekutita se la unua kondiĉo estas falsa. Ripari ĝin per nestis IF:

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

Nun ni rigardu atente - la tuta korpo de la ellasilfunkcio montriĝis "envolvita". IF. Kaj ĉi tio signifas, ke nenio malhelpas nin forigi ĉi tiun kondiĉon de la proceduro uzanta WHEN-kondiĉoj:

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

Ĉi tiu aliro permesas ŝpari servilajn rimedojn kun garantio se la kondiĉo estas falsa.

#2: AŬ/KAJ ĉeno

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

Alie, oni povas akiri ke ambaŭ EXISTS estos vera, sed ambaŭ estos ekzekutitaj.

Sed se ni certe scias, ke unu el ili multe pli ofte estas "vera" (aŭ "malvera" - por AND-ĉenoj) - ĉu eblas iel "pliigi ĝian prioritaton" por ke la dua ne estu ekzekuta denove?

Rezultas, ke ĝi eblas - la algoritme alproksimiĝas al la temo de la artikolo PostgreSQL Antipatterns: Malofta eniro atingas la mezon de JOIN.

Ni simple "ŝovu sub KAZON" ambaŭ ĉi tiujn kondiĉojn:

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

En ĉi tiu kazo, ni ne difinis ELSE-valoro, tio estas, se ambaŭ kondiĉoj estas malveraj CASE revenos NULL, kiu estas interpretata kiel FALSE в WHERE- kondiĉoj.

Ĉi tiu ekzemplo povas esti kombinita alimaniere - por gustumi kaj kolorigi:

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

#3: kiel [ne] skribi kondiĉojn

Ni pasigis du tagojn por analizi la kialojn de la "stranga" ekfunkciigo de ĉi tiu ellasilo - ni vidu kial.

Fonto:

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

Problemo #1: Malegaleco ne respondecas pri NULL

Ni supozu, ke ĉio OLD-kampoj gravis NULL. Kio okazos?

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

Kaj el la vidpunkto pri ellaborado de la kondiĉoj NULL ekvivalento FALSE, kiel menciite supre.

decido: uzi operatoron IS DISTINCT FROM el ROW-funkciigisto, komparante tutajn rekordojn samtempe:

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

Problemo numero 2: malsama efektivigo de la sama funkcieco

Komparu:

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

Kial estas ekstraj investoj? SELECT? Funkcio to_regclass? Kial ĝi estas malsama...

Ni riparu:

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

Problemo #3: bool-preteco

Ni formatu la fonton:

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

Ho ... Fakte, montriĝis, ke en la kazo de la vero de iu el la unuaj du kondiĉoj, la tuta kondiĉo iĝas TRUE, ignorante malegalecojn. Kaj ĉi tio tute ne estas tio, kion ni volis.

Ni riparu:

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

Problemo #4 (malgranda): kompleksa AŬ kondiĉo por unu kampo

Efektive, ni havis problemojn en n-ro 3 ĝuste ĉar estis tri kondiĉoj. Sed anstataŭ ili, vi povas elteni unu, uzante la mekanismon coalesce ... IN:

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

Tiel ni NULL "kapto", kaj kompleksa OR Vi ne devas tumulti kun krampoj.

Tuta

Ni riparu tion, kion ni ricevis:

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

Kaj konsiderante ke ĉi tiu ellasilfunkcio nur povas esti uzata en UPDATEellasilon pro la ĉeesto OLD/NEW en la supra-nivela kondiĉo, tiam ĉi tiu kondiĉo povas ĝenerale esti elprenita en WHEN-kondiĉo kiel montrite en #1...

fonto: www.habr.com

Aldoni komenton