🥇Protivzorci PostgreSQL: Vrednotenje pogoja v SQL

SQL ni C++, niti ni JavaScript. Zato je vrednotenje logičnih izrazov drugačno in to sploh ni isto:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Med optimizacijo načrta izvajanja poizvedbe PostgreSQL lahko poljubno "preuredi" enakovredne pogoje, ne izračunajte nobenega od njih za posamezne zapise, glejte stanje uporabljenega indeksa ... Skratka, najlažje je domnevati, da ste ne more upravljati v kakšnem vrstnem redu bodo (in ali bodo sploh izračunane) enako pogoji.

Torej, če še vedno želite upravljati prednost, morate strukturno naredijo te pogoje neenake s pogojnikom izrazi и izvajalci.

Podatki in delo z njimi so osnova našega kompleksa VLSI, zato je za nas zelo pomembno, da operacije na njih potekajo ne le pravilno, ampak tudi učinkovito. Oglejmo si konkretne primere, kjer lahko pride do napak pri vrednotenju izrazov in kje je vredno izboljšati njihovo učinkovitost.

#0: RTFM

Začetek primer iz dokumentacije:

Ko je vrstni red vrednotenja pomemben, ga je mogoče popraviti s konstrukcijo CASE. Na primer, na ta način se izognete deljenju z ničlo v stavku WHERE nezanesljiv:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Varna možnost:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Uporabljena konstrukcija CASE ščiti izraz pred optimizacijo, zato ga je treba uporabiti le, kadar je to potrebno.

#1: stanje sprožitve

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

Zdi se, da je vse dobro, toda ... Nihče ne obljublja, da bo vloženo SELECT ne bo izvedeno, če je prvi pogoj napačen. Popravi z ugnezdeni IF:

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

Zdaj pa natančno poglejmo - izkazalo se je, da je celotno telo sprožilne funkcije "zavito". IF. In to pomeni, da nam nič ne preprečuje, da bi to stanje odstranili iz postopka z uporabo WHEN-pogoji:

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

Ta pristop vam omogoča, da prihranite vire strežnika z garancijo, če je pogoj napačen.

#2: Veriga ALI/IN

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

Sicer pa se da dobiti oboje EXISTS bo res, ampak oba bosta izvršena.

Če pa zagotovo vemo, da je eden od njih "resničen" veliko pogosteje (ali "napačen" - npr AND-chains) - ali je mogoče nekako "povečati njegovo prioriteto", tako da se drugi ne izvede še enkrat?

Izkazalo se je, da je to mogoče - algoritemski pristop je blizu temi članka Protivzorci PostgreSQL: redki vnosi dosežejo sredino JOIN.

Samo "potisnimo pod CASE" oba ta pogoja:

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

V tem primeru se nismo opredelili ELSE-vrednost, to je, če sta oba pogoja napačna CASE se bo vrnil NULL, ki se razlaga kot FALSE в WHERE- pogoji.

Ta primer lahko kombinirate na drug način - po okusu in barvi:

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

#3: kako [ne] napisati pogojev

Dva dni smo analizirali razloge za “čudno” proženje tega sprožilca – poglejmo zakaj.

Vir:

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

Problem #1: Neenakost ne upošteva NULL

Predpostavimo, da vse OLD- polja so pomembna NULL. Kaj se bo zgodilo?

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

In z vidika izdelave pogojev NULL enakovreden FALSE, kot je navedeno zgoraj.

odločitev: uporabi operator IS DISTINCT FROM od ROW-operater, ki primerja celotne zapise hkrati:

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

Problem številka 2: različna implementacija iste funkcionalnosti

Primerjaj:

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

Zakaj obstajajo dodatne naložbe SELECT? Funkcija to_regclass? Zakaj je drugače...

Popravimo:

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

Problem št. 3: prednost boola

Oblikujmo izvor:

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

Ups ... Dejansko se je izkazalo, da se v primeru resničnosti katerega koli od prvih dveh pogojev celoten pogoj spremeni v TRUE, brez upoštevanja neenakosti. In to sploh ni tisto, kar smo si želeli.

Popravimo:

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

Problem #4 (majhen): kompleksen pogoj ALI za eno polje

Pravzaprav smo imeli v številki 3 težave ravno zato, ker so bili trije pogoji. Toda namesto njih lahko dobite z enim, z uporabo mehanizma coalesce ... IN:

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

Tudi mi smo NULL "catch", in zapleteno OR Ni se vam treba ubadati z oklepaji.

Skupno

Popravimo, kar imamo:

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

In glede na to, da je to sprožilno funkcijo mogoče uporabiti samo v UPDATEsproži zaradi prisotnosti OLD/NEW v stanju višje ravni, potem je to stanje na splošno mogoče odstraniti WHEN-stanje, kot je prikazano v #1...

Vir: www.habr.com

Protivzorci PostgreSQL: Vrednotenje pogojev v SQL