🥇PostgreSQL Antipatterns: kalkile kondisyon nan SQL

SQL se pa C++, epi li pa JavaScript. Se poutèt sa, kalkil la nan ekspresyon lojik rive yon fason diferan, epi sa a se pa nan tout menm bagay la:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Nan pwosesis pou optimize plan ekzekisyon rechèch PostgreSQL la ka abitrèman "ranje" kondisyon ekivalan, pa kalkile kèk nan yo pou dosye endividyèl yo, gen rapò yo ak kondisyon yo nan endèks la aplike... An brèf, fason ki pi fasil la se asime ke ou pa ka kontwole nan ki lòd yo pral (e si yo pral kalkile nan tout) egal kondisyon.

Se poutèt sa, si ou toujou vle jere priyorite, ou bezwen estrikti li fè kondisyon sa yo inegal itilize kondisyonèl ekspresyon и operatè yo.

Done ak travay avèk yo se baz la konplèks VLSI nou an, Se poutèt sa li trè enpòtan pou nou ke operasyon sou yo fèt pa sèlman kòrèkteman, men tou, avèk efikasite. Ann gade nan egzanp espesifik kote erè nan kalkil ekspresyon yo ka fè, epi ki kote li vo amelyore efikasite yo.

#0: RTFM

Kòmanse egzanp nan dokiman yo:

Lè lòd evalyasyon an enpòtan, li ka kaptire lè l sèvi avèk konstriksyon an CASE. Pou egzanp, sa a se yon fason pou evite divizyon pa zewo nan yon fraz WHERE enfidèl:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Opsyon san danje:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Konsepsyon yo itilize nan fason sa a CASE pwoteje ekspresyon an soti nan optimize, kidonk li ta dwe itilize sèlman lè sa nesesè.

#1: kondisyon deklanche

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

Tout sanble gade byen, men... Pa gen moun ki pwomèt ke envestisman an SELECT pa pral egzekite si premye kondisyon an se fo. Ann ranje li ak enbrike IF:

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

Koulye a, ann gade ak anpil atansyon - tout kò a nan fonksyon an deklanche se "vlope" nan IF. Sa vle di ke pa gen anyen ki anpeche nou retire kondisyon sa a nan pwosedi a lè l sèvi avèk WHEN-kondisyon:

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

Apwòch sa a garanti pou konsève pou resous sèvè lè kondisyon an fo.

#2: OSWA/AK chèn

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

Sinon, ou ka fini ak tou de EXISTS pral "vre", men tou de pral rive vre.

Men, si nou konnen pou asire w ke youn nan yo se "vre" pi souvan (oswa "fo" - pou AND-chèn) - èske li posib pou yon jan kanmenm "ogmante priyorite li" pou dezyèm lan pa egzekite yon lòt fwa ankò?

Li sanble ke li posib - apwòch la algoritm se tou pre sijè a nan atik la PostgreSQL Antipatterns: yon dosye ra pral rive nan mitan yon JOIN.

Ann jis "pouse" tou de kondisyon sa yo nan CASE:

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

Nan ka sa a nou pa t defini ELSE-valè, se sa ki, si tou de kondisyon yo fo CASE pral retounen NULL, ki entèprete kòm FALSE в WHERE-kondisyon.

Egzanp sa a ka konbine nan lòt fason - depann sou gou ak koulè:

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

#3: ki jan [pa] ekri kondisyon yo

Nou te pase de jou analize rezon pou operasyon "etranj" deklanche sa a - ann wè poukisa.

Sous:

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

Pwoblèm #1: inegalite pa respekte NULL

Ann imajine ke tout bagay OLD-fields te gen siyifikasyon NULL. Kisa ki pral rive?

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

Ak nan pwen de vi nan travay deyò kondisyon yo NULL ekivalan FALSE, jan mansyone pi wo a.

desizyon: sèvi ak operatè IS DISTINCT FROM soti nan ROW-operatè, konpare tout dosye nan yon fwa:

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

Pwoblèm #2: aplikasyon diferan nan menm fonksyonalite a

Ann konpare:

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

Poukisa gen plis envestisman isit la? SELECT? Yon fonksyon to_regclass? Poukisa li diferan? ..

Ann ranje:

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

Pwoblèm #3: priyorite nan operasyon bool

Ann fòma sous la:

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

Oops... An reyalite, li te tounen soti ke si nenpòt nan de premye kondisyon yo se vre, kondisyon an antye vire nan TRUE, san yo pa pran an kont inegalite. Ak sa a se pa nan tout sa nou te vle.

Ann ranje:

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

Pwoblèm #4 (ti): konplèks OSWA kondisyon pou yon jaden

Aktyèlman, nou te gen pwoblèm nan nimewo 3 jisteman paske te gen twa kondisyon. Men, olye pou yo yo ou ka jwenn pa ak yon sèl, lè l sèvi avèk mekanis la coalesce ... IN:

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

Se konsa, nou NULL "nou pral trape", ak difisil OR Pa gen okenn nesesite kloti ak parantèz.

Nan total

Ann anrejistre sa nou jwenn:

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

Men, si ou konsidere ke fonksyon deklanche sa a ka sèlman itilize nan UPDATE-deklanche akòz disponiblite OLD/NEW nan kondisyon an nivo siperyè, Lè sa a, kondisyon sa a ka jeneralman mete nan WHEN-kondisyon, jan yo montre nan #1...

Sous: www.habr.com

PostgreSQL Antipatterns: Evalyasyon kondisyon nan SQL