Antipatterns PostgreSQL : "Il ne doit y en avoir qu'un !"

En SQL, vous décrivez « ce » que vous souhaitez réaliser, et non « comment » cela doit être exécuté. Par conséquent, le problème du développement de requêtes SQL dans le style « tel qu'on l'entend, tel qu'il est écrit » prend sa place d'honneur, aux côtés de caractéristiques du calcul des conditions en SQL.

Aujourd’hui, à l’aide d’exemples extrêmement simples, voyons à quoi cela peut conduire dans le cadre d’une utilisation GROUP/DISTINCT и LIMIT avec eux.

Maintenant, si vous avez écrit dans la demande "Connectez d'abord ces signes, puis jetez tous les doublons, il ne devrait en rester qu'un 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 », parfois cela a un effet désagréable sur les performances, et parfois cela donne des effets complètement inattendus du point de vue du développeur.

Bon, peut-être pas si spectaculaire, mais...

« Doux couple » : REJOINDRE + DISTINCTER

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

Ce serait clair ce qu'ils voulaient sélectionner les enregistrements X pour lesquels il existe des enregistrements dans Y qui sont liés à la condition remplie. J'ai écrit une demande via JOIN - a obtenu des valeurs pk plusieurs fois (combien exact d'entrées appropriées sont apparues dans Y). Comment enlever? Certainement DISTINCT!

C'est particulièrement « gratifiant » lorsque pour chaque enregistrement X, il y a plusieurs centaines d'enregistrements Y associés, puis les doublons sont héroïquement supprimés...

Comment réparer? Pour commencer, sachez que le problème peut être modifié pour « sélectionner les enregistrements X pour lesquels dans Y il y en a AU MOINS UN associé à la condition remplie » - après tout, nous n'avons besoin de rien de l'enregistrement Y lui-même.

EXISTE imbriqué

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

Certaines versions de PostgreSQL comprennent que dans EXISTS, il suffit de trouver la première entrée qui apparaît, les plus anciennes ne le font pas. C'est pourquoi je préfère toujours indiquer LIMIT 1 à l'intérieur EXISTS.

JOINT 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, de restituer immédiatement certaines données de l'enregistrement Y associé trouvé. Une option similaire est discutée dans l'article "Antipatterns PostgreSQL : un enregistrement rare atteindra le milieu d'un JOIN".

« Pourquoi payer plus » : DISTINCT [ON] + LIMITE 1

Un avantage supplémentaire de ces transformations de requêtes est la possibilité de limiter facilement la recherche d'enregistrements si un ou plusieurs d'entre eux seulement 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, lisons la demande et essayons de comprendre ce que le SGBD est proposé de faire :

• relier les signes
• unique par X.pk
• parmi les entrées restantes, sélectionnez-en une

Alors qu'est-ce que tu as eu ? "Une seule entrée" des uniques - et si nous prenons celui-ci parmi les 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 dois juste demander" : GROUPE implicite + LIMITE

Des choses similaires se produisent à des moments différents chèques de non vide signes ou CTE au fur et à mesure de l’avancement de la demande :

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

Fonctions d'agrégation (count/min/max/sum/...) sont exécutés avec succès sur l'ensemble de l'ensemble, même sans instructions explicites GROUP BY. Seulement avec LIMIT ils ne sont pas très sympathiques.

Le développeur peut penser "S'il y a des enregistrements là-bas, alors je n'ai besoin que de LIMIT". Mais ne fais pas ça ! Car pour la base c'est :

• compte ce qu'ils veulent selon tous les dossiers
• donne autant de lignes qu'ils le demandent

En fonction des conditions cibles, il convient d'effectuer l'une des substitutions suivantes :

• (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 peser en grammes » : DISTINCT + LIMITE

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un développeur naïf peut croire sincèrement que la requête cessera de s'exécuter. dès que nous trouvons 1 $ des premières valeurs différentes qui apparaissent.

Dans le futur, cela pourrait fonctionner et fonctionnera grâce à un nouveau nœud Analyse des sauts d'index, dont la mise en œuvre est actuellement en cours d'élaboration, mais pas encore.

Pour l'instant d'abord tous les enregistrements seront récupérés, sont uniques, et ce n'est qu'à partir d'eux que le montant demandé sera restitué. C'est particulièrement triste si nous voulions quelque chose comme $ 1 = 4, et il y a des centaines de milliers d'enregistrements dans la table...

Pour ne pas être triste en vain, utilisons une requête récursive "DISTINCT est pour les pauvres" de PostgreSQL Wiki:

Source: habr.com