🥇Antipattern PostgreSQL: JOIN dan OR yang berbahaya

Berhati-hati dengan operasi yang membawa penampan...
Menggunakan pertanyaan kecil sebagai contoh, mari kita lihat beberapa pendekatan universal untuk mengoptimumkan pertanyaan dalam PostgreSQL. Sama ada anda menggunakannya atau tidak, terpulang kepada anda, tetapi anda perlu mengetahui tentangnya.

Dalam beberapa versi PG yang berikutnya, keadaan mungkin berubah apabila penjadual menjadi lebih pintar, tetapi untuk 9.4/9.6 ia kelihatan lebih kurang sama, seperti dalam contoh di sini.

Mari kita ambil permintaan yang sangat nyata:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

tentang nama jadual dan medanNama medan dan jadual "Rusia" boleh dilayan secara berbeza, tetapi ini adalah soal rasa. Kerana ia di sini di Tensor tiada pembangun asing, dan PostgreSQL membenarkan kami memberi nama walaupun dalam hieroglif, jika mereka disertakan dalam petikan, maka kami lebih suka menamakan objek dengan jelas dan jelas supaya tidak ada percanggahan.
Mari lihat rancangan yang terhasil:

[lihat explain.tensor.ru]

144ms dan hampir 53K penimbal - iaitu, lebih daripada 400MB data! Dan kami akan bernasib baik jika kesemuanya berada dalam cache pada masa permintaan kami, jika tidak, ia akan mengambil masa berkali-kali lebih lama apabila dibaca dari cakera.

Algoritma adalah yang paling penting!

Untuk mengoptimumkan sebarang permintaan, anda mesti terlebih dahulu memahami perkara yang perlu dilakukan.
Mari biarkan pembangunan struktur pangkalan data itu sendiri di luar skop artikel ini buat masa ini, dan bersetuju bahawa kita boleh secara relatif "murah" tulis semula permintaan itu dan/atau gulung ke pangkalan beberapa perkara yang kami perlukan Indeks.

Jadi permintaannya:
— menyemak kewujudan sekurang-kurangnya beberapa dokumen
- dalam keadaan yang kita perlukan dan jenis tertentu
- di mana pengarang atau penghibur adalah pekerja yang kita perlukan

SERTAI + HAD 1

Selalunya lebih mudah bagi pembangun untuk menulis pertanyaan di mana sebilangan besar jadual mula-mula dicantumkan, dan kemudian hanya tinggal satu rekod daripada keseluruhan set ini. Tetapi lebih mudah untuk pembangun tidak bermakna lebih cekap untuk pangkalan data.
Dalam kes kami hanya terdapat 3 jadual - dan apakah kesannya...

Mula-mula mari kita buang sambungan dengan jadual "Jenis Dokumen", dan pada masa yang sama beritahu pangkalan data bahawa rekod jenis kami adalah unik (kami tahu ini, tetapi penjadual belum tahu lagi):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Ya, jika jadual/CTE terdiri daripada medan tunggal rekod tunggal, maka dalam PG anda juga boleh menulis seperti ini, bukannya

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Penilaian malas dalam pertanyaan PostgreSQL

BitmapOr vs UNION

Dalam sesetengah kes, Imbasan Timbunan Bitmap akan menelan kos yang tinggi - contohnya, dalam keadaan kita, apabila agak banyak rekod memenuhi syarat yang diperlukan. Kami mendapatnya kerana Keadaan ATAU bertukar menjadi BitmapOr- operasi dalam rancangan.
Mari kembali kepada masalah asal - kita perlu mencari rekod yang sepadan sesiapa daripada syarat - iaitu, tidak perlu mencari semua rekod 59K di bawah kedua-dua syarat. Terdapat cara untuk menyelesaikan satu syarat, dan pergi ke yang kedua hanya apabila tiada yang ditemui pada yang pertama. Reka bentuk berikut akan membantu kami:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

LIMIT “Luaran” 1 memastikan bahawa carian tamat apabila rekod pertama ditemui. Dan jika ia sudah dijumpai di blok pertama, blok kedua tidak akan dilaksanakan (tidak pernah dilaksanakan berkenaan dengan).

"Menyembunyikan keadaan sukar di bawah CASE"

Terdapat saat yang sangat menyusahkan dalam pertanyaan asal - menyemak status terhadap jadual berkaitan "DocumentExtension". Tanpa mengira kebenaran syarat lain dalam ungkapan (contohnya, d.“Dipadamkan” TIDAK BENAR), sambungan ini sentiasa dilaksanakan dan "sumber kos". Lebih kurang daripada mereka akan dibelanjakan - bergantung pada saiz meja ini.
Tetapi anda boleh mengubah suai pertanyaan supaya carian untuk rekod berkaitan berlaku hanya apabila ia benar-benar perlu:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Sekali dari jadual yang dipautkan kepada kami tiada satu pun medan diperlukan untuk hasilnya, maka kita mempunyai peluang untuk menukar JOIN menjadi syarat pada subquery.
Mari tinggalkan medan diindeks "di luar kurungan CASE", tambah syarat mudah daripada rekod ke blok WHEN - dan kini pertanyaan "berat" dilaksanakan hanya apabila menghantar ke THEN.

Nama keluarga saya ialah "Jumlah"

Kami mengumpulkan pertanyaan yang terhasil dengan semua mekanik yang diterangkan di atas:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Melaraskan [kepada] indeks

Mata terlatih mendapati bahawa keadaan diindeks dalam subblok UNION sedikit berbeza - ini kerana kami sudah mempunyai indeks yang sesuai di atas meja. Dan jika ia tidak wujud, ia patut dibuat: Dokumen(Person3, DocumentType) и Dokumen(DocumentType, Employee).
tentang susunan medan dalam keadaan ROWDari sudut pandangan perancang, sudah tentu anda boleh menulis (A, B) = (constA, constB)dan (B, A) = (constB, constA). Tetapi apabila merakam mengikut susunan medan dalam indeks, permintaan sedemikian adalah lebih mudah untuk nyahpepijat kemudian.
Apa yang ada dalam rancangan?

[lihat explain.tensor.ru]

Malangnya, kami tidak bernasib baik dan tiada apa yang ditemui di blok UNION pertama, jadi yang kedua masih dilaksanakan. Tetapi walaupun begitu - sahaja 0.037ms dan 11 penimbal!
Kami telah mempercepatkan permintaan dan mengurangkan pengepaman data dalam ingatan beberapa ribu kali, menggunakan teknik yang agak mudah - hasil yang baik dengan sedikit copy-paste. 🙂

Sumber: www.habr.com

Antipattern PostgreSQL: JOIN dan OR Memudaratkan