ProHoster > Blog > administration > Antipatterns PostgreSQL : évaluation des conditions en SQL

Antipatterns PostgreSQL : évaluation des conditions en SQL

SQL n'est pas C++, ni JavaScript. Par conséquent, l'évaluation des expressions logiques est différente, et ce n'est pas du tout la même chose :

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

En optimisant le plan d'exécution d'une requête PostgreSQL peut "réarranger" arbitrairement les conditions équivalentes, n'en calculez aucun pour les enregistrements individuels, reportez-vous à l'état de l'index appliqué ... En bref, le plus simple est de supposer que vous ne peut pas gérer l'ordre dans lequel ils seront (et s'ils seront calculés du tout) égal conditions

Par conséquent, si vous souhaitez toujours gérer la priorité, vous devez structurellement rendre ces conditions inégales au conditionnel expressions и opérateurs.

Antipatterns PostgreSQL : évaluation des conditions en SQL
Les données et travailler avec elles sont la base de notre complexe VLSI, il est donc très important pour nous que les opérations sur ceux-ci soient effectuées non seulement correctement, mais aussi efficacement. Examinons des exemples concrets où des erreurs d'évaluation d'expressions peuvent être commises et où il vaut la peine d'améliorer leur efficacité.

#0 : RTFM

Départ exemple tiré de la documentation:

Lorsque l'ordre d'évaluation est important, il peut être fixé avec le construit CASE. Par exemple, cette façon d'éviter la division par zéro dans une phrase WHERE non fiable:

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

Options sûres :

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

La construction utilisée CASE protège l'expression de l'optimisation, elle ne doit donc être utilisée que lorsque cela est nécessaire.

#1 : condition de déclenchement

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

Tout semble bien paraître, mais... Personne ne promet que les investis SELECT ne sera pas exécuté si la première condition est fausse. Fixez-le avec imbriqué IF:

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

Maintenant, regardons attentivement - tout le corps de la fonction de déclenchement s'est avéré être "enveloppé" dans IF. Et cela signifie que rien ne nous empêche de supprimer cette condition de la procédure en utilisant WHEN-conditions:

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

Cette approche vous permet d'économiser les ressources du serveur avec une garantie si la condition est fausse.

#2 : Chaîne OU/ET

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

Sinon, on peut obtenir que les deux EXISTS sera vrai, mais les deux seront exécutés.

Mais si nous savons avec certitude que l'un d'entre eux est "vrai" beaucoup plus souvent (ou "faux" - par AND-chaînes) - est-il possible d'une manière ou d'une autre "d'augmenter sa priorité" afin que la seconde ne soit pas exécutée à nouveau ?

Il s'avère que c'est possible - l'approche algorithmique est proche du sujet de l'article Antipatterns PostgreSQL : Une entrée rare atteint le milieu d'un JOIN.

Disons simplement "poussons sous 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éfini ELSE-value, c'est-à-dire si les deux conditions sont fausses CASE reviendra NULL, qui est interprété comme FALSE в WHERE- conditions.

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éclenchement "étrange" de ce déclencheur - voyons pourquoi.

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 tient pas compte de NULL

Supposons que tout OLD-les champs comptaient NULL. Que va-t-il se passer ?

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

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

décision: utiliser l'opérateur IS DISTINCT FROM à partir de ROW-opérateur, 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é

Comparons:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Pourquoi y a-t-il des investissements supplémentaires SELECT? Une fonction to_regclass? Pourquoi est-ce différent...

Réparons :

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Problème n ° 3: bool priorité

Formatons 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 se transforme en TRUE, au mépris des inégalités. Et ce n'est pas du tout ce que nous voulions.

Réparons :

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Problème #4 (petit) : condition OR 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 vous en tirer avec un, en utilisant le mécanisme coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Alors sommes-nous NULL "attrape", et complexe OR Vous n'avez pas à vous soucier des parenthèses.

En tout

Réparons ce que nous avons :

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Et étant donné que cette fonction de déclenchement ne peut être utilisée que dans UPDATEdéclenchement dû à la présence OLD/NEW dans la condition de niveau supérieur, alors cette condition peut généralement être retirée dans WHEN-état comme indiqué dans #1...

Source: habr.com

Ajouter un commentaire