Índices y EXPLAIN

La base de datos no es magia: cuando una consulta va lenta, casi siempre es porque está haciendo un escaneo secuencial (lee todas las filas) en vez de usar un índice. Este artículo te enseña cómo funcionan los índices, cuándo crearlos y — lo más valioso — cómo leer el plan de ejecución con EXPLAIN para diagnosticar el problema real.

Qué es un índice

Un índice es una estructura de datos auxiliar (normalmente un B-tree) que copia las columnas indexadas de forma ordenada, junto a punteros a las filas originales. Es como el índice de un libro: no lees todas las páginas, saltas directo a la palabra.

Tabla productos (desordenada)          Índice idx_productos_nombre (B-tree)
+----+----------+--------+            nombre         → puntero
| id | nombre   | precio |            "Cien años"    → fila 9
| 1  | "Rayuela"| 12.50  |            "Dune"         → fila 4
| 2  | "Dune"   | 15.00  |            "Rayuela"      → fila 1
| 3  | "El lobo"| 9.99   |            "El lobo"      → fila 3
+----+----------+--------+

El B-tree mantiene las claves ordenadas y balanceadas, así que buscarlas cuesta O(log n): con un millón de filas, ~20 comparaciones, en vez de un millón.

CREATE INDEX idx_productos_nombre ON productos (nombre);

💡 El B-tree es un árbol donde cada nodo tiene muchos hijos y está balanceado: la altura crece muy despacio. De ahí que buscar en un índice con millones de filas sea casi instantáneo.

Cómo acelera una búsqueda

Sin índice, el motor lee cada fila (sequential scan) hasta encontrar las que coinciden:

EXPLAIN ANALYZE
SELECT * FROM productos WHERE nombre = 'Dune';

Seq Scan on productos  (cost=0.00..22.50 rows=1 width=40) (actual time=0.03..0.50 rows=1 loops=1)
  Filter: (nombre = 'Dune'::text)
Planning Time: 0.2 ms
Execution Time: 0.6 ms

Con índice, usa el árbol y solo toca la fila que coincide:

Index Scan using idx_productos_nombre on productos  (cost=0.14..8.16 rows=1 width=40)
  Index Cond: (nombre = 'Dune'::text)
Execution Time: 0.1 ms

⚠️ En tablas pequeñas el planificador ignora el índice a propósito: escanear 100 filas en secuencial es más barato que subir y bajar el árbol. Un Seq Scan no es un error; un Seq Scan sobre una tabla de 10 millones de filas con un filtro selectivo sí lo es.

Cuándo crear un índice

Índice sobre una columna que aparece mucho en:

• WHERE con igualdad o rango: WHERE usuario_id = 42.
• JOIN: la clave foránea del lado “muchos”: ON pi.producto_id = p.id.
• ORDER BY de una columna: permite devolver filas ya ordenadas sin sort.
• GROUP BY: ayuda a agrupar.

CREATE INDEX idx_pedidos_usuario ON pedidos (usuario_id);
CREATE INDEX idx_pedido_items_producto ON pedido_items (producto_id);
CREATE INDEX idx_productos_precio ON productos (precio);

Cuándo NO crear un índice

• Columnas con pocos valores distintos (baja selectividad): un booleano o un estado con 3 valores no gana nada.
• Columnas que se escriben muchísimo: cada INSERT/UPDATE/DELETE debe actualizar también el índice. Muchos índices = escrituras lentas.
• Tablas pequeñas: el escaneo secuencial es más barato.
• Si la query no lo va a usar: indexar por capricho es dinero y espacio tirados.

💡 Regla: cada índice acelera la lectura pero penaliza la escritura y ocupa espacio en disco y memoria. Antes de añadir, pregunta “¿esta columna aparece en un WHERE/JOIN con alta selectividad?”. Si la respuesta es no, no lo crees.

Índices compuestos y orden de columnas

Un índice de varias columnas sirve para consultas que filtran por varias a la vez. El orden de las columnas importa muchísimo: el índice se ordena por la primera, luego por la segunda, etc. Sirve para prefijos de izquierda a derecha.

CREATE INDEX idx_pedidos_usuario_fecha
  ON pedidos (usuario_id, creado_en);

Funciona con:

WHERE usuario_id = 42;                              -- usa la primera columna ✅
WHERE usuario_id = 42 AND creado_en > '2025-01-01'; -- usa ambas ✅
WHERE creado_en > '2025-01-01';                     -- NO usa el índice ❌

⚠️ Un índice compuesto no puede “saltarse” la primera columna: la condición de la segunda solo se aprovecha si también filtras por la primera. Poner primero la columna más selectiva suele dar mejores resultados.

Índice único y parcial

Índice único impone unicidad y, además, acelera búsquedas:

CREATE UNIQUE INDEX idx_usuarios_email ON usuarios (email);
-- INSERT con email duplicado → error de violación de unicidad

