Fundamentos del Diseño de Índices
Los índices aceleran las búsquedas que coinciden con un prefijo de clave de izquierda a derecha. Diseña para las formas de consulta reales: columnas WHERE, JOIN y ORDER BY que aparecen juntas en los planes de PostgreSQL 18.4.
Receta
Tarjeta de referencia rápida - lista para copiar y pegar.
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 50;Cuándo usar esto: Filtros o uniones frecuentes muestran escaneos secuenciales (seq scans) en tablas grandes con selectividad demostrable.
Ejemplo de Trabajo
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total numeric(12,2) NOT NULL
);
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
(random() * 5000)::bigint,
CASE WHEN random() < 0.1 THEN 'shipped' ELSE 'pending' END,
now() - (random() * interval '180 days'),
(random() * 200)::numeric(12,2)
FROM generate_series(1, 300000);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;Lo que esto demuestra:
- Índice compuesto que coincide con el filtro y la ordenación.
LIMITsatisfecho sin nodo de ordenación separado cuando el plan es ideal.- Prueba a través de
EXPLAINdespués deANALYZE.
Análisis Profundo
Cómo Funciona
- B-tree es el predeterminado: igualdad en las columnas principales, luego rango en la siguiente columna.
- Las columnas
INCLUDEpermiten escaneos solo de índice (index-only scans) para cargas útiles seleccionadas. - Los índices parciales
WHEREreducen el tamaño para subconjuntos estables (status = 'open'). - Cada índice supone una carga para
INSERT/UPDATE/DELETEcon WAL y suciedad de búfer.
Lista de Verificación de Diseño
| Cláusula de consulta | Sugerencia de índice |
|---|---|
WHERE a = ? AND b > ? | (a, b) |
JOIN ON child.parent_id | Índice en parent_id |
ORDER BY created_at DESC | Coincidir la dirección de ordenación en el índice |
| Indicador de baja selectividad | Índice parcial con predicado |
Notas de SQL
CREATE INDEX orders_open_customer_idx
ON orders (customer_id, created_at)
WHERE status = 'open';Trampas Comunes
- Indexar cada columna de filtro por separado - El planificador puede combinar muchos índices débiles mediante bitmap. Solución: Un índice compuesto intencionado por cada consulta frecuente.
- Orden de columnas incorrecto -
(created_at, customer_id)fallaWHERE customer_id = ?. Solución: La columna de igualdad más selectiva a la izquierda. - Índices anchos que incluyen blobs JSON - Páginas infladas,
vacuumlento. Solución:INCLUDEsolo escalares necesarios. - Crear índice sin CONCURRENTLY en producción - Bloquea escrituras. Solución:
CREATE INDEX CONCURRENTLYen migraciones. - Omitir ANALYZE después de crear un índice - El planificador ignora el nuevo índice. Solución: Analizar la tabla antes de la validación.
Alternativas
| Alternativa | Usar Cuando | No Usar Cuando |
|---|---|---|
| BRIN en series temporales | Marcas de tiempo de solo adición | Búsquedas puntuales |
| GIN en claves JSONB | Consultas de contención | Igualdad escalar simple |
| Tabla de caché desnormalizada | Desequilibrio extremo de lecturas | Necesidades de normalización fuerte |
Preguntas Frecuentes
¿Cuántos índices por tabla?
No hay un máximo fijo; vigila la tasa de escritura y la presión del autovacuum. Audita los índices no utilizados trimestralmente.
¿UNIQUE vs INDEX simple?
UNIQUE impone una restricción y proporciona una ruta de búsqueda. Usa UNIQUE cuando la regla de negocio lo requiera.
¿Importa DESC en el índice?
Sí, para escaneos de índice inversos que evitan ordenaciones en ORDER BY ... DESC.
¿Índice de cobertura (Covering index)?
INCLUDE almacena columnas adicionales en las páginas hoja sin ordenar la clave de búsqueda.
¿Uso de índice Hash?
Raro en PostgreSQL moderno; B-tree maneja la mayoría de los casos de igualdad.
¿Índice en expresión?
CREATE INDEX ON lower(email) cuando las consultas usan la misma expresión.
¿Clave foránea (FK) sin índice?
La unión de hijos y las eliminaciones en cascada se ven afectadas. Indexa las columnas FK del hijo.
¿Claves primarias UUID?
Los UUID aleatorios fragmentan los índices; considera identificadores ordenados por tiempo para tablas con muchas inserciones.
¿Monitorizar el uso?
pg_stat_user_indexes.idx_scan en una ventana móvil.
¿Siguiente?
Orden de índices multicolumna para reglas de prefijo izquierdo.
Relacionado
- Orden de Índices Multicolumna - Reglas de la columna principal
- Índices Duplicados y Redundantes - Auditoría de desperdicio
- Seq Scan vs Index Scan - Cuándo un índice es mejor
- Índices B-tree - Detalles de la ruta de acceso
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+.