Indexación en PostgreSQL: de 8 segundos a 12ms en una query real

Hace tres meses recibí una alerta: una query en producción estaba tardando 8 segundos. La tabla tenía 4.2 millones de registros. Después de dos horas de análisis y tres cambios específicos, bajó a 12ms. Te cuento exactamente qué hice.

El punto de partida: EXPLAIN ANALYZE

Antes de tocar cualquier índice, hay que entender qué está pasando. EXPLAIN ANALYZE es tu radiografía:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
  AND u.created_at > '2024-01-01'
GROUP BY u.id, u.name, u.email
ORDER BY order_count DESC
LIMIT 50;

El output que vi:

Sort (cost=284920.43..284920.56 rows=50) (actual time=8234.421..8234.432 rows=50)
  -> HashAggregate (cost=284919.00..284919.50 rows=50)
      -> Hash Left Join (cost=45230.00..278419.00)
          -> Seq Scan on users (cost=0.00..38500.00 rows=820000)
              Filter: ((status = 'active') AND (created_at > '2024-01-01'))
              Rows Removed by Filter: 3380000

Tres palabras que me alertaron: Seq Scan on users. PostgreSQL estaba leyendo los 4.2M de registros para filtrar 820K activos creados en 2024. Sin índice.

Fix 1: Índice parcial (el más impactante)

La mayoría de queries filtra por status = 'active'. En lugar de indexar toda la columna status, creé un índice parcial que solo indexa los usuarios activos:

CREATE INDEX CONCURRENTLY idx_users_active_created
ON users (created_at)
WHERE status = 'active';

CONCURRENTLY es clave en producción: crea el índice sin bloquear la tabla. Tarda más, pero no para el sistema.

Resultado: el Seq Scan desapareció. Bajó a 340ms. Mejor, pero no suficiente.

Fix 2: Covering Index para el JOIN

El JOIN con orders seguía siendo costoso. PostgreSQL hacía un index scan en orders(user_id) y luego un heap fetch para leer orders.id. Con un covering index, evitas el heap fetch:

CREATE INDEX CONCURRENTLY idx_orders_user_covering
ON orders (user_id)
INCLUDE (id, created_at);

La cláusula INCLUDE agrega columnas al índice sin que sean parte de la key de búsqueda. El índice tiene todo lo que la query necesita; no tiene que ir a la tabla.

Resultado: 89ms. Casi ahí.

Fix 3: Statistics y autovacuum

EXPLAIN mostraba estimaciones de rows muy alejadas de la realidad (estimaba 50K, la realidad era 820K). Esto hace que el planner tome decisiones subóptimas sobre qué algoritmo de join usar.

-- Aumentar la granularidad de estadísticas para estas columnas
ALTER TABLE users ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE users ALTER COLUMN created_at SET STATISTICS 500;

-- Forzar recolección inmediata
ANALYZE users;

Con estadísticas más precisas, el planner eligió Hash Join sobre Nested Loop, que es más eficiente con el volumen de datos que manejamos.

Resultado final: 12ms. De 8 segundos a 12 milisegundos.

El toolkit de diagnóstico que uso siempre

-- Ver índices existentes y su uso
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan DESC;

-- Ver queries lentas (requiere pg_stat_statements)
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Tamaño de índices
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'users';

Lo que aprendí

Los índices no son gratis: cada índice ralentiza los INSERTs y UPDATEs. La estrategia correcta es indexar las queries de lectura críticas, no todas las columnas por defecto. El análisis con EXPLAIN ANALYZE tarda 20 minutos. La optimización correcta salva horas de latencia acumulada.

Mide primero. Indexa después. Siempre.