ProHoster > Blog > administración > Antipatrones de PostgreSQL: evaluación de condiciones en SQL

Antipatrones de PostgreSQL: evaluación de condiciones en SQL

SQL no es C++, ni es JavaScript. Por lo tanto, la evaluación de las expresiones lógicas es diferente, y esto no es lo mismo en absoluto:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Al optimizar el plan de ejecución de una consulta PostgreSQL puede "reorganizar" arbitrariamente las condiciones equivalentes, no calcule ninguno de ellos para registros individuales, consulte la condición del índice aplicado ... En resumen, la forma más fácil es asumir que usted no puedo manejar el orden en que serán (y si se calcularán en absoluto) igual condiciones

Por lo tanto, si aún desea administrar la prioridad, necesita estructuralmente hacer que estas condiciones sean desiguales con condicional expresiones и operadores.

Antipatrones de PostgreSQL: evaluación de condiciones en SQL
Los datos y trabajar con ellos es la base de nuestro complejo VLSI, por lo que es muy importante para nosotros que las operaciones en ellos se realicen no solo correctamente, sino también de manera eficiente. Veamos ejemplos concretos donde se pueden cometer errores en la evaluación de expresiones y donde vale la pena mejorar su eficiencia.

#0: RFM

Comenzando ejemplo de la documentación:

Cuando el orden de evaluación es importante, se puede arreglar con el constructo CASE. Por ejemplo, de esta manera para evitar la división por cero en una oración WHERE faltón:

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

Opción segura:

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

La construcción utilizada CASE protege la expresión de la optimización, por lo que solo debe usarse cuando sea necesario.

#1: condición de activación

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

Todo parece ir bien, pero... Nadie promete que lo invertido SELECT no se ejecutará si la primera condición es falsa. arreglarlo con anidado IF:

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

Ahora echemos un vistazo detenidamente: todo el cuerpo de la función de activación resultó estar "envuelto" en IF. Y esto significa que nada nos impide eliminar esta condición del procedimiento usando WHEN-condiciones:

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

Este enfoque le permite ahorrar recursos del servidor con una garantía si la condición es falsa.

#2: cadena O/Y

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

De lo contrario, se puede obtener que ambos EXISTS será cierto, pero ambos serán ejecutados.

Pero si sabemos con certeza que uno de ellos es "verdadero" con mucha más frecuencia (o "falso" - por AND-cadenas) - ¿es posible de alguna manera "aumentar su prioridad" para que el segundo no se ejecute una vez más?

Resulta que es posible: el enfoque algorítmico está cerca del tema del artículo. Antipatrones de PostgreSQL: la entrada rara llega a la mitad de un JOIN.

Simplemente "empujemos bajo CASE" estas dos condiciones:

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

En este caso, no definimos ELSE-valor, es decir, si ambas condiciones son falsas CASE volverá NULL, que se interpreta como FALSE в WHERE- condiciones.

Este ejemplo se puede combinar de otra manera, al gusto y color:

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

#3: cómo [no] escribir condiciones

Pasamos dos días analizando las razones de la activación "extraña" de este desencadenante; veamos por qué.

Fuente:

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 ...

Problema n.º 1: la desigualdad no tiene en cuenta NULL

Supongamos que todo OLD-campos importados NULL. ¿Lo que sucederá?

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

Y desde el punto de vista de la elaboración de las condiciones NULL equivalente FALSE, como se ha mencionado más arriba.

Solución: usar operador IS DISTINCT FROM de ROW-operador, comparando registros completos a la vez:

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

Problema número 2: implementación diferente de la misma funcionalidad

Comparar

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

¿Por qué hay inversiones extra? SELECT? Una función to_regclass? ¿Por qué es diferente...

Arreglemos:

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

Problema #3: precedencia bool

Formateemos la fuente:

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

Vaya ... De hecho, resultó que en el caso de la verdad de cualquiera de las dos primeras condiciones, toda la condición se convierte en TRUE, sin tener en cuenta las desigualdades. Y esto no es en absoluto lo que queríamos.

Arreglemos:

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

Problema #4 (pequeño): condición OR compleja para un campo

En realidad, tuvimos problemas en el No. 3 precisamente porque había tres condiciones. Pero en lugar de ellos, puedes arreglártelas con uno, usando el mecanismo coalesce ... IN:

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

Así somos nosotros NULL "atrapar" y complejo OR No tienes que preocuparte por los paréntesis.

En total

Arreglemos lo que tenemos:

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

Y dado que esta función de disparo solo se puede utilizar en UPDATEdesencadenar debido a la presencia OLD/NEW en la condición de nivel superior, entonces esta condición generalmente se puede sacar en WHEN-condición como se muestra en el #1...

Fuente: habr.com

Añadir un comentario