ProHoster > Blog > Pangangasiwa > Mga Antipattern ng PostgreSQL: "Dapat isa lang!"

Mga Antipattern ng PostgreSQL: "Dapat isa lang!"

Sa SQL, inilalarawan mo ang "ano" na gusto mong makamit, hindi "kung paano" ito dapat isagawa. Samakatuwid, ang problema sa pagbuo ng mga query sa SQL sa estilo ng "tulad ng narinig ay kung paano ito isinulat" ay pumapalit sa lugar ng karangalan, kasama ang mga tampok ng pagkalkula ng mga kondisyon sa SQL.

Ngayon, gamit ang napakasimpleng mga halimbawa, tingnan natin kung ano ang maaaring humantong sa konteksto ng paggamit GROUP/DISTINCT ΠΈ LIMIT kasama nila.

Ngayon, kung sumulat ka sa kahilingan "Ikonekta muna ang mga palatandaang ito, at pagkatapos ay itapon ang lahat ng mga duplicate, dapat isa na lang ang natitira kopya para sa bawat susi" - ito ay eksakto kung paano ito gagana, kahit na ang koneksyon ay hindi kinakailangan sa lahat.

At kung minsan ikaw ay mapalad at ito ay "gumagana lamang", kung minsan ito ay may hindi kasiya-siyang epekto sa pagganap, at kung minsan ay nagbibigay ito ng mga epekto na ganap na hindi inaasahan mula sa punto ng view ng developer.

Mga Antipattern ng PostgreSQL: "Dapat isa lang!"
Well, marahil ay hindi masyadong kamangha-manghang, ngunit...

β€œSweet couple”: SUMALI + DISTINCT

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

Magiging malinaw kung ano ang gusto nila piliin ang mga tala X kung saan mayroong mga tala sa Y na nauugnay sa natupad na kondisyon. Sumulat ng isang kahilingan sa pamamagitan ng JOIN β€” nakakuha ng ilang pk value ng ilang beses (eksaktong kung gaano karaming angkop na mga entry ang lumitaw sa Y). Paano tanggalin? tiyak DISTINCT!

Ito ay lalo na "kasiya-siya" kapag para sa bawat X-record mayroong ilang daang nauugnay na Y-record, at pagkatapos ay ang mga duplicate ay bayanihang tinanggal...

Mga Antipattern ng PostgreSQL: "Dapat isa lang!"

Paano ayusin? Upang magsimula sa, mapagtanto na ang problema ay maaaring baguhin sa "pumili ng mga tala X kung saan sa Y ay may KAHIT ISA na nauugnay sa natupad na kondisyon" - pagkatapos ng lahat, hindi namin kailangan ng anuman mula sa Y-record mismo.

Nested EXISTS

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

Ang ilang mga bersyon ng PostgreSQL ay nauunawaan na sa EXISTS ay sapat na upang mahanap ang unang entry na lalabas, ang mga nakatatanda ay hindi. Samakatuwid mas gusto kong palaging ipahiwatig LIMIT 1 sa loob EXISTS.

LATERAL JOIN

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;

Ang parehong opsyon ay nagbibigay-daan, kung kinakailangan, upang agad na ibalik ang ilang data mula sa natagpuang nauugnay na Y-record. Ang isang katulad na pagpipilian ay tinalakay sa artikulo "Mga Antipattern ng PostgreSQL: ang isang bihirang tala ay aabot sa gitna ng isang SUMALI".

β€œBakit magbayad ng higit pa”: DISTINCT [ON] + LIMIT 1

Ang isang karagdagang benepisyo ng naturang mga pagbabago sa query ay ang kakayahang madaling limitahan ang paghahanap para sa mga talaan kung kailangan lamang ng isa o ilan sa mga ito, tulad ng sa sumusunod na kaso:

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

Ngayon ay binabasa namin ang kahilingan at sinisikap na maunawaan kung ano ang iminungkahing gawin ng DBMS:

  • pag-uugnay ng mga palatandaan
  • natatangi ng X.pk
  • mula sa natitirang mga entry, pumili ng isa

Kaya ano ang nakuha mo? "Isang entry lang" from the unique ones - and if we take this one of the non-unique ones, will the result change somehow?.. β€œAnd if there is no difference, why pay more?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ΄ΡΡƒΠ½ΡƒΡ‚ΡŒ подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

At eksakto ang parehong paksa sa GROUP BY + LIMIT 1.

β€œMay itatanong lang ako”: implicit GROUP + LIMIT

Ang mga katulad na bagay ay nangyayari sa iba't ibang paraan non-emptiness checks mga palatandaan o CTE habang umuusad ang kahilingan:

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

Pinagsama-samang mga function (count/min/max/sum/...) ay matagumpay na naisakatuparan sa buong set, kahit na walang tahasang mga tagubilin GROUP BY. Kasama lamang LIMIT hindi sila masyadong palakaibigan.

Maaaring mag-isip ang developer "kung may mga talaan doon, kailangan ko ng hindi hihigit sa LIMIT". Ngunit huwag gawin iyon! Dahil para sa base ito ay:

  • bilangin ang gusto nila ayon sa lahat ng mga talaan
  • magbigay ng maraming linya hangga't hinihiling nila

Depende sa target na kundisyon, angkop na gawin ang isa sa mga sumusunod na pamalit:

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

"Magkano ang mabibitin sa gramo": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ang isang walang muwang na developer ay maaaring taos-pusong naniniwala na ang kahilingan ay hihinto sa pagpapatupad. sa sandaling mahanap namin ang $1 ng unang magkakaibang mga halaga na makikita.

Sa hinaharap, maaari at gagana ito salamat sa isang bagong node Index Laktawan Scan, ang pagpapatupad nito ay kasalukuyang ginagawa, ngunit hindi pa.

Sa ngayon muna lahat ng mga tala ay kukunin, ay natatangi, at mula sa kanila lamang ibabalik ang hinihiling na halaga. Nakakalungkot lalo na kung may gusto tayo $ 1 = 4, at mayroong daan-daang libong mga tala sa talahanayan...

Upang hindi malungkot nang walang kabuluhan, gumamit tayo ng recursive query Ang "DISTINCT ay para sa mahihirap" mula sa PostgreSQL Wiki:

Mga Antipattern ng PostgreSQL: "Dapat isa lang!"

Pinagmulan: www.habr.com

Magdagdag ng komento