Hoy nos metemos en el emocionante (y un poco aterrador) mundo de la optimización de consultas usando CTE (Common Table Expressions). Si ya aprendiste cómo crear CTE (lo vimos en las lecciones anteriores), ahora es el momento de hablar de los detalles internos, los “peligros ocultos” y cómo sacarles el máximo partido.
A primera vista, los CTE parecen perfectos: el código se ve limpio, se escribe fácil y te permite dividirlo en bloques lógicos. Pero hay un pequeño (o no tan pequeño) detalle. PostgreSQL tiene una estrategia específica para trabajar con CTE que afecta su rendimiento.
Cuando PostgreSQL ve WITH, normalmente materializa el resultado del CTE. Esto significa que los datos devueltos por el CTE primero se calculan y se guardan como una tabla temporal, que luego se usa en la consulta principal. Esto es cómodo para reutilizar, pero puede ser un problema si:
- El volumen de datos en el CTE es enorme y solo se usa una parte del resultado.
- El CTE se llama demasiadas veces, lo que aumenta el overhead.
- Creamos CTE innecesariamente complejos que en realidad no hacen falta.
Conociendo la materialización
La materialización es el proceso por el cual PostgreSQL guarda el resultado del CTE en memoria o en disco (depende del tamaño de los datos). Esto significa que los datos se extraen solo una vez, pero si usas el CTE solo en un sitio, la materialización puede ser innecesaria. Por ejemplo:
WITH large_set AS (
SELECT *
FROM students_grades
WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;
En este caso, PostgreSQL primero crea una tabla temporal con el resultado completo del CTE (grade > 60), y luego filtra las filas donde grade > 90. Esto añade un paso intermedio innecesario y afecta al rendimiento.
¿Cómo evitar la materialización innecesaria?
Desde PostgreSQL 12 se puede evitar la materialización del CTE cuando no hace falta. Para esto se usa la palabra clave MATERIALIZED (por defecto) o NOT MATERIALIZED. Ejemplo:
WITH large_set AS NOT MATERIALIZED (
SELECT *
FROM students_grades
WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;
Aquí le decimos a PostgreSQL que no materialice los datos de large_set, sino que meta la consulta directamente en la expresión principal. Así la consulta es más eficiente porque no se crea una tabla intermedia.
¿Cuándo es útil la materialización?
¡No pienses que la materialización siempre es mala! Si los datos del CTE se usan varias veces en la consulta o deben calcularse de forma independiente, la materialización puede ser útil. Ejemplo:
WITH materialized_example AS (
SELECT *
FROM students_grades
WHERE grade > 60
)
SELECT student_id
FROM materialized_example
WHERE grade > 90
UNION ALL
SELECT student_id
FROM materialized_example
WHERE grade < 70;Aquí la materialización evita recalcular el filtro grade > 60 varias veces.
Optimización de consultas usando índices
Para que los CTE vayan más rápido, deberías usar índices en las tablas base de donde sacas los datos. Por ejemplo:
CREATE INDEX idx_students_grades_grade ON students_grades(grade);
WITH filtered_students AS (
SELECT student_id, grade
FROM students_grades
WHERE grade > 90
)
SELECT *
FROM filtered_students;El índice en la columna grade permite a PostgreSQL sacar más rápido las filas que cumplen grade > 90. Esto es clave cuando trabajas con tablas grandes.
Dividir grandes CTE en otros más pequeños
Si un CTE devuelve muchos datos que luego se filtran o agregan, es mejor dividirlo en varios pasos. En vez de un solo CTE complicado, es más cómodo crear varios pequeños:
Mal (CTE grande):
WITH large_query AS (
SELECT s.student_id, AVG(g.grade) AS avg_grade
FROM students s
JOIN grades g ON s.student_id = g.student_id
WHERE g.subject_id = 101 AND g.grade > 85
GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;
Mejor (dividido en pasos):
WITH filtered_grades AS (
SELECT student_id, grade
FROM grades
WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;
Así PostgreSQL puede optimizar mejor la ejecución de la consulta.
Ejemplo práctico: análisis de estructura y optimización
Vamos a ver un ejemplo más complejo. Tenemos tablas de estudiantes, cursos y notas. Queremos encontrar estudiantes con una nota media alta y mostrar su lista junto con los cursos correspondientes:
WITH high_achievers AS (
SELECT student_id, AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 90
),
student_courses AS (
SELECT e.student_id, c.course_name
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;
Esta consulta se puede optimizar si añades índices a las tablas grades y enrollments, lo que acelera el filtrado y los joins.
Monitoring: análisis de rendimiento
Para saber si tu consulta es eficiente, usa EXPLAIN o EXPLAIN ANALYZE. Por ejemplo:
EXPLAIN ANALYZE
WITH high_achievers AS (
SELECT student_id, AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;
Esta consulta te muestra cuánto tarda cada paso y te ayuda a ver dónde puedes mejorar el rendimiento.
Vamos a ver EXPLAIN ANALYZE con más detalle en los siguientes niveles :P
Errores comunes al optimizar CTE
- Olvidar los índices. Si filtras datos en el CTE pero la tabla base no tiene índice, el rendimiento va a sufrir.
- Usar CTE demasiado grandes. Si una consulta hace demasiado, puede llevar a materializar grandes volúmenes de datos.
- Abusar de
NOT MATERIALIZED. A veces la materialización sí es necesaria para evitar recalcular el CTE. - Ignorar el monitoring. Sin analizar con
EXPLAINpuedes no darte cuenta de que tus consultas van lentas.
¡Ahora ya puedes optimizar consultas usando CTE, evitando trampas y mejorando el rendimiento! Recuerda que los CTE son una herramienta, no una solución mágica. Úsalos con cabeza y serán tus mejores amigos en PostgreSQL.
GO TO FULL VERSION