Índice parcial indexa solo un subconjunto de filas; es más pequeño y rápido, ideal cuando solo buscas por un estado concreto:

-- Solo los pedidos pendientes (pocos) vs todos (muchos)
CREATE INDEX idx_pedidos_pendientes ON pedidos (creado_en)
  WHERE estado = 'pendiente';

💡 Un índice parcial sobre “pendientes” es pequeño y cabe en caché, mientras que un índice completo sería enorme. Es la diferencia entre una búsqueda de milisegundos y una de segundos en tablas con millones de pedidos históricos.

EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra el plan de ejecución sin ejecutar. EXPLAIN ANALYZE lo ejecuta de verdad y añade los tiempos reales (y contadores actual time, rows, loops).

EXPLAIN ANALYZE
SELECT p.id, p.total
FROM pedidos p
WHERE p.usuario_id = 42;

Index Scan using idx_pedidos_usuario on pedidos p
  (cost=0.29..8.31 rows=1 width=8)
  (actual time=0.01..0.02 rows=1 loops=1)
  Index Cond: (usuario_id = 42)
Planning Time: 0.1 ms
Execution Time: 0.03 ms

Cómo leer un plan

Lee el plan de dentro hacia fuera: primero lo más profundo (acceso a tablas), después las operaciones de combinación/orden, y el Execution Time al final es el total.

💡 Señales de alerta: un Seq Scan sobre una tabla grande con un Filter (el filtro se aplica después de leer), un Sort innecesario, o un Execution Time que no cuadra con el rows estimado. Esas son las pistas para crear un índice.

Queries que rompen los índices

Un índice sobre col no se usa si aplicas una función sobre la columna en el WHERE, porque la búsqueda ordenada del B-tree ya no coincide:

-- ❌ Función sobre la columna: fuerza a evaluar TODAS las filas
SELECT * FROM usuarios WHERE UPPER(email) = 'ANA@X.COM';

-- ✅ Equivalente que sí usa el índice (si existe)
SELECT * FROM usuarios WHERE email = 'ana@x.com';

-- ❌ Leading wildcard: el B-tree no puede buscar "termina en"
SELECT * FROM productos WHERE nombre LIKE '%soledad';

-- ✅ Suffix wildcard: sí puede usar el índice
SELECT * FROM productos WHERE nombre LIKE 'Cien%';

Otros rompe-índices típicos:

• WHERE CAST(col AS text) = '5' sobre una columna numérica.
• WHERE fecha + interval '1 day' > now() en vez de WHERE fecha > now() - interval '1 day'.
• WHERE col ILIKE '%x%' (comodín al inicio en ambos lados).

⚠️ La regla general: nunca envuelvas la columna indexada en una función. Si necesitas buscar por minúsculas, crea un índice de expresión CREATE INDEX ... ON t (lower(col)) en lugar de romper el de la columna.

Mantenimiento: bloat y VACUUM

En PostgreSQL, UPDATE y DELETE no sobrescriben: marcan la fila vieja como muerta y crean una nueva versión (MVCC). Con el tiempo se acumulan filas muertas = bloat. El índice crece, el escaneo se degrada y el disco se llena.

-- Tamaño real de la tabla y su índice
SELECT pg_size_pretty(pg_total_relation_size('pedidos'));

-- Filas muertas de una tabla
SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'pedidos';

VACUUM limpia las filas muertas:

VACUUM pedidos;                -- limpia, sin bloquear (no reutiliza espacio a disco)
VACUUM FULL pedidos;           -- compacta de verdad, PERO bloquea la tabla

PostgreSQL ejecuta autovacuum solo, pero en tablas con mucho UPDATE/DELETE conviene vigilarlo. El bloat de índices (páginas muertas dentro del índice) se recupera con REINDEX:

REINDEX INDEX idx_pedidos_usuario;

⚠️ VACUUM FULL y REINDEX bloquean la escritura mientras se ejecutan: en producción hazlos en ventanas de baja carga o con herramientas online como pg_repack. El VACUUM normal no bloquea, pero tampoco devuelve el espacio al sistema operativo.

Flujo de diagnóstico rápido

1. EXPLAIN ANALYZE la query lenta.
2. ¿Seq Scan con Filter sobre una tabla grande? → índice sobre la columna filtrada.
3. ¿Sort enorme? → índice compuesto con esa columna.
4. ¿Índice existe pero no se usa? → revisa si una función “rompe” la columna, o la selectividad es baja.
5. ¿Filas muertas en crecimiento? → vigila autovacuum, plantéate VACUUM/REINDEX.

Para profundizar

• Use The Index, Luke!: el mejor libro gratis sobre índices y SQL de rendimiento.
• PostgreSQL — Indexes: documentación oficial sobre tipos y uso.
• PostgreSQL — EXPLAIN: cómo leer planes de ejecución.
• PostgreSQL — Routine Vacuuming: mantenimiento y bloat.
• PGTune: calculadora de parámetros de PostgreSQL para tu hardware.