PostgreSQL Antipatterns: "D'r moat mar ien wêze!"

Yn SQL beskriuwe jo "wat" jo wolle berikke, net "hoe" it moat wurde útfierd. Dêrom nimt it probleem fan it ûntwikkeljen fan SQL-fragen yn 'e styl fan "sa't it wurdt heard is hoe't it skreaun is" syn eareplak, tegearre mei skaaimerken fan berekkenjen betingsten yn SQL.

Lit ús hjoed, mei ekstreem ienfâldige foarbylden, sjen wêr't dit ta kin liede yn 'e kontekst fan gebrûk GROUP/DISTINCT и LIMIT mei harren.

No, as jo yn it fersyk skreaun hawwe "Earst ferbine dizze tekens, en smyt dan alle duplikaten út, der moat mar ien oerbliuwe kopiearje foar elke kaai" - dit is krekt hoe't it sil wurkje, sels as de ferbining hielendal net nedich wie.

En soms binne jo gelok en it "wurket gewoan", soms hat it in onaangenaam effekt op prestaasjes, en soms jout it effekten dy't folslein ûnferwacht binne út it eachpunt fan 'e ûntwikkelders.

No, miskien net sa spektakulêr, mar ...

"Sweet pear": JOIN + DISTINCT

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

It soe dúdlik wêze wat se woene selektearje records X wêrfoar records yn Y binne dy't relatearre binne oan de foldien betingst. Skreau in fersyk fia JOIN - krige ferskate kearen wat pk-wearden (krekt hoefolle geskikte yngongen ferskynden yn Y). Hoe ferwiderje? Wis DISTINCT!

It is foaral "befredigend" as d'r foar elke X-record ferskate hûndert relatearre Y-records binne, en dan wurde de duplikaten heroysk fuorthelle ...

Hoe reparearje? Om te begjinnen, realisearje dat it probleem kin wurde wizige oan "selektearje records X wêrfoar yn Y MINSTEIN ONE is assosjearre mei de foldien betingst" - wy hawwe ommers neat nedich fan it Y-record sels.

Nested BESTAAT

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

Guon ferzjes fan PostgreSQL begripe dat yn EXISTS it genôch is om de earste yngong te finen dy't opkomt, âldere net. Dêrom wol ik leaver altyd oanjaan LIMIT 1 binnen 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;

Deselde opsje lit, as it nedich is, fuortendaliks guon gegevens weromjaan fan it fûn assosjearre Y-record. In fergelykbere opsje wurdt besprutsen yn it artikel "PostgreSQL Antipatterns: in seldsum rekord sil it midden fan in JOIN berikke".

"Wêrom mear betelje": DISTINCT [ON] + LIMIT 1

In ekstra foardiel fan sokke querytransformaasjes is de mooglikheid om it sykjen nei records maklik te beheinen as mar ien of in pear fan har nedich binne, lykas yn it folgjende gefal:

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

No lêze wy it fersyk en besykje te begripen wat de DBMS wurdt foarsteld om te dwaan:

• ferbinen de tekens
• unyk troch X.pk
• út de oerbleaune yngongen, selektearje ien

Dus wat hast krigen? "Mar ien yngong" fan 'e unike - en as wy dizze ien fan' e net-unike nimme, sil it resultaat dan op ien of oare manier feroarje? .. "En as d'r gjin ferskil is, wêrom dan mear betelje?"

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

En krekt itselde ûnderwerp mei GROUP BY + LIMIT 1.

"Ik moat gewoan freegje": ymplisyt GROUP + LIMIT

Fergelykbere dingen komme op ferskillende net-leechheidskontrôles tekens as CTE's as it fersyk foarútgiet:

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

Aggregate funksjes (count/min/max/sum/...) wurde mei súkses útfierd op 'e heule set, sels sûnder eksplisite ynstruksjes GROUP BY. Allinne mei LIMIT se binne net hiel freonlik.

De ûntwikkelder kin tinke "as d'r records binne, dan haw ik net mear nedich as LIMIT". Mar doch dat net! Want foar de basis is it:

• telle wat se wolle neffens alle records
• jou safolle rigels as se freegje

Ofhinklik fan 'e doelbetingsten is it passend om ien fan' e folgjende ferfangings te meitsjen:

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

"Hoefolle te hingjen yn grammen": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

In naïve ûntwikkelder kin oprjocht leauwe dat it fersyk sil stopje mei it útfieren. sa gau as wy $1 fine fan 'e earste ferskillende wearden dy't tsjinkomme.

Eartiids yn 'e takomst kin en sil dit wurkje tanksij in nije knooppunt Index Skip Scan, wêrfan de útfiering no útwurke wurdt, mar noch net.

Foar no earst alle records wurde ophelle, binne unyk, en allinich fan har sil it frege bedrach weromjûn wurde. It is foaral spitich as wy sokssawat woenen $ 1 = 4, en d'r binne hûnderttûzenen records yn 'e tabel ...

Om net om 'e nocht tryst te wêzen, litte wy in rekursive query brûke "DISTINCT is foar de earmen" fan PostgreSQL Wiki:

Boarne: www.habr.com