Antipattern PostgreSQL: "Ana mung siji!"

Ing SQL, sampeyan nerangake "apa" sing pengin digayuh, dudu "carane" kudu ditindakake. Mulane, masalah ngembangake pitakon SQL kanthi gaya "kaya sing dirungokake yaiku carane ditulis" njupuk papan pakurmatan, bebarengan karo fitur ngetung kondisi ing SQL.

Dina iki, nggunakake conto sing gampang banget, ayo goleki apa sing bisa ditindakake ing konteks panggunaan GROUP/DISTINCT и LIMIT karo wong-wong mau.

Saiki, yen sampeyan nulis ing panyuwunan "Sambungake tandha-tandha iki dhisik, banjur uncalan kabeh duplikat, kudune mung kari siji salinan kanggo saben kunci" - iki persis carane bakal bisa, sanajan sambungan ora perlu ing kabeh.

Lan kadhangkala sampeyan begja lan "mung dianggo", kadhangkala duwe efek ora nyenengake ing kinerja, lan kadhangkala menehi efek sing ora dikarepke saka sudut pandang pangembang.

Ya, mungkin ora spektakuler, nanging ...

"Pasangan manis": NGGABUNGA + BEDA

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

Bakal cetha apa sing dikarepake pilih cathetan X sing ana cathetan ing Y sing ana hubungane karo kondisi kawujud. Nulis panjalukan liwat JOIN - entuk sawetara nilai pk kaping pirang-pirang (persis pira entri sing cocog katon ing Y). Carane mbusak? temtunipun DISTINCT!

Iku utamané "gratifying" nalika kanggo saben X-rekaman ana sawetara atus related Y-rekaman, lan banjur duplikat heroically dibusak ...

Carane ndandani? Kanggo miwiti, elinga yen masalah bisa diowahi "Pilih cathetan X sing ing Y ana paling ora siji sing digandhengake karo kondisi sing wis rampung" - sawise kabeh, kita ora perlu apa-apa saka Y-rekaman dhewe.

Nested ana

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

Sawetara versi PostgreSQL ngerti yen ing EXISTS cukup kanggo nemokake entri pisanan sing muncul, sing luwih lawas ora. Mulane aku luwih seneng nuduhake LIMIT 1 ing njero EXISTS.

LATERAL GABUNG

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 sing padha ngidini, yen perlu, bali sawetara data saka rekaman Y sing digandhengake. Pilihan sing padha dibahas ing artikel kasebut "Antipattern PostgreSQL: rekaman langka bakal tekan tengah JOIN".

"Napa mbayar luwih": DISTINCT [ON] + LIMIT 1

Keuntungan tambahan saka transformasi pitakon kasebut yaiku kemampuan kanggo matesi kanthi gampang telusuran rekaman yen mung siji utawa sawetara sing dibutuhake, kaya ing kasus ing ngisor iki:

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

Saiki kita maca panjaluk kasebut lan nyoba ngerti apa sing diusulake DBMS:

• nyambungake tandha
• unik dening X.pk
• saka entri sing isih ana, pilih salah siji

Dadi apa sing sampeyan entuk? "mung siji entri" saka sing unik - lan yen kita njupuk salah siji sing ora unik iki, apa asil bakal ganti piye wae?.. "Lan yen ora ana bedane, kenapa mbayar luwih?"

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

Lan persis topik padha karo GROUP BY + LIMIT 1.

"Aku mung kudu takon": GROUP implisit + LIMIT

Perkara sing padha dumadi ing beda mriksa non-kosong tandha utawa CTEs nalika panyuwunan terus:

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

Fungsi agregat (count/min/max/sum/...) kasil dieksekusi ing kabeh set, sanajan tanpa instruksi sing jelas GROUP BY. Mung karo LIMIT padha ora grapyak banget.

Pangembang bisa mikir "Yen ana cathetan, mula aku ora butuh luwih saka LIMIT". Nanging aja ngono! Amarga kanggo dhasar yaiku:

• ngetung apa sing dikarepake miturut kabeh cathetan
• menehi minangka akeh baris sing takon

Gumantung ing kondisi target, iku cocok kanggo nggawe salah siji saka substitusi ing ngisor iki:

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

"Pinten kanggo nyumerepi ing gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Pangembang naif bisa kanthi tulus percaya yen panjaluk kasebut bakal mandheg dieksekusi. sanalika kita nemokake $1 saka nilai beda pisanan sing ditemokake.

Kadhangkala ing mangsa ngarep iki bisa lan bakal bisa digunakake amarga simpul anyar Indeks Skip Scan, implementasine sing saiki lagi digarap, nanging durung.

Kanggo saiki dhisik kabeh cathetan bakal dijupuk, unik, lan mung saka wong-wong mau jumlah sing dijaluk bakal bali. Iku utamané sedih yen kita wanted soko kaya $ 1 = 4, lan ana atusan ewu cathetan ing tabel...

Supaya ora sedhih kanthi sia-sia, ayo nggunakake pitakon rekursif "DISTINCT kanggo wong miskin" saka PostgreSQL Wiki:

Source: www.habr.com