ProHoster > blog > administrasi > PostgreSQL Antipatterns: Evaluasi Kondisi dalam SQL

PostgreSQL Antipatterns: Evaluasi Kondisi dalam SQL

SQL bukan C++, juga bukan JavaScript. Oleh karena itu, evaluasi ekspresi logis berbeda, dan ini sama sekali bukan hal yang sama:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Sambil mengoptimalkan rencana eksekusi kueri PostgreSQL dapat sewenang-wenang "mengatur ulang" kondisi yang setara, jangan hitung salah satunya untuk catatan individual, lihat kondisi indeks yang diterapkan ... Singkatnya, cara termudah adalah dengan berasumsi bahwa Anda tidak bisa mengatur urutan di mana mereka akan (dan apakah mereka akan dihitung sama sekali) sama kondisi.

Oleh karena itu, jika Anda masih ingin mengelola prioritas, Anda perlu melakukannya secara struktural membuat kondisi ini tidak seimbang dengan kondisional ekspresi и operator.

PostgreSQL Antipatterns: Evaluasi Kondisi dalam SQL
Data dan bekerja dengan mereka adalah dasarnya dari kompleks VLSI kami, jadi sangat penting bagi kami bahwa operasi pada mereka dilakukan tidak hanya dengan benar, tetapi juga efisien. Mari kita lihat contoh konkret di mana kesalahan dalam evaluasi ekspresi dapat dibuat, dan di mana efisiensinya perlu ditingkatkan.

#0: RTFM

Mulai contoh dari dokumentasi:

Ketika urutan evaluasi penting, dapat diperbaiki dengan konstruk CASE. Misalnya, cara ini untuk menghindari pembagian dengan nol dalam sebuah kalimat WHERE tidak bisa diandalkan:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Opsi aman:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Konstruksi yang digunakan CASE melindungi ekspresi dari pengoptimalan, sehingga hanya boleh digunakan jika diperlukan.

#1: kondisi pemicu

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

Segalanya tampak terlihat bagus, tapi... Tidak ada yang menjanjikan investasi itu SELECT tidak akan dieksekusi jika kondisi pertama salah. Perbaiki dengan bersarang IF:

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

Sekarang mari kita perhatikan baik-baik - seluruh badan fungsi pemicu ternyata "terbungkus". IF. Dan ini berarti tidak ada yang menghalangi kami untuk menghapus kondisi ini dari penggunaan prosedur WHEN-kondisi:

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

Pendekatan ini memungkinkan Anda menghemat sumber daya server dengan jaminan jika kondisinya salah.

#2: ATAU/DAN rantai

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

Jika tidak, dapat diperoleh keduanya EXISTS akan benar, tapi keduanya akan dieksekusi.

Tetapi jika kita tahu pasti bahwa salah satunya "benar" lebih sering (atau "salah" - untuk AND-chains) - apakah mungkin untuk "meningkatkan prioritasnya" sehingga yang kedua tidak dieksekusi sekali lagi?

Ternyata itu mungkin - pendekatan algoritme dekat dengan topik artikel PostgreSQL Antipatterns: Entri langka mencapai tengah GABUNG.

Mari kita "mendorong berdasarkan KASUS" kedua kondisi ini:

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

Dalam hal ini, kami tidak mendefinisikan ELSE-nilai, yaitu, jika kedua kondisi salah CASE akan kembali NULL, yang diartikan sebagai FALSE в WHERE- kondisi.

Contoh ini dapat digabungkan dengan cara lain - sesuai selera dan warna:

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

#3: bagaimana [tidak] menulis kondisi

Kami menghabiskan dua hari untuk menganalisis alasan pemicu "aneh" dari pemicu ini - mari kita lihat alasannya.

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 tidak memperhitungkan NULL

Mari kita asumsikan semuanya OLD-bidang penting NULL. Apa yang akan terjadi?

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

Dan dari sudut pandang mengerjakan kondisi NULL setara FALSE, seperti yang disebutkan di atas.

keputusan: menggunakan operator IS DISTINCT FROM dari ROW-operator, membandingkan seluruh catatan sekaligus:

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

Masalah nomor 2: implementasi berbeda dari fungsi yang sama

Bandingkan:

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

Mengapa ada investasi tambahan SELECT? Sebuah fungsi to_regclass? Kenapa berbeda...

Mari kita perbaiki:

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

Masalah #3: bool diutamakan

Mari format sumbernya:

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

Ups ... Nyatanya, ternyata dalam kasus kebenaran salah satu dari dua syarat pertama, seluruh syarat berubah menjadi TRUE, dengan mengabaikan ketidaksetaraan. Dan ini sama sekali bukan yang kami inginkan.

Mari kita perbaiki:

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

Masalah #4 (kecil): kondisi OR kompleks untuk satu bidang

Sebenarnya kami bermasalah di No. 3 justru karena ada tiga syarat. Tapi alih-alih mereka, Anda bisa bertahan dengan satu, menggunakan mekanismenya coalesce ... IN:

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

Kita juga NULL "menangkap", dan kompleks OR Anda tidak perlu repot dengan tanda kurung.

Total

Mari kita perbaiki apa yang kita punya:

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

Dan mengingat bahwa fungsi pemicu ini hanya dapat digunakan di UPDATEpemicu karena keberadaannya OLD/NEW dalam kondisi tingkat atas, maka kondisi ini umumnya dapat dibawa keluar WHEN-kondisi seperti di no 1...

Sumber: www.habr.com

Tambah komentar