🥇PostgreSQL Antipatterns: Evaluasi Kondisi ing SQL

SQL dudu C++, uga JavaScript. Mulane, evaluasi ekspresi logis beda, lan iki ora padha:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Nalika ngoptimalake rencana eksekusi pitakon PostgreSQL bisa sewenang-wenang "nyusun" kahanan sing padha, aja ngetung salah siji saka wong-wong mau kanggo cathetan individu, deleng kondisi indeks sing ditrapake ... Singkatnya, cara paling gampang yaiku nganggep yen sampeyan ora bisa ngatur urutan sing bakal ditindakake (lan apa bakal diitung kabeh) padha kahanan.

Mulane, yen sampeyan isih pengin ngatur prioritas, sampeyan kudu struktural nggawe kahanan iki ora padha kanthi kondisional ekspresi и operator.

Data lan nggarap wong-wong mau minangka basis kompleks VLSI kita, dadi penting banget kanggo kita yen operasi kasebut ditindakake ora mung kanthi bener, nanging uga kanthi efisien. Ayo goleki conto konkrit ing ngendi kesalahan ing evaluasi ekspresi bisa ditindakake, lan ing ngendi kudu nambah efisiensi.

#0: RTFM

miwiti conto saka dokumentasi:

Nalika urutan evaluasi penting, bisa diatasi kanthi konstruksi CASE. Contone, cara iki kanggo ngindhari divisi kanthi nol ing ukara WHERE ora 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;
Konstruksi sing digunakake CASE nglindhungi expression saka Optimization, supaya mung kudu digunakake yen perlu.

# 1: kondisi pemicu

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

Kabeh katon apik, nanging ... Ora ana sing janji yen nandur modal SELECT ora bakal dieksekusi yen kondisi pisanan salah. Ndandani karo bersarang IF:

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

Saiki ayo dideleng kanthi teliti - kabeh awak fungsi pemicu dadi "dibungkus". IF. Lan iki tegese ora ana sing ngalangi kita mbusak kondisi iki saka prosedur nggunakake WHEN- syarat:

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

Pendekatan iki ngidini sampeyan nyimpen sumber daya server kanthi jaminan yen kondisi kasebut salah.

# 2: UTAWA / LAN chain

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

Yen ora, bisa dipikolehi sing loro EXISTS bakal bener, nanging loro bakal kaleksanan.

Nanging yen kita ngerti manawa salah sijine "bener" luwih asring (utawa "palsu" - kanggo AND-chains) - apa bisa "nambah prioritas" supaya sing nomer loro ora dieksekusi maneh?

Pranyata iku bisa - pendekatan algorithmically cedhak topik artikel Antipattern PostgreSQL: Entri langka tekan tengah JOIN.

Ayo mung "shove ing CASE" loro kondisi iki:

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

Ing kasus iki, kita ora nemtokake ELSE-nilai, sing, yen loro kondisi iku palsu CASE bakal bali NULL, sing ditegesi minangka FALSE в WHERE- syarat.

Conto iki bisa digabungake kanthi cara liya - kanggo rasa lan werna:

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

#3: carane [ora] nulis kahanan

Kita ngenteni rong dina kanggo nganalisa sebab-sebab pemicu "aneh" pemicu iki - ayo goleki kenapa.

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: Ketimpangan ora nyatakake NULL

Ayo nganggep kabeh OLD- lapangan penting NULL. Apa sing bakal kelakon?

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

Lan saka sudut pandang nggarap kahanan NULL padha karo FALSE, kaya kasebut ing ndhuwur.

kaputusan: gunakake operator IS DISTINCT FROM saka ROW-operator, mbandhingake kabeh rekaman bebarengan:

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

Masalah nomer 2: implementasine beda saka fungsi sing padha

Banding:

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

Apa ana investasi ekstra SELECT? A fungsi to_regclass? Kok beda...

Ayo ndandani:

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

Masalah #3: precedence bool

Ayo format sumber:

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

Adhuh ... Nyatane, ing kasus bebener salah siji saka rong kondisi pisanan, kabeh kondisi dadi. TRUE, ora nggatekake ketimpangan. Lan iki ora kabeh sing dikarepake.

Ayo ndandani:

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

Masalah # 4 (cilik): Komplek UTAWA kondisi kanggo siji lapangan

Bener, kita duwe masalah ing nomer 3 amarga ana telung kahanan. Nanging tinimbang wong-wong mau, sampeyan bisa njaluk karo siji, nggunakake mekanisme coalesce ... IN:

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

Semono uga kita NULL "nyekel", lan kompleks OR Sampeyan ora kudu repot karo kurung.

Total

Ayo ndandani apa sing entuk:

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

Lan diwenehi fungsi pemicu iki mung bisa digunakake ing UPDATEpemicu amarga saka ngarsane OLD/NEW ing kondisi tingkat ndhuwur, banjur kondisi iki umume bisa dijupuk metu ing WHEN- kondisi kaya sing ditampilake ing nomer 1 ...

Source: www.habr.com

Antipattern PostgreSQL: Evaluasi Kondisi ing SQL