🥇Mga Antipattern ng PostgreSQL: Pagsusuri ng Kondisyon sa SQL

Ang SQL ay hindi C++, at hindi rin ito JavaScript. Samakatuwid, ang pagsusuri ng mga lohikal na expression ay naiiba, at hindi ito ang parehong bagay:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Habang ino-optimize ang execution plan ng isang PostgreSQL query maaaring arbitraryong "muling ayusin" ang mga katumbas na kondisyon, huwag kalkulahin ang alinman sa mga ito para sa mga indibidwal na rekord, sumangguni sa kondisyon ng inilapat na index ... Sa madaling salita, ang pinakamadaling paraan ay ipagpalagay na ikaw hindi kayang pamahalaan ang pagkakasunud-sunod kung saan sila (at kung sila ay kakalkulahin sa lahat) pantay mga kondisyon.

Samakatuwid, kung gusto mo pa ring pamahalaan ang priyoridad, kailangan mong structurally gawing hindi pantay ang mga kundisyong ito na may kondisyon ekspresyon и mga operator.

Data at pakikipagtulungan sa kanila ang batayan ng aming VLSI complex, kaya napakahalaga para sa amin na ang mga operasyon sa mga ito ay isinasagawa hindi lamang nang tama, ngunit mahusay din. Tingnan natin ang mga konkretong halimbawa kung saan maaaring gumawa ng mga pagkakamali sa pagsusuri ng expression, at kung saan ito ay nagkakahalaga ng pagpapabuti ng kanilang kahusayan.

#0: RTFM

Nagsisimula halimbawa mula sa dokumentasyon:

Kapag ang pagkakasunud-sunod ng pagsusuri ay mahalaga, maaari itong ayusin sa pamamagitan ng konstruksyon CASE. Halimbawa, sa ganitong paraan upang maiwasan ang paghahati ng zero sa isang pangungusap WHERE hindi mapagkakatiwalaan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Ligtas na opsyon:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Ang ginamit na konstruksiyon CASE pinoprotektahan ang expression mula sa pag-optimize, kaya dapat lang itong gamitin kung kinakailangan.

#1: kundisyon ng trigger

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

Mukhang maganda ang lahat, ngunit... Walang nangangako na ang namuhunan SELECT ay hindi isasagawa kung mali ang unang kundisyon. Ayusin ito sa nakapugad IF:

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

Ngayon tingnan nating mabuti - ang buong katawan ng pag-andar ng pag-trigger ay naging "nakabalot" sa IF. At nangangahulugan ito na walang pumipigil sa amin na alisin ang kundisyong ito mula sa pamamaraang ginagamit WHEN-kondisyon:

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

Binibigyang-daan ka ng diskarteng ito na i-save ang mga mapagkukunan ng server na may garantiya kung mali ang kundisyon.

#2: O/AT chain

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

Kung hindi, maaari itong makuha na pareho EXISTS ay magiging totoo, ngunit kapwa mapapatupad.

Ngunit kung alam nating sigurado na ang isa sa kanila ay "totoo" nang mas madalas (o "mali" - para sa AND-chains) - posible bang kahit papaano ay "dagdagan ang priyoridad nito" upang ang pangalawa ay hindi maisakatuparan muli?

Ito ay lumalabas na posible - ang algorithm na diskarte ay malapit sa paksa ng artikulo Mga PostgreSQL Antipattern: Ang bihirang tala ay aabot sa gitna ng isang SUMALI.

"I-shove under CASE" lang natin ang parehong kundisyong ito:

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

Sa kasong ito, hindi namin tinukoy ELSE-value, iyon ay, kung ang parehong mga kundisyon ay mali CASE babalik NULL, na binibigyang kahulugan bilang FALSE в WHERE- kundisyon.

Ang halimbawang ito ay maaaring pagsamahin sa ibang paraan - sa panlasa at kulay:

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

#3: paano [hindi] magsulat ng mga kundisyon

Gumugol kami ng dalawang araw sa pagsusuri sa mga dahilan para sa "kakaibang" pag-trigger ng trigger na ito - tingnan natin kung bakit.

Pinagmulan:

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 hindi pagkakapantay-pantay ay hindi isinasaalang-alang ang NULL

Ipagpalagay natin na ang lahat OLD-ang mga patlang ay mahalaga NULL. Ano ang mangyayari?

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

At mula sa punto ng view ng pagtatrabaho sa mga kondisyon NULL katumbas FALSE, gaya ng nabanggit sa itaas.

desisyon: gumamit ng operator IS DISTINCT FROM mula sa ROW-operator, naghahambing ng buong mga tala nang sabay-sabay:

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

Problema numero 2: iba't ibang pagpapatupad ng parehong pag-andar

Ihambing natin ang:

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

Bakit may extra investments SELECT? Isang function to_regclass? Bakit iba...

Ayusin natin:

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

Problema #3: bool precedence

I-format natin ang pinagmulan:

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

Oops ... Sa katunayan, lumabas na sa kaso ng katotohanan ng alinman sa unang dalawang kundisyon, ang buong kundisyon ay nagiging TRUE, binabalewala ang mga hindi pagkakapantay-pantay. At hindi ito ang gusto namin.

Ayusin natin:

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

Problema #4 (maliit): kumplikado O kundisyon para sa isang field

Actually, nagkaproblema kami sa No. 3 precisely because there were three conditions. Ngunit sa halip na sa kanila, maaari kang makakuha ng isa, gamit ang mekanismo coalesce ... IN:

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

Ganun din tayo NULL "huli", at kumplikado OR Hindi mo kailangang mag-abala sa mga panaklong.

Sa kabuuan

Ayusin natin ang nakuha natin:

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

At dahil magagamit lang ang trigger function na ito sa UPDATEtrigger dahil sa presensya OLD/NEW sa mataas na antas na kondisyon, kung gayon ang kundisyong ito ay karaniwang maaaring alisin WHEN-kondisyon tulad ng ipinapakita sa #1...

Pinagmulan: www.habr.com

Mga Antipattern ng PostgreSQL: Pagsusuri ng Kondisyon sa SQL