sábado, 28 de febrero de 2015

PostgreSQL: Un optimizador demasiado inteligente

¿Has tenido alguna mala experiencia con algún optimizador?


Mi primera mala experiencia fue con algún compilador de C, uno de Borland creo recordar. Depurando con ejecución paso a paso, llegaba a un sitio en el que misteriosamente no se ejecutaba el código. Lo que pasaba era que por defecto el IDE estaba configurado para optimizar y el código generado no se podía depurar.

El planificador de PostgreSQL


En PostgreSQL el planificador (planner) es el módulo del sistema que interpreta el código SQL para su ejecución. Incluye optimizaciones en las que hace auténticas virguerías para que la ejecución sea eficiente.

Observa el siguiente código y trata de predecir cual es el resultado de la consulta:

CREATE TABLE test(id INTEGER, name TEXT, PRIMARY KEY (id) );

INSERT INTO test(id, name) VALUES
                (1 , 'one'  ),
                (2 , 'two'  ),
                (3 , 'three')
;

SELECT CASE WHEN (r_count < 0) THEN CAST('The concatenation avoids this whole CAST sentence being pre-evaluated.'||r_count AS INTEGER)
            ELSE CAST(r_count AS INTEGER)
       END AS demonstration
FROM (SELECT COUNT(*) AS r_count FROM test WHERE (id < 0)
     ) foo
;

No hay ningún id negativo, por lo que cabría esperar una ejecución correcta de la consulta con 0 resultados. Además, en general, la función COUNT nunca devolverá un valor negativo, por lo que es obvio que nunca se producirá el primer WHEN.

Pero no, la consulta genera un error en tiempo de compilación (considerando compilación el análisis del código previo a su ejecución) porque el planificador es "tan inteligente" que precalcula todo el código que considera constante respecto a la ejecución de la consulta. En este caso, evalua la sentencia:

CAST('This case never happens but the CAST throws an exception.' AS INTEGER)

antes de lanzar la consulta, generando un error porque la String no se puede convertir a Integer.

Desde mi ignorancia, no sé si está definido el orden correcto de evaluación o si eso es algo que se deja a disposición de la implementación. Lo que está claro, es que hay que saber estas cosas y ademas, que en este caso, no es lo más óptimo malgastar tiempo en precalcular cosas que nunca se deberían calcular dado que la consulta no va a devolver resultados.

(Nota: se ha comprobado dicho comportamiento en las versiones de PostgreSQL: 8.3.7, 8.4.8, 8.4.22, 9.1.14 y 9.2.8.)

Como evitar el problema


Para no tener ese problema, basta con hacer que el código potencialmente peligroso o costoso dependa de la consulta, es decir, que no sea precalculable. El ejemplo, podría quedar así:

SELECT CASE WHEN (r_count < 0) THEN CAST('The concatenation avoids this whole CAST sentence being pre-evaluated.'||r_count AS INTEGER)
            ELSE CAST(r_count AS INTEGER)
       END AS demonstration
FROM (SELECT COUNT(*) AS r_count FROM test WHERE (id < 0)
     ) foo
;

Destacar que raramente se puede escribir código que no dependa de la consulta que sea más costoso que la consulta en sí.

Enlaces


Enlaces de interés relacionados con este artículo:

(Actualizado 28/02/2015)