🌭 👇🏿 👨🏼‍🚒 Antipattern PostgreSQL: "Seharusnya hanya tersisa satu!" 🦊 🌎 💰

Dalam SQL, Anda mendeskripsikan "apa" yang ingin Anda dapatkan, bukan "bagaimana" itu harus dilakukan. Oleh karena itu, masalah pengembangan kueri SQL dalam gaya "saat Anda mendengarnya saat menulis" menggantikannya, bersama dengan kekhasan kondisi penghitungan dalam SQL .

Hari ini, dengan menggunakan contoh yang sangat sederhana, mari kita lihat apa yang dapat mengarah pada hal ini dalam konteks penggunaan GROUP/DISTINCTdan LIMITbersama-sama dengannya.

Sekarang, jika Anda menulis dalam permintaan "pertama-tama hubungkan pelat ini, dan kemudian buang semua duplikat, seharusnya hanya ada satu salinan untuk setiap kunci" - inilah cara kerjanya, bahkan jika sambungan tidak diperlukan sama sekali.

Dan terkadang Anda beruntung dan "berhasil", terkadang memiliki 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": JOIN + DISTINCT

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

Bagaimana menjadi jelas bahwa kami ingin memilih catatan X tersebut, yang Y memiliki catatan terkait dengan kondisi terpenuhi . Kami menulis permintaan melalui JOIN- kami mendapat beberapa nilai pk beberapa kali (persis berapa banyak rekaman yang cocok di Y ternyata). Bagaimana cara menghapusnya? Tentu saja DISTINCT!

Ini sangat "membahagiakan" ketika untuk setiap X-record ada beberapa ratus Y-record yang terhubung, dan kemudian duplikatnya dihapus secara heroik ...

Bagaimana cara memperbaikinya? Untuk memulainya, sadari bahwa tugas dapat dimodifikasi untuk "memilih record X yang Y memiliki SETIDAKNYA SATU yang terkait dengan kondisi berjalan" - bagaimanapun juga, kita tidak memerlukan apa pun dari Y-record itu sendiri.

EXISTS Bersarang

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

Beberapa versi PostgreSQL memahami bahwa itu cukup untuk menemukan catatan pertama yang tersedia di EXISTS, yang lebih lama tidak. Oleh karena itu, saya lebih suka untuk selalu merinci LIMIT 1di 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, pada saat yang sama untuk segera mengembalikan beberapa data dari Y-record tertaut yang ditemukan. Opsi serupa dibahas dalam artikel "PostgreSQL Antipatterns: record rare will fly to middle of a JOIN" .

"Mengapa membayar lebih banyak": DISTINCT [ON] + LIMIT 1

Keuntungan tambahan dari transformasi kueri tersebut adalah kemampuan untuk dengan mudah membatasi iterasi atas rekaman jika hanya satu / beberapa di antaranya 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 DBMS usulkan untuk dilakukan:

kami menghubungkan pelat
dapat dikhususkan oleh X.pk
pilih satu dari rekaman yang tersisa

Artinya, Anda punya apa? "Satu catatan" dari yang unik - dan jika Anda mengambil yang ini yang tidak unik, apakah hasilnya akan berubah entah bagaimana? .. "Dan jika tidak ada perbedaan, mengapa membayar lebih?"

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

Dan topiknya sama persis dengan GROUP BY + LIMIT 1.

"Saya hanya bertanya": GROUP + LIMIT implisit

Hal serupa ditemui selama berbagai pemeriksaan untuk non-kosongnya pelat atau CTE selama eksekusi permintaan:

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

Fungsi agregat ( count/min/max/sum/...) berhasil dijalankan di seluruh set, bahkan tanpa indikasi eksplisit GROUP BY. Hanya saja LIMITmereka tidak terlalu bersahabat dengan mereka.

Developer mungkin berpikir “jika ada record disana, maka saya tidak perlu lagi LIMIT” . Tapi jangan! Karena untuk alasnya adalah:

hitung apa yang Anda inginkan di semua rekaman
berikan baris sebanyak yang Anda minta

Bergantung pada kondisi target, maka tepat untuk membuat salah satu pergantian pemain di sini:

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

"Berapa banyak yang harus digantung dalam gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Pengembang yang naif dapat dengan jujur percaya bahwa kueri akan berhenti segera setelah kami menemukan $ 1 pertama nilai berbeda yang muncul .

Suatu saat di masa mendatang ini mungkin dan akan bekerja berkat node Index Skip Scan yang baru , yang implementasinya sedang dikerjakan, tetapi belum.

Sejauh ini, pada awalnya, semua-semua catatan akan diambil , dibuat unik, dan hanya dari mereka yang akan dikembalikan berapa banyak yang diminta. Sangat menyedihkan jika kita menginginkan sesuatu seperti $ 1 = 4 , dan ada ratusan ribu catatan di tabel ...

Agar tidak sedih dengan sia-sia, kita akan menggunakan kueri rekursif "DISTINCT for the poor" dari Wiki PostgreSQL :

Antipattern PostgreSQL: "Seharusnya hanya tersisa satu!"