PostgreSQL
EXPLAIN y análisis de planes
Lectura de EXPLAIN y EXPLAIN ANALYZE para interpretar costos, filas, tiempos, loops, buffers y diferencias entre estimaciones y ejecución real.
- Última actualización
- Actualizada
- Nivel
- Profundización
PostgreSQL
Lectura de EXPLAIN y EXPLAIN ANALYZE para interpretar costos, filas, tiempos, loops, buffers y diferencias entre estimaciones y ejecución real.
EXPLAIN no califica una query como buena o mala. Expone el árbol elegido, sus estimaciones y —con ANALYZE— el trabajo observado. El valor está en comparar hipótesis con evidencia.
EXPLAIN planifica sin ejecutar la sentencia:
EXPLAIN
SELECT ...;EXPLAIN ANALYZE ejecuta y añade métricas reales:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;La diferencia es crítica. El primer plan contiene cost y rows estimados; el segundo añade actual time, actual rows, loops y otros contadores.
Una query puede ser lenta por razones distintas:
Sin plan y contexto, crear un índice al azar puede empeorar escrituras sin resolver la causa.
EXPLAIN (
ANALYZE,
BUFFERS,
WAL,
VERBOSE,
SETTINGS,
SUMMARY,
FORMAT TEXT
)
SELECT ...;Opciones:
ANALYZE: ejecuta.BUFFERS: páginas hit/read/dirtied/written.WAL: registros/bytes WAL generados cuando aplica.VERBOSE: columnas y detalles adicionales.SETTINGS: parámetros no default relevantes.TIMING OFF: reduce overhead de timing por nodo conservando filas/loops.FORMAT JSON|YAML|XML: salida estructurada.Los hijos alimentan al padre. Lee primero los nodos inferiores:
Limit
→ Sort
→ Hash Join
→ Seq Scan orders
→ Hash
→ Seq Scan customersPregunta por cada nodo:
cost=12.50..480.00 rows=100 width=72No son métricas reales:
El plan con menor cost fue el elegido. La unidad no es milisegundo.
actual time=0.050..12.400 rows=1000 loops=25Los valores suelen ser por loop. Trabajo aproximado:
1000 rows × 25 loops = 25 000 filas producidasUn nodo que parece barato puede dominar por repetición.
El tiempo de un nodo incluye tiempo de sus hijos, por lo que no debes sumar todos los tiempos del árbol directamente.
La comparación más valiosa:
estimated rows: 10
actual rows: 500 000Una diferencia grande puede cambiar:
Busca el primer nodo donde divergen; los errores posteriores pueden ser consecuencia.
Rows Removed by Filter: 900000Indica filas leídas pero descartadas en ese nodo.
Puede sugerir:
Evalúa porcentaje, páginas y frecuencia; un número alto aislado no dicta una solución.
Index Cond: (tenant_id = 10)
Recheck Cond: (payload @> ...)
Filter: (deleted_at IS NULL)Index Cond: limita desde el índice.Recheck Cond: verifica candidatos, común en bitmap/lossy/GIN/GiST.Filter: evalúa después de obtener la tuple.Mover una condición a index access requiere un índice/operator class compatible y semántica sargable.
Contadores:
shared hit: page encontrada en shared buffers.shared read: PostgreSQL solicitó lectura; puede venir de OS cache, no necesariamente disco físico.shared dirtied: se modificó.shared written: se escribió durante la operación.temp read/written: archivos temporales.local: objetos temporales.Ejemplo:
Buffers: shared hit=100 read=5000, temp read=2000 written=2000Muestra presión de lectura y spill, pero necesitas contexto de cache y concurrencia.
Sort Method: quicksort Memory: 2048kBo:
Sort Method: external merge Disk: 500MBUn spill puede deberse a:
work_mem insuficiente para ese nodo.No subas work_mem globalmente sin calcular concurrencia. Primero reduce filas/columnas o añade orden útil cuando procede.
Buckets: 131072 Batches: 8 Memory Usage: 8192kBVarios batches indican que el hash se dividió y puede usar temp I/O.
Causas:
Solución no siempre es más memoria; puede ser orden de join, filtro o estadísticas.
Detalles:
Heap Blocks: exact=1000 lossy=500
Rows Removed by Index Recheck: ...Lossy bitmap resume páginas en vez de cada tuple cuando falta memoria. Debe recheckear más filas.
Puede seguir siendo el mejor plan, pero revela coste adicional.
Heap Fetches: 15000Muchos heap fetches significan que la visibility map no permitió responder únicamente desde el índice.
Investiga:
Plan:
Nested Loop
→ Seq Scan outer (100000 rows)
→ Index Scan inner (rows=1 loops=100000)Aunque cada lookup sea rápido, 100 000 viajes internos pueden dominar.
Pregunta:
Planning Time: 15 ms
Execution Time: 2 msQueries dinámicas con muchos joins/partitions pueden gastar más planificando.
Execution Time incluye overhead del executor y de instrumentación, pero no necesariamente:
Mide end-to-end además del servidor.
Para DML, EXPLAIN ANALYZE puede mostrar tiempos de triggers:
Trigger audit_order: time=... calls=...Un statement aparentemente simple puede ejecutar trabajo oculto.
Con WAL puedes observar:
Útil para backfills, índices y writes. Una operación rápida puede generar WAL enorme y atrasar réplicas.
Mira:
Si se planearon 4 y se lanzaron 0, el sistema pudo no tener workers disponibles.
La distribución desigual puede revelar skew.
Sección:
JIT:
Functions: ...
Timing: Generation ... Inlining ... Optimization ... Emission ...Si JIT consume más que la ejecución ahorrada, revisa umbrales o query. No lo desactives globalmente por una observación aislada.
Ejecutar la query calienta cache. La segunda ejecución puede ser más rápida.
Para comparar:
Una query parametrizada puede tener comportamientos distintos:
branch pequeña → 100 filas
branch grande → 10 millonesCaptura values seguros o distribuciones, no secretos.
Compara:
Una sola muestra puede ocultar skew.
EXPLAIN ANALYZE ejecuta INSERT/UPDATE/DELETE/MERGE.
Prueba posible:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE ...;
ROLLBACK;Limitaciones:
NOTIFY/side effects tienen reglas propias.Usa staging o copia cuando exista riesgo.
Aunque sea SELECT, puede ejecutar funciones con side effects si usas ANALYZE. Comprueba la consulta antes.
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;Ventajas:
No dependas de formato textual para parsers.
Query:
SELECT *
FROM orders
WHERE tenant_id = 10
AND status = 'pending';Plan:
Seq Scan
estimated rows=100
actual rows=500000
Rows Removed by Filter=2000000Proceso:
pg_stats.Crear índice antes de entender skew puede ser correcto por accidente, pero no corrige el modelo de estimación.
Extensión que registra planes de queries lentas:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = onTiene overhead, especialmente con ANALYZE/BUFFERS. Configura de forma controlada y protege logs sensibles.
No muestra plan, pero ayuda a identificar queries por:
Después obtén EXPLAIN con parámetros representativos.
No optimices si cumple SLA y coste total es bajo.
EXPLAIN de ejecución aislada puede ser rápido. Revisa wait events y blocking.
Puede mejorar o empeorar por nueva muestra; monitorea regresión.
Nodos pueden no consumir toda su entrada. Actual rows refleja lo solicitado, no capacidad total.
Aparece CTE Scan y puede ocultar trabajo inicial separado.
Tiempos remotos y estimates pueden ser incompletos.
Ignora child costoso.
Incluyen hijos y loops.
OS cache puede responder.
Riesgo global de memoria.
Modifica datos.
No prueba la mejora.
No representa tiempo real.
actual time de todos los nodos?Heap Fetches en Index Only Scan?pg_stat_activity, wait events, blockers y duración de transacciones.Estadísticas y selectividad explica de dónde salen las estimaciones que determinan el plan.