Todo lo que llevas leído en el módulo son reglas, y en rendimiento las reglas se equivocan. ¿De verdad usa el índice esa consulta? ¿De verdad IN (SELECT ...) se convierte en un semi-join, como prometió 07-05? ¿De verdad el problema son las estadísticas? Hay una sola forma honesta de responder, y es la herramienta que convierte este módulo entero en un método: EXPLAIN. Aquí aprenderás a leer un plan de ejecución nodo a nodo, a distinguir el plan estimado del real, a reconocer la señal que delata unas estadísticas desfasadas, y a interpretar los quince nodos que aparecen en el 95 % de los planes. Y harás la demostración central del módulo: construirás una tabla de pruebas de dos millones de filas —porque con los 20 productos de TiendaVerde no hay nada que demostrar— y verás con tus ojos la diferencia entre recorrer dos millones de filas y saltar directamente a diez.

1. EXPLAIN y EXPLAIN ANALYZE
2. Cómo se lee un plan
3. cost, rows, width, actual time y loops
4. La señal más útil: estimado frente a real
5. Los nodos que más vas a ver
6. Opciones útiles de EXPLAIN
7. El banco de pruebas: dos millones de filas
8. La demostración: sin índice y con índice
9. Y en TiendaVerde real: por qué el índice se ignora
10. Verificando la promesa de 07-05
11. Medir en otros motores y herramientas de visualización
12. Mantenimiento: VACUUM, ANALYZE y el bloat
13. Un método en seis pasos
14. Errores Comunes y Consejos
15. Ejercicios
16. Conclusión del módulo

El primero cuesta microsegundos y te dice qué camino elegiría; el segundo cuesta lo que cueste la consulta y te dice qué pasó de verdad.

⚠️ El aviso imprescindible. EXPLAIN ANALYZE ejecuta la consulta de verdad. Con un SELECT es inofensivo; con un UPDATE, un DELETE o un INSERT, modifica los datos. La forma segura de analizarlos es envolverlos en una transacción que se deshace:

El plan se muestra, las filas se borran… y el ROLLBACK lo deshace todo. BEGIN, COMMIT y ROLLBACK son el módulo 9; aquí basta con usar este patrón como una precaución obligatoria. Es la misma red de seguridad que ya viste en 05-04 antes de un DELETE sin WHERE.

Un plan es un árbol de nodos: cada uno recibe filas de sus hijos, hace algo con ellas y se las pasa a su padre. La salida de texto lo representa con sangría y flechas ->, y se lee de una forma concreta:

De dentro hacia fuera y de abajo hacia arriba. El nodo más sangrado se ejecuta primero; el de la primera línea, el último, y es el que produce el resultado final.

En texto, ese mismo árbol se imprime al revés: Limit arriba del todo, los dos Seq Scan al fondo. Las dos preguntas que responde la forma del árbol son siempre las mismas: ¿por dónde entra el motor a los datos? (los nodos de las hojas) y ¿cómo los combina? (los nodos de unión).

Cada línea de un plan lleva sus números. Con EXPLAIN a secas solo hay estimaciones; con ANALYZE se añade la segunda mitad entre paréntesis:

El coste de arranque distingue dos familias de nodos: los que emiten filas según las leen (Seq Scan, Index Scan, Nested Loop) tienen arranque ≈ 0; los que necesitan todas las filas antes de devolver la primera (Sort, Hash, HashAggregate) lo tienen alto. Eso importa mucho con LIMIT.

La trampa de loops. actual time y rows son medias por ejecución, no totales. Un nodo con actual time=0.05..0.08 rows=3 loops=20000 no tardó 0,08 ms: tardó 0,08 × 20.000 = 1,6 segundos, y produjo 60.000 filas. Multiplica siempre por loops antes de decidir dónde está el problema.

Al final del plan aparecen Planning Time (lo que tardó en decidir el plan) y Execution Time (lo que tardó en ejecutarlo). Si son comparables, tienes una consulta trivial ejecutada muchísimas veces: el caso de las sentencias preparadas y, a menudo, del N+1 de 08-04.

Si solo te llevas una cosa de esta lección, que sea esta: compara rows= estimadas con rows= reales en cada nodo. Una divergencia grande —de un orden de magnitud o más— es la causa raíz de casi todos los planes malos.

El planificador esperaba 42 filas y salieron 284.561. Con 42 filas, encadenar un Nested Loop es la decisión perfecta; con 284.561, es una catástrofe. El plan no está mal elegido: está bien elegido para una realidad que no existe.

