Antipatterns PostgreSQL: "Kudu ngan aya hiji!"

Dina SQL, anjeun ngajelaskeun "naon" anu anjeun hoyong kéngingkeun, sanés "kumaha" éta kedah dilakukeun. Ku alatan éta, masalah ngembangkeun queries SQL dina gaya "sakumaha eta uninga kumaha eta ditulis" nyokot tempat na ngahargaan, babarengan jeung peculiarities evaluasi kaayaan dina SQL.

Kiwari, nganggo conto anu saderhana pisan, hayu urang tingali naon anu tiasa nyababkeun dina kontéks pamakean GROUP/DISTINCT и LIMIT sareng maranehna.

Éta upami anjeun nyerat dina pamundut "Sambungkeun heula tablet ieu, teras buang sadaya duplikat, kuduna ngan hiji conto pikeun tiap konci" - Ieu persis kumaha éta bakal dianggo, sanajan sambungan teu diperlukeun pisan.

Sarta kadangkala anjeun untung na eta "ngan jalan", kadang boga pangaruh pikaresepeun dina kinerja, sarta kadangkala méré épék anu kacida teu kaduga tina sudut pandang pamekar.

Nya, panginten henteu spektakuler, tapi…

"Pasangan amis": gabung + béda

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

Kumaha bakal jelas naon maranéhna hayang pilih rékaman sapertos X, nu di Y aya pakait sareng kaayaan kaeusi. Dikintunkeun pamundut via JOIN - nampi sababaraha nilai pk sababaraha kali (persis sabaraha rékaman anu cocog dina Y). Kumaha miceun? Tangtu DISTINCT!

Utamana "pikaresepeun" nalika pikeun unggal catetan X aya sababaraha ratus rékaman Y anu aya hubunganana, teras duplikat dihapus sacara heroik ...

Kumaha carana ngalereskeun? Pikeun mimitian ku, nyadar yén tugas bisa dirobah jadi "Pilih rékaman X anu aya sahenteuna SATU dina Y pakait sareng kaayaan anu kaeusi" - barina ogé, urang teu butuh nanaon ti Y-catetan sorangan.

Nested EXISTS

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

Sababaraha vérsi PostgreSQL ngartos yén dina EXISTS cukup pikeun mendakan catetan anu munggaran anu muncul, anu langkung lami henteu. Ku alatan éta, kuring leuwih resep sok nunjukkeun LIMIT 1 di jero 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;

Pilihan anu sami ngamungkinkeun, upami diperyogikeun, langsung ngabalikeun sababaraha data tina catetan Y anu aya hubunganana dina waktos anu sami. Pilihan anu sami dibahas dina tulisan "PostgreSQL Antipatterns: catetan langka bakal ngahontal tengah JOIN".

"Naha mayar langkung": DISTINCT [ON] + LIMIT 1

Kauntungan tambahan tina transformasi query sapertos kitu nyaéta kamampuan pikeun ngabatesan enumerasi rékaman upami ngan ukur hiji/saeutik di antarana, sapertos dina kasus ieu:

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

Ayeuna urang maca pamundut sareng coba ngartos naon anu sakuduna dilakukeun ku DBMS:

• urang sambungkeun piring
• unik ku X.pk
• milih salah sahiji rékaman sésana

Janten naon anu anjeun kéngingkeun? "Sababaraha hiji catetan" ti anu unik - sareng upami anjeun nyandak salah sahiji anu henteu unik ieu, naha hasilna bakal robih? .. "Sareng upami henteu aya bédana, naha mayar langkung?"

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

Jeung persis tema sarua jeung GROUP BY + LIMIT 1.

"Kuring ngan kudu nanya": GROUP implisit + LIMIT

Hal sarupa lumangsung dina béda cék nonemptiness labél atawa CTEs sakumaha pamundut progresses:

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

Fungsi agrégat (count/min/max/sum/...) suksés dieksekusi dina sakabéh set, sanajan tanpa sacara eksplisit nangtukeun GROUP BY. Ngan didieu jeung LIMIT aranjeunna henteu pisan ramah.

pamekar bisa mikir "Ayeuna, upami aya catetan di dinya, kuring peryogi langkung ti LIMIT". Tapi anjeun teu kudu! Kusabab pikeun dasarna nyaéta:

• cacah naon maranéhna rék dina sakabéh rékaman
• masihan saloba garis sakumaha maranéhna ménta

Gumantung kana kaayaan target, éta pantes pikeun ngadamel salah sahiji substitusi ieu:

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

"Sabaraha ngagantung dina gram": Béda + wates

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Pamekar naif tiasa leres-leres yakin yén palaksanaan pamundut bakal lirén, pas urang manggihan mimiti $1 nilai béda nu datang di sakuliah.

Sometime di mangsa nu bakal datang, ieu bisa jeung bakal dianggo berkat titik anyar Indéks Skip Scan, palaksanaan anu ayeuna keur digawé kaluar, tapi henteu acan.

Sajauh heula kabéh rékaman bakal dipulut, anu unik, sarta ngan saloba di antarana sakumaha dipénta bakal balik. Ieu utamana hanjelu lamun urang hayang hal kawas $ 1 = 4, sareng aya ratusan rébu rékaman dina tabél ...

Dina raraga teu jadi sedih sia, urang bakal ngagunakeun query rekursif "DISTINCT for the Miskin" ti PostgreSQL Wiki:

sumber: www.habr.com