🥇PostgreSQL Antipatterns: ngitung kaayaan dina SQL

SQL teu C ++, sarta lain JavaScript. Ku alatan éta, itungan éksprési logis lumangsung béda, sarta ieu teu hal anu sarua:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Dina prosés ngaoptimalkeun rencana palaksanaan query PostgreSQL bisa wenang "nyusun" kaayaan sarimbag, Entong ngitung sababaraha di antarana pikeun rékaman individu, hubunganana sareng kaayaan indéks anu diterapkeun ... Pondokna, cara panggampangna nyaéta nganggap yén anjeun teu bisa ngadalikeun dina urutan naon maranéhna bakal (jeung naha maranéhna bakal diitung pisan) sarua kaayaan.

Janten, upami anjeun masih hoyong ngatur prioritas, anjeun kedah nyusun éta nyieun kaayaan ieu unequal ngagunakeun kondisional babasan и operator.

Data sareng damel sareng aranjeunna mangrupikeun dasarna kompléks VLSI kami, ku kituna penting pisan pikeun urang yén operasi dina aranjeunna dilaksanakeun henteu ngan ukur leres, tapi ogé éfisién. Hayu urang nempo conto husus dimana kasalahan dina ngitung ekspresi bisa dijieun, sarta dimana eta sia ngaronjatkeun efisiensi maranéhanana.

#0: RTFM

Ngawitan conto tina dokuméntasi:

Nalika urutan evaluasi penting, éta bisa direbut ngagunakeun construct CASE. Contona, ieu cara pikeun nyingkahan division ku nol dina kalimah WHERE teu bisa dipercaya:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Pilihan aman:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Desain dipaké ku cara kieu CASE ngajaga éksprési tina optimasi, jadi ngan kudu dipake lamun perlu.

# 1: kaayaan pemicu

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

Sagalana sigana kasampak alus, tapi ... salah No janji yén investasi SELECT moal dieksekusi lamun kaayaan kahiji palsu. Hayu urang ngalereskeun eta kalawan disarangkeun IF:

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

Ayeuna hayu urang tingali taliti - sakabeh awak fungsi pemicu "dibungkus" dina IF. Ieu ngandung harti yén euweuh nyegah urang tina nyoplokkeun kaayaan ieu tina prosedur ngagunakeun WHEN-kaayaan:

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

Pendekatan ieu dijamin ngahemat sumber daya server nalika kondisina palsu.

# 2: ATAWA / AND ranté

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

Upami teu kitu, anjeun tiasa mungkas nepi ka duanana EXISTS bakal "leres", tapi duanana bakal kaeusi.

Tapi upami urang terang pasti yén salah sahijina "leres" langkung sering (atanapi "palsu" - pikeun AND-chains) - naha mungkin waé "ningkatkeun prioritasna" supados anu kadua henteu dieksekusi deui?

Tétéla éta mungkin - pendekatan algorithmic deukeut topik artikel PostgreSQL Antipatterns: rékaman langka bakal ngahontal tengah hiji JOIN.

Hayu urang "nyorong" duanana kaayaan ieu dina CASE:

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

Dina hal ieu kami henteu nangtukeun ELSE-nilai, nyaeta, lamun duanana kaayaan anu palsu CASE bakal mulang NULL, nu diinterpretasi salaku FALSE в WHERE-kaayaan.

Conto ieu tiasa digabungkeun ku cara anu sanés - gumantung kana rasa sareng warna:

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

#3: kumaha [teu] nulis kaayaan

Kami nyéépkeun dua dinten pikeun nganalisis alesan pikeun operasi "anéh" tina pemicu ieu - hayu urang tingali naha.

Sumber:

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

Masalah #1: kateusaruaan teu ngahargaan NULL

Hayu urang ngabayangkeun éta sagalana OLD-widang miboga harti NULL. Naon anu bakal kajadian?

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

Sareng tina sudut pandang ngerjakeun kaayaan NULL sarua FALSE, sakumaha disebutkeun di luhur.

kaputusan: ngagunakeun operator IS DISTINCT FROM от ROW-operator, ngabandingkeun sakabéh rékaman sakaligus:

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

Masalah #2: palaksanaan anu béda tina fungsi anu sami

Bandingkeun:

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

Naha aya investasi tambahan di dieu? SELECT? Hiji fungsi to_regclass? Naha béda?..

Hayu urang ngalereskeun:

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

Masalah #3: prioritas operasi bool

Hayu urang format sumberna:

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

Aduh... Malah, tétéla lamun salah sahiji dua kaayaan munggaran bener, sakabéh kaayaan robah jadi TRUE, tanpa nyokot kana akun inequalities. Sareng ieu sanés pisan anu urang pikahoyong.

Hayu urang ngalereskeun:

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

Masalah # 4 (leutik): kompleks ATAWA kaayaan pikeun hiji widang

Sabenerna, urang ngalaman masalah di No 3 persis sabab aya tilu kaayaan. Tapi tinimbang aranjeunna bisa meunang ku hiji, ngagunakeun mékanisme nu coalesce ... IN:

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

Jadi urang NULL "urang gé nyekel", jeung hésé OR Teu kudu dipager ku kurung.

dina total

Hayu urang ngarekam naon anu urang ngagaduhan:

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

Tur upami Anjeun salah nganggap yén fungsi pemicu ieu ngan bisa dipaké dina UPDATE-pemicu alatan kasadiaan OLD/NEW dina kaayaan tingkat luhur, mangka kaayaan ieu umumna bisa ditempatkeun di WHEN- kaayaan, sakumaha ditémbongkeun dina # 1 ...

sumber: www.habr.com

Antipatterns PostgreSQL: Evaluasi Kaayaan dina SQL