Índice, EXPLAIN y la query lenta

Casi toda query lenta que he investigado caía en una de tres categorías: falta el índice, el índice existe pero no puede usarse, o el índice se usa y aun así la base va a la tabla a buscar el resto.

Las tres tienen diagnóstico y corrección distintos, y las tres aparecen en el EXPLAIN.

Qué es un índice, por dentro#

Un índice de base relacional es un B+tree. Cada nodo ocupa una página de disco y guarda centenares de claves con punteros. Con un factor de ramificación de unos trescientos, un árbol de tres niveles direcciona veintisiete millones de registros.

Tres accesos a disco en el peor caso, y los dos primeros niveles casi siempre están en memoria, así que en la práctica es uno.

La variante "más" significa que los datos quedan solo en las hojas, y las hojas están enlazadas entre sí en una lista. Eso deja los nodos internos más ligeros y convierte el barrido por rango en caminar por una lista enlazada.

Por eso el índice de base de datos es un árbol y no un hash: el hash resuelve igualdad en tiempo constante y no resuelve ni rangos ni ordenación. Y los rangos y la ordenación son la mayor parte de tus queries.

El coste del índice#

El índice acelera la lectura y frena la escritura, porque toda inserción, actualización y borrado tiene que actualizar todos los árboles relevantes. El índice también ocupa disco y memoria.

Consecuencia: un índice no usado es puro coste. Y casi toda base de producción tiene varios.

En Postgres, la consulta es directa:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Los índices con cero barridos desde el último reset de las estadísticas son candidatos a eliminación. Comprueba antes que no sean de unicidad: esos tienen otra función.

El orden de las columnas lo decide todo#

Un índice compuesto sigue el principio de la guía telefónica: ordenado por apellido y después por nombre.

Encuentras todos los "Silva" rápidamente. Encuentras "Silva, Juan" rápidamente. Pero si solo sabes el nombre "Juan", la guía no ayuda: habría que leerla entera.

Un índice en (a, b) sirve para:

• filtro en a
• filtro en a y b

No sirve para un filtro solo en b.

La regla práctica para montar un índice compuesto: pon primero las columnas usadas con igualdad, después la columna usada con rango, y por último las usadas solo para ordenar.

Qué impide que el índice se use#

Existen patrones que anulan el índice aun estando creado. Los más frecuentes:

Función sobre la columna. WHERE upper(email) = 'X' no usa el índice en email. Solución: crear un índice sobre la expresión, o normalizar en la escritura.

LIKE con comodín al principio. WHERE nombre LIKE '%silva' no puede usar un B-tree, porque el árbol está ordenado por el comienzo de la cadena. Para eso existe el índice de trigramas o la búsqueda textual.

Comparación entre tipos distintos. Una columna varchar comparada con un número fuerza conversión y tumba el índice.

Baja selectividad. Si la condición devuelve la mitad de la tabla, barrer la tabla es más rápido que saltar del índice a la tabla miles de veces. La base lo sabe y elige el Seq Scan a propósito. En ese caso, el Seq Scan no es el problema.

Index only scan: la optimización subestimada#

Cuando la base usa un índice, normalmente encuentra el puntero y va a la tabla a buscar las columnas que faltan. Son dos accesos.

Si el índice contiene todas las columnas que la query necesita, la base responde sin tocar la tabla. Eso es un index only scan, y es un acceso en vez de dos.

En Postgres lo consigues con INCLUDE:

CREATE INDEX idx_pedidos_cliente
ON pedidos (cliente_id, creado_en)
INCLUDE (estado, valor);

Las columnas del INCLUDE no participan en la ordenación: solo quedan almacenadas en la hoja. Es la diferencia entre pagar un acceso y pagar dos, en cada ejecución.

En mi experiencia, es el cambio de índice con mejor relación entre esfuerzo y ganancia que existe, y el menos aplicado.

Cómo leer un EXPLAIN ANALYZE#

Quieres mirar tres cosas, en este orden.

Primera: el tipo de acceso. Un Seq Scan en una tabla grande con filtro selectivo es señal de índice faltante. Index Scan es bueno. Index Only Scan es óptimo. Bitmap Heap Scan aparece cuando muchas filas encajan y la base decide ordenar los accesos a disco, normalmente razonable.

Segunda: estimación contra realidad. El plan muestra cuántas filas esperaba la base y cuántas vinieron. Si esperaba diez y vinieron cien mil, las estadísticas están desactualizadas, y el plan elegido fue malo por falta de información. Ejecuta ANALYZE en la tabla.

Tercera: dónde está el tiempo. Cada nodo del plan muestra el tiempo acumulado. Busca el nodo que consume la mayor porción y trabaja en él. No optimices el resto.

Y añade BUFFERS al EXPLAIN: muestra cuántas páginas se leyeron de la caché y cuántas del disco. Una query que lee mucho del disco es candidata a caber mejor en memoria o a necesitar un índice más ligero.

El guion completo#

Cuando una query esté lenta:

1. Ejecuta EXPLAIN (ANALYZE, BUFFERS).
2. Si es Seq Scan con filtro selectivo → crea el índice, respetando el orden de las columnas.
3. Si es Index Scan pero con muchos accesos a la tabla → considera INCLUDE para convertirlo en Index Only Scan.
4. Si la estimación está muy equivocada → ejecuta ANALYZE y considera aumentar el objetivo de estadística en esa columna.
5. Si nada de eso lo resuelve → el problema puede no ser la query. Puede ser un lock, puede ser I/O saturado, puede ser un plan malo por parámetro. Ahí subes un nivel y te vas a observabilidad.