Índices de PostgreSQL explicados: EXPLAIN, B-tree y el infierno del N+1

La consulta que funcionaba perfecta en desarrollo se arrastra en producción. Casi siempre hay tres sospechosos: falta un índice, el planificador eligió mal, o el ORM lanzó 500 consultas sin que te enteraras. Este artículo te da el kit para diagnosticar cualquiera de las tres con confianza.

Sin índice: la búsqueda es un escaneo completo

Una tabla PostgreSQL es físicamente un montón (heap) de filas. Un SELECT ... WHERE email = 'x' sin índice obliga a leer todas las filas y comparar una a una (Sequential Scan). Con 100 filas, instantáneo; con 10 millones, segundos — multiplicado por cada petición concurrente.

Un índice B-tree reorganiza conceptualmente los valores de una columna en un árbol equilibrado: encontrar un valor cuesta O(log n) — 10 millones de filas ≈ 23 saltos. Es la diferencia entre hojear un diccionario entero y usar su índice lateral.

CREATE INDEX idx_usuarios_email ON usuarios (email);

Regla de oro: indexa columnas que aparezcan en WHERE, JOIN ... ON, ORDER BY y restricciones únicas. No indexes todo: cada índice ralentiza escrituras (hay que actualizarlo) y ocupa espacio.

Leer EXPLAIN ANALYZE sin miedo

El comando que responde "¿qué está haciendo mi consulta realmente?":

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42 ORDER BY creado DESC LIMIT 20;

Salida típica antes del índice:

Sort  (cost=... rows=20) (actual time=1842.1..1842.2 rows=20)
  Sort Key: creado DESC
  Sort Method: top-N heapsort
  -> Seq Scan on pedidos (actual time=0.4..1780.5 rows=84112)
       Filter: (cliente_id = 42)
       Rows Removed by Filter: 9915888

Leerlo así:

• Seq Scan = recorre TODA la tabla. Con millones de filas y filtro selectivo, es el delito.
• Rows Removed by Filter: 9915888 = leyó 10 millones para devolver 84 mil. Ratio brutal.
• actual time=1780 = ahí están tus 1,8 segundos.
• El nodo superior (Sort) ordena en memoria tras recoger todo.

Después de CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id, creado DESC):

Limit (actual time=0.31..0.85 rows=20)
  -> Index Scan using idx_pedidos_cliente on pedidos (rows=20)
       Index Cond: (cliente_id = 42)

0,85 ms. Tres órdenes de magnitud. Fíjate además en el detalle fino: al incluir creado DESC como segunda columna del índice, Postgres lee las filas YA ordenadas y el nodo Sort desaparece — eso es diseñar el índice para la consulta concreta, no solo para el filtro.

Índices compuestos: el orden importa

Un índice (a, b) sirve para filtros por a, por a AND b, y para ORDER BY a — pero NO para filtrar solo por b (no puede saltar a la mitad del árbol). Piénsalo como guía telefónica ordenada por apellido+nombre: encuentras todos los "García" al vuelo, pero localizar todos los "José" exige recorrerlo entero (eso sería otro índice).

Columna de selectividad primero (la que más descarta), y columnas de ordenación después. Los covering indexes van un paso más allá: CREATE INDEX ... INCLUDE (columnas_del_select) permite responder desde el propio índice sin tocar la tabla (Index Only Scan).

El N+1: el asesino silencioso de los ORM

El patrón más común de lentitud no es una consulta lenta sino DOSCIENTAS rápidas:

# Código inocente
pedidos = Pedido.objects.all()[:50]
for p in pedidos:
    print(p.cliente.nombre)   # ← ¡una query POR pedido!

51 consultas donde bastaba 1 (por eso N+1). En desarrollo con SQLite local pasa desapercibido; en producción cada ida al servidor de BD suma latencia de red: 50 × 5 ms = 250 ms extra mínimo, escalando con tráfico.

Diagnóstico: activar logging de queries en desarrollo y contarlas por request (django-debug-toolbar, rack-mini-profiler, Prisma con $extends de logging, Apollo tracing...). Solución: eager loading explícito — select_related/prefetch_related (Django), includes (Rails/Prisma), join fetch (JPA) — que convierte el grafo en JOIN o segunda consulta IN (...).

Regla práctica: si renderizas una lista y accedes a relaciones dentro del bucle, sospecha N+1 automáticamente.

Cuándo un índice NO se usa

Síntomas clásicos que confunden a todo el mundo:

• Funciones sobre la columna: WHERE lower(email) = ... ignora el índice de email. Solución: índice funcional CREATE INDEX ON usuarios (lower(email)).
• LIKE con comodín inicial: '%texto' no puede usar B-tree. Para búsquedas por sufijo o texto libre: pg_trgm o full-text search.
• Baja selectividad: un índice sobre activo booleano con 98% de trues no ayuda — el planificador acierta ignorándolo.
• Estadísticas obsoletas: el planificador decide con estadísticas; tras cargas masivas, ANALYZE tabla.
• Tipos distintos: comparar columna text con parámetro numérico fuerza casts que invalidan el índice.

Y formatear esas consultas complejas para revisarlas con calma es lo que hace nuestro formateador SQL — legibilidad primero, diagnóstico después.

Preguntas frecuentes

¿Cuántos índices son demasiados? No hay número mágico: cada uno cuesta en INSERT/UPDATE y espacio. Regla práctica: un índice debe justificarse por una consulta real medida (pg_stat_user_indexes muestra usage_idx_scan — índices con 0 lecturas son candidatos a borrarse).

¿REINDEX periódico? En versiones modernas casi nunca hace falta; bloat grave suele indicar autovacuum mal ajustado. REINDEX CONCURRENTLY puntual tras borrados masivos sí puede ayudar.

¿Cómo encuentro las consultas lentas sin esperar quejas? pg_stat_statements: extension oficial que acumula tiempo total, media y frecuencia por consulta normalizada. Ordena por total_exec_time y tendrás tu lista priorizada real.

Formatea tus consultas para leerlas de verdad con nuestro SQL Formatter, gratis y directamente en tu navegador.