🥇PostgreSQL Antipatterns: Procjena uvjeta u SQL-u

SQL nije C++, niti je JavaScript. Dakle, vrednovanje logičkih izraza je drugačije, a to uopće nije ista stvar:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Tijekom optimizacije plana izvršenja PostgreSQL upita može proizvoljno "preurediti" ekvivalentne uvjete, nemojte računati nijedan od njih za pojedinačne zapise, pogledajte stanje primijenjenog indeksa ... Ukratko, najlakše je pretpostaviti da ste ne može upravljati redoslijed kojim će biti (i hoće li se uopće izračunavati) jednak Uvjeti.

Stoga, ako i dalje želite upravljati prioritetom, morate strukturalno čine te uvjete nejednakima s kondicionalom izraze и operatori.

Podaci i rad s njima su osnova našeg VLSI kompleksa, stoga nam je vrlo važno da se operacije na njima izvode ne samo ispravno, već i učinkovito. Pogledajmo konkretne primjere gdje se mogu napraviti pogreške u izračunu izraza i gdje je vrijedno poboljšati njihovu učinkovitost.

#0: RTFM

Polazeći primjer iz dokumentacije:

Kada je redoslijed evaluacije važan, može se popraviti konstrukcijom CASE. Na primjer, na ovaj način izbjeći dijeljenje s nulom u rečenici WHERE nepouzdan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Sigurna opcija:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Korištena konstrukcija CASE štiti izraz od optimizacije, pa ga treba koristiti samo kada je to potrebno.

#1: stanje okidača

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

Čini se da sve izgleda dobro, ali... Nitko ne obećava uloženo SELECT neće se izvršiti ako je prvi uvjet lažan. Popravite to pomoću ugniježđeni IF:

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

Pogledajmo sada pažljivo - pokazalo se da je cijelo tijelo funkcije okidača "umotano". IF. A to znači da nas ništa ne sprječava da ovo stanje uklonimo iz postupka pomoću WHEN-Uvjeti:

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

Ovaj pristup vam omogućuje da uštedite resurse poslužitelja uz jamstvo ako je uvjet lažan.

#2: ILI/I lanac

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

Inače, može se dobiti da oboje EXISTS bit će istina, ali oba će biti pogubljena.

Ali ako sa sigurnošću znamo da je jedan od njih mnogo češće "istinit" (ili "lažan" - npr AND-chains) - je li moguće nekako "povećati njegov prioritet" tako da se drugi ne izvršava još jednom?

Ispada da je moguće - algoritamski pristup je blizak temi članka PostgreSQL Antipatterns: Rijedak unos doseže sredinu JOIN-a.

Hajdemo samo "gurnuti pod CASE" oba ova uvjeta:

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

U ovom slučaju nismo definirali ELSE-vrijednost, odnosno ako su oba uvjeta lažna CASE će se vratiti NULL, što se tumači kao FALSE в WHERE- Uvjeti.

Ovaj primjer se može kombinirati na drugi način - po ukusu i boji:

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

#3: kako [ne] pisati uvjete

Proveli smo dva dana analizirajući razloge "čudnog" aktiviranja ovog okidača - da vidimo zašto.

Izvor:

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: Nejednakost ne uzima u obzir NULL

Pretpostavimo da sve OLD-polja su važna NULL. Što će se dogoditi?

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

I sa stajališta razrade uvjeta NULL ekvivalent FALSE, kao što je gore navedeno.

odluka: koristi operator IS DISTINCT FROM iz ROW-operator, koji uspoređuje cijele zapise odjednom:

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

Problem broj 2: različita implementacija iste funkcionalnosti

Idemo usporediti:

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

Zašto postoje dodatna ulaganja SELECT? Funkcija to_regclass? Zašto je drugačije...

Popravimo:

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

Problem #3: Bool prioritet

Formatirajmo izvor:

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

Ups... Zapravo, pokazalo se da se u slučaju istinitosti bilo kojeg od prva dva uvjeta cijeli uvjet pretvara u TRUE, zanemarujući nejednakosti. A ovo uopće nije ono što smo htjeli.

Popravimo:

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

Problem #4 (mali): složeni ILI uvjet za jedno polje

Zapravo, u 3. smo imali problema upravo zato što su postojala tri uvjeta. Ali umjesto njih, možete se snaći s jednim, koristeći mehanizam coalesce ... IN:

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

I mi smo NULL "catch", i složen OR Ne morate se mučiti sa zagradama.

Ukupno

Popravimo ovo što imamo:

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

A s obzirom da se ova funkcija okidača može koristiti samo u UPDATEokidač zbog prisutnosti OLD/NEW u stanju više razine, tada se ovo stanje općenito može izvaditi WHEN-stanje kao što je prikazano u #1...

Izvor: www.habr.com

PostgreSQL Antipatterns: Procjena uvjeta u SQL-u