Interpretación del plan de ejecución: lectura y análisis de nodos ( Seq Scan, Index Scan, Hash Join)

Hoy vamos a ver qué son los nodos del plan de ejecución en PostgreSQL, cómo leerlos y, lo más importante, cómo darte cuenta de que algo va mal. Vas a entender por qué tu base de datos a veces prefiere usar el costoso Seq Scan, aunque parece que ya tienes un índice, o incluso varios.

Cuando PostgreSQL construye el plan de ejecución de una consulta, lo divide en etapas llamadas nodos. Cada nodo es como un "paso" que el servidor de base de datos ejecuta para procesar tu consulta. Los tipos principales de nodos incluyen:

Sequential Scan (Seq Scan)

Seq Scan o escaneo secuencial es la forma más simple de sacar datos de una tabla. PostgreSQL literalmente toma la tabla, lee sus filas una por una y comprueba si cumplen las condiciones de tu consulta.

¿Cuándo se usa Seq Scan?
Seq Scan se usa si:

• La tabla no tiene un índice adecuado para acelerar la consulta.
• La condición de filtrado es demasiado general como para que el índice sea útil (por ejemplo, sacar más del 50% de los datos).
• PostgreSQL piensa que leer la tabla entera es más rápido que usar el índice (a veces pasa con tablas muy pequeñas).

EXPLAIN SELECT * FROM students WHERE age > 18;

Ejemplo de resultado:

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Fíjate en Seq Scan on students — aquí PostgreSQL te dice que va a leer la tabla "students" entera.

Problemas con Seq Scan: Si la tabla es enorme, el escaneo secuencial puede tardar muchísimo.

Index Scan

Index Scan es escanear datos usando un índice. Cuando creas un índice en PostgreSQL, es como hacer un "índice" de contenidos para tu tabla. Si la consulta puede usar el índice, PostgreSQL no recorre toda la tabla, sino solo las partes necesarias.

¿Cuándo se usa Index Scan?

• La consulta tiene condiciones de filtrado sobre una columna indexada (por ejemplo, WHERE).
• Se usan operaciones de comparación como =, <, >, BETWEEN, etc.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

Ejemplo de resultado:

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Aquí Index Scan using idx_students_age muestra que PostgreSQL está usando el índice idx_students_age. Leer la tabla fila por fila se cambia por un acceso mucho más rápido usando el índice.

Ventajas de Index Scan:

• Las consultas van mucho más rápido en tablas grandes.
• Se reduce la cantidad de datos leídos del disco.

Problemas de Index Scan:
Si tu consulta devuelve demasiados datos (por ejemplo, más de la mitad de la tabla), usar el índice puede ser incluso más lento que el Seq Scan.

Hash Join

Hash Join se usa para unir dos tablas según una condición de join (por ejemplo, ON students.course_id = courses.id). PostgreSQL crea una tabla hash para una de las tablas (la más pequeña) y la usa para buscar coincidencias en la otra tabla.

¿Cuándo se usa Hash Join?

• Al unir tablas con INNER JOIN, LEFT JOIN, etc.
• Cuando PostgreSQL cree que Hash Join es más eficiente que otros métodos de join.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

Ejemplo de resultado:

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Aquí Hash Join une dos tablas. Fíjate que PostgreSQL primero hace un Seq Scan para ambas tablas y luego construye la tabla hash (Hash).

Ventajas de Hash Join:

• Procesa rápido tablas de tamaño medio.
• Es eficiente para unir tablas con muchas filas.

Problemas de Hash Join:
Si la tabla hash ocupa más memoria de la disponible, PostgreSQL usará disco para guardarla, lo que hace el join mucho más lento.

Ejemplo de análisis de un plan de ejecución

Vamos a ver un ejemplo real.

Consulta:

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Resultado:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Interpretación:

1. Hash Join: Nodo principal. PostgreSQL une las tablas students y courses.
   • actual time: de 1.00 a 2.50 ms.
   • rows=5: la consulta devolvió 5 filas.
2. Nodos anidados:
   • Seq Scan on students: lee secuencialmente la tabla students y aplica el filtro (age > 18).
   • Rows Removed by Filter = 3: 3 filas no cumplían la condición.
   • Hash: PostgreSQL crea la tabla hash para la tabla courses.

Comparación y elección de nodos

Cuando analizas el plan de ejecución, la clave es entender por qué PostgreSQL eligió ese método para procesar los datos. A veces tienes que intervenir para arreglarlo, por ejemplo, añadiendo un índice o reescribiendo la consulta. Aquí van algunos consejos:

• Si ves Seq Scan en una tabla grande, piensa en los índices.
• Si Hash Join va muy lento, revisa la memoria disponible para PostgreSQL.
• Usa EXPLAIN ANALYZE para comparar los valores estimados y reales de las métricas (rows, time).

A estas alturas ya tienes una idea básica de cómo leer los planes de ejecución de consultas e interpretar sus nodos. En las próximas clases hablaremos de problemas típicos de optimización y cómo resolverlos.