😜 🎎 👃🏿 Antipatterns PostgreSQL: "Il ne devrait en rester qu'un seul!" 👔 🦉 🐁

En SQL, vous décrivez «ce que» vous voulez obtenir, pas «comment» cela doit être fait. Par conséquent, le problème du développement de requêtes SQL dans le style "tel que vous l'entendez pendant que vous écrivez" prend sa place d'honneur, ainsi que les particularités du calcul des conditions en SQL .

Aujourd'hui, à l'aide d'exemples extrêmement simples, voyons ce que cela peut conduire dans le contexte de l'utilisation GROUP/DISTINCTet LIMITavec eux.

Maintenant, si vous avez écrit dans la demande "connectez d'abord ces plaques, puis supprimez tous les doublons, il ne devrait y avoir qu'une seule copie pour chaque clé" - c'est exactement ainsi que cela fonctionnera, même si la connexion n'était pas du tout nécessaire.

Et parfois vous avez de la chance et cela "fonctionne juste", parfois cela a un effet désagréable sur les performances, et parfois cela donne des effets absolument inattendus du point de vue du développeur.

Eh bien, peut-être pas si spectaculaire, mais ...

"Sweet couple": REJOIGNEZ + DISTINCT

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

Comment serait-il clair que nous voulions sélectionner de tels enregistrements X, pour lesquels Y a ceux associés à une condition remplie . Nous avons écrit une requête à travers JOIN- nous avons obtenu des valeurs pk plusieurs fois (exactement combien d'enregistrements correspondants dans Y se sont avérés être). Comment enlever? Bien sûr DISTINCT!

C'est particulièrement "heureux" quand pour chaque enregistrement X il y a plusieurs centaines d'enregistrements Y liés, puis les doublons sont héroïquement supprimés ...

Comment y remédier? Pour commencer, sachez que la tâche peut être modifiée pour «sélectionner de tels enregistrements X pour lesquels Y a AU MOINS UN associé à une condition en cours d'exécution» - après tout, nous n'avons besoin de rien de l'enregistrement Y lui-même.

EXISTES imbriqués

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

Certaines versions de PostgreSQL comprennent qu'il suffit de trouver le premier enregistrement disponible dans EXISTS, les plus anciennes ne le font pas. Par conséquent, je préfère toujours spécifier à l' LIMIT 1intérieur EXISTS.

JOINDRE LATÉRAL

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 même option permet, si nécessaire, en même temps de renvoyer immédiatement certaines données de l'enregistrement Y lié trouvé. Une option similaire est discutée dans l'article "Antipatterns PostgreSQL: une entrée rare volera au milieu d'un JOIN" .

"Pourquoi payer plus": DISTINCT [ON] + LIMIT 1

Un avantage supplémentaire de ces transformations de requête est la possibilité de restreindre facilement l'itération sur les enregistrements si seulement un / plusieurs d'entre eux sont nécessaires, comme dans le cas suivant:

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

Maintenant, nous lisons la demande et essayons de comprendre ce que le SGBD propose de faire:

nous connectons les plaques
uniqueizable par X.pk
sélectionnez-en un parmi les enregistrements restants

Autrement dit, vous avez quoi? "Un seul enregistrement" des uniques - et si vous prenez celui-ci des non-uniques, le résultat changera-t-il d'une manière ou d'une autre? .. "Et s'il n'y a pas de différence, pourquoi payer plus?"

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

Et exactement le même sujet avec GROUP BY + LIMIT 1.

"Je viens de demander": GROUPE implicite + LIMITE

Des choses similaires sont rencontrées lors de différents contrôles de non-vide de la plaque ou du CTE lors de l'exécution de la requête:

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

Les fonctions d'agrégation ( count/min/max/sum/...) s'exécutent avec succès sur l'ensemble complet, même sans indication explicite GROUP BY. Seulement LIMITils ne sont pas très amicaux avec eux.

Le développeur peut penser "s'il y a des enregistrements là-bas, alors je n'ai plus besoin de LIMIT" . Mais non! Parce que pour la base c'est:

comptez ce que vous voulez sur tous les disques
donne autant de lignes que tu le demandes

En fonction des conditions cibles, il convient de faire une des substitutions ici:

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

"Combien pendre en grammes": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un développeur naïf peut honnêtement penser que la requête s'arrêtera dès que nous trouverons les premières valeurs différentes de 1 $ qui se présentent .

Dans le futur, cela peut fonctionner et fonctionnera grâce au nouveau nœud Index Skip Scan , dont la mise en œuvre est actuellement en cours d'élaboration, mais pas encore.

Jusqu'à présent, dans un premier temps, tous les enregistrements seront récupérés , uniques et seuls seront renvoyés le nombre demandé. C'est particulièrement triste si nous voulions quelque chose comme $ 1 = 4 , et il y a des centaines de milliers d'enregistrements dans le tableau ...

Afin de ne pas être triste en vain, nous utiliserons la requête récursive "DISTINCT pour les pauvres" du wiki PostgreSQL :

Antipatterns PostgreSQL: "Il ne devrait en rester qu'un seul!"