Indexación de bases de datos: una inmersión profunda

Las queries lentas rara vez son un problema de la base de datos — son un problema de indexación. Un índice faltante convierte una búsqueda de 2ms en un full table scan de 2 segundos. Un índice mal ubicado desperdicia espacio en disco y frena las escrituras sin ningún beneficio en lectura. Entender cómo funcionan realmente los índices cambia la forma en que diseñas esquemas y escribes queries.

Cómo funcionan los índices B-tree

El tipo de índice por defecto en PostgreSQL, MySQL y la mayoría de las bases de datos relacionales es un B-tree. Es una estructura de árbol balanceada y ordenada que permite búsquedas O(log n) en lugar de escaneos secuenciales O(n).

-- Without an index, this scans every row in the table
SELECT * FROM orders WHERE customer_id = 'cust_abc123';
-- Seq Scan on orders: rows=1,000,000, time=1,847ms
 
-- With an index, it traverses a tree to find matching rows
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- Index Scan using idx_orders_customer_id: rows=47, time=0.8ms

El índice almacena los valores de customer_id en orden, con punteros a las filas reales de la tabla. En lugar de leer un millón de filas, la base de datos recorre un árbol de quizás 3-4 niveles de profundidad.

Índices compuestos y orden de columnas

Un índice compuesto cubre múltiples columnas. El orden de las columnas determina qué queries se benefician.

-- ❌ Two separate indexes — only one gets used per query
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_date ON orders (created_at);
 
-- This query can only use ONE of these indexes, then filters the rest
SELECT * FROM orders
WHERE status = 'shipped' AND created_at > '2020-01-01';
 
-- ✅ Composite index — covers the entire WHERE clause
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
 
-- Now the database uses one index seek for both conditions

La regla del prefijo izquierdo importa: un índice compuesto sobre (status, created_at) da soporte a queries que filtran solo por status, o por status + created_at, pero no por created_at solo. Piénsalo como una guía telefónica — puedes encontrar a todos los García, o García + Juan, pero no puedes encontrar eficientemente a todos los Juan de cualquier apellido.

-- ✅ Uses the composite index (leftmost prefix)
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2020-01-01';
 
-- ❌ Cannot use the composite index efficiently
SELECT * FROM orders WHERE created_at > '2020-01-01';

Índices de cobertura

Un índice de cobertura incluye todas las columnas que una query necesita, eliminando la necesidad de leer la fila real de la tabla. Esto es un index-only scan — el camino de lectura más rápido posible.

-- Query needs customer_id and total
SELECT customer_id, total FROM orders WHERE status = 'completed';
 
-- Regular index — finds rows via index, then fetches from table (heap)
CREATE INDEX idx_status ON orders (status);
 
-- Covering index — all needed columns are IN the index
CREATE INDEX idx_status_covering ON orders (status) INCLUDE (customer_id, total);
-- Index Only Scan: no heap fetches needed

La contrapartida: los índices de cobertura son más grandes y más lentos de actualizar. Úsalos para queries con mucha lectura sobre columnas que cambian poco.

Índices parciales

¿Para qué indexar filas que nunca consultas? Los índices parciales cubren un subconjunto de la tabla, ahorrando espacio y sobrecarga de escritura.

-- ❌ Full index — includes millions of completed orders you rarely query
CREATE INDEX idx_orders_status ON orders (status);
 
-- ✅ Partial index — only indexes the rows you actually filter on
CREATE INDEX idx_orders_active ON orders (status)
WHERE status IN ('pending', 'processing', 'shipped');

// Common use case: "active" records in a soft-delete system
// Only 5% of users are active, but 95% of queries filter on active
 
// The partial index is 20x smaller and 20x faster to maintain

Cuándo los índices perjudican

Los índices no son gratis. Cada INSERT, UPDATE y DELETE debe actualizar todos los índices relevantes. El exceso de indexación frena las escrituras y desperdicia almacenamiento.

-- ❌ Over-indexed table — every write updates 8 indexes
-- orders table with indexes on:
-- (id), (customer_id), (status), (created_at), (updated_at),
-- (total), (shipping_method), (payment_status)
 
-- Each INSERT now writes to 9 locations (1 table + 8 indexes)

EXPLAIN es tu mejor amigo

Antes de agregar un índice, usa EXPLAIN ANALYZE para entender el plan de ejecución actual. Después de agregarlo, verifica que el índice realmente se esté usando.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'cust_abc123'
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;
 
-- Look for:
-- "Seq Scan" → needs an index
-- "Index Scan" → using an index
-- "Index Only Scan" → best case, covering index
-- "Bitmap Index Scan" → multiple indexes combined
-- "actual time" → real execution time, not estimates

No confíes en las estimaciones del planificador de queries — usa siempre ANALYZE para ver los tiempos de ejecución reales. Y ejecuta ANALYZE sobre la tabla después de agregar datos para que el planificador tenga estadísticas actualizadas.

Puntos clave

1. Los índices B-tree convierten escaneos O(n) en búsquedas O(log n) — un solo índice faltante puede hacer una query 1000 veces más lenta
2. El orden de las columnas en índices compuestos importa — la regla del prefijo izquierdo determina qué queries se benefician
3. Los índices de cobertura eliminan las búsquedas en la tabla — incluye las columnas seleccionadas con frecuencia usando INCLUDE
4. Los índices parciales ahorran espacio — indexa solo las filas que tus queries realmente filtran
5. El exceso de indexación frena las escrituras — cada índice se mantiene en cada mutación
6. Valida siempre con EXPLAIN ANALYZE — no adivines, mide