PostgreSQL Antipatterns: "Pasti hanya ada satu!"

Dalam SQL, Anda menjelaskan “apa” yang ingin Anda capai, bukan “bagaimana” hal itu harus dijalankan. Oleh karena itu, masalah mengembangkan kueri SQL dengan gaya "seperti yang terdengar seperti yang tertulis" mendapat tempat yang terhormat, bersama dengan fitur penghitungan kondisi dalam SQL.

Hari ini, dengan menggunakan contoh yang sangat sederhana, mari kita lihat apa dampaknya dalam konteks penggunaan GROUP/DISTINCT и LIMIT dengan mereka.

Sekarang, jika Anda menulis dalam permintaan “Pertama-tama sambungkan tanda-tanda ini, lalu buang semua duplikatnya, seharusnya hanya ada satu yang tersisa salin untuk setiap kunci" - beginilah cara kerjanya, meskipun koneksi tidak diperlukan sama sekali.

Dan terkadang Anda beruntung dan “berhasil”, terkadang memberikan efek yang tidak menyenangkan pada kinerja, dan terkadang memberikan efek yang sama sekali tidak terduga dari sudut pandang pengembang.

Yah, mungkin tidak terlalu spektakuler, tapi...

“Pasangan manis”: GABUNG + BERBEDA

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Akan jelas apa yang mereka inginkan pilih record X yang di dalamnya terdapat record di Y yang terkait dengan kondisi terpenuhi. Menulis permintaan melalui JOIN — mendapat beberapa nilai pk beberapa kali (berapa banyak entri yang sesuai yang muncul di Y). Bagaimana cara menghapusnya? Tentu DISTINCT!

Ini sangat “menggembirakan” ketika untuk setiap X-record ada beberapa ratus Y-record yang terkait, dan kemudian duplikatnya dihapus secara heroik...

Bagaimana cara memperbaiki? Untuk memulainya, sadari bahwa masalahnya dapat dimodifikasi “pilih record X yang di Y ada SETIDAKNYA SATU yang terkait dengan kondisi terpenuhi” - lagipula, kita tidak memerlukan apa pun dari Y-record itu sendiri.

ADA Bersarang

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Beberapa versi PostgreSQL memahami bahwa di EXISTS cukup menemukan entri pertama yang muncul, sedangkan yang lebih lama tidak. Oleh karena itu saya lebih memilih untuk selalu menunjukkan LIMIT 1 dalam EXISTS.

GABUNG LATERAL

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Opsi yang sama memungkinkan, jika perlu, untuk segera mengembalikan beberapa data dari data Y terkait yang ditemukan. Opsi serupa dibahas dalam artikel "Antipattern PostgreSQL: catatan langka akan mencapai pertengahan GABUNG".

“Mengapa membayar lebih”: DISTINCT [ON] + LIMIT 1

Manfaat tambahan dari transformasi kueri tersebut adalah kemampuan untuk dengan mudah membatasi pencarian rekaman jika hanya satu atau beberapa rekaman yang diperlukan, seperti dalam kasus berikut:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Sekarang kita membaca permintaan tersebut dan mencoba memahami apa yang diusulkan untuk dilakukan oleh DBMS:

• menghubungkan tanda-tanda tersebut
• unik oleh X.pk
• dari entri yang tersisa, pilih satu

Jadi apa yang kamu dapatkan? "Hanya satu entri" dari yang unik - dan jika kita mengambil yang tidak unik ini, apakah hasilnya akan berubah?.. “Dan jika tidak ada perbedaan, mengapa harus membayar lebih?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Dan topik yang persis sama dengan GROUP BY + LIMIT 1.

“Saya hanya perlu bertanya”: GROUP + LIMIT implisit

Hal serupa terjadi pada waktu yang berbeda pemeriksaan non-kekosongan tanda tangan atau CTE saat permintaan berlangsung:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Fungsi agregat (count/min/max/sum/...) berhasil dijalankan di seluruh rangkaian, bahkan tanpa instruksi eksplisit GROUP BY. Hanya dengan LIMIT mereka tidak terlalu ramah.

Pengembang bisa berpikir “jika ada catatan di sana, maka saya memerlukan tidak lebih dari LIMIT”. Tapi jangan lakukan itu! Karena untuk dasarnya adalah:

• menghitung apa yang mereka inginkan menurut semua catatan
• berikan baris sebanyak yang mereka minta

Tergantung pada kondisi target, adalah tepat untuk melakukan salah satu dari substitusi berikut:

• (count + LIMIT 1) = 0 pada NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 pada EXISTS(LIMIT 1)
• count >= N pada (SELECT count(*) FROM (... LIMIT N))

“Berapa beratnya dalam gram”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Pengembang yang naif mungkin dengan tulus percaya bahwa permintaan tersebut akan berhenti dijalankan. segera setelah kami menemukan $1 dari nilai berbeda pertama yang muncul.

Suatu saat nanti hal ini mungkin dan akan berhasil berkat node baru Indeks Lewati Pemindaian, yang implementasinya sedang dikerjakan, namun belum.

Untuk saat ini dulu semua catatan akan diambil, bersifat unik, dan hanya dari mereka jumlah yang diminta akan dikembalikan. Sangat menyedihkan jika kita menginginkan sesuatu seperti itu $ 1 = 4, dan ada ratusan ribu catatan di tabel...

Agar tidak bersedih sia-sia, mari kita gunakan query rekursif "DISTINCT adalah untuk masyarakat miskin" dari PostgreSQL Wiki:

Sumber: www.habr.com