Las cuatro causas, en orden de frecuencia: estadísticas desfasadas tras una carga o un crecimiento, que se arreglan con ANALYZE tabla;; columnas correlacionadas que el planificador supone independientes, con CREATE STATISTICS; un predicado que no sabe estimar (expresión compleja, función propia), que hay que reescribir o indexar como expresión; y una muestra demasiado pequeña para una distribución muy desigual, con ALTER TABLE ... SET STATISTICS. Las cuatro se desarrollan en 08-04. Cuando estimado y real se parecen, el plan suele ser el mejor disponible: si aun así va lento, el problema es de índices o de volumen, no del planificador.

Dos líneas auxiliares que conviene mirar siempre: Filter: con su Rows Removed by Filter: (filas leídas y tiradas — si son millones, falta un índice o el filtro debería estar antes) e Index Cond: (la parte de la condición que sí resolvió el índice; lo que quede en Filter es lo que hubo que comprobar fila a fila).

Van entre paréntesis y separadas por comas: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;.

Por qué BUFFERS importa tanto: el tiempo depende de si la máquina está ocupada, de si el dato estaba en caché y de qué más se ejecuta a la vez. Los bloques leídos, no. Un plan que lee 16.250 bloques leerá 16.250 bloques hoy, mañana y en el portátil de tu compañero. En una máquina compartida —un servidor de integración, un contenedor, la nube— los bloques son la única métrica reproducible que tienes.

Aquí hay que ser honesto: con TiendaVerde no se puede demostrar nada de esto. Veinte productos y cuarenta y siete líneas caben en una página de disco, y el planificador hará Seq Scan siempre y con toda la razón. Enseñarte un plan inventado sobre esas tablas sería mentirte. Así que vamos a construir una tabla grande y reproducible con generate_series.

⚠️ BANCO DE PRUEBAS DEL MÓDULO 8. La tabla pedidos_grandes no forma parte del esquema canónico de TiendaVerde (01-06). No tiene claves foráneas, no está relacionada con ninguna otra tabla y ningún módulo posterior la usa. Cuando termines la lección, bórrala: DROP TABLE pedidos_grandes;.

(Una ejecución de ejemplo. El tamaño exacto depende de la versión y de la alineación de los tipos; el orden de magnitud, no.) Frente a la única página de productos, aquí hay dieciséis mil doscientas. Ahora sí hay algo que optimizar.

Empecemos sin índice: cliente_id no tiene ninguno — solo lo tiene id, por su clave primaria.

(Una ejecución de ejemplo: los milisegundos y los costes dependen de tu máquina. Lo que hay que leer son las proporciones y los tipos de nodo.) Tres cosas que este plan grita: Parallel Seq Scan, porque no hay índice y hay que recorrer la tabla entera —es tan grande que PostgreSQL reparte el trabajo entre tres procesos y los recoge con Gather—; Rows Removed by Filter: 666663 por trabajador, es decir dos millones de filas leídas y tiradas para quedarse con diez; y Buffers: shared hit=16250, las 16.250 páginas de la tabla, todas.

Ahora el índice, y la misma consulta:

La comparación, que es la razón de ser del módulo entero:

Y fíjate en Index Cond frente a Filter: en el segundo plan no hay Filter. El índice no filtró después de leer: se posicionó directamente en las diez filas. Esa es la diferencia entre buscar y descartar.

Con un filtro que devuelve bastantes filas dispersas, el planificador elige un camino intermedio:

Se lee de abajo arriba: el Bitmap Index Scan recorre el índice y construye un mapa de bits de los bloques que contienen coincidencias; el Bitmap Heap Scan los lee en orden físico, una sola vez cada uno. Con 2.010 filas repartidas por casi 2.000 bloques distintos, un Index Scan haría 2.010 saltos aleatorios; el bitmap los convierte en un recorrido casi secuencial de 1.988 bloques. Es la respuesta del planificador a "muchas filas, pero no toda la tabla".

Vuelve al mundo de las 20 filas y crea el índice más razonable del catálogo, sobre productos.precio:

Seq Scan, con el índice recién creado y sin estrenar. Y el planificador tiene toda la razón. Mira su coste: 1,25. Se descompone así: 1 página × 1,0 (leer el único bloque de la tabla) + 20 filas × 0,01 (procesar cada una) + 20 × 0,0025 (evaluar el filtro) = 1,25. No hay nada más barato que eso, porque la tabla entera es un solo acceso.

