🥇 Antipattern PostgreSQL: GABUNG dan OR yang berbahaya

Waspadalah terhadap operasi yang membawa buffer...
Dengan menggunakan kueri kecil sebagai contoh, mari kita lihat beberapa pendekatan universal untuk mengoptimalkan kueri di PostgreSQL. Apakah Anda menggunakannya atau tidak, itu terserah Anda, tetapi ada baiknya Anda mengetahuinya.

Dalam beberapa versi PG selanjutnya situasinya mungkin berubah seiring dengan semakin cerdasnya penjadwal, namun untuk 9.4/9.6 tampilannya kira-kira sama, seperti pada contoh di sini.

Mari kita terima 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 tabel dan fieldNama bidang dan tabel “Rusia” dapat diperlakukan berbeda, tetapi ini masalah selera. Karena di sini di Tensor tidak ada pengembang asing, dan PostgreSQL memungkinkan kami memberi nama bahkan dalam hieroglif, jika memang demikian diapit tanda petik, maka kita lebih memilih untuk memberi nama benda secara jelas dan jelas agar tidak terjadi kejanggalan.
Mari kita lihat rencana yang dihasilkan:

[lihat penjelasan.tensor.ru]

144ms dan hampir 53K buffer - yaitu, lebih dari 400MB data! Dan kita akan beruntung jika semuanya ada di cache pada saat permintaan kita, jika tidak maka akan memakan waktu berkali-kali lebih lama saat membaca dari disk.

Algoritmenya paling penting!

Untuk mengoptimalkan permintaan apa pun, Anda harus terlebih dahulu memahami apa yang harus dilakukannya.
Mari kita tinggalkan pengembangan struktur database itu sendiri di luar cakupan artikel ini untuk saat ini, dan setuju bahwa kita dapat melakukannya dengan relatif “murah” menulis ulang permintaan tersebut dan/atau masukkan ke dasar beberapa hal yang kita perlukan Indeks.

Jadi permintaannya:
— memeriksa keberadaan setidaknya beberapa dokumen
- dalam kondisi yang kita perlukan dan jenis tertentu
- dimana penulis atau pelakunya adalah karyawan yang kita butuhkan

GABUNG + BATAS 1

Seringkali lebih mudah bagi pengembang untuk menulis kueri yang pertama kali menggabungkan sejumlah besar tabel, dan kemudian hanya satu catatan yang tersisa dari seluruh kumpulan ini. Namun lebih mudah bagi pengembang bukan berarti lebih efisien untuk database.
Dalam kasus kami hanya ada 3 tabel - dan apa efeknya...

Pertama-tama mari kita hilangkan koneksi dengan tabel "Jenis Dokumen", dan pada saat yang sama memberi tahu database bahwa catatan tipe kami unik (kami mengetahui hal ini, tetapi penjadwal belum mengetahuinya):

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

Ya, jika tabel/CTE terdiri dari satu bidang dari satu catatan, maka di PG Anda bahkan dapat menulis seperti ini, alih-alih

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

Evaluasi malas dalam kueri PostgreSQL

BitmapAtau vs UNION

Dalam beberapa kasus, Pemindaian Tumpukan Bitmap akan memakan banyak biaya - misalnya, dalam situasi kita, ketika cukup banyak catatan yang memenuhi kondisi yang diperlukan. Kami mendapatkannya karena Kondisi OR berubah menjadi BitmapOr- operasi sesuai rencana.
Mari kita kembali ke masalah awal - kita perlu menemukan catatan yang sesuai untuk siapa saja dari ketentuan - yaitu, tidak perlu mencari semua 59K catatan dalam kedua kondisi. Ada cara untuk memenuhi satu syarat, dan lanjutkan ke yang kedua hanya ketika tidak ada yang ditemukan di yang pertama. Desain berikut akan membantu kami:

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

LIMIT “Eksternal” 1 memastikan bahwa pencarian berakhir ketika catatan pertama ditemukan. Dan jika sudah ditemukan pada blok pertama maka blok kedua tidak akan dieksekusi (tidak pernah dieksekusi mengenai).

“Menyembunyikan kondisi sulit dalam CASE”

Ada momen yang sangat merepotkan dalam kueri awal - memeriksa status terhadap tabel terkait "DocumentExtension". Terlepas dari kebenaran kondisi lain dalam ekspresi (misalnya, d."Dihapus" TIDAK BENAR), koneksi ini selalu dijalankan dan “menghabiskan sumber daya”. Lebih atau kurang dari mereka akan dibelanjakan - tergantung pada ukuran tabel ini.
Namun Anda dapat mengubah kueri sehingga pencarian rekaman terkait hanya terjadi bila benar-benar diperlukan:

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 tabel tertaut ke kami не нужно для результата ни одно из полей, maka kita mempunyai kesempatan untuk mengubah JOIN menjadi kondisi pada subquery.
Mari kita tinggalkan bidang yang diindeks "di luar tanda kurung CASE", tambahkan kondisi sederhana dari catatan ke blok WHEN - dan sekarang kueri "berat" hanya dijalankan ketika meneruskan ke THEN.

Nama belakang saya adalah "Total"

Kami mengumpulkan kueri yang dihasilkan dengan semua mekanisme yang dijelaskan 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;

Menyesuaikan [ke] indeks

Mata yang terlatih memperhatikan bahwa kondisi indeks di subblok UNION sedikit berbeda - ini karena kami sudah memiliki indeks yang sesuai di tabel. Dan jika mereka tidak ada, ada baiknya dibuat: Dokumen (Orang3, Tipe Dokumen) и Dokumen (Tipe Dokumen, Karyawan).
tentang urutan bidang dalam kondisi ROWС точки зрения планировщика, конечно, можно написать и (A, B) = (konstanta, konstantaB)Dan (B, A) = (konstanB, konstanta). Namun saat merekam dalam urutan bidang dalam indeks, permintaan seperti itu akan lebih mudah untuk di-debug nanti.
Apa rencananya?

[lihat penjelasan.tensor.ru]

Sayangnya kami kurang beruntung dan tidak ditemukan apa pun di blok UNION pertama, sehingga blok kedua tetap dijalankan. Namun demikian - saja 0.037ms dan 11 buffer!
Kami telah mempercepat permintaan dan mengurangi pemompaan data ke memori beberapa ribu kali, menggunakan teknik yang cukup sederhana - hasil yang bagus dengan sedikit copy-paste. 🙂

Sumber: www.habr.com

PostgreSQL Antipatterns: GABUNG dan OR yang Berbahaya