PostgreSQL Antipatterns: "Panofanira kunge paine imwe chete!"

MuSQL, unotsanangura "chii" chaunoda kuwana, kwete "sei" chinofanira kuitwa. Naizvozvo, dambudziko rekugadzira mibvunzo yeSQL muchimiro che "sekunzwikwa kwayakaitwa" inotora nzvimbo yerukudzo, pamwe chete. peculiarities of condition evaluation muSQL.

Nhasi, tichishandisa mienzaniso yakapusa, ngationei kuti izvi zvinogona kutungamira kunei mumamiriro ekushandisa GROUP/DISTINCT и LIMIT navo.

Ndizvo kana wakanyora mukukumbira “Tanga wabatanidza mahwendefa aya, wobva warasa mamwe makopi, panofanira kuva neimwe chete muenzaniso wekiyi yega yega" - izvi ndizvo chaizvo zvichashanda, kunyangwe kubatana kwacho kwaisadikanwa zvachose.

Uye dzimwe nguva unoita rombo rakanaka uye "inongoshanda", dzimwe nguva ine isingafadzi mhedzisiro pakuita, uye dzimwe nguva inopa mhedzisiro isingatarisirwe zvachose kubva pakuona kwemugadziri.

Zvakanaka, pamwe hazvisi zvinoshamisa, asi ...

"Sweet couple": JOIN + DISTINCT

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

Ko zvaizobuda pachena sei kuti vaidei sarudza zvinyorwa zvakadaro X, izvo muY zvinobatanidzwa nemamiriro akazadzikiswa. Akatumira chikumbiro kuburikidza JOIN - yakagamuchira humwe hunokosha hwepk kakawanda (chaizvo kuti mangani marekodhi akakodzera akave ari muY). Nzira yekubvisa sei? Zvirokwazvo DISTINCT!

Zvinonyanya "kunakidza" kana kune yega X-rekodhi paine mazana akati wandei ane hukama maY-rekodhi, uyezve zvakapetwa zvinobviswa zvine hugamba ...

Nzira yekugadzirisa sei? Kutanga, ziva kuti basa racho rinogona kugadziriswa kuti rive "sarudza iwo marekodhi X ane ZVINOITA IMWE muY inobatana nemamiriro ari kuzadzikiswa" - mushure mezvose, isu hatidi chero chinhu kubva kuY-rekodhi pachayo.

Nested EXISTS

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

Dzimwe shanduro dzePostgreSQL dzinonzwisisa kuti mu EXISTS zvakakwana kuti uwane rekodhi rekutanga rinouya, vakuru havadaro. Naizvozvo, ndinosarudza kugara ndichiratidzira LIMIT 1 mukati 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;

Sarudzo imwechete inobvumira, kana zvichidikanwa, kukurumidza kudzorera imwe data kubva kune yakawanikwa yakabatana Y-rekodhi panguva imwe chete. Sarudzo yakafanana inokurukurwa munyaya yacho "PostgreSQL Antipatterns: isingawanzo rekodhi inosvika pakati peJOIN".

"Nei uchibhadhara zvakawanda": DISTINCT [ON] + LIMIT 1

Imwezve mukana wekushandurwa kwemubvunzo wakadai kugona kudzikamisa nyore kuverengerwa kwemarekodhi kana imwe chete / mashoma acho achidiwa, sezviri muchiitiko chinotevera:

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

Iye zvino tinoverenga chikumbiro uye edza kunzwisisa izvo DBMS inofanirwa kuita:

• tinobatanidza mahwendefa
• rakasiyana neX.pk
• sarudza chimwe chezvinyorwa zvasara

Saka wawanei? "Imwe rekodhi" kubva kune akasiyana - uye kana iwe ukatora iyi isiri-yakasarudzika, mhedzisiro yacho ichachinja here? .. "Uye kana pasina mutsauko, sei uchibhadhara?"

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

Uye dingindira rimwechete naro GROUP BY + LIMIT 1.

"Ini ndofanira kubvunza": ichokwadi GROUP + LIMIT

Zvinhu zvakafanana zvinoitika mune zvakasiyana macheki asina maturo mavara kana maCTE apo chikumbiro chinoenderera mberi:

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

Aggregate mabasa (count/min/max/sum/...) vanoitwa zvinobudirira pane yese seti, kunyangwe pasina kujekesa GROUP BY. Pano chete ne LIMIT havana ushamwari zvakanyanya.

Mugadziri anogona kufunga "Zvino, kana paine zvinyorwa ipapo, saka ini handidi zvinopfuura LIMIT". Asi iwe haufanirwe kudaro! Nekuti kune hwaro ndiko:

• verenga zvavanoda pamarekodhi ese
• vape mitsetse yakawanda sezvavanokumbira

Zvichienderana nemamiriro ezvinhu anotarirwa, zvakakodzera kuita imwe yeiyi inotsiva:

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

"Yakawanda sei yekurembera mumagiramu": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Mugadziri asina ruzivo anogona kutenda nemoyo wese kuti kuitwa kwechikumbiro kuchamira, patinongowana yekutanga $ 1 akasiyana maitiro anouya.

Imwe nguva mune ramangwana, izvi zvinogona uye zvichashanda nekutenda kune itsva node Index Skip Scan, kushandiswa kwaro kuri kuitwa, asi kwete.

So far kutanga marekodhi ese achatorwa, akasiyana, uye akawanda awo akumbirwa chete ndiwo achadzorerwa. Zvinonyanya kusuruvara kana taida chimwe chinhu chakadaro $ 1 = 4, uye kune mazana ezviuru zvezvinyorwa mutafura ...

Kuti tirege kusuruvara pasina, isu tichashandisa mubvunzo wekudzokorora "DISTINCT yevarombo" kubva kuPostgreSQL Wiki:

Source: www.habr.com