Postgres EXPLAIN ANALYZE: Entender y Optimizar Consultas

La mayoría de los desarrolladores ejecutan EXPLAIN ANALYZE del mismo modo en que revisan un stack trace de un error que no entienden: lo pegan en la consulta, reciben una pared de texto, buscan un número grande por encima y adivinan. Esa suposición normalmente es “agrega un índice” o “es el JOIN”, y a veces aciertan por accidente. El plan en sí ya te dice exactamente qué es lento y por qué; el problema es que a la mayoría nunca nos enseñaron a leerlo como un documento estructurado en lugar de como un bloque intimidante de texto monoespaciado.

Esa carencia cuesta tiempo real. Un desarrollador que no sabe leer un plan probará tres arreglos no relacionados antes de encontrar el correcto: recortar SELECT *, luego añadir una capa de caché, y por último un índice, cuando el plan ya decía “falta un índice” en los primeros diez segundos si sabías dónde mirar. Este post recorre la gramática real de un plan de consulta de Postgres: qué codifica cada línea, qué números son estimaciones frente a realidad, cómo los conteos de loops multiplican costos ocultos, qué están midiendo realmente los contadores de buffers y las tres formas de plan recurrentes que son la manera en que Postgres te dice que falta un índice.

Aprenderás:

• Cómo leer la estructura en árbol de un plan y saber qué nodo realmente está impulsando el costo
• La diferencia entre las filas estimadas del planificador y las filas reales de PostgreSQL, y por qué una gran diferencia entre ambas es la señal más útil de todo el plan
• Por qué un nodo que parece barato, ejecutado dentro de un loop, puede dominar el tiempo total de la consulta
• Cómo leer la salida de BUFFERS: shared hit vs. read, y qué te dice eso sobre la presión de caché
• Los tres patrones de plan — escaneo secuencial sobre una tabla grande filtrada, nested loop con un alto conteo de loops y sort que se derrama a disco — que casi siempre significan un índice ausente o incorrecto
• Un ejemplo completo paso a paso diagnosticando una consulta genuinamente lenta solo a partir de su plan

Tabla de contenido

1. Qué hace realmente EXPLAIN ANALYZE
2. Anatomía de una línea del plan
3. Filas estimadas vs. reales
4. Loops: por qué un nodo barato puede dominar
5. Leer buffers: aciertos de caché vs. lecturas de disco
6. Los tres patrones que significan falta de índice
7. Un recorrido completo
8. Errores comunes
9. A dónde ir desde aquí

Qué hace realmente EXPLAIN ANALYZE

EXPLAIN por sí solo le pregunta al planificador qué haría: estima un plan usando estadísticas de las tablas y lo imprime sin ejecutar nada. EXPLAIN ANALYZE sí ejecuta la consulta, mide el tiempo de cada paso y luego imprime el mismo árbol anotado con lo que realmente ocurrió. Esa distinción importa más de lo que parece: EXPLAIN es seguro de ejecutar contra cualquier cosa, incluyendo un DROP envuelto en una transacción que piensas revertir, pero EXPLAIN ANALYZE realmente ejecuta un INSERT, UPDATE o DELETE a menos que lo envuelvas en BEGIN; ... ROLLBACK;.

La salida que realmente quieres para depuración real es esta forma:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days'
  AND o.status = 'pending';

ANALYZE te da tiempos reales y conteos reales de filas. BUFFERS te da información de aciertos de caché, que está desactivada por defecto por razones históricas pero es genuinamente útil en toda investigación real: actívala siempre. Omite COSTS OFF salvo que estés pegando el plan en algún lugar donde necesite poder compararse entre ejecuciones; quieres las estimaciones de costo para compararlas con los números reales.

Un plan es un árbol, se lee de abajo hacia arriba y de adentro hacia afuera. Los nodos más internos y con más sangría se ejecutan primero; su salida alimenta al nodo directamente encima de ellos. La línea superior es lo último que sucede y donde se reportan el tiempo total y el costo total. Es tentador leerlo de arriba hacia abajo como si fuera prosa, pero el flujo real de datos —y normalmente el cuello de botella real— vive en las hojas de abajo.

