En este tutorial vamos a ver diferentes formas de implementar queries correladas y el impacto que tiene en el rendimiento y su tiempo de ejecución.
Índice de contenidos
- 1. Introducción
- 2. Entorno
- 3. Preparando la base datos
- 4. Queries Correladas: Implementaciones y rendimiento
- 5.Comparativa de rendimiento
- 6. Conclusiones
- 7. Referencias
1. Introducción
Como continuación del tutorial anterior sobre las formas de medir el rendimiento de las queries, en este tutorial analizaremos diferentes formas de implementar queries correladas o subconsultas y el impacto que tiene sobre el rendimiento y tiempo de ejecución.
2. Entorno
El tutorial está escrito usando el siguiente entorno:
- Hardware: MacBook Pro Retina 15′ (2,5 Ghz Intel Core i7, 16GB DDR3).
- Base de datos Postgres 12
- Python 3.
- Sistema Operativo: Mac OS Catalina 10.15.4.
- Docker versión 18.06.1-ce, build e68fc7a
3. Preparando la base datos
Como entorno de pruebas, usaremos la base de datos sobre películas importada en el anterior tutorial donde hablaba de formas de medir el rendimiento de tus queries en Postgres.
4. Queries Correladas: Implementaciones y rendimiento
En este apartado realizaremos diferentes implementaciones sobre el mismo concepto de query. Imaginemos que nos piden que hagamos una query capaz de recuperar las 10 películas con más presupuesto de media de toda la base de datos.
Como primera aproximación se nos puede ocurrir seria usar DISTINCT y ordenar descendentemente por budget. Una posible implementación podría ser la siguiente:
SELECT DISTINCT title,
budget
FROM movies
WHERE budget IS NOT NULL
ORDER BY budget DESC,
title DESC
LIMIT 10;
Un tutorial sobre la optimización de subqueries que da una solución que no utiliza subqueries, !!BIEN !!. Así que para resolver el problema nos vamos a poner la restricción de implementarla con una query correlada.
En los siguientes apartados os mostraremos diferentes implementaciones de esta misma query objetivo con queries correladas.
4.1. En el WHERE
Una posible implementación sería utilizar una subquery como criterio en la cláusula WHERE:
SELECT m.title,
m.budget
FROM movies m
WHERE m.budget IS NOT NULL
AND m.budget IN (SELECT DISTINCT budget
FROM movies
WHERE budget IS NOT NULL
AND m.title = title
ORDER BY budget DESC)
ORDER BY budget DESC,
title DESC
LIMIT 10
Pero si quisiéramos hacer uso de la función AVG para aplicar explícitamente los criterios de la query solicitada («las 10 películas con más presupuesto que la media») quedaría algo así:
SELECT m.title,
m.budget
FROM movies m
WHERE m.budget IS NOT NULL
AND m.budget >= (SELECT Avg(budget) AS avg_budget
FROM movies
WHERE budget IS NOT NULL
AND m.title = title
GROUP BY title
ORDER BY avg_budget DESC,
title DESC)
ORDER BY budget DESC,
title DESC
LIMIT 10;
4.2. Usando INNER JOIN
Esta sería otra posible implementación realizando un INNER JOIN con la subquery:
SELECT m.title,
m.budget
FROM movies m
INNER JOIN (SELECT title,
Avg(budget) AS avg_budget
FROM movies
WHERE budget IS NOT NULL
GROUP BY title) temp
ON m.title = temp.title
WHERE m.budget IS NOT NULL
AND m.budget >= temp.avg_budget
ORDER BY budget DESC,
title DESC
LIMIT 10;
4.3. Como otro campo de la query
Una alternativa es incluir los resultados de la subquery como un campo de la select y utilizar este campo como criterio del WHERE. Una posible implementación podría ser la siguiente:
SELECT *
FROM (SELECT m.title,
m.budget,
(SELECT Avg(budget)
FROM movies
WHERE budget IS NOT NULL
AND m.title = title
GROUP BY title) AS avg_budget
FROM movies m
WHERE m.budget IS NOT NULL) AS temp
WHERE budget >= avg_budget
ORDER BY avg_budget DESC,
title DESC
LIMIT 10;
4.4. Usando VIEW
Otra posibilidad sería crear una vista con la query que vamos a utilizar como criterio, en nuestro caso la select con la media. La vista y la query que la usa quedaría así:
CREATE view avg_budget
AS
SELECT title,
Avg(budget) AS avg_budget
FROM movies
WHERE budget IS NOT NULL
GROUP BY title
ORDER BY avg_budget DESC,
title DESC;
SELECT m.title,
m.budget
FROM movies m
INNER JOIN avg_budget
ON m.title = avg_budget.title
WHERE m.budget >= avg_budget.avg_budget
ORDER BY avg_budget DESC,
title DESC
LIMIT 10;
Esta alternativa como ventaja simplifica la query y facilita su lectura. Como posible desventaja sería la creación de un nuevo objeto en la base de datos para la realización de la consulta, es decir, la vista creada.
4.5. Usando WITH
Una alternativa similar a la anterior sería utilizar la sentencia WITH.
WITH avg_budget_cte AS
(
SELECT title,
Avg(budget) AS avg_budget
FROM movies
WHERE budget IS NOT NULL
GROUP BY title
ORDER BY avg_budget DESC,
title DESC)
SELECT m.title,
m.budget
FROM avg_budget_cte cte
INNER JOIN movies m
ON cte.title = m.title
WHERE m.budget >= cte.avg_budget limit 10;
WITH nos permite dividir las consultas complicadas en partes más simples. Estas declaraciones denominadas Common Table Expressions o CTE , pueden considerarse como tablas temporales que existen solo para una consulta. También pueden usarse con sentencias SELECT, INSERT, UPDATE, o DELETE.
5. Comparativa de rendimiento
Para realizar esta comparativa utilizaremos las estadísticas de Postgres tal y como indicamos en el tutorial anterior. A las queries anteriormente expuestas añadiremos un campo vacío (») con un alias que identifique de qué tipo de implementación se trata y así nos sea más fácil interpretar los resultados. Los resultados obtenidos son los siguientes:
A la vista de los resultados podemos sacar en claro lo siguiente:
- El top 3 con el mejor rendimiento son las subqueries implementado con INNER JOIN, VISTA y WITH (de menor a mayor tiempo). Con un rendimiento aproximado de las 3 implementaciones de 20 ms.
- La peor implementación, (la subquery como campo de la select) es de casi 100 ms. Un 80% más que las mejores implementaciones.
- El uso de la función de agrupación AVG incrementa el tiempo en casi un 25% (de 49 a 63 ms).
¿Y si no hubiéramos tenido la restricción de usar una subquery (por el tema central del tutorial) para realizar la implementación de la query?. Estos son los resultados comparando la primera implementación (sin subquery y con el distinct) y la implementación que ha obtenido el mejor rendimiento.
Como podemos ver hay un incremento de un 50% (de 8 a 18 ms).
6. Conclusiones
Como hemos visto en el tutorial, existe una diferencia importante en el rendimiento dependiendo de las diferentes implementaciones para obtener un mismo resultado. Y si mantenemos la implementación lo más simple posible y nos paramos a pensar un poco, obtendremos los mejores resultados.
Espero que os sirva este tutorial para cuando tengáis la imposición (y esta vez no fijada por mi) de utilizar una subquery y podáis mejorar el rendimiento.
