Protivzorci PostgreSQL: "Mora biti samo eden!"

V SQL opisujete, "kaj" želite dobiti, ne "kako" naj bi to naredili. Zato problem razvijanja SQL poizvedb v slogu »kakor se sliši, tako se piše« zaseda svoje častno mesto skupaj z posebnosti vrednotenja stanja v SQL.

Danes si na izjemno preprostih primerih poglejmo, do česa lahko to privede v kontekstu uporabe GROUP/DISTINCT и LIMIT z njimi.

To je, če ste napisali v zahtevi »Najprej povežite te tablice in nato vrzite ven vse dvojnike, mora biti samo eden primerek za vsak ključ" - točno tako bo delovalo, tudi če povezava sploh ni bila potrebna.

In včasih imate srečo in "preprosto deluje", včasih ima neprijeten učinek na delovanje, včasih pa daje učinke, ki so z vidika razvijalca popolnoma nepričakovani.

No, morda ne tako spektakularno, ampak …

"Sladki par": JOIN + DISTINCT

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

Kako bi bilo jasno, kaj so hoteli izberite takšne zapise X, za katere so v Y povezani z izpolnjenim pogojem. Oddano povpraševanje prek JOIN - večkrat prejel nekaj vrednosti pk (natančno koliko primernih zapisov se je izkazalo v Y). Kako odstraniti? Vsekakor DISTINCT!

Še posebej "prijetno" je, ko je za vsak X-zapis nekaj sto povezanih Y-zapisov, nato pa se dvojniki junaško odstranijo ...

Kako popraviti? Za začetek se zavedajte, da je nalogo mogoče spremeniti v "izberi tiste zapise X, za katere je VSAJ EN v Y povezan z izpolnjenim pogojem" - navsezadnje ne potrebujemo ničesar od samega Y-zapisa.

Ugnezdeni OBSTAJA

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

Nekatere različice PostgreSQL razumejo, da je v EXISTS dovolj, da poiščete prvi zapis, na katerega naletite, starejše pa ne. Zato raje vedno navedem LIMIT 1 znotraj EXISTS.

STRANSKI SPOJ

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;

Ista možnost omogoča, da po potrebi takoj vrnete nekaj podatkov iz najdenega povezanega Y-zapisa hkrati. Podobna možnost je obravnavana v članku "PostgreSQL Antipatterns: redki zapis bo dosegel sredino JOIN".

"Zakaj plačati več": DISTINCT [ON] + LIMIT 1

Dodatna prednost takšnih transformacij poizvedb je zmožnost enostavne omejitve oštevilčenja zapisov, če je potreben le eden/nekaj od njih, kot v naslednjem primeru:

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

Zdaj preberemo zahtevo in poskušamo razumeti, kaj naj bi DBMS naredil:

• plošče povežemo
• unikat X.pk
• izberite enega od preostalih zapisov

Kaj si torej dobil? "Ena plošča" od edinstvenih - in če vzamete to eno od needinstvenih, se bo rezultat nekako spremenil? .. "In če ni razlike, zakaj plačati več?"

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

In popolnoma ista tema z GROUP BY + LIMIT 1.

"Samo vprašati moram": implicitna SKUPINA + OMEJITEV

Podobne stvari se pojavljajo v različnih preverjanja nepraznine oznake ali CTE, ko zahteva napreduje:

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

Agregatne funkcije (count/min/max/sum/...) se uspešno izvedejo na celotnem nizu, tudi brez izrecne navedbe GROUP BY. Samo tukaj z LIMIT niso zelo prijazni.

Razvijalec lahko razmišlja "Če so tam zapisi, potem ne potrebujem več kot LIMIT". Ampak ni ti treba! Ker je za osnovo:

• računajo, kar hočejo na vseh zapisih
• dajte toliko vrstic, kot jih zahtevajo

Glede na ciljne pogoje je primerno narediti eno od naslednjih zamenjav:

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

"Koliko obesiti v gramih": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivni razvijalec lahko iskreno verjame, da se bo izvedba zahteve ustavila, takoj, ko najdemo prvih 1 $ različnih vrednosti, na katere naletimo.

Nekoč v prihodnosti bo to morda delovalo in bo delovalo zahvaljujoč novemu vozlišču Index Skip Scan, katerega implementacija je trenutno v pripravi, vendar še ne.

Zaenkrat najprej vsi zapisi bodo pridobljeni, so edinstveni in vrnjenih bo samo toliko od njih, kot je zahtevano. Še posebej žalostno je, če smo želeli kaj takega $ 1 = 4, v tabeli pa je na stotine tisoč zapisov ...

Da ne bomo žalostni zaman, bomo uporabili rekurzivno poizvedbo "DISTINCT for the Poor" iz PostgreSQL Wiki:

Vir: www.habr.com