Anatomía de una línea del plan

Aquí tienes un solo nodo de un plan real, anotado parte por parte:

Seq Scan on orders o  (cost=0.00..18734.00 rows=812 width=24)
                       (actual time=0.021..142.558 rows=790 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 199210
  Buffers: shared hit=210 read=8312

Desglosándolo:

• Seq Scan on orders o — la operación y la tabla (o alias) sobre la que se ejecuta. Un escaneo secuencial lee cada fila de la tabla o índice en orden físico; no es automáticamente malo, pero es lo que debes notar en una tabla grande.
• cost=0.00..18734.00 — el rango de costo estimado del planificador, en unidades de costo arbitrarias del planificador (no milisegundos). El primer número es el costo estimado para devolver la primera fila; el segundo es el costo estimado para devolver todas las filas. Estos números solo son comparables con otros números de costo del mismo plan, nunca entre consultas o servidores.
• rows=812 — la estimación del planificador sobre cuántas filas producirá este nodo, basada en estadísticas de la tabla.
• width=24 — el ancho promedio estimado de fila en bytes.
• actual time=0.021..142.558 — milisegundos reales medidos: tiempo hasta la primera fila y luego tiempo hasta finalizar, promediados a través de todas las iteraciones del loop (más sobre eso abajo).
• rows=790 loops=1 — el número real de filas que este nodo devolvió y cuántas veces se ejecutó.
• Filter / Rows Removed by Filter — un filtro posterior al escaneo aplicado después de recuperar las filas. Un número grande de “rows removed” junto a un conteo final pequeño de filas es una señal fuerte de que el escaneo está haciendo mucho más trabajo del necesario.
• Buffers — cuántas páginas de 8KB se tocaron, divididas entre aciertos de caché y lecturas de disco.

Cada nodo en el árbol tiene esta misma forma. Una vez que puedes interpretar una línea, puedes interpretar el plan entero: la habilidad consiste por completo en saber qué números comparar entre sí.

Filas estimadas vs. reales

Esta es la comparación de mayor valor en todo el plan. El planificador estima rows=812 antes de ejecutar nada, basándose en estadísticas reunidas por ANALYZE (el comando de mantenimiento, no la opción de EXPLAIN; de forma confusa, comparten nombre). Después de la ejecución, Postgres informa lo que realmente salió. Cuando estos dos números están cerca, el planificador tenía buena información y casi con certeza eligió un buen plan. Cuando están desviados por 10x, 100x o más, todo lo construido encima de esa estimación —estrategia de JOIN, asignación de memoria, orden de ejecución— se decidió usando mala información, y el plan resultante suele ser malo de formas difíciles de predecir solo a partir de la estimación.

-> Index Scan using idx_orders_status on orders
     (cost=0.42..8.44 rows=1 width=24)
     (actual rows=48000 loops=1)

Una estimación de 1 fila frente a un valor real de 48,000 es una desestimación enorme. Esto normalmente ocurre por una de algunas razones:

• Estadísticas obsoletas. La tabla cambió significativamente desde la última vez que se ejecutó ANALYZE, y autovacuum no se ha puesto al día. Ejecuta ANALYZE orders; manualmente y compara.
• Columnas correlacionadas. El planificador asume por defecto que las columnas son independientes. Un filtro WHERE status = 'pending' AND created_at > now() - interval '7 days' puede ser mucho más (o menos) selectivo en combinación que cualquiera de las columnas por separado, y las estadísticas por defecto no capturan esa correlación. CREATE STATISTICS de Postgres para estadísticas extendidas existe precisamente para arreglar esto.
• Distribución de datos no uniforme. Si el 90% de las filas comparten un valor en una columna sesgada, el objetivo de estadísticas por defecto (100 buckets por defecto) puede no resolver ese sesgo con suficiente precisión. Aumentar default_statistics_target, o configurarlo por columna con ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, le da al planificador un histograma más detallado.

Cuando estés haciendo depuración general de consultas lentas en Postgres, esta comparación es el primer lugar donde mirar, antes que buffers, antes que números de costo, antes que cualquier otra cosa: una mala estimación suele ser la causa raíz, y todo lo que viene después es un síntoma.

Loops: por qué un nodo barato puede dominar

loops=1 significa que el nodo se ejecutó una vez. Bajo un nested loop join, un nodo interno puede ejecutarse una vez por fila producida por el lado externo, y el actual time reportado para ese nodo es el promedio por loop, no el total. Esta es la interpretación errónea más común de todo el plan.

Nested Loop  (actual time=0.045..891.223 rows=48000 loops=1)
  -> Seq Scan on customers c (actual time=0.010..12.400 rows=4000 loops=1)
  -> Index Scan using idx_orders_customer on orders o
       (actual time=0.008..0.019 rows=12 loops=4000)

Ese index scan interno parece trivialmente barato: 0.019ms. Pero se ejecutó 4,000 veces —una por cada fila de customer del escaneo externo— así que su contribución real es aproximadamente 0.019ms * 4000 ≈ 76ms, no 0.019ms. Multiplica siempre el tiempo real por el conteo de loops antes de juzgar el costo real de un nodo. Un tiempo minúsculo por loop con un conteo de loops de cinco cifras suele ser el verdadero cuello de botella escondido a simple vista, mientras que el nodo con el mayor número único de actual time contribuye comparativamente poco.

Este también es el mecanismo detrás del clásico bug de “funciona bien con 100 filas, se derrumba con 100,000”: un nested loop es una buena estrategia cuando el lado externo es pequeño, y una que empeora linealmente a medida que crece, que es exactamente la forma del problema N+1 que también aparece a nivel ORM; consulta el post sobre Django N+1 para ver ese patrón desde el lado de la aplicación.

Leer buffers: aciertos de caché vs. lecturas de disco

Buffers: shared hit=210 read=8312 informa accesos a páginas de 8KB contra la caché de buffers compartidos:

• shared hit — páginas ya encontradas en la caché de buffers compartidos de PostgreSQL. Rápido; efectivamente velocidad de RAM.
• shared read — páginas que tuvieron que traerse de disco (o de la caché de páginas del sistema operativo, que Postgres no puede distinguir del I/O real de disco) porque no estaban en la caché de buffers.
• shared dirtied — páginas modificadas en esta operación, relevante en escrituras.
• shared written — páginas escritas para hacer espacio, a menudo una señal de presión en la caché de buffers.

Un read alto en relación con hit en una consulta que se ejecuta con frecuencia indica que el conjunto de trabajo no cabe cómodamente en shared_buffers, o que simplemente se trata de una caché fría en una consulta que casi no se ejecuta. Ejecuta el mismo EXPLAIN (ANALYZE, BUFFERS) dos veces seguidas; si la segunda ejecución muestra mayormente hits donde la primera mostró mayormente reads, estabas viendo números de caché fría, no el costo en estado estable.

Los buffers también son la señal de costo más honesta disponible, porque no están escalados por unidades de costo arbitrarias del planificador: son conteos literales de páginas, comparables directamente entre consultas diferentes y entre planes distintos para la misma consulta. Cuando dos índices candidatos producen planes con actual time similar, el que tiene menos accesos totales a buffers está haciendo genuinamente menos trabajo de I/O y se sostendrá mejor bajo carga concurrente.

Los tres patrones que significan falta de índice

Después de suficientes planes, tres formas reaparecen constantemente, y las tres apuntan a la misma causa raíz.

1. Un escaneo secuencial con un filtro selectivo sobre una tabla grande.

Seq Scan on orders o  (cost=0.00..18734.00 rows=812 width=24)
                       (actual time=0.021..142.558 rows=790 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 199210

Escanear 200,000 filas para quedarse con 790 es un plan que dedica casi todo su trabajo a descartar filas. Rows Removed by Filter varios órdenes de magnitud por encima del conteo final de filas, en una tabla demasiado grande para caber cómodamente en caché, es Postgres diciendo implícitamente “no tengo un índice para saltar directamente a las filas que quieres”. Un índice sobre status —o mejor, un índice parcial (WHERE status = 'pending') si ese valor es raro— convierte esto en un index scan que toca solo las filas coincidentes.

2. Un nested loop con un conteo de loops muy alto alimentando un escaneo interno sin índice.

-> Seq Scan on order_items oi
     (actual time=0.412..3.891 rows=6 loops=4000)
   Filter: (order_id = o.id)

Un escaneo secuencial interno ejecutándose miles de veces, filtrando cada vez toda la tabla order_items hasta dejar un puñado de filas coincidentes, es el patrón de loops de la sección anterior combinado con la ausencia de un índice sobre la columna del join. Un índice sobre order_items(order_id) convierte cada uno de esos 4,000 escaneos secuenciales en una búsqueda barata por índice, y el tiempo total de la consulta normalmente cae un orden de magnitud o más.

3. Un sort que se derrama a disco en lugar de completarse en memoria.

Sort  (cost=41293.55..41808.36 rows=205925 width=32)
      (actual time=387.223..421.009 rows=205925 loops=1)
  Sort Method: external merge  Disk: 7128kB
  -> Seq Scan on orders ...

Sort Method: external merge Disk: ...kB significa que el sort no cupo en work_mem y se derramó a archivos temporales en disco, significativamente más lento que un quicksort en memoria o un top-N heapsort. Aquí aplican dos arreglos independientes, y no son mutuamente excluyentes: aumentar work_mem para la sesión o la consulta si el servidor tiene margen, o —normalmente la mejor solución— añadir un índice que coincida con la cláusula ORDER BY para evitar por completo el sort y hacer que las filas salgan ya ordenadas desde un Index Scan en lugar de un nodo Sort.

Los tres patrones comparten la misma historia subyacente: el planificador está haciendo lo mejor que puede con una estrategia secuencial o de fuerza bruta porque no hay una mejor ruta disponible. Para un recorrido completo de qué tipo de índice encaja con cada una de estas formas — B-tree, GIN, GiST o BRIN — la guía de tipos de índices de Postgres cubre esa decisión en profundidad.

Un recorrido completo

Tomemos un endpoint genuinamente lento: “listar pedidos pendientes de la última semana para la cuenta de un cliente, primero los más recientes”. La consulta:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'pending'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

Primer plan, antes de cualquier cambio:

Limit  (actual time=203.441..203.448 rows=14 loops=1)
  -> Sort  (actual time=203.439..203.443 rows=14 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 26kB
        -> Seq Scan on orders  (actual time=0.033..201.887 rows=14 loops=1)
              Filter: ((customer_id = 4821) AND (status = 'pending')
                       AND (created_at > now() - interval '7 days'))
              Rows Removed by Filter: 611982
              Buffers: shared hit=402 read=9812

Leyéndolo: el sort es trivial (14 filas, quicksort en memoria). El Seq Scan descartó 611,982 filas para quedarse con 14, tocando más de 10,000 páginas de buffer — patrón uno, sin ambigüedad. La solución es un índice compuesto que coincida con las columnas del filtro, incluyendo la clave de ordenamiento para que la base de datos potencialmente también pueda omitir un paso de sort separado:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Volviendo a ejecutar la misma consulta después de construir el índice:

Limit  (actual time=0.061..0.089 rows=14 loops=1)
  -> Index Scan using idx_orders_customer_status_created on orders
        (actual time=0.060..0.086 rows=14 loops=1)
        Index Cond: ((customer_id = 4821) AND (status = 'pending')
                      AND (created_at > (now() - interval '7 days')))
        Buffers: shared hit=6

De 203ms a 0.089ms, los accesos a buffers bajaron de ~10,200 a 6, y no hay ningún nodo Sort en absoluto: el orden de columnas del índice ya coincide con lo que necesita LIMIT 20. Ese es todo el ciclo de diagnóstico: leer el árbol, encontrar el nodo con la mayor contribución real al tiempo, hacer coincidir su forma con uno de los tres patrones, corregir el índice y volver a ejecutar para confirmar que la forma del plan realmente cambió.

Errores comunes

Error: comparar números de costo entre consultas distintas. El costo es una estimación interna del planificador sin unidades, calibrada por random_page_cost y similares; solo tiene sentido en relación con otros nodos del mismo plan. Solución: compara actual time y conteos de buffers para comparaciones entre consultas, no cost.

Error: leer actual time en un nodo con loops como si fuera un total. Como se mostró arriba, ese número es un promedio por loop. Solución: multiplícalo siempre por loops antes de juzgar la contribución real de un nodo.

Error: ejecutar EXPLAIN ANALYZE una sola vez con caché fría y concluir que la consulta es lenta en producción. La primera ejecución después de un reinicio o contra datos tocados rara vez paga I/O de disco que el tráfico en estado estable normalmente no paga. Solución: ejecútala dos veces y confía en la proporción de aciertos de buffer, no solo en el primer número.

Error: asumir que “index scan” siempre supera a “seq scan”. En una tabla pequeña, o cuando una consulta necesita de todos modos la mayoría de las filas de la tabla, un escaneo secuencial es genuinamente más rápido: no hay sobrecarga de recorrer el índice. Solución: juzga por el tiempo real y el conteo de filas, no solo por el nombre del nodo.

Error: agregar un índice y no volver a ejecutar EXPLAIN ANALYZE para confirmar que el plan realmente cambió. Postgres no necesariamente usará un índice nuevo si sus estadísticas todavía favorecen el plan anterior, o si el índice no coincide con la columna líder del filtro de la consulta. Solución: vuelve a ejecutar siempre y comprueba que cambió la forma del plan, no solo que la consulta es más rápida; un tiempo más rápido por sí solo no prueba que la solución generalice.

A dónde ir desde aquí

Este post cubre cómo leer un solo plan de forma aislada. El resto de esta serie cubre las decisiones que determinan qué planes son siquiera posibles en primer lugar:

• Elegir el tipo de índice correcto — B-tree, GIN, GiST y BRIN, y qué formas de consulta ayuda realmente cada uno.
• Encontrar y eliminar consultas N+1 en Django — el patrón de loops de este post, visto desde el lado del ORM en lugar del lado del plan.
• Connection pooling para apps Python — porque un plan de consulta rápido no ayuda si las solicitudes están en cola esperando una conexión.
• pgvector vs. una base de datos vectorial dedicada — leer planes para búsqueda por similitud tiene sus propias peculiaridades que vale la pena conocer.
• Migraciones de Postgres sin tiempo de inactividad — construir precisamente los índices que este post recomienda, sin bloquear la tabla que intentas acelerar.

Cierre

Un plan de consulta de Postgres no es una salida misteriosa para ojear buscando números que den miedo: es un informe estructurado y honesto de exactamente lo que hizo la base de datos, en qué orden y cómo se comparó eso con lo que esperaba. Léelo de abajo hacia arriba, compara primero las filas estimadas con las reales, multiplica los nodos con loops por su conteo de loops antes de juzgar su costo, revisa la proporción de aciertos de buffer para detectar presión de caché y compara lo que ves con los tres patrones que significan “el planificador no tiene una buena opción aquí”. Una vez que ese orden de lectura se vuelve automático, EXPLAIN ANALYZE deja de ser una pared de texto y pasa a ser la herramienta de depuración más rápida de todo el stack.

La próxima vez que una consulta sea lenta, ¿tu primer movimiento vendrá del plan o de una suposición?