Índices avanzados - covering, parciales, funcionales y especializados

Más allá del índice B-tree básico

Un B-tree estándar es un índice balanceado que acelera operaciones de igualdad (=), rango (<, >, BETWEEN) y ordenación (ORDER BY). Es el tipo por defecto en todos los motores y resuelve el 80% de los casos. Sin embargo, hay escenarios donde índices especializados son 10x a 100x más rápidos.

Covering indexes: Index Only Scan

Un covering index contiene todas las columnas que la query necesita, evitando acceder a la tabla (el "heap"). PostgreSQL 11+ soporta la cláusula INCLUDE para columnas que no participan en la búsqueda pero se incluyen en el índice.

-- Sin INCLUDE: el indice tiene solo (cliente_id, fecha)
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos(cliente_id, fecha);

-- Query: SELECT importe FROM pedidos WHERE cliente_id = 42 AND fecha > '2026-01-01'
-- El indice encuentra las filas, pero hay que ir al heap para leer 'importe'

-- Con INCLUDE: el indice tambien guarda importe (sin participar en la busqueda)
CREATE INDEX idx_pedidos_cliente_fecha_incl
ON pedidos(cliente_id, fecha) INCLUDE (importe);

-- Misma query: ahora el motor puede hacer Index Only Scan
EXPLAIN SELECT importe FROM pedidos WHERE cliente_id = 42 AND fecha > '2026-01-01';
-- Index Only Scan using idx_pedidos_cliente_fecha_incl

Cuándo usar covering: cuando una query lee pocas columnas y se ejecuta miles de veces al día. El ahorro por Index Only Scan vale el espacio extra.

En MySQL, la alternativa es añadir la columna al índice compuesto, aunque participe en el ORDER BY:

-- MySQL: el orden importa para la busqueda
CREATE INDEX idx_pedidos_cliente_fecha_importe
ON pedidos(cliente_id, fecha, importe);

Índices parciales: WHERE en la definición

Un índice parcial solo indexa las filas que cumplen una condición. Más pequeño, más rápido de escribir, y más selectivo al buscar.

-- Tabla con 100M pedidos, pero solo el 2% estan 'pendientes'
CREATE INDEX idx_pedidos_pendientes
ON pedidos(fecha_creacion)
WHERE estado = 'pendiente';

-- Las queries que filtren por estado='pendiente' usan este indice
SELECT * FROM pedidos
WHERE estado = 'pendiente'
ORDER BY fecha_creacion
LIMIT 10;

Casos típicos:

• Filas raras: WHERE estado = 'error', WHERE flag_importante = TRUE.
• Rango activo: WHERE fecha > '2025-01-01' para indexar solo datos recientes.
• Valores no NULL: WHERE columna IS NOT NULL para ignorar la gran mayoría de NULLs.

PostgreSQL soporta parciales de forma nativa. MySQL no los tiene; se emulan con índices funcionales (ver más abajo).

Índices funcionales / de expresión

Un índice funcional se construye sobre una expresión, no una columna. Permite consultas que de otra forma no usarían índice:

-- WHERE con UPPER anula el indice convencional
CREATE INDEX idx_usuarios_email ON usuarios(email);
SELECT * FROM usuarios WHERE UPPER(email) = 'JUAN@EMAIL.COM';  -- Seq Scan!

-- Indice funcional
CREATE INDEX idx_usuarios_email_upper ON usuarios(UPPER(email));
-- Ahora la query usa el indice

Otros ejemplos:

-- Truncado de fecha a mes (para agrupar por mes rapido)
CREATE INDEX idx_pedidos_mes ON pedidos(DATE_TRUNC('month', fecha));

-- Hash MD5 de email (si las comparaciones son por hash)
CREATE INDEX idx_usuarios_email_hash ON usuarios(MD5(email));

-- Subcadena inicial (para prefijos)
CREATE INDEX idx_productos_prefijo ON productos(LEFT(codigo, 3));

Restricción: la función debe ser IMMUTABLE (determinista). Funciones que dependen del contexto actual (NOW(), CURRENT_USER) no se pueden indexar.

MySQL 8+ soporta functional indexes con la misma sintaxis; en versiones anteriores se simulan con generated columns indexadas.

BRIN: índice minimalista para datos ordenados

BRIN (Block Range Index) de PostgreSQL guarda metadatos por bloques de páginas (ej. rango min/max de una columna por cada 128 páginas). Muy barato en espacio (KB vs GB de un B-tree) pero solo útil cuando la columna está físicamente correlacionada con el orden de inserción.

Caso típico: series temporales insertadas por fecha.

-- Tabla de logs: 500M filas, insertadas cronologicamente
CREATE INDEX idx_logs_fecha_brin ON logs USING BRIN(fecha);

-- Query por rango de fechas: muy rapida, indice de 1 MB
SELECT * FROM logs WHERE fecha BETWEEN '2026-01-01' AND '2026-01-31';

Frente a un B-tree convencional de varios GB, BRIN es ideal cuando:

