Antipatterns de PostgreSQL: "Només en queda un!"

En SQL, descriu "què" voleu aconseguir, no "com" s'ha d'executar. Per tant, el problema de desenvolupar consultes SQL a l'estil de "tal com s'escolta és com s'escriu" ocupa el seu lloc d'honor, juntament amb característiques del càlcul de condicions en SQL.

Avui, utilitzant exemples extremadament senzills, anem a veure a què pot comportar això en el context d'ús GROUP/DISTINCT и LIMIT amb ells.

Ara, si heu escrit a la sol·licitud "primer connecteu aquests signes i després llenceu tots els duplicats, només n'hauria de quedar un còpia per a cada clau" - això és exactament com funcionarà, encara que la connexió no fos necessària.

I de vegades tens sort i "només funciona", de vegades té un efecte desagradable en el rendiment i de vegades produeix efectes completament inesperats des del punt de vista del desenvolupador.

Bé, potser no és tan espectacular, però...

“Dolça parella”: UNEIX-TE + DISTINGUEIX

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

Quedaria clar què volien seleccioneu els registres X per als quals hi hagi registres a Y relacionats amb la condició complerta. Va escriure una sol·licitud a través de JOIN — va obtenir alguns valors pk diverses vegades (exactament quantes entrades adequades van aparèixer a Y). Com eliminar? Certament DISTINCT!

És especialment "gratificant" quan per a cada registre X hi ha diversos centenars de registres Y relacionats, i després els duplicats s'eliminen heroicament...

Com arreglar? Per començar, tingueu en compte que el problema es pot modificar "seleccioneu els registres X per als quals a Y hi ha ALMENÍS UN associat amb la condició complerta" - Al cap i a la fi, no necessitem res del propi registre Y.

Anidat EXISTEIX

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

Algunes versions de PostgreSQL entenen que a EXISTS n'hi ha prou amb trobar la primera entrada que apareix, les més antigues no. Per això prefereixo indicar sempre LIMIT 1 dins EXISTS.

UNIÓ LATERAL

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;

La mateixa opció permet, si cal, retornar immediatament algunes dades del registre Y associat associat. Una opció similar es parla a l'article "Antipatterns de PostgreSQL: un registre rar arribarà a la meitat d'un JOIN".

"Per què pagar més": DISTINCT [ACTIVAT] + LÍMIT 1

Un avantatge addicional d'aquestes transformacions de consulta és la possibilitat de limitar fàcilment la cerca de registres si només se'n necessita un o alguns, com en el cas següent:

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

Ara llegim la sol·licitud i intentem entendre què es proposa fer el SGBD:

• connectant els senyals
• únic per X.pk
• de les entrades restants, seleccioneu-ne una

Aleshores, què has aconseguit? "Només una entrada" dels únics -i si prenem aquest dels no únics, canviarà d'alguna manera el resultat?... "I si no hi ha diferència, per què pagar més?"

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

I exactament el mateix tema amb GROUP BY + LIMIT 1.

“Només he de preguntar”: implícit GRUP + LÍMIT

Coses semblants ocorren en diferents controls de no buit signes o CTE a mesura que avança la sol·licitud:

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

Funcions agregades (count/min/max/sum/...) s'executen correctament a tot el conjunt, fins i tot sense instruccions explícites GROUP BY. Només amb LIMIT no són gaire amables.

El desenvolupador pot pensar "Si hi ha registres, no necessito més que LIMIT". Però no ho facis! Perquè per a la base és:

• compta el que volen segons tots els registres
• donar tantes línies com demanen

Depenent de les condicions objectiu, convé fer una de les substitucions següents:

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

“Quant penjar en grams”: DISTINCT + LÍMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un desenvolupador ingenu pot creure sincerament que la sol·licitud deixarà d'executar-se. tan bon punt trobem $1 dels primers valors diferents que es troben.

En el futur, això pot funcionar i funcionarà gràcies a un nou node Escaneig de saltar índex, la implementació del qual s'està treballant actualment, però encara no.

De moment primer es recuperaran tots els registres, són únics, i només d'ells es retornarà l'import sol·licitat. És especialment trist si volíem alguna cosa així $ 1 = 4, i hi ha centenars de milers de registres a la taula...

Per no estar tristos en va, utilitzem una consulta recursiva "DISTINCT és per als pobres" de PostgreSQL Wiki:

Font: www.habr.com