Leer EXPLAIN ANALYZE en PostgreSQL para principiantes
1. Descripción general
EXPLAIN ANALYZE es la herramienta más importante para diagnosticar queries lentos en PostgreSQL. Este artículo te ayuda a leer y entender cada parte del output: node type, cost, rows, actual time, y saber cuándo una query tiene un problema. Después de leerlo, te sentirás seguro leyendo execution plans y sabrás dónde añadir un index o cómo modificar la query.
2. EXPLAIN vs EXPLAIN ANALYZE
EXPLAIN solo muestra el plan estimado (estimated plan) basado en estadísticas, sin ejecutar la query. EXPLAIN ANALYZE ejecuta realmente la query y mide el tiempo real.
-- Solo ver el plan estimado, no ejecutar la query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Ejecutar la query y medir el tiempo real
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;Nota:
EXPLAIN ANALYZEejecuta la query realmente, así que si es unUPDATEoDELETE, los datos se cambiarán. Úsalo en una transaction y hazROLLBACKsi solo quieres testear:
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;3. Estructura de un nodo en el plan
Cada línea en el output es un nodo — un paso en el procesamiento de datos. Ejemplo con la tabla orders:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;Seq Scan on orders (cost=0.00..1834.00 rows=10 width=72)
(actual time=0.015..12.430 rows=8 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 msExplicación de cada parte:
Seq Scan on orders: tipo de nodo, aquí es un escaneo secuencial de toda la tabla.cost=0.00..1834.00: costo estimado (startup cost .. total cost), unidad relativa, no en ms.rows=10: número de filas estimado que el planner cree que devolverá.width=72: tamaño promedio de cada fila (bytes).actual time=0.015..12.430: tiempo real (ms) — desde que empieza a devolver la primera fila .. hasta completar todo el nodo.rows=8 loops=1: número de filas reales devueltas, y cuántas veces se ejecuta este nodo (loops).Rows Removed by Filter: número de filas escaneadas pero que no pasaron el filter — si este número es grande, es una señal de que falta un index.
4. Comparar Seq Scan e Index Scan
4.1. Cuando no hay index
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;Seq Scan on orders (cost=0.00..1834.00 rows=10 width=72)
(actual time=0.015..12.430 rows=8 loops=1)
Filter: (customer_id = 42)
Execution Time: 12.465 msPostgreSQL tiene que leer toda la tabla y luego filtrar cada fila. Con tablas grandes, esta es la causa principal de lentitud.
4.2. Después de añadir un index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;Index Scan using idx_orders_customer_id on orders
(cost=0.29..8.31 rows=10 width=72)
(actual time=0.018..0.025 rows=8 loops=1)
Index Cond: (customer_id = 42)
Execution Time: 0.045 msComparación: el cost baja de 1834 a 8.31, y execution time baja de 12.465 ms a 0.045 ms. Este es un ejemplo clásico de cómo un index ayuda a PostgreSQL a saltar directamente a las filas que necesita, sin tener que escanear toda la tabla.
Nota: PostgreSQL no siempre usa un index aunque exista. Si la query devuelve una gran parte de la tabla (por ejemplo > 20-30% de las filas), el planner generalmente elige
Seq Scanporque es más barato que hacer saltos aleatorios a través del index y luego volver a la tabla (random I/O).
5. Leer un plan con JOIN
Cuando una query tiene join, el plan tendrá múltiples nodos anidados. Los nodos internos se ejecutan primero, y el resultado se pasa al nodo padre.
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';Hash Join (cost=45.50..520.75 rows=200 width=40)
(actual time=1.200..8.900 rows=195 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o
(cost=0.00..420.00 rows=10000 width=12)
(actual time=0.010..3.500 rows=10000 loops=1)
-> Hash (cost=40.00..40.00 rows=440 width=36)
(actual time=1.100..1.100 rows=440 loops=1)
-> Seq Scan on customers c
(cost=0.00..40.00 rows=440 width=36)
(actual time=0.005..0.700 rows=440 loops=1)
Filter: (country = 'VN'::text)
Execution Time: 9.050 msCómo leerlo: Hash Join es el nodo más externo (root), recibe datos de dos nodos internos (Seq Scan on orders y Hash que contiene Seq Scan on customers). PostgreSQL construye una hash table a partir de la tabla customers ya filtrada, luego escanea orders y coincide contra la hash table. El tiempo actual time del nodo padre ya incluye el tiempo de los nodos internos.
6. Señales de alerta a tener en cuenta
6.1. Estimación muy diferente de la realidad
Seq Scan on orders (cost=0.00..1834.00 rows=10 width=72)
(actual time=0.015..12.430 rows=9500 loops=1)El planner predice rows=10 pero realmente devuelve 9500. Una diferencia tan grande generalmente significa que las estadísticas están obsoletas. Soluciona esto con:
ANALYZE orders;Este comando actualiza las estadísticas de distribución de datos para que el planner haga estimaciones más precisas.
6.2. Loops anormalmente grande (Nested Loop)
Nested Loop (cost=0.43..5000.00 rows=1 width=40)
(actual time=0.020..450.000 rows=1 loops=1)
-> Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
-> Index Scan on orders o
(actual time=0.002..0.450 rows=1 loops=1000)loops=1000 significa que el nodo Index Scan on orders se ejecuta 1000 veces (una vez por cada fila del nodo externo). Si cada ejecución toma tiempo significativo, el costo total será muy alto aunque el actual time del nodo por sí solo parezca pequeño. Multiplica actual time por loops para estimar el tiempo total real del nodo.
6.3. Usar BUFFERS para ver I/O
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;Index Scan using idx_orders_customer_id on orders
(cost=0.29..8.31 rows=10 width=72)
(actual time=0.018..0.025 rows=8 loops=1)
Index Cond: (customer_id = 42)
Buffers: shared hit=4
Execution Time: 0.045 msshared hit=4 significa que 4 bloques se leyeron desde el cache (memoria), no del disco. Si ves shared read grande, significa que los datos se leyeron del disco — señal de cache miss, posiblemente porque shared_buffers es muy pequeño o porque la query escanea demasiados datos fríos.
7. Usar EXPLAIN en código Java/Spring
Cuando debuggeas performance en una aplicación Spring Boot, puedes loguear el plan directamente con JdbcTemplate:
@Service
public class QueryDiagnosticsService {
private final JdbcTemplate jdbcTemplate;
public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<String> explainAnalyze(String sql) {
String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
return jdbcTemplate.queryForList(query, String.class);
}
}explainAnalyze recibe la SQL original, la envuelve con EXPLAIN (ANALYZE, FORMAT TEXT) y devuelve cada línea del plan como un String. Úsalo en entorno dev/staging para loguear el plan cuando detectes queries lentos.
@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);
List<String> plan = service.explainAnalyze(
"SELECT * FROM orders WHERE customer_id = 42"
);
assertThat(plan)
.isNotEmpty()
.anyMatch(line -> line.contains("Execution Time"));
}Este test no aserta números específicos (porque el tiempo varía según la máquina), solo verifica que el plan devuelto contiene la línea Execution Time — es suficiente para confirmar que el comando se ejecutó correctamente y devolvió un plan válido.
Nota: No ejecutes
EXPLAIN ANALYZEen production para queries conINSERT/UPDATE/DELETEsin envolver en transaction +ROLLBACK, porque se ejecutarán realmente.
8. Cuándo NO necesitas EXPLAIN ANALYZE
- La query ya es rápida y estable (menos de unos ms, pocos datos) — no necesita optimización, el esfuerzo no vale la pena.
- Estás en fase temprana de diseño de schema, aún no tienes suficientes datos reales para que las estadísticas reflejen la realidad (con pocas filas, Seq Scan siempre vence a Index Scan).
- La query corre en una tabla temporal (
TEMP TABLE) pequeña una sola vez — el overhead de análisis no compensa comparado con el beneficio.
9. Conclusión
EXPLAIN ANALYZE te muestra la diferencia entre lo que PostgreSQL cree que pasará (cost, rows estimado) y lo que realmente pasa (actual time, rows real). Cuando leas un plan, presta atención al orden de ejecución (de adentro hacia afuera), al tipo de nodo (Seq Scan o Index Scan), y especialmente a la discrepancia entre estimate y actual — esa es la clave para saber si la query necesita un index, necesita ejecutar ANALYZE de nuevo en la tabla, o necesita ser reescrita.
¿Te gusta este tipo de explicación? Cris The Dev publica un vídeo corto cada día en YouTube y TikTok.
Más sobre este tema
Transacciones y niveles de aislamiento en PostgreSQL
Aprende cómo PostgreSQL maneja transacciones, los cuatro niveles de aislamiento, fenómenos como lost update y write skew, y cómo configurar y reintentar en Spring Boot.
Depuración de Base de Datos con IA en Databricks con PostgreSQL: Guía Detallada
Aprende cómo Databricks utiliza IA para diagnosticar errores de base de datos, aplicado a PostgreSQL con ejemplos de Spring Boot, HikariCP y consultas lentas específicas.
Índices en PostgreSQL: B-tree, GIN y cuándo usarlos
Diferencia entre índices B-tree y GIN en PostgreSQL, cómo crearlos, leer EXPLAIN, y elegir el tipo de índice adecuado para cada situación real.