¿Qué hace efectivo a un índice compuesto, y qué es la selectividad de un índice?

#Selectividad: el número que decide si un índice sirve de algo

Antes de hablar de índices compuestos, necesitas el concepto que determina si cualquier índice vale la pena sobre una columna: la selectividad. La selectividad es la proporción entre valores distintos y filas totales: valores_distintos / filas_totales, un número entre 0 y 1. Una columna como email en una tabla de usuarios es muy selectiva — casi cada fila tiene un valor único, así que una búsqueda por igualdad sobre ella elimina casi todas las demás filas de inmediato. Una columna como status con tres valores posibles (active, pending, closed) en una tabla de un millón de filas tiene poca selectividad — una coincidencia por igualdad con status = 'active' todavía podría devolver 300.000 filas, y recorrer un índice para traer 300.000 punteros a filas dispersas puede ser más lento que simplemente escanear la tabla secuencialmente.

#Índices compuestos y la regla del prefijo por la izquierda

Un índice compuesto (multi-columna) es un único B-tree cuya clave combina más de una columna, ordenado primero por la primera columna, luego por la segunda columna dentro de los empates de la primera, y así sucesivamente. Ese orden tiene una consecuencia directa, llamada normalmente la regla del prefijo por la izquierda: un índice compuesto sobre (a, b, c) puede resolver eficientemente una consulta que filtra por a, o por a y b, o por a, b y c — pero en general no puede resolver eficientemente una consulta que filtra solo por b, o solo por c, o por (b, c) sin a, porque el árbol no está ordenado por b de forma independiente de a.

-- orders(customer_id, status, created_at, total_cents)
CREATE INDEX idx_orders_customer_status_date
    ON orders (customer_id, status, created_at);

-- Usa el índice COMPLETO eficientemente: restringe customer_id (izquierda),
-- status (segunda) y created_at (tercera) en orden.
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
  AND created_at > '2026-01-01';

-- Usa solo el prefijo customer_id del índice (sigue siendo útil,
-- solo que no reduce tanto como la consulta anterior).
SELECT * FROM orders
WHERE customer_id = 42;

-- No puede usar este índice en absoluto: se salta la columna
-- de más a la izquierda, customer_id.
SELECT * FROM orders
WHERE status = 'shipped'
  AND created_at > '2026-01-01';

Esa última consulta necesitaría un índice distinto — ya sea (status, created_at) por sí solo, o (status, created_at, customer_id) — según qué otras consultas también necesiten ser rápidas, porque casi nunca estás optimizando para una sola consulta de forma aislada.

#Orden de columnas: igualdad antes que rango

El orden de las columnas en un índice compuesto no es solo sobre qué columna es "más importante" — es sobre cuáles predicados son de igualdad y cuáles son de rango. Un B-tree solo puede mantenerse útilmente ordenado para una columna posterior si toda columna anterior está restringida a un único valor (una coincidencia de igualdad). En el momento en que una columna tiene un predicado de rango (>, <, BETWEEN), todo lo que viene después en el índice deja de ser útil para seguir reduciendo, porque dentro de ese rango la columna siguiente no está ordenada de ninguna forma que ayude.

-- Bueno: columna de igualdad (customer_id) primero, columna de rango
-- (created_at) al final.
CREATE INDEX idx_good ON orders (customer_id, created_at);

SELECT * FROM orders
WHERE customer_id = 42
  AND created_at BETWEEN '2026-01-01' AND '2026-02-01';
-- El árbol se reduce exactamente a customer_id = 42, y dentro de esa
-- porción angosta, created_at está ordenado, así que el escaneo de
-- rango es un recorrido ajustado a lo largo de la cadena de hojas.

-- Malo: columna de rango primero, columna de igualdad segunda.
CREATE INDEX idx_bad ON orders (created_at, customer_id);

SELECT * FROM orders
WHERE customer_id = 42
  AND created_at BETWEEN '2026-01-01' AND '2026-02-01';
-- El árbol se reduce primero por created_at, produciendo una porción
-- amplia que contiene todos los clientes de ese rango de fechas, y
-- customer_id no está ordenado dentro de esa porción, así que no
-- puede reducirse más — el motor tiene que revisar customer_id
-- fila por fila a través de toda la porción.

#Índices de cobertura: saltarse la tabla por completo

Un índice compuesto que casualmente contiene todas las columnas que necesita una consulta — en el filtro, el orden y la lista de SELECT — permite que la base de datos responda la consulta directamente desde el índice, sin tocar jamás la tabla subyacente (un "index-only scan" en términos de Postgres?). Agregar columnas que no son parte de la clave únicamente para esto suele hacerse con una cláusula INCLUDE, para que viajen junto con las páginas hoja del índice sin formar parte de la clave de ordenamiento:

CREATE INDEX idx_orders_covering
    ON orders (customer_id, status)
    INCLUDE (total_cents);

-- total_cents se puede recuperar directamente desde las páginas
-- hoja del índice, así que esta consulta nunca tiene que visitar
-- la tabla heap de orders.
SELECT total_cents FROM orders
WHERE customer_id = 42 AND status = 'shipped';

#Selectividad con ejemplos

Cuando te pregunten esto en una entrevista, recorre primero la selectividad (¿un índice ayuda siquiera aquí?), luego la regla del prefijo por la izquierda (¿el orden de las columnas coincide con cómo filtra la consulta?), y luego igualdad antes que rango (¿el orden deja que el árbol se mantenga angosto para tantas columnas como sea posible?) — en ese orden, que es exactamente como razona un planificador de consultas también.