Introducción

Las consultas lentas son el síntoma más visible de un sistema backend bajo carga. En PostgreSQL, la diferencia entre una consulta que escanea millones de filas y una que recupera exactamente diez suele reducirse a una sola cosa: el índice correcto. Esta lección recorre los tres tipos de índices que usarás con más frecuencia en el trabajo backend — B-tree, compuesto y parcial — y te muestra cómo leer un plan de EXPLAIN ANALYZE para saber cuál necesitas.

Índices B-tree: El Caballo de Batalla por Defecto

Un índice B-tree es lo que PostgreSQL crea cuando simplemente escribes CREATE INDEX. Mantiene los valores de la columna ordenados, por lo que las búsquedas por igualdad (=) y por rango (>, <, BETWEEN) son rápidas. Úsalo siempre que una columna aparezca con frecuencia en cláusulas WHERE o en condiciones de JOIN.

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email = 'alice@example.com';

Sin el índice, PostgreSQL realiza un escaneo secuencial — lee cada fila. Con el índice, salta directamente a la entrada coincidente. En una tabla con diez millones de filas, esa diferencia es el salto entre 200ms y 2ms.

Índices Compuestos: El Orden Importa

Cuando una consulta filtra u ordena por múltiples columnas, un índice compuesto puede servir para ambas a la vez. El detalle crítico es el orden de las columnas: el índice solo puede usarse de forma eficiente cuando las columnas iniciales están restringidas.

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Aquí el índice sobre (user_id, created_at) permite a PostgreSQL filtrar por user_id y tener ya las filas ordenadas por created_at — sin necesidad de un paso de ordenación adicional. Un error común es crear el índice con las columnas en el orden equivocado. Un índice sobre (created_at, user_id) no ayudaría a esta consulta, porque user_id no es la columna inicial.

Índices Parciales: Indexa Solo lo que Importa

Un índice parcial añade una cláusula WHERE al propio índice, de modo que solo almacena las filas que cumplen una condición. Esto mantiene el índice pequeño y rápido cuando la mayoría de tus consultas solo interesan por un subconjunto de la tabla.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

Si el 99% de los pedidos ya están completados y solo el1% está pendiente, un índice completo sobre status sería un desperdicio. El índice parcial podría ser 1000 veces más pequeño e igualmente útil para las consultas que realmente importan.

Cómo Leer EXPLAIN ANALYZE

EXPLAIN ANALYZE ejecuta la consulta y reporta el plan real que PostgreSQL eligió, con tiempos y recuentos de filas reales. Las señales más importantes a buscar:

- Sequential Scan sobre una tabla grande → probablemente necesitas un índice. - Filas eliminadas por el filtro mucho mayores que las Filas estimadas por el planificador → tus estadísticas están desactualizadas; ejecuta ANALYZE nombre_tabla. - Un nodo Sort encima de un conjunto grande → considera un índice que coincida con el ORDER BY, o añade un LIMIT antes.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Busca Index Scan usando idx_orders_user_date en la salida. Si ves Seq Scan en su lugar, el índice no se está utilizando — generalmente porque la tabla es demasiado pequeña para que el planificador se moleste, o tus estadísticas están desactualizadas.

Errores Comunes

Indexar cada columna es peor que no indexar ninguna: cada índice ralentiza las escrituras (INSERT, UPDATE, DELETE) porque PostgreSQL también debe actualizar el índice. Una buena regla es: indexa las columnas que aparecen en WHERE, ORDER BY o JOIN de consultas que se ejecutan con frecuencia y tocan muchas filas.

Otro error frecuente es usar una función sobre la columna indexada en WHERE, lo que impide el uso del índice:

-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';

-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

Si necesitas búsqueda insensible a mayúsculas y minúsculas, almacena una columna normalizada o crea un índice funcional: CREATE INDEX ON users (lower(email)).

Resumen

La indexación efectiva en PostgreSQL se reduce a hacer coincidir el tipo de índice y el orden de las columnas con los patrones reales de consulta. B-tree maneja la mayoría de las búsquedas por igualdad y rango. Los índices compuestos combinan filtrado y ordenación cuando la columna inicial está restringida. Los índices parciales reducen el índice solo a las filas que importan. EXPLAIN ANALYZE es tu bucle de retroalimentación — ejecútalo siempre antes y después de añadir un índice para confirmar que el plan realmente cambió.