🥇Antipattern PostgreSQL: Penilaian Keadaan dalam SQL

SQL bukan C++, begitu juga JavaScript. Oleh itu, penilaian ungkapan logik adalah berbeza, dan ini bukan perkara yang sama sama sekali:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Semasa mengoptimumkan pelan pelaksanaan pertanyaan PostgreSQL boleh sewenang-wenangnya "menyusun semula" keadaan yang setara, jangan kira mana-mana daripada mereka untuk rekod individu, rujuk kepada keadaan indeks yang digunakan ... Pendek kata, cara paling mudah adalah dengan menganggap bahawa anda tidak boleh menguruskan susunan di mana mereka akan berada (dan sama ada mereka akan dikira sama sekali) sama rata syarat.

Oleh itu, jika anda masih mahu mengurus keutamaan, anda perlu secara berstruktur menjadikan syarat ini tidak sama rata dengan bersyarat ungkapan и pengendali.

Data dan bekerja dengan mereka adalah asas kompleks VLSI kami, jadi adalah sangat penting bagi kami bahawa operasi pada mereka dilakukan bukan sahaja dengan betul, tetapi juga dengan cekap. Mari lihat contoh konkrit di mana kesilapan dalam penilaian ekspresi boleh dibuat, dan di mana ia patut meningkatkan kecekapannya.

#0: RTFM

Bermula contoh dari dokumentasi:

Apabila susunan penilaian adalah penting, ia boleh diperbaiki dengan konstruk CASE. Sebagai contoh, cara ini untuk mengelakkan pembahagian dengan sifar dalam ayat WHERE tidak boleh dipercayai:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Pilihan selamat:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Binaan yang digunakan CASE melindungi ungkapan daripada pengoptimuman, jadi ia hanya boleh digunakan apabila perlu.

#1: keadaan pencetus

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

Segala-galanya nampaknya kelihatan baik, tetapi... Tiada siapa yang menjanjikan bahawa yang dilaburkan SELECT tidak akan dilaksanakan jika syarat pertama adalah palsu. Betulkan dengan bersarang IF:

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

Sekarang mari kita lihat dengan teliti - seluruh badan fungsi pencetus ternyata "dibungkus" masuk IF. Dan ini bermakna tiada apa yang menghalang kita daripada mengeluarkan keadaan ini daripada prosedur menggunakan WHEN-syarat:

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

Pendekatan ini membolehkan anda menyimpan sumber pelayan dengan jaminan jika syarat itu palsu.

#2: rantai ATAU/DAN

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

Jika tidak, ia boleh didapati bahawa kedua-duanya EXISTS akan menjadi benar, tetapi kedua-duanya akan dilaksanakan.

Tetapi jika kita tahu dengan pasti bahawa salah satu daripadanya adalah "benar" lebih kerap (atau "palsu" - untuk AND-chains) - adakah mungkin untuk "meningkatkan keutamaannya" supaya yang kedua tidak dilaksanakan sekali lagi?

Ternyata ia mungkin - pendekatan algoritma hampir dengan topik artikel Antipattern PostgreSQL: Entri jarang sampai ke tengah JOIN.

Mari kita "sorong di bawah CASE" kedua-dua syarat ini:

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

Dalam kes ini, kami tidak mentakrifkan ELSE-nilai, iaitu, jika kedua-dua syarat adalah palsu CASE akan kembali NULL, yang ditafsirkan sebagai FALSE в WHERE- syarat.

Contoh ini boleh digabungkan dengan cara lain - secukup rasa dan warna:

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

#3: bagaimana [tidak] menulis syarat

Kami menghabiskan dua hari untuk menganalisis sebab pencetus "pelik" pencetus ini - mari lihat sebabnya.

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: Ketaksamaan tidak mengambil kira NULL

Mari kita anggap bahawa segala-galanya OLD-bidang penting NULL. Apa yang akan berlaku?

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

Dan dari sudut melihat syarat-syarat NULL bersamaan FALSE, seperti yang dinyatakan di atas.

keputusan: gunakan operator IS DISTINCT FROM daripada ROW-pengendali, membandingkan keseluruhan rekod sekaligus:

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

Masalah nombor 2: pelaksanaan berbeza bagi fungsi yang sama

Bandingkan dengan:

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

Kenapa ada pelaburan tambahan SELECT? Satu fungsi to_regclass? Kenapa berbeza...

Mari kita betulkan:

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

Masalah #3: keutamaan bool

Mari format sumber:

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

Oops ... Sebenarnya, ternyata dalam kes kebenaran mana-mana dua syarat pertama, keseluruhan syarat berubah menjadi TRUE, tidak mengambil kira ketidaksamaan. Dan ini sama sekali bukan yang kami mahukan.

Mari kita betulkan:

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

Masalah #4 (kecil): kompleks ATAU keadaan untuk satu medan

Sebenarnya, kami mempunyai masalah di No 3 kerana terdapat tiga syarat. Tetapi bukannya mereka, anda boleh bertahan dengan satu, menggunakan mekanisme coalesce ... IN:

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

Begitu juga kita NULL "tangkap", dan kompleks OR Anda tidak perlu kecoh dengan kurungan.

Dalam jumlah

Mari kita betulkan apa yang kita dapat:

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

Dan memandangkan fungsi pencetus ini hanya boleh digunakan dalam UPDATEpencetus kerana kehadiran OLD/NEW dalam keadaan peringkat atas, maka keadaan ini secara amnya boleh dibawa keluar WHEN-keadaan seperti yang ditunjukkan dalam #1...

Sumber: www.habr.com

Antipattern PostgreSQL: Penilaian Keadaan dalam SQL