PostgreSQL Antipatterns: feltételek számítása SQL-ben

Az SQL nem C++, és nem JavaScript. Ezért a logikai kifejezések kiértékelése másképp történik, és ez korántsem ugyanaz:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

A PostgreSQL lekérdezésvégrehajtási tervének optimalizálása során tetszőlegesen "áthelyezheti" az egyenértékű feltételeket, nem kiértékelve bizonyosakat egyes rekordok esetében, az alkalmazott index feltételeként kezelni... Röviden, a legegyszerűbb, ha azt feltételezzük, hogy ön nem tudja irányítani azt, hogy milyen sorrendben (és hogy egyáltalán) lesznek kiértékelve egyenrangú feltételek.

Ezért, ha mégis szeretné irányítani a prioritást, struktúrálisan ezeket a feltételeket egyenlőtlenné kell tenni feltételes kifejezésekkel és operátorokkal.

Az adatok és az azokkal végzett munka alapja a mi SBIS rendszerünkben, ezért nagyon fontos számunkra, hogy a rajtuk végzett műveletek ne csak helyesek, hanem hatékonyak is legyenek. Nézzük meg a konkrét példákat, ahol előfordulhatnak a kifejezések kiértékelésének hibái, és ahol érdemes javítani a hatékonyságukat.Kezdő

#0: RTFM

Amikor a kiértékelés sorrendje fontos, azt rögzíthetjük ) engedélyezni kell a:

CASE . Például ez a módszer nem megbízható a nullával való osztás elkerülésére:SELECT ... WHERE x > 0 AND y/x > 1.5; AHOL Biztonságosabb változat:

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

Így használt konstrukció

védelmet nyújt a kifejezés optimalizálása ellen, ezért csak szükség esetén érdemes használni.

KEZD . Például ez a módszer nem megbízható a nullával való osztás elkerülésére: HA cond(NEW.fld) ÉS EXISTS(SELECT ...) THEN

#1: условие в триггере

...

VÉGE HA; SELECT VISSZA ÚJ; VÉGE; Úgy tűnik, minden rendben van, de... Senki sem ígérte, hogy a beágyazott:

nem fog végrehajtódni az első feltétel hamissága esetén. Javítsuk ki beágyazott

HA Úgy tűnik, minden rendben van, de... Senki sem ígérte, hogy a beágyazottKEZD HA cond(NEW.fld) THENHA EXISTS(SELECT ...) THEN:

...

VÉGE HA;

#2: OR/AND-цепочка

VÉGE HA;

VISSZA ÚJ; VÉGE; Most nézzük meg alaposan - a riasztási funkció minden részét "csomagolva" találjuk Azt is jelenti, hogy semmi sem akadályozza meg, hogy ezt a feltételt eltávolítsuk a eljárásból.

WHEN ÉS-láncok) — nem lehetne valahogyan "megnövelni a prioritását", hogy a második ne fussak feleslegesen?

Kiderült, hogy lehet — az algoritmikus megközelítés közel áll a cikk témájához PostgreSQL antipattern-ek: egy ritka bejegyzés eljut a JOIN közepéig.

Egyszerűen csak "becsúsztatjuk a CASE alá" mindkét feltételt:

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

Ebben az esetben nem határoztuk meg ELSE-érték, tehát ha mindkét feltétel hamis . Például ez a módszer nem megbízható a nullával való osztás elkerülésére: visszaadja NULL, ami úgy értelmezhető, mint HAMIS be. AHOL-feltétel.

Ezt a példát másképp is kombinálni lehet — ízlés szerint:

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

#3: как [не] надо писать условия

A kiváltó okok "különös" működésének elemzésére két napot fordítottunk — nézzük meg, miért.

Forráskód:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Összes"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"BérDokumentum"')::oid)
     AND (   OLD."DokumentSzervezetünk" <> NEW."DokumentSzervezetünk"
          OR OLD."Törölt" <> NEW."Törölt"
          OR OLD."Dátum" <> NEW."Dátum"
          OR OLD."Idő" <> NEW."Idő"
          OR OLD."Létrehozó" <> NEW."Létrehozó" ) ) THEN ...

Probléma №1: az egyenlőtlenség nem veszi figyelembe a NULL-t

Tegyük fel, hogy mind OLD-mezők értéke NULL. Mi történik?

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

A feltétel teljesítése szempontjából NULL egyenértékű HAMIS, ahogyan azt korábban említettük.

Megoldás: használja a IS DISTINCT FROM tól ROW-operátort, az egész bejegyzéseket hasonlítva össze:

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

Probléma №2: különböző megvalósítások azonos funkcióval

Hasonlítsuk össze:

NEW."Dokument_" = (select '"Összes"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"BérDokumentum"')::oid)

Miért van itt felesleges beágyazás SELECT? А функция to_regclass? А по-разному-то почему?..

Javítsuk ki:

NEW."Dokument_" = '"Összes"'::regclass::oid
NEW."Dokument_" = '"BérDokumentum"'::regclass::oid

Probléma №3: a bool-operációk prioritása

Formázzuk meg a forrást:

{... IS NULL} OR
{... Összes} OR
{... BérDokumentum} AND
( {... egyenlőtlenségek} )

Ups… Valójában úgy alakult, hogy ha bármelyik első két feltétel igaz, az egész feltétel átalakul IGAZ, az egyenlőtlenségek figyelembevételével. És ez egyáltalán nem az, amit akartunk.

Javítsuk ki:

(
  {... IS NULL} OR
  {... Összes} OR
  {... BérDokumentum}
) AND
( {... egyenlőtlenségek} )

Probléma №4 (kicsi): bonyolult OR-feltétel egy mezőre

A valóságban a №3-ban felmerült problémák éppen azért merültek fel, mert három feltétel volt. De helyette egyet is használhatnánk, a coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Összes"', '"BérDokumentum"')

Így fogjuk NULL „elkapni”, és bonyolult VAGY zárójelek nélkül is megoldhatjuk.

Összesen

Rögzítjük, amit elértünk:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Csomag"', '"Bérdokumentum"') AND
  (
    OLD."DokumentAWidget"
  , OLD."Törölt"
  , OLD."Dátum"
  , OLD."Idő"
  , OLD."Létrehozó"
  ) IS DISTINCT FROM (
    NEW."DokumentAWidget"
  , NEW."Törölt"
  , NEW."Dátum"
  , NEW."Idő"
  , NEW."Létrehozó"
  )
) THEN ...

Ha figyelembe vesszük, hogy ez a trigger funkció csak UPDATE-triggerre alkalmazható a OLD/NEW a felső szintű feltételben, akkor ezt a feltételt egyáltalán ki lehet emelni egy HA cond(NEW.fld) THEN-feltételbe, ahogy az az #1-ben bemutatásra került...

Forrás: habr.com