Indices compuestos en SQL: regla del prefijo izquierdo y orden de columnas

Un indice compuesto cubre varias columnas en una sola estructura. El orden correcto de columnas convierte el filtrado, la ordenacion e incluso la lectura de las columnas necesarias en una unica pasada por el indice; el orden equivocado lo deja casi inutil.

La regla del prefijo izquierdo

Un B-tree ordena las filas por columnas de izquierda a derecha, como palabras en un diccionario. Por eso el indice solo se aprovecha empezando por su columna mas a la izquierda y sin huecos.

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

• WHERE user_id = 42 funciona, es el prefijo izquierdo.
• WHERE user_id = 42 AND created_at > now() - interval '7 days' usa ambas columnas.
• WHERE created_at > now() - interval '7 days' no puede usar este indice: la segunda columna es inalcanzable sin la primera.

Gotcha: la igualdad en la primera columna "desbloquea" la ordenacion y los rangos en la segunda. Pero un rango en la primera columna anula la busqueda puntual en la segunda, porque despues de > o < las filas ya no estan ordenadas por la columna siguiente.

Orden de columnas: filtro, luego orden, luego rango

Regla practica: primero las columnas de igualdad, despues la columna de ordenacion y al final la columna de rango.

-- "pedidos recientes de un usuario, los nuevos primero"
SELECT id, amount, status
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

El indice (user_id, created_at DESC) es ideal: user_id acota por igualdad y created_at DESC devuelve las filas ya ordenadas, asi que no hace falta un paso de ordenacion aparte.

• La igualdad (=, IN) va primero.
• La columna del ORDER BY va justo despues de las igualdades.
• El rango (>, <, BETWEEN) va al final, porque rompe el orden de todo lo que viene despues en el indice.

Cubrir un ORDER BY y la direccion de orden

Para que el indice elimine el paso de ordenacion, la direccion del ORDER BY debe coincidir con el indice o ser su inverso completo. PostgreSQL puede recorrer un B-tree en ambos sentidos.

CREATE INDEX idx_emp_dept_salary
  ON employees (dept, salary DESC);

SELECT name, salary
FROM employees
WHERE dept = 'eng'
ORDER BY salary DESC
LIMIT 10;

ORDER BY salary ASC tambien funciona sin ordenar, es un recorrido inverso. Pero un ORDER BY dept ASC, salary ASC mixto contra un indice (dept, salary DESC) fuerza una reordenacion: las direcciones por columna no se pueden mezclar a voluntad.

Columnas INCLUDE e index-only scan

Si el indice contiene todas las columnas que la consulta necesita, el motor no toca la tabla, es un index-only scan. Las columnas por las que no filtras conviene anadirlas con INCLUDE: viven solo en las hojas y no inflan la navegacion por el arbol.

CREATE INDEX idx_orders_user_inc
  ON orders (user_id, created_at DESC)
  INCLUDE (amount, status);

SELECT created_at, amount, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

• En PostgreSQL el index-only scan necesita una tabla con vacuum reciente (el visibility map).
• MySQL/InnoDB no tiene la palabra clave INCLUDE, pero la clave primaria se anade automaticamente a cada indice secundario, y un "covering index" se logra listando todas las columnas en el propio indice.
• ClickHouse es distinto: su indice primario es disperso y no unico, y el ORDER BY de la tabla define el orden de los datos en disco.

Cuando un compuesto supera a dos indices simples

El planificador puede combinar dos indices separados sobre user_id y created_at mediante un bitmap, pero eso son dos lecturas mas una interseccion, mas lento que un solo indice que ya guarda los datos ordenados.

-- combinacion bitmap de dos indices: funciona, pero no aporta orden
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_created ON orders (created_at);

• Un indice compuesto gana cuando las columnas se consultan siempre juntas en WHERE/ORDER BY.
• Dos indices simples son mas flexibles cuando las columnas tambien se consultan por separado.
• No abuses de los indices: cada uno ralentiza INSERT/UPDATE y ocupa espacio. (a, b) ya sirve para consultas sobre a, asi que un indice aparte sobre a suele sobrar.