Cómo leer planes de consulta: EXPLAIN y EXPLAIN (ANALYZE, BUFFERS)

Cuando una consulta de repente se arrastra, adivinar es una pérdida de tiempo: pregúntale a la base de datos qué planea hacer realmente. En PostgreSQL, para eso sirve EXPLAIN: imprime el plan de ejecución que eligió el planificador. Y EXPLAIN (ANALYZE, BUFFERS) va más allá, ejecutando de verdad la consulta e informando los tiempos reales y los accesos a disco. Este artículo recorre cómo leer un plan, en qué se diferencia Seq Scan de Index Scan, por qué las estimaciones se desvían de la realidad y cómo un plan revela un índice ausente.

Usaremos un pequeño esquema de comercio electrónico: users, orders y employees.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN solo estima el plan sin ejecutar la consulta. EXPLAIN ANALYZE la ejecuta de verdad (¡cuidado con INSERT/UPDATE/DELETE!) y adjunta números reales.

-- plan and estimates only; the query does NOT run
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

-- real execution + timings + cache/disk reads
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

Una línea superior típica de un plan:

Seq Scan on orders  (cost=0.00..1834.00 rows=120 width=64)
                    (actual time=0.015..12.4 rows=118 loops=1)

Lo que importa aquí:

• cost=0.00..1834.00 — coste estimado en unidades arbitrarias: coste de arranque y coste total. No son milisegundos.
• rows=120 — cuántas filas espera el planificador.
• actual time=... y actual rows=118 — lo que realmente ocurrió.
• loops=1 — cuántas veces se ejecutó el nodo (crucial dentro de bucles anidados).

Para ejecutar EXPLAIN ANALYZE sobre una consulta que modifica datos sin efectos secundarios, envuélvela en una transacción:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;  -- nothing is actually deleted

Seq Scan vs Index Scan

Un Seq Scan (recorrido secuencial) lee la tabla entera fila por fila. Eso no siempre es malo: si necesitas la mayor parte de la tabla, leerla de corrido sale más barato que saltar de un lado a otro por un índice.

Un Index Scan usa un índice para localizar con precisión las filas coincidentes. Gana cuando el predicado selecciona una fracción pequeña de la tabla (un filtro selectivo).

-- selective: one user out of a million -> Index Scan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- non-selective: half the table -> a Seq Scan is expected and fine
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'paid';

También existe el Bitmap Heap Scan, un término medio: PostgreSQL reúne las coincidencias del índice en un mapa de bits y luego lee la tabla en lotes según el orden físico. El planificador lo elige para una selectividad "media": de cientos a miles de filas.

Resiste la tentación de "arreglar" el planificador con hints; en su lugar, dale estadísticas frescas:

ANALYZE orders;  -- rebuild the data distribution statistics

Detalle a tener en cuenta: MySQL también tiene EXPLAIN ANALYZE (desde la 8.0), pero el formato de salida difiere y los tiempos se informan por nodo en tiempo real. ClickHouse usa EXPLAIN PLAN / EXPLAIN PIPELINE y no tiene el concepto de índice a nivel de fila; en su lugar tiene una clave primaria dispersa y el salto de gránulos.

Filas estimadas vs reales

La habilidad más valiosa de todas es comparar rows (estimación) con actual rows (realidad). Una diferencia grande significa que el planificador tenía malas estadísticas y puede haber elegido un plan pobre.

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'DE' AND o.created_at > '2026-01-01';

Si ves algo como:

Nested Loop  (rows=5 ... actual rows=48000 loops=1)

— eso es una señal de alarma. El planificador esperaba 5 filas y eligió un Nested Loop, pero obtuvo 48000. Con ese volumen, un Hash Join habría sido mucho más barato. Causas habituales de la diferencia:

• estadísticas obsoletas — se corrige con ANALYZE;
• columnas correlacionadas (p. ej. city y country) — se ayuda con CREATE STATISTICS;
• expresiones complejas en el filtro que no tienen estadísticas.

Lee las estimaciones de abajo hacia arriba: un error en un nodo hoja se infla a través de todo el árbol del join.

Cómo detectar un índice ausente

Un índice ausente tiene una firma muy reconocible: un Seq Scan sobre una tabla grande con un predicado selectivo en Filter, donde Rows Removed by Filter es enorme.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE email = 'anna@example.com';

Seq Scan on employees  (cost=0.00..21000 rows=1 ...)
  Filter: (email = 'anna@example.com')
  Rows Removed by Filter: 499999
  Buffers: shared read=8200

Las señales de advertencia:

• un Seq Scan donde esperabas una sola fila (rows=1);
• Rows Removed by Filter: 499999 — el motor recorrió media tabla para nada;
• Buffers: shared read=8200 — muchos bloques traídos desde el disco.

La solución es un índice sobre la columna filtrada:

CREATE INDEX idx_employees_email ON employees (email);

Después de eso, el mismo plan se convierte en un Index Scan con Buffers: shared read=4 y tiempos de microsegundos. Para consultas que solo necesitan unas pocas columnas, considera un índice de cobertura:

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at) INCLUDE (status);

Detalle a tener en cuenta: un índice no se usará si la columna está envuelta en una función (WHERE lower(email) = ...); el planificador no puede emparejarlo. O bien construye un índice funcional (CREATE INDEX ... ON employees (lower(email))), o bien saca la función del predicado.

Adopta el hábito de ejecutar EXPLAIN (ANALYZE, BUFFERS) en cualquier consulta lenta antes de recurrir a un índice o reescribir el SQL. El plan te dice exactamente a dónde se va el tiempo, y a partir de ahí es ingeniería, no adivinanza.