👩🏿‍🌾 🖕 ♂️ Antipatterns PostgreSQL: cálculo de condiciones en SQL ⚰️ 🕣 🙃

SQL no es C ++ ni JavaScript. Por lo tanto, el cálculo de las expresiones lógicas es diferente, y esto no es lo mismo:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

En el proceso de optimización del plan de ejecución de la consulta, PostgreSQL puede "reorganizar" arbitrariamente las condiciones equivalentes , no calcular ninguna de ellas para registros individuales, asignarlas a la condición del índice aplicable ... En resumen, es más fácil asumir que no puede controlar en qué orden Se calcularán las condiciones de los pares (y si es que lo hay) .

Por lo tanto, si aún desea controlar la prioridad, debe hacer que estas condiciones sean desiguales estructuralmente utilizando expresiones y operadores condicionales .

Los datos y trabajar con ellos son 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 específicos en los que se pueden cometer errores de cálculo de expresiones y donde vale la pena mejorar su eficiencia.

# 0: RTFM

Ejemplo de partida de la documentación :

Cuando el orden de cálculo es importante, se puede arreglar usando la construcción CASE. Por ejemplo, tal forma de evitar dividir por cero en una oración WHEREno es confiable:
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 de esta manera CASEprotege la expresión de la optimización, por lo tanto, solo debe usarse si es necesario.

# 1: condición en el disparador

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

Parece que todo se ve bien, pero ... Nadie promete que lo adjunto SELECTno se ejecutará si la primera condición es falsa. Corregir usando anidadoIF :

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

Ahora veamos cuidadosamente: todo el cuerpo de la función de disparo resultó estar "envuelto" IF. Y esto significa que nada nos impide eliminar esta condición del procedimiento usando la WHENcondición :

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

Este enfoque permite el ahorro garantizado de los recursos del servidor en condiciones falsas.

# 2: cadena OR / AND

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

De lo contrario, puede obtener que ambos EXISTSserán "verdaderos", pero ambos se cumplirán .

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

Resulta que es posible: el enfoque del algoritmo está cerca del tema del artículo PostgreSQL Antipatterns: un registro raro llegará a la mitad de JOIN .

Simplemente "pop en CASO" estas dos condiciones:

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

En este caso, no determinamos el ELSEvalor -es decir, si ambas condiciones son falsas , CASEregresará NULL, lo que se interpreta como FALSEen la WHEREcondición-.

Este ejemplo se puede combinar de otra manera: para saborear y colorear:

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

# 3: cómo [no] escribir condiciones

Pasamos dos días analizando los motivos del desencadenante "extraño" 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 # 1: la desigualdad no considera NULL

Imagina que todos los OLDcampos importan NULL. ¿Lo que sucederá?

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

Y desde el punto de vista de resolver las condiciones es NULLequivalente FALSE, como se mencionó anteriormente.

Solución : utilice el operador IS DISTINCT FROMdel operador ROW, 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 anidamiento adicional SELECT? ¿Qué hay de la función to_regclass? Y de diferentes maneras, ¿por qué? ..

Solución:

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

Problema # 3: prioridad de operaciones bool

Formatear 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 TRUE, sin tener en cuenta las desigualdades. Y esto no es en absoluto lo que queríamos.

Reparar:

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

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

En realidad, los problemas en el número 3 surgieron precisamente porque había tres condiciones. Pero en lugar de ellos, puedes hacer uno, usando el mecanismo coalesce ... IN:

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

Así que NULL"atrapamos", y complejos ORcon paréntesis no tienen que cercar.

Total

Arreglamos lo que tenemos:

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

Y si tenemos en cuenta que esta función de disparo solo se puede usar en el UPDATEdisparador debido a la presencia de OLD/NEWun nivel superior en la condición, entonces esta condición generalmente se puede poner en la WHENcondición -como se mostró en el # 1 ...

Antipatterns PostgreSQL: cálculo de condiciones en SQL