• Los datos se insertan en orden.
• Las consultas son por rango (no por igualdad única).
• Quieres minimizar espacio y tiempo de mantenimiento.

Si las filas se borran o actualizan mucho, BRIN pierde precisión y puede requerir VACUUM para resumir bloques.

GIN: para múltiples valores por fila

GIN (Generalized Inverted Index) está optimizado para columnas que contienen varios valores por fila: arrays, JSONB, full-text (tsvector).

-- Tabla con columna JSONB
CREATE TABLE eventos (id SERIAL, datos JSONB);
INSERT INTO eventos(datos) VALUES
    ('{"usuario": "alan", "tags": ["sql", "tutorial"]}'),
    ('{"usuario": "maria", "tags": ["python", "ml"]}');

-- Indice GIN sobre la columna completa
CREATE INDEX idx_eventos_datos ON eventos USING GIN(datos);

-- Buscar eventos con tag 'sql'
SELECT * FROM eventos WHERE datos @> '{"tags": ["sql"]}';  -- usa GIN

-- Indice GIN con jsonb_path_ops: mas pequeno, solo para operador @>
CREATE INDEX idx_eventos_datos_path ON eventos USING GIN(datos jsonb_path_ops);

GIN con full-text search:

ALTER TABLE articulos ADD COLUMN busqueda tsvector;
UPDATE articulos SET busqueda = to_tsvector('spanish', titulo || ' ' || contenido);
CREATE INDEX idx_articulos_busqueda ON articulos USING GIN(busqueda);

SELECT * FROM articulos
WHERE busqueda @@ to_tsquery('spanish', 'sql & avanzado');

GIN es lento al insertar/actualizar (reconstruye inverted lists) pero muy rápido al buscar.

GiST: geoespacial y generalización

GiST (Generalized Search Tree) soporta operadores de contención y proximidad: geometría (PostGIS), rangos, direcciones IP.

-- PostGIS: encontrar puntos dentro de un poligono
CREATE INDEX idx_tiendas_ubicacion ON tiendas USING GIST(ubicacion);
SELECT * FROM tiendas WHERE ST_Within(ubicacion, :poligono_madrid);

-- Ranges: saber si una reserva se solapa con otra
CREATE INDEX idx_reservas_periodo ON reservas USING GIST(periodo);
SELECT * FROM reservas WHERE periodo && tsrange('2026-04-20', '2026-04-25');

GiST es más general pero más lento que GIN para full-text. Para texto con muchas búsquedas, usa GIN. Para geo u operadores de solapamiento, GiST.

Hash indexes (PostgreSQL)

Específicos para operador = (no rango). PostgreSQL los tenía deprecated hasta la versión 10; ahora son seguros para WAL y recomendables para igualdad estricta:

CREATE INDEX idx_usuarios_email_hash ON usuarios USING HASH(email);
-- Util solo para WHERE email = 'x@y.com'

Para la mayoría de casos, un B-tree es igual de rápido y más flexible. Hash tiene nicho en índices muy grandes con equality-only.

Mantenimiento: bloat y REINDEX

Los índices también acumulan bloat (espacio de filas muertas). Indicadores:

SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Soluciones:

-- REINDEX bloquea la tabla (no recomendable en prod caliente)
REINDEX INDEX idx_pedidos_cliente;

-- REINDEX CONCURRENTLY (PostgreSQL 12+): sin bloqueo
REINDEX INDEX CONCURRENTLY idx_pedidos_cliente;

-- pg_repack (extension externa): reindex + vacuum sin lock
-- pg_repack -d mi_bd -i idx_pedidos_cliente

Detectar índices no usados

Un índice que nadie usa consume espacio y ralentiza cada INSERT/UPDATE. Identificarlos:

SELECT schemaname, tablename, indexname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
  AND NOT indisunique
  AND indexname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Cuidado con:

• Índices que backup constraints (primary, unique): no los borres aunque idx_scan sea 0.
• Índices recién creados: dales unos días de tráfico antes de juzgar.
• Índices usados solo por cron jobs mensuales: verifica el rango temporal.

Tabla resumen

| Tipo | Cuándo usar | Operadores | Tamaño | Motores | |------|-------------|------------|--------|---------| | B-tree | Default, igualdad y rango | =, <, >, BETWEEN, IS NULL | Medio | Todos | | Hash | Igualdad estricta | = | Pequeño | PG 10+ | | BRIN | Series temporales, datos ordenados | Rango | Muy pequeño | PG | | GIN | JSONB, arrays, full-text | @>, ?, @@ | Grande | PG | | GiST | Geo, rangos, proximidad | &&, <@, <-> | Medio | PG | | Covering (B-tree INCLUDE) | Index Only Scan frecuente | Igual que B-tree | Mayor que B-tree | PG 11+, SQL Server | | Parcial (WHERE) | Filtrar filas raras/activas | Igual que B-tree | Pequeño | PG | | Funcional | Consultas con expresiones | Igual que B-tree | Medio | PG, MySQL 8+ |