Podemos obligarle a usar el índice para ver qué habría pasado:

Coste 12,35 frente a 1,25: diez veces más caro. Y el tiempo real, el doble. El motivo es exactamente el de 08-01: usar el índice obliga a leer la página de metadatos, bajar por el árbol, obtener siete ctid y volver a leer la misma página de la tabla que el Seq Scan habría leído de un tirón. Tres o cuatro accesos para hacer el trabajo de uno. Tres conclusiones, que cierran la primera lección del módulo: un índice sobre una tabla que cabe en una página nunca compensa, y el planificador lo sabe; enable_seqscan = off es una herramienta de diagnóstico, no una solución, y sirve para responder a "¿qué habría hecho con el índice?" y nada más; y los índices que creaste en 08-02 no aceleran nada hoy — son correctos, están bien diseñados y serían decisivos con volumen real, pero con 20 filas no hay problema que resolver.

07-05 afirmó que PostgreSQL convierte IN (SELECT ...) en un semi-join, y que por eso su rendimiento es equivalente al de un JOIN. Comprobémoslo:

Hash Semi Join. Escribiste una subconsulta y el motor ejecutó un join — un join especial que se detiene en la primera coincidencia de cada fila izquierda, y por eso devuelve 12 clientes y no 20 filas. La promesa era cierta, y ahora no lo crees porque lo leíste: lo has visto. Prueba a cambiar IN por NOT IN y verás que el Semi Join desaparece y aparece un filtro con una subconsulta ejecutada aparte: la confirmación visual de por qué 07-05 desaconsejaba NOT IN.

Nota de dialecto: los conceptos viajan (recorrido secuencial, acceso por índice, tipos de join, estimado frente a real), pero el vocabulario y las unidades no. El cost de PostgreSQL no es comparable con el de MySQL ni con el de Oracle. Y hay diferencias de fondo: SQL Server y MySQL/InnoDB usan índices agrupados, donde la tabla es el índice primario, así que su equivalente del Index Scan no necesita el segundo acceso del que hablaba 08-01. El SET STATISTICS IO ON de SQL Server es el pariente cercano de BUFFERS, y por la misma razón: cuenta lecturas lógicas, que son reproducibles.

Y cuatro herramientas que hacen legible un plan de cien líneas: explain.dalibo.com, donde pegas el plan (mejor en FORMAT JSON) y lo dibuja como árbol resaltando el nodo más caro y las estimaciones erróneas; pev2, el mismo visualizador integrable en tus propias herramientas; auto_explain, un módulo del servidor que registra automáticamente el plan de toda consulta que supere un umbral de tiempo —imprescindible para lo que solo falla en producción—; y pg_stat_statements, el ranking de qué consultas analizar (08-03).

Hay una parte del rendimiento que no depende de tus consultas. En PostgreSQL, un UPDATE no modifica la fila: escribe una versión nueva y marca la vieja como muerta. Un DELETE tampoco borra: marca. Las versiones muertas se acumulan y engordan tablas e índices sin aportar nada: es el bloat. Sus efectos son medibles: una tabla con un 60 % de espacio muerto ocupa el triple de páginas de las necesarias, así que cada Seq Scan lee el triple, la caché rinde un tercio y los Index Only Scan pierden eficacia (suben los Heap Fetches).

En condiciones normales, autovacuum se encarga solo. Se vuelve un problema cuando una tabla recibe muchísimas actualizaciones, o cuando una transacción lleva horas abierta e impide limpiar filas muertas que aún podría necesitar. Y aquí llegamos al límite de este módulo: el porqué de todo esto —por qué el motor guarda varias versiones de cada fila y por qué una transacción abierta bloquea la limpieza— es el modelo MVCC, y se estudia en el módulo 9. Por ahora quédate con lo operativo: ANALYZE tras cualquier carga masiva, y n_dead_tup disparado como señal de alarma.

Esto es lo que de verdad hay que llevarse del módulo. Ante una consulta lenta:

