índices: el truco que convierte minutos en milisegundos

Si EXPLAIN es la radiografía que diagnostica el problema, los índices son el tratamiento más efectivo. Un índice bien puesto puede transformar una query de 30 segundos en 30 milisegundos — literalmente 1000 veces más rápido. Es la optimización con mejor ratio esfuerzo/impacto que existe en el mundo de las bases de datos.

Pero como toda herramienta poderosa, los índices tienen un coste. Crearlos sin criterio puede EMPEORAR las cosas. Esta lección te enseña cuándo un índice es la respuesta correcta, cuándo no lo es, y cómo tomar esa decisión como un profesional.

### La analogía perfecta: el índice de un libro

Imagina un libro de 800 páginas sin índice. Te pido que encuentres todas las menciones de "particionamiento". Tu única opción: leer las 800 páginas de principio a fin. Eso es un Sequential Scan — lento pero siempre funciona.

Ahora imagina que el libro tiene un índice al final: "Particionamiento: páginas 124, 267, 431, 589". Vas directamente a esas páginas. No lees las otras 796. Eso es un Index Scan — dramáticamente más rápido para búsquedas específicas.

Pero el índice ocupa espacio (20 páginas extra al final del libro). Y cada vez que el autor añade contenido, tiene que actualizar el índice. Eso es exactamente el trade-off de un índice en base de datos: lecturas más rápidas, pero escrituras ligeramente más lentas y espacio adicional.

### Cómo funciona un B-Tree (el índice más común)

El 90% de los índices en bases de datos son B-Trees (árboles balanceados). La idea: los datos se organizan en una estructura de árbol donde cada "nodo" apunta a rangos de valores. Para buscar un valor, solo necesitas recorrer unos pocos niveles del árbol en vez de todas las filas.

### Crear índices en PostgreSQL

DuckDB es columnar y no necesita índices tradicionales (su almacenamiento por columnas ya es eficiente para analítica). Pero en PostgreSQL, donde vivirán tus tablas de producción, los índices son ESENCIALES. Veamos cómo crearlos:

1-- Indice simple: una columna
2CREATE INDEX idx_pedidos_cliente_id ON pedidos (cliente_id);
3
4-- Indice compuesto: varias columnas (orden importa!)
5CREATE INDEX idx_pedidos_fecha_estado ON pedidos (fecha, estado);
6
7-- Indice parcial: solo filas que cumplen una condicion
8CREATE INDEX idx_pedidos_completados ON pedidos (fecha) 
9    WHERE estado = 'completado';
10
11-- Verificar que se usa (PostgreSQL):
12EXPLAIN ANALYZE
13SELECT * FROM pedidos WHERE cliente_id = 4237;
14-- Deberia mostrar "Index Scan using idx_pedidos_cliente_id"

### Cuándo Sí crear un índice

• ▹Columnas en WHERE que filtran una fracción pequeña de la tabla (alta selectividad): WHERE email = "ana@..." → pocas filas de millones
• ▹Columnas en JOIN (claves foráneas): pedidos.cliente_id para hacer JOIN con clientes.id
• ▹Columnas en ORDER BY cuando usas LIMIT: ORDER BY fecha DESC LIMIT 10 → el índice evita ordenar toda la tabla
• ▹Columnas en WHERE de queries frecuentes: si una query se ejecuta 1000 veces/segundo, un índice es imprescindible
• ▹índice parcial para queries con filtro fijo: WHERE estado = "completado" aparece en todas tus queries → índice parcial

### Cuándo NO crear un índice

• ▹Tablas pequeñas (< 10.000 filas): el Seq Scan es tan rápido que el índice no aporta
• ▹Columnas de baja selectividad: WHERE genero = "M" → el 50% de la tabla. El índice no ayuda si necesitas la mitad
• ▹Tablas con muchos INSERTs/UPDATEs: cada escritura tiene que actualizar TODOS los índices. En tablas de logs de alta velocidad, demasiados índices matan el rendimiento de escritura
• ▹Cuando ya hay demasiados índices: cada índice ocupa espacio y ralentiza escrituras. En producción he visto tablas con 25 índices donde ninguno se usaba
• ▹Columnas que cambian constantemente: un índice en "ultimo_acceso" que se actualiza cada segundo es una mala idea

### El coste oculto: espacio y escrituras

Un índice no es gratis. Ocupa espacio en disco (a veces tanto como la tabla misma). Y cada vez que insertas, actualizas o borras una fila, el índice se tiene que actualizar también. En una tabla de logs con 100.000 inserts por segundo, cada índice adicional añade latencia a cada escritura. El balance es: ícuánto ganas en lecturas vs cuánto pierdes en escrituras?

### ¿Por qué DuckDB no necesita índices tradicionales?

DuckDB almacena datos por columnas y usa "zonemaps" (metadata que dice "en este bloque de 1024 filas, el valor mínimo de fecha es X y el máximo es Y"). Si tu filtro pide fecha > "2024-06-01" y el bloque tiene max_fecha = "2024-03-15", DuckDB SALTA ese bloque entero sin leerlo. Es una forma de "índice automático" incorporada en el almacenamiento columnar.

Por eso no necesitas crear índices manuales en DuckDB — su arquitectura columnar ya optimiza las lecturas analíticas. Pero en PostgreSQL (orientado a filas), los índices siguen siendo imprescindibles para rendimiento.

### Más allá del B-Tree: lo que verás en producción

El B-Tree es el caballo de batalla, pero PostgreSQL tiene más herramientas. Tres que vas a ver en equipos de verdad:

• ▹Bitmap Index Scan — cuando tu filtro no es ni muy selectivo ni muy amplio (entre el 1% y el 10% de la tabla), PostgreSQL no usa Index Scan ni Seq Scan: usa un Bitmap. Primero marca en un mapa de bits qué páginas de disco contienen filas que cumplen el filtro, y luego lee solo esas páginas en orden físico. Es más rápido que ir fila a fila (Index Scan) y más eficiente que leer toda la tabla (Seq Scan).
• ▹BRIN (Block Range Index) — para columnas que crecen con el tiempo (fechas de inserción, IDs secuenciales). Un BRIN ocupa kilobytes donde un B-Tree ocupa gigabytes, porque solo guarda el min/max de cada bloque de páginas. Ideal para tablas de eventos o logs con decenas de millones de filas.
• ▹CREATE INDEX CONCURRENTLY — un CREATE INDEX normal bloquea las escrituras en la tabla mientras se construye. En una tabla de producción con inserts cada segundo, eso es inaceptable. CONCURRENTLY construye el índice en segundo plano sin bloquear. Tarda más, pero la tabla sigue operativa.

1-- BRIN: perfecto para tablas append-only (logs, eventos)
2CREATE INDEX idx_eventos_fecha_brin ON eventos_log
3    USING brin (timestamp);
4-- Ocupa ~48 KB en 50M filas (un B-Tree ocuparía ~1 GB)
5
6-- CONCURRENTLY: no bloquea escrituras
7CREATE INDEX CONCURRENTLY idx_pedidos_fecha
8    ON pedidos (fecha);
9-- Tarda más, pero la app sigue funcionando mientras se crea
10
11-- Encontrar índices muertos (PostgreSQL):
12SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
13FROM pg_stat_user_indexes
14WHERE idx_scan = 0
15ORDER BY pg_relation_size(indexrelid) DESC;