Análisis de rendimiento de funciones y procedimientos: usando EXPLAIN ANALYZE

EXPLAIN ANALYZE te ayuda a entender cómo "piensa" PostgreSQL cuando ejecuta tu consulta:

• Qué pasos se hacen para procesar los datos.
• Cuánto tiempo tarda cada paso.
• Por qué una consulta se ejecuta lento — ya sea por un escaneo completo de tabla (en inglés Seq Scan) o por un índice que falta.

El comando EXPLAIN ANALYZE realmente ejecuta la consulta y te muestra cómo PostgreSQL optimiza la ejecución. Imagínate que desmontas un reloj para ver cómo funciona su mecanismo. Eso mismo hace EXPLAIN ANALYZE, pero con tus consultas SQL.

Sintaxis de EXPLAIN ANALYZE

Vamos a empezar fácil. Así se ve el comando básico:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Esta consulta ejecuta el SELECT y te muestra cómo PostgreSQL procesa los datos.

El resultado de EXPLAIN ANALYZE es un árbol de ejecución de la consulta. Cada nivel del árbol describe un paso que PostgreSQL realiza:

• Operation Type — tipo de operación (por ejemplo, Seq Scan, Index Scan).
• Cost — cuán costosa considera PostgreSQL esta operación.
• Rows — cuántas filas se esperan y cuántas realmente se obtienen.
• Time — cuánto tiempo tardó la operación.

Ejemplo de salida:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Fíjate en Seq Scan on students. Eso significa que PostgreSQL está mirando TODAS las filas de la tabla students. Si la tabla es grande, esto puede ser MUY LENTO.

Ejemplos de uso de EXPLAIN ANALYZE

Vamos a ver algunos ejemplos prácticos donde vas a aprender a detectar y arreglar problemas en tus consultas.

Ejemplo 1: escaneo completo de tabla

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Salida:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

El problema aquí es que PostgreSQL hace un Seq Scan, o sea, recorre todas las filas de la tabla. Si hay millones de filas, esto se convierte en un cuello de botella de rendimiento.

Solución: vamos a crear un índice en la columna age.

CREATE INDEX idx_students_age ON students(age);

Ahora ejecuta la misma consulta:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Salida:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Ahora vemos Index Scan en vez de Seq Scan. ¡Genial, ahora la consulta vuela!

Ejemplo 2: consulta compleja con JOIN

Imagina que tienes dos tablas: students y courses. Queremos saber los nombres de los estudiantes y los nombres de los cursos en los que están inscritos.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

La salida puede ser algo así:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Como ves, PostgreSQL lo tiene todo controlado: usa índices en las tablas enrollments y courses, y la ejecución es rápida. Pero si falta algún índice, puedes ver un Seq Scan, lo que ralentiza todo.

Optimización del rendimiento de funciones

Ahora imagina que tienes una función que devuelve una lista de estudiantes mayores de cierta edad:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Puedes analizar el rendimiento de esta función usando EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Acelerando la ejecución de la función

Si la función tarda mucho, probablemente el problema es un escaneo completo de tabla. Para arreglarlo:

1. Asegúrate de que la columna usada en los filtros (age) tenga un índice.
2. Revisa cuántas filas hay en la tabla y piensa en particionar si hay demasiados datos.

Cuellos de botella y cómo arreglarlos

1. Escaneo completo de tablas (Seq Scan). Usa índices para acelerar la búsqueda de filas. Pero ojo, demasiados índices pueden hacer más lenta la inserción de datos.

2. Muchas filas en el resultado. Si tu consulta devuelve millones de filas, piensa en añadir filtros (WHERE, LIMIT) o paginación (OFFSET).

3. Operaciones "costosas". Algunas operaciones como ordenar, agregar o unir tablas grandes pueden usar muchos recursos. Usa índices o divide la consulta en varios pasos.