Índices compuestos en Postgres: siete patrones que los equipos diseñan mal

El índice existe. EXPLAIN muestra un Index Scan. La latencia p99 sigue en 800 ms. Se creó el índice compuesto sobre (created_at, tenant_id, status) porque esas tres columnas aparecen en la cláusula WHERE — pero la consulta filtra tenant_id = 42 y status = 'active' con created_at > now() - interval '30 days'. Postgres usa el índice primero para el rango de fechas, lee treinta días de filas de todos los tenants y luego filtra. El índice compuesto en Postgres funciona exactamente como dicta el orden btree. El equipo construyó decoración.

Los índices compuestos se ordenan lexicográficamente: filas ordenadas por la columna uno, y dentro de valores iguales de la columna uno por la columna dos, y así sucesivamente. El planner solo puede usar el índice de forma eficiente cuando la consulta aporta un prefijo contiguo de columnas — empezando por la izquierda. Entender esa regla evita siete patrones recurrentes de mal uso que desperdician disco, ralentizan escrituras y engañan dashboards que cuentan índices sin leer planes.

La regla del prefijo izquierdo gobierna cada decisión de índice compuesto

Un índice btree sobre (a, b, c) soporta:

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b > ?
• WHERE a = ? ORDER BY b

No soporta:

• WHERE b = ? solo
• WHERE a = ? AND c = ? sin b en el predicado (Postgres puede usar skip scan en algunas versiones, pero no conviene confiar en ello)
• WHERE c = ? solo

El orden de la cláusula WHERE en SQL no importa — el planner reordena predicados. El orden de columnas del índice importa por completo.

El orden de columnas del índice no es estilístico. Es el plan de consulta.

Regla práctica: predicados de igualdad primero, en orden de selectividad dentro de las igualdades; predicados de rango y columnas de ORDER BY al final. (tenant_id, status, created_at) para WHERE tenant_id = ? AND status = ? AND created_at > ?. No (created_at, tenant_id, status) cuando created_at es un rango sobre una columna temporal de alta cardinalidad.

Siete patrones de índices compuestos que los equipos diseñan mal

1. Columna de rango al inicio del índice

Liderar con created_at o updated_at cuando la consulta usa un rango (>, <, BETWEEN) obliga al índice a escanear cada fila en la ventana de fechas antes de aplicar filtros de tenant o status. Colocar la columna de rango al final, después de filtros de igualdad en columnas de alta selectividad.

2. Columna de baja cardinalidad primero

Liderar con status (cinco valores) o is_active (booleano) estrecha poco. El índice agrupa la mayor parte de la tabla en pocos buckets. (tenant_id, status) supera a (status, tenant_id) cuando ambas aparecen en consultas — tenant suele filtrar más filas.

3. Indexar para condiciones OR

WHERE tenant_id = 1 OR tenant_id = 2 puede no usar un índice compuesto de forma eficiente. WHERE tenant_id IN (1, 2) suele comportarse mejor. Índices separados en ramas OR a veces superan a un compuesto mal ordenado. Revisar EXPLAIN — Bitmap Index Scans que fusionan varios índices son válidos.

4. Índices de una columna redundantes

Un índice sobre (tenant_id, created_at) hace redundante un (tenant_id) independiente para consultas que filtran por tenant_id. Eliminar el índice redundante — duplica el costo de escritura. Mantener índices separados solo cuando los patrones de consulta divergen de verdad.

5. Columnas INCLUDE confundidas con columnas de acceso

CREATE INDEX ... INCLUDE (col) almacena columnas en páginas hoja para index-only scans pero no hace que col sea buscable vía el btree. Poner columnas de filtro solo en INCLUDE cuando deben ser condiciones de índice produce index-only scans que aún leen toda la partición del tenant.

6. Índice compuesto para ordenar sin prefijo de filtro

ORDER BY created_at DESC solo necesita (created_at DESC) — no (user_id, created_at DESC) salvo que las consultas siempre filtren user_id primero. La columna líder debe aparecer en la cláusula WHERE o el planner no puede alcanzar la columna de orden de forma eficiente.

7. Oportunidad de índice parcial desaprovechada

