Orden de Índices de Múltiples Columnas

Los índices compuestos B-tree respetan las reglas de prefijo izquierdo: el planificador solo puede usar el índice mientras los predicados coincidan con las columnas líderes en orden en PostgreSQL 18.4.

Receta

Tarjeta de receta de referencia rápida - lista para copiar y pegar.

-- Bueno para: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX events_tenant_status_created_idx
ON events (tenant_id, status, created_at);

Cuándo usar esto: Consultas que filtran por la columna B pero el índice comienza con la columna A y los planes muestran un escaneo secuencial a pesar de un índice existente.

Ejemplo de Trabajo

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id int NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX events_wrong_order_idx ON events (status, tenant_id, created_at);
CREATE INDEX events_right_order_idx ON events (tenant_id, status, created_at);
 
INSERT INTO events (tenant_id, status, created_at)
SELECT
  (i % 100) + 1,
  CASE WHEN i % 20 = 0 THEN 'failed' ELSE 'ok' END,
  now() - (random() * interval '30 days')
FROM generate_series(1, 400000) AS i;
 
ANALYZE events;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE tenant_id = 7 AND status = 'failed' AND created_at >= now() - interval '1 day';

Elimina el índice incorrecto en implementaciones reales después de verificar que se elige el índice correcto.

Lo que esto demuestra:

• Columnas de igualdad antes que columnas de rango
• El tenant (selectivo en el contexto de la aplicación) a menudo precede al estado
• El nombre del índice en EXPLAIN confirma cuál índice compuesto se utiliza

Análisis Profundo

Cómo Funciona

• Regla: el índice es utilizable para predicados en prefijos izquierdos de (col1), (col1, col2), (col1, col2, col3).
• Omitir la columna líder (WHERE status = ? solamente) no puede usar (tenant_id, status) eficientemente.
• Mezcla de igualdad y rango: poner las igualdades primero, el rango al final (tenant_id =, status =, created_at >).
• Dirección de ordenación: alinear DESC con ORDER BY común para evitar ordenaciones.

Heurísticas de Ordenación

Notas SQL

-- Dos índices cuando dos formas de consulta dominan
CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at);
CREATE INDEX events_status_created_idx ON events (status, created_at)
WHERE status IN ('failed', 'retry');

Errores Comunes

• Un mega-índice para todos los informes - No sirve bien a ninguna consulta. Solución: 2-3 compuestos probados más índices parciales.
• Columna líder de baja cardinalidad - status primero cuando siempre se combina con tenant_id. Solución: Poner tenant_id a la izquierda a menos que un índice parcial solucione el segmento de status.
• Coerción de tipo implícita - La discrepancia de tipo en el predicado omite el índice. Solución: Coincidir el tipo de columna en SQL y ORM.
• OR entre columnas - El orden del índice no puede salvar OR entre rutas no prefijas. Solución: Reescribir con UNION ALL o múltiples índices parciales.
• Índices duplicados con columnas permutadas - Amplificación de escritura duplicada. Solución: Auditoría de índices redundantes.

Alternativas

Preguntas Frecuentes

Relacionado

• Conceptos Básicos de Diseño de Índices - Diseño general
• Índices Duplicados y Redundantes - Limpieza
• Estadísticas Extendidas - ndistinct compuesto
• Índices Parciales y de Cobertura - Variantes

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.