Get best of the week

PostgreSQL Antipatterns: calcul des conditions en SQL

SQL n'est ni C ++, ni JavaScript. Par conséquent, le calcul des expressions logiques est différent, et ce n'est pas la même chose:
WHERE fncondX() AND fncondY()
= fncondX() && fncondY()

Dans le processus d'optimisation du plan d'exécution des requêtes, PostgreSQL peut "réorganiser" arbitrairement les conditions équivalentes , n'en calculer aucune pour des enregistrements individuels, l'affecter à la condition de l'index utilisé ... En bref, il est plus facile de supposer que vous ne pouvez pas contrôler dans quel ordre Les conditions des pairs seront calculées (et si elles le sont) .

Par conséquent, si vous souhaitez toujours contrôler la priorité, vous devez structurellement rendre ces conditions inégales à l' aide d' expressions et d' opérateurs conditionnels .


Les données et leur utilisation sont la base de notre complexe VLSI , il est donc très important pour nous que les opérations sur celles-ci soient effectuées non seulement correctement, mais aussi efficacement. Examinons des exemples spécifiques où des erreurs de calcul d'expression peuvent être commises et où leur efficacité mérite d'être améliorée.

# 0: RTFM


À partir exemple de la documentation :

Lorsque l'ordre de calcul est important, il peut être corrigé à l'aide de la construction CASE. Par exemple, une telle façon d'éviter de diviser par zéro dans une phrase n'est WHEREpas fiable:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Option sûre:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

La construction utilisée de cette manière CASEprotège l'expression de l'optimisation, elle ne doit donc être utilisée que si nécessaire.

# 1: condition dans le déclencheur


BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Il semble que tout a l'air bien, mais ... Personne ne promet que la pièce jointe SELECTne sera pas exécutée si la première condition est fausse. Corriger en utilisant imbriquéIF :

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Examinons maintenant attentivement - tout le corps de la fonction de déclenchement s'est avéré être «enveloppé» IF. Et cela signifie que rien ne nous empêche de supprimer cette condition de la procédure en utilisant la condition WHEN-condition :

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Cette approche permet une économie garantie des ressources du serveur dans de fausses conditions.

# 2: Chaîne OU / ET


SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Sinon, vous pouvez obtenir que les deux EXISTSseront "vrais", mais les deux seront remplis .

Mais si nous savons avec certitude que l'une d'elles est «vraie» beaucoup plus souvent (ou «fausse» pour la ANDchaîne), est-il possible en quelque sorte d '«augmenter sa priorité» pour que la seconde ne soit pas répétée?

Il s'avère que c'est possible - l'approche algorithmique est proche du sujet de l'article de PostgreSQL Antipatterns: un enregistrement rare atteindra le milieu du JOIN .

Disons simplement «pop under CASE» ces deux conditions:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Dans ce cas, nous n'avons pas déterminé la ELSEvaleur-, c'est-à-dire que si les deux conditions sont fausses , elle CASEsera renvoyée NULL, ce qui est interprété comme FALSEdans la condition WHERE-c.

Cet exemple peut être combiné d'une autre manière - au goût et à la couleur:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

# 3: comment [ne pas] écrire les conditions


Nous avons passé deux jours à analyser les raisons du déclencheur «étrange» de ce déclencheur - voyons pourquoi.

La source:

IF( NEW."_" is null or NEW."_" = (select '""'::regclass::oid) or NEW."_" = (select to_regclass('""')::oid)
     AND (   OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW."" ) ) THEN ...

Problème n ° 1: l'inégalité ne prend pas en compte NULL


Imaginez que tous les OLDchamps soient importants NULL. Que va-t-il se passer?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Et du point de vue de l'élaboration des conditions est NULLéquivalent FALSE, comme mentionné ci-dessus.

Solution : utilisez l'opérateur IS DISTINCT FROMde l' ROWopérateur, en comparant des enregistrements entiers à la fois:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Problème numéro 2: implémentation différente de la même fonctionnalité


Comparer:

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

Pourquoi y a-t-il des emboîtements supplémentaires SELECT? Et la fonction to_regclass? Et de différentes manières, pourquoi? ..

Correction:

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

Problème n ° 3: priorité des opérations booléennes


Formatez la source:

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

Oups ... En fait, il s'est avéré que dans le cas de la vérité de l'une des deux premières conditions, la condition entière devient TRUE, sans tenir compte des inégalités. Et ce n'est pas du tout ce que nous voulions.

Réparer:

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

Problème 4 (petit): condition OU complexe pour un champ


En fait, nous avons eu des problèmes dans le numéro 3 précisément parce qu'il y avait trois conditions. Mais au lieu d’eux, vous pouvez en faire un, en utilisant le mécanisme coalesce ... IN:

coalesce(NEW."_"::text, '') IN ('', '""', '""')

Donc, nous NULL«attrapons», et complexes ORavec des crochets n'ont pas à clôturer.

Total


Nous réparons ce que nous avons obtenu:

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

Et si nous prenons en compte que cette fonction de déclenchement ne peut être utilisée que dans le UPDATEdéclencheur en raison de la présence d' OLD/NEWun niveau supérieur dans la condition, cette condition peut généralement être placée dans la condition WHEN-comme cela a été montré dans # 1 ...

More articles:


All Articles