WHERE status = 'pending' ORDER BY total DESC LIMIT 50 en una tabla donde el 2% de filas están pending pide:

CREATE INDEX idx_pending_by_total
  ON orders (total_amount_cents DESC)
  INCLUDE (order_id, user_id, created_at)
  WHERE status = 'pending';

Un compuesto completo sobre (status, total_amount_cents) indexa el 100% de filas cuando solo el 2% necesita indexación. Los índices parciales son más pequeños, más rápidos de mantener y coinciden con la consulta — hasta que el filtro se amplía a status IN ('pending', 'processing'), cuando un compuesto más amplio puede ganar.

EXPLAIN distingue decoración de índices útiles

Ejecutar EXPLAIN (ANALYZE, BUFFERS) — no EXPLAIN solo. Los buffer hits revelan presión real de I/O.

Una línea Filter sobre tenant_id bajo un Index Scan sobre created_at es la prueba definitiva del patrón #1. El índice trabajó — el trabajo equivocado.

Row-Level Security amplifica errores de índice. Políticas que llaman auth.uid() por fila convierten planes ya marginales en desastres. Corregir el orden del índice primero, luego aplicar patrones de rendimiento de RLS — la pelea con el planner es la misma.

¿Cómo deben diseñar los equipos índices compuestos en Postgres?

Estas preguntas evitan índices que parecen correctos en archivos de migración y fallan en producción.

¿Importa el orden de columnas en la consulta?

No. El orden de predicados en SQL es irrelevante. El orden de columnas del índice lo es todo. Diseñar índices desde patrones de consulta, no desde el orden en que aparecen columnas en cláusulas WHERE.

¿Cuántas columnas van en un índice compuesto?

Rara vez más de tres salvo uso de tabla extremadamente estilizado. La documentación de Postgres advierte que compuestos anchos suelen ser poco útiles. Preferir índices parciales focalizados sobre compuestos de cuatro columnas de propósito general que nada usa de forma eficiente.

¿Cuándo usar índices separados en lugar de un compuesto?

Cuando las consultas filtran subconjuntos distintos de columnas sin prefijos líderes compartidos — consultas (tenant_id) y (status, created_at) pueden necesitar dos índices fusionados por Bitmap Scan en lugar de un compuesto que no sirve bien a ninguna.

Una visión opuesta

La postura contraria sostiene que el almacenamiento es barato y los índices deben añadirse con liberalidad — mejor sobre-indexar que perder una consulta lenta.

La amplificación de escritura discrepa. Cada índice se actualiza en cada insert y en actualizaciones de columnas indexadas. Índices redundantes y mal ordenados ralentizan escrituras, aumentan trabajo de vacuum y confunden al planner con rutas competidoras. En cargas serverless con connection pooling, escrituras más lentas componen latencia en muchas transacciones cortas.

Indexar con intención. Medir con EXPLAIN ANALYZE. Eliminar índices que los planes de producción nunca usan.

Key takeaways

• Los índices compuestos en Postgres siguen reglas de prefijo izquierdo — diseñar desde patrones de consulta, no listas de columnas.
• Columnas de igualdad primero; columnas de rango y ORDER BY al final.
• Filter tras Index Scan significa orden incorrecto — filas leídas y descartadas.
• Índices parciales superan compuestos completos cuando las consultas filtran una fracción pequeña de filas.
• Eliminar índices de una columna redundantes absorbidos por compuestos.
• INCLUDE habilita index-only scans pero no hace columnas buscables.
• Verificar con EXPLAIN (ANALYZE, BUFFERS), no solo con la existencia del índice.

Conclusion

Los índices compuestos no son "añadir todas las columnas del WHERE". Son órdenes de sort btree que deben alinearse con cómo las consultas eliminan filas. Los siete patrones de mal uso comparten una causa raíz: crear índices desde intuición de migración en lugar de leer planes de consulta.

La auditoría es simple: tomar las cinco consultas de producción más lentas, ejecutar EXPLAIN ANALYZE, buscar líneas Filter en columnas indexadas, reordenar o reemplazar índices, medir de nuevo. Al planner no le importan las intenciones. Le importa el orden del prefijo.