1. Reproduce y mide. Consigue la consulta exacta con sus parámetros reales y ejecútala con EXPLAIN (ANALYZE, BUFFERS). Sin medida no hay diagnóstico.
2. Localiza el nodo culpable. Busca el nodo con más actual time multiplicado por loops, y el que más bloques lee. Casi siempre es uno solo y está abajo del todo.
3. Compara estimado con real. Si rows estimadas y reales divergen mucho, el problema son las estadísticas: ANALYZE, y vuelve al paso 1. No sigas optimizando sobre un plan basado en datos falsos.
4. Mira qué está leyendo de más. Seq Scan sobre una tabla grande con un Rows Removed by Filter enorme = falta un índice o la condición no es sargable. Sort ... Disk = falta un índice de ordenación o memoria. Nested Loop con miles de loops = mala estimación en la rama izquierda.
5. Aplica UNA sola intervención y vuelve a medir. Reescribir (08-04) antes que indexar (08-02); indexar antes que cambiar la arquitectura. Un cambio cada vez, o no sabrás cuál funcionó.
6. Verifica que el plan cambió, no solo que el tiempo bajó (pudo bajar por la caché). Y semanas después, comprueba en pg_stat_user_indexes que el índice se sigue usando.

• Lanzar EXPLAIN ANALYZE sobre un UPDATE o un DELETE sin transacción. Lo ejecuta de verdad. BEGIN ... ROLLBACK, siempre.
• Leer el plan de arriba abajo como si fuera código. Se lee de dentro afuera: el nodo más sangrado va primero. Y no olvides multiplicar por loops: un nodo de 0,08 ms con 20.000 vueltas son 1,6 segundos, y suele ser el culpable.
• Comparar el cost de dos motores, o de dos servidores con configuración distinta. Son unidades arbitrarias y relativas.
• Fiarse solo del tiempo en una máquina compartida. Usa BUFFERS: los bloques leídos son reproducibles. Y no midas una sola vez: la primera ejecución llena la caché.
• Dejar enable_seqscan = off puesto. Es para diagnosticar, jamás para producción: obligas al planificador a elegir mal el resto de sus decisiones. Y no optimices sin ANALYZE previo: si las estimaciones están mal, todo lo que hagas encima está mal.
• Consejo: guarda el plan de antes. Un fichero con el EXPLAIN previo al cambio es la única prueba objetiva de que has mejorado algo.
• Consejo: analiza con datos reales de producción. Un plan sobre 100 filas de desarrollo no dice absolutamente nada sobre 20 millones, y los problemas que solo aparecen a las tres de la mañana no se reproducen a mano: para eso está auto_explain.

Lee este plan, tomado de un TiendaVerde de tamaño real (2 millones de pedidos, no el banco de pruebas de esta lección):

1. ¿En qué orden se ejecutan los nodos?
2. ¿Cuál es el problema principal y qué señal lo delata?
3. ¿Cuánto tiempo total consume realmente el Index Scan?
4. Propón dos intervenciones, en el orden en que las probarías.

Sobre pedidos_grandes ya creada e indexada por cliente_id, predice antes de ejecutar qué nodo elegirá el planificador en cada caso, y después compruébalo con EXPLAIN ANALYZE.

Escribe la secuencia completa de comandos —incluidas las precauciones— para analizar el rendimiento de este DELETE sin modificar los datos, y di qué buscarías en el plan.

Solución 1

1. El orden. Primero el Seq Scan sobre pedidos (el nodo más profundo de la rama izquierda); para cada fila que produce, el Nested Loop ejecuta el Index Scan sobre clientes; el Nested Loop emite el resultado.

2. El problema es una estimación catastróficamente errónea. La señal está en el Seq Scan: rows=38 estimadas frente a rows=18422 reales, casi 500 veces más. Con 38 filas, un Nested Loop es la elección perfecta; con 18.422 se convierte en 18.422 búsquedas indexadas. Y hay dos síntomas de apoyo: Rows Removed by Filter: 1981578 (se leen dos millones de filas para quedarse con el 0,9 %, así que falta un índice sobre estado) y Buffers: shared hit=1204885, más de un millón de bloques leídos para devolver 18.422 filas.

3. El tiempo del Index Scan. actual time es por ejecución: 0,004 ms × 18.422 loops ≈ 74 ms. Es decir, el Index Scan no es el problema: de los 9.421 ms, 1.842 son el Seq Scan, 74 el Index Scan, y el resto se lo come la maquinaria de repetir el bucle 18.422 veces. Olvidar multiplicar por loops es el error de lectura más común.

4. Las intervenciones, en orden. Primero, ANALYZE pedidos; y volver a mirar el plan: es gratis, instantáneo y ataca la causa raíz — si la estimación pasa a ser correcta, el planificador cambiará solo el Nested Loop por un Hash Join y el tiempo caerá sin tocar nada más. Después, si la estimación ya era correcta, un índice para los pendientes, mejor parcial: CREATE INDEX ... ON pedidos (cliente_id) WHERE estado = 'pendiente';, porque son menos del 1 % y así el índice es minúsculo (08-02).

Solución 2

Solución 3

La precaución clave es el BEGIN ... ROLLBACK: EXPLAIN ANALYZE ejecuta el DELETE de verdad, y sin la transacción perderías las filas. Qué buscar en el plan:

• Cómo localiza las filas. Sin índice sobre fecha_pedido, un Seq Scan con un Rows Removed by Filter enorme. Con índice, un Bitmap Heap Scan — y como los datos se generaron en orden cronológico, las filas a borrar están físicamente juntas, que es el mejor escenario posible.
• Cuántas filas borra. El rows real del nodo de borrado: unos 285.000 (365 días × 782 al día). Y la comparación estimado/real, como siempre.
• Una consideración que este módulo no puede resolver: un DELETE de 285.000 filas deja 285.000 versiones muertas que VACUUM tendrá que limpiar, y mantiene bloqueos durante toda la transacción. Por eso los borrados masivos se hacen por lotes, y por eso el particionado (08-04) es tan atractivo para los históricos: borrar un año entero es desconectar una partición, no ejecutar un DELETE.

Cierras el módulo 8 con el método, no solo con las reglas:

• EXPLAIN estima; EXPLAIN ANALYZE ejecuta y mide — y por eso un UPDATE o un DELETE hay que envolverlos en BEGIN ... ROLLBACK.
• Un plan es un árbol que se lee de dentro afuera. cost son unidades relativas, rows y width son estimaciones, y actual time y rows son medias por ejecución: hay que multiplicarlas por loops.
• La señal más útil de todas es la divergencia entre las filas estimadas y las reales. Cuando es grande, el plan está bien elegido para una realidad que no existe, y la causa suele ser ANALYZE.
• Sabes reconocer los quince nodos habituales, desde el Seq Scan hasta el Gather, y qué significa que aparezcan Rows Removed by Filter, Heap Fetches o Sort Method: external merge Disk. Y sabes que BUFFERS es mejor métrica que el tiempo en cualquier máquina compartida: los bloques leídos son reproducibles.
• Lo has visto medido, no contado: sobre dos millones de filas, la misma consulta pasa de 16.250 bloques y ~142 ms a 13 bloques y ~0,09 ms. Mientras que sobre los 20 productos de TiendaVerde el índice cuesta diez veces más que el Seq Scan, y el planificador acierta al ignorarlo. Y la promesa de 07-05 queda verificada con su nombre en el plan: Hash Semi Join.

Y con esto se cierra el módulo 8. En cinco lecciones has pasado de "no sé por qué esto va lento" a tener un procedimiento: sabes qué es un B-tree y por qué 15 millones de filas caben en 3 niveles; sabes que TiendaVerde tenía once índices que nadie creó y once claves foráneas sin cubrir, porque PostgreSQL no las indexa; sabes crear índices compuestos, parciales, de expresión y cubridores, y elegir entre B-tree, hash, GIN, GiST, BRIN y SP-GiST; sabes —y esto es lo más raro de encontrar— cuándo no indexar; sabes reescribir una consulta para hacerla sargable, detectar un N+1 y actualizar las estadísticas del planificador; y sabes leer un plan de ejecución y decidir con datos.

Pero hay una suposición que hemos mantenido durante ochenta y tantas lecciones sin decirlo en voz alta: que somos el único usuario de la base de datos. Todo lo que has medido aquí supone que nadie más está leyendo ni escribiendo al mismo tiempo, que ninguna fila cambia mientras la consultas y que ningún UPDATE compite con el tuyo por la misma línea de pedido. Eso es falso en cualquier sistema real: en TiendaVerde, un cliente confirma un pedido mientras el almacén actualiza el stock del mismo producto y el analista lanza el informe mensual sobre esas mismas tablas. En el módulo 9, Transacciones, verás qué es una transacción y las cuatro garantías ACID, cómo se controlan con BEGIN, COMMIT, ROLLBACK y SAVEPOINT, qué son los niveles de aislamiento y qué anomalías permite cada uno, y cómo funcionan los bloqueos y los interbloqueos — incluido, por fin, el modelo MVCC que explica el bloat de esta lección y por qué CREATE INDEX CONCURRENTLY tenía que existir.