PostgreSQL Antipatterns: "Það verður að vera aðeins eitt!"

Í SQL lýsir þú „hvað“ þú vilt fá, ekki „hvernig“ það ætti að gera. Þess vegna tekur vandamálið við að þróa SQL fyrirspurnir í stíl við „eins og það heyrist er hvernig það er skrifað“ í heiðurssess, ásamt sérkenni ástandsmats í SQL.

Í dag, með því að nota mjög einföld dæmi, skulum við sjá hvað þetta getur leitt til í samhengi við notkun GROUP/DISTINCT и LIMIT með þeim.

Það er ef þú skrifaðir í beiðnina „Tengdu fyrst þessi skilti og hentu síðan út öllum afritunum, það ætti bara að vera einn dæmi fyrir hvern lykil" - þetta er nákvæmlega hvernig það mun virka, jafnvel þótt tengingin væri alls ekki þörf.

Og stundum ertu heppinn og það „virkar bara“, stundum hefur það óþægileg áhrif á frammistöðu og stundum gefur það áhrif sem eru algjörlega óvænt frá sjónarhóli þróunaraðilans.

Jæja, kannski ekki eins stórkostlegt, en…

„Sætt par“: JOIN + DISTINCT

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

Það er ljóst hvað þú vilt velja slíkar færslur X, sem í Y eru tengdar við uppfyllt skilyrði. Lögð fram beiðni í gegnum JOIN - fékk nokkur gildi pk nokkrum sinnum (nákvæmlega hversu margar viðeigandi færslur reyndust vera í Y). Hvernig á að fjarlægja? Svo sannarlega DISTINCT!

Það er sérstaklega „skemmtilegt“ þegar fyrir hverja X-skrá eru nokkur hundruð tengdar Y-skrár, og síðan eru afrit fjarlægðar hetjulega ...

Hvernig á að laga? Til að byrja með, gerðu þér grein fyrir því að hægt er að breyta verkefninu í "veljið þær færslur X sem það er A.m.k. EIN í Y sem tengist því skilyrði sem er uppfyllt" - þegar allt kemur til alls þurfum við ekkert af Y-plötunni sjálfri.

Hreiður ER TIL

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

Sumar útgáfur af PostgreSQL skilja að í EXISTS er nóg að finna fyrstu plötuna sem rekst á, eldri ekki. Þess vegna kýs ég að gefa alltaf til kynna LIMIT 1 innan EXISTS.

HÍÐARSAMÞING

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;

Sami valkostur gerir, ef nauðsyn krefur, að skila strax sumum gögnum úr tengdri Y-skrá sem fannst á sama tíma. Svipaður valkostur er ræddur í greininni „PostgreSQL Antipatterns: sjaldgæf skrá mun ná miðju JOIN“.

„Af hverju að borga meira“: DISTINCT [ON] + LIMIT 1

Viðbótarkostur við slíkar fyrirspurnabreytingar er hæfileikinn til að takmarka upptalningu skráa á auðveldan hátt ef aðeins er þörf á einum / fáum þeirra, eins og í eftirfarandi tilfelli:

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

Nú lesum við beiðnina og reynum að skilja hvað DBMS á að gera:

• við tengjum plöturnar
• einstakt af X.pk
• veldu eina af færslunum sem eftir eru

Svo hvað fékkstu? "Einhver plata" frá þeim einstöku - og ef þú tekur þennan af þeim sem ekki eru einstöku, mun niðurstaðan einhvern veginn breytast? .. "Og ef það er enginn munur, af hverju að borga meira?"

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

Og nákvæmlega sama þema með GROUP BY + LIMIT 1.

"Ég verð bara að spyrja": óbeint GRÓP + LIMIT

Svipaðir hlutir eiga sér stað í mismunandi tómleikaathuganir merki eða CTE eftir því sem beiðnin heldur áfram:

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

Samanlagt aðgerðir (count/min/max/sum/...) eru keyrðar með góðum árangri á öllu settinu, jafnvel án þess að tilgreina það sérstaklega GROUP BY. Aðeins hér með LIMIT þeir eru ekki mjög vinalegir.

Framkvæmdaraðilinn getur hugsað „Nú, ef það eru færslur þarna, þá þarf ég ekki meira en LIMIT“. En þú þarft ekki! Vegna þess að fyrir grunninn er það:

• telja það sem þeir vilja á öllum skrám
• gefa eins margar línur og þeir biðja um

Það fer eftir markmiðsskilyrðum, það er rétt að gera eina af eftirfarandi skiptingum:

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

„Hversu mikið á að hanga í grömmum“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Barnlaus þróunaraðili gæti trúað því í einlægni að framkvæmd beiðni muni hætta, um leið og við finnum fyrstu $1 mismunandi gildin sem rekast á.

Einhvern tíma í framtíðinni gæti og mun þetta virka þökk sé nýjum hnút Index Skip Scan, sem nú er verið að vinna að framkvæmd, en ekki enn.

Hingað til fyrst allar skrár verða sóttar, eru einstök og aðeins eins mörgum þeirra og beðið er um verður skilað. Það er sérstaklega sorglegt ef við vildum eitthvað eins og $ 1 = 4, og það eru hundruð þúsunda skráa í töflunni ...

Til þess að vera ekki dapur til einskis munum við nota endurkvæma fyrirspurn „DISTINCT for the Poor“ frá PostgreSQL Wiki:

Heimild: www.habr.com