Analizar y optimizar índices SQL con EXPLAIN
Identifica índices faltantes, sin uso e ineficientes leyendo planes de ejecución y midiendo el costo de las consultas con EXPLAIN.
Visión General
Los índices son la herramienta principal para hacer que las consultas SQL sean rápidas, pero agregarlos a ciegas puede desperdiciar espacio, ralentizar escrituras e incluso hacer consultas más lentas. El enfoque correcto es comenzar con el plan de ejecución. EXPLAIN y EXPLAIN ANALYZE revelan si la base de datos está escaneando toda la tabla o usando un índice, y estiman el costo de cada paso para que puedas apuntar a los cuellos de botella más grandes primero.
Cuándo Usar
- For alternatives, see Database Replication.
Usa este recurso cuando:
- Una consulta sea más lenta de lo esperado y sospeches un índice faltante.
- Quieras verificar que un índice recién creado se está usando.
- Estés revisando logs de consultas lentas o dashboards de rendimiento.
- Necesites decidir entre un índice B-tree, GIN o parcial.
Solución
Analizar una consulta con EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE customer_id = 1234
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;
-- Crear un índice compuesto si el plan muestra un escaneo secuencial
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);
Encontrar índices no usados y duplicados
-- PostgreSQL: encontrar índices que nunca se han usado desde el último reset
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Resetear estadísticas para obtener datos frescos
SELECT pg_stat_reset();
-- Encontrar índices duplicados (mismas columnas, diferentes nombres)
SELECT pg_size_pretty(pg_relation_size(indexrelid)) AS size,
relname AS table,
indexrelname AS index,
string_agg(attname, ', ' ORDER BY array_position(ix.indkey, attnum)) AS columns
FROM pg_index ix
JOIN pg_class c ON c.oid = ix.indrelid
JOIN pg_class ci ON ci.oid = ix.indexrelid
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(ix.indkey)
GROUP BY relname, indexrelname, ix.indkey
HAVING count(*) > 0
ORDER BY pg_relation_size(indexrelid) DESC;
Crear un índice parcial para registros activos
-- Indexar solo filas no eliminadas: ahorra espacio cuando la mayoría están eliminadas
CREATE INDEX idx_orders_active_customer
ON orders (customer_id)
WHERE deleted_at IS NULL;
-- Verificar que el índice parcial se usa
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1234 AND deleted_at IS NULL;
Crear un índice de cobertura para index-only scans
-- Incluir columnas en el índice para evitar heap lookups
CREATE INDEX idx_orders_covering
ON orders (customer_id, created_at DESC)
INCLUDE (total_amount, status);
-- Esta consulta puede ahora usar un Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, created_at, total_amount, status
FROM orders
WHERE customer_id = 1234
ORDER BY created_at DESC
LIMIT 50;
Crear un índice de expresión para consultas calculadas
-- Indexar email en minúsculas para búsquedas case-insensitive
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
-- La consulta debe coincidir con la expresión exactamente
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Indexar extracción de fecha para reportes mensuales
CREATE INDEX idx_orders_month
ON orders (DATE_TRUNC('month', created_at));
Eliminar índices no usados de forma segura
-- Paso 1: Monitorear al menos una semana para capturar patrones semanales
-- Paso 2: Verificar el tamaño del índice antes de eliminar
SELECT pg_size_pretty(pg_relation_size('idx_orders_old_status'));
-- Paso 3: Eliminar el índice
DROP INDEX CONCURRENTLY idx_orders_old_status;
-- CONCURRENTLY previene el bloqueo de la tabla durante la eliminación
Explicación
EXPLAIN (ANALYZE, BUFFERS) ejecuta la consulta y reporta tiempo real de ejecución más estadísticas de I/O. Busca Seq Scan en tablas grandes, lo que significa que la base de datos lee cada fila. Si el filtro es selectivo, un índice compuesto en (customer_id, created_at) permite a la base de datos saltar a las filas relevantes y devolverlas en orden. El orden del índice debe coincidir con columnas de igualdad primero, luego rangos, y finalmente ordenamiento.
Métricas clave a leer del output:
| Métrica | Significado | Alerta |
|---|---|---|
Seq Scan | Escaneo completo de tabla | En tablas con >10k filas |
cost=0.00..5234.12 | Unidades de costo estimado | Comparar antes/después |
rows=1000000 | Filas estimadas | Muy diferente de real = stats desactualizadas |
actual time=234.5..567.8 | Milisegundos reales | Comparar con baseline |
Buffers: shared hit=1234 read=5678 | Cache hits vs disk reads | Alto read = I/O bottleneck |
Mantenimiento de Índices
Reconstruir índices fragmentados
-- Verificar bloat de índices
SELECT schemaname, relname, indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
-- Reconstruir índice sin bloquear la tabla
REINDEX INDEX CONCURRENTLY idx_orders_customer_created;
-- Reconstruir todos los índices de una tabla
REINDEX TABLE CONCURRENTLY orders;
Actualizar estadísticas después de cargas masivas
-- Después de importar grandes datasets, actualizar estadísticas del planificador
ANALYZE orders;
-- Establecer target de estadísticas para estimaciones más precisas
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;
Variantes
| Tipo de índice | Mejor para | Ejemplo |
|---|---|---|
| B-tree | Igualdad y rango | WHERE id = 5 o WHERE date > '2024-01-01' |
| GIN | Array, JSONB, full-text | WHERE tags @> ARRAY['x'] |
| BRIN | Tablas muy grandes, naturalmente ordenadas | Datos de series temporales |
| Parcial | Subconjunto de filas | WHERE deleted_at IS NULL |
| Cobertura | Index-only scans | INCLUDE (col1, col2) |
| Expresión | Columnas calculadas | LOWER(email) |
-- Índice GIN para consultas JSONB
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);
SELECT * FROM events WHERE metadata @> '{"type": "click"}';
-- Índice BRIN para time-series (footprint pequeño, rápido para datos ordenados)
CREATE INDEX idx_metrics_time_brin ON metrics USING BRIN (recorded_at);
Lo que funciona
- Mide siempre antes y después.
EXPLAIN ANALYZEda prueba concreta de mejora. - Indexa columnas de igualdad primero. Son más selectivas que las de rango.
- Mantén índices estrechos. Incluye solo columnas que la consulta necesita.
- Elimina índices no usados. Consumen espacio en disco y ralentizan escrituras.
- Monitorea rendimiento de escritura. Cada índice agrega costo a
INSERT,UPDATEyDELETE. - Usa CONCURRENTLY en producción. Crear o eliminar índices sin
CONCURRENTLYbloquea la tabla. - Ejecuta ANALYZE después de cargas masivas. Estadísticas desactualizadas causan malas decisiones del planificador.
Errores Comunes
- Agregar un índice para cada consulta lenta. Demasiados índices dañan throughput de escritura y mantenimiento.
- Orden incorrecto de columnas en índices compuestos. La columna principal debe ser la usada en filtros de igualdad.
- Indexar columnas de baja cardinalidad solas. Un índice en
statuscon solo tres valores raramente es útil. - Olvidar actualizar estadísticas. Ejecuta
ANALYZEdespués de cargas masivas para que el planificador tenga conteos precisos. - Asumir que el planificador usará el índice. Confirma siempre con
EXPLAIN; los hints son último recurso. - Crear índices sin CONCURRENTLY en producción. Esto bloquea la tabla y causa downtime.
- Ignorar el bloat de índices. Índices fragmentados crecen más de lo necesario y ralentizan scans. Ejecuta
REINDEX CONCURRENTLYperiódicamente.
Comparación de Rendimiento
| Escenario | Sin índice | Con índice compuesto | Mejora |
|---|---|---|---|
| 1M filas, filtro de igualdad | 340ms (Seq Scan) | 0.8ms (Index Scan) | 425x |
| 1M filas, rango + sort | 520ms (Seq Scan + Sort) | 1.2ms (Index Scan) | 433x |
| 10M filas, count por FK | 4.2s (Seq Scan) | 15ms (Index Only Scan) | 280x |
| 500K filas, filtro JSONB | 890ms (Seq Scan) | 3ms (GIN Scan) | 296x |
Checklist de Monitoreo
Ejecuta estas consultas semanalmente para mantener la salud de los índices:
-- 1. Top 10 índices más grandes (candidatos para revisión)
SELECT relname, indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
-- 2. Índices con cero scans (no usados)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- 3. Tablas con presión de sequential scan
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100 AND seq_tup_read > idx_tup_fetch
ORDER BY seq_tup_read DESC;
Programa estos checks como un cron job semanal y registra los resultados para seguir las tendencias de salud de los índices a lo largo del tiempo.
Técnicas Avanzadas
Optimización de index-only scan
Maximiza index-only scans incluyendo columnas frecuentemente accedidas:
-- Crear un índice de cobertura para patrones de consulta comunes
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, created_at DESC)
INCLUDE (total_amount, status, shipping_address);
-- Verificar index-only scan en el plan de ejecución
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, created_at, total_amount, status
FROM orders
WHERE customer_id = 1234
ORDER BY created_at DESC
LIMIT 50;
Índices hipotéticos para pruebas
Prueba el impacto de índices antes de crearlos:
-- Habilitar extensión hypopg para índices hipotéticos
CREATE EXTENSION IF NOT EXISTS hypopg;
-- Probar un índice hipotético sin crearlo
SELECT * FROM hypopg_create_index('orders', 'customer_id, created_at');
-- Explicar con el índice hipotético
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234 AND created_at > '2024-01-01';
-- Limpiar índices hipotéticos
SELECT hypopg_reset();
Estadísticas de uso de índices por patrón de consulta
Rastrea qué consultas usan qué índices:
-- Habilitar extensión pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Encontrar consultas que se beneficiarían de índices específicos
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY total_time DESC
LIMIT 20;
Ejecución paralela de consultas con índices
Aprovecha workers paralelos para escaneos grandes:
-- Establecer workers paralelos para escaneos grandes de tabla
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 100;
SET parallel_tuple_cost = 0.01;
-- Verificar ejecución paralela en el plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM orders WHERE status = 'completed';
Particionamiento de índices para tablas grandes
Particiona índices junto con particionamiento de tabla:
-- Crear tabla particionada con índices locales
CREATE TABLE orders_partitioned (
id BIGSERIAL,
customer_id INTEGER,
created_at TIMESTAMP DEFAULT NOW(),
status TEXT
) PARTITION BY RANGE (created_at);
-- Crear particiones con índices locales
CREATE TABLE orders_2024_q1 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE INDEX idx_orders_2024_q1_customer
ON orders_2024_q1 (customer_id);
-- La consulta usa pruning de partición + índice local
EXPLAIN ANALYZE
SELECT * FROM orders_partitioned
WHERE customer_id = 1234
AND created_at >= '2024-01-01';
Índices hash para comparaciones de igualdad
Usa índices hash para consultas de solo igualdad en columnas grandes:
-- Índice hash para igualdad exacta en columnas de texto grandes
CREATE INDEX idx_users_email_hash
ON users USING HASH (email);
-- Solo funciona para igualdad, no rango o pattern matching
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';
Filtros Bloom para filtrado multi-columna
Usa índices bloom para filtrado multi-columna eficiente:
-- Habilitar extensión bloom
CREATE EXTENSION IF NOT EXISTS bloom;
-- Crear índice bloom para múltiples columnas
CREATE INDEX idx_orders_bloom
ON orders USING bloom (customer_id, status, created_at);
-- Lookup multi-columna eficiente
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1234
AND status = 'pending'
AND created_at > '2024-01-01'; Preguntas frecuentes
¿Cuál es la diferencia entre EXPLAIN y EXPLAIN ANALYZE?
EXPLAIN muestra el plan planificado. EXPLAIN ANALYZE ejecuta la consulta realmente y reporta tiempos y filas reales procesadas.
¿Cómo sé si un índice está siendo usado?
Busca Index Scan o Index Only Scan en el plan. Seq Scan en una tabla grande generalmente significa que el índice no se usa. También puedes revisar pg_stat_user_indexes.idx_scan para ver uso acumulado.
¿Debería agregar un índice a cada columna de clave foránea?
Generalmente sí, especialmente si la columna se usa en JOINs, WHERE o búsquedas de hijos. Pero verifica uso con EXPLAIN.
¿Cuántos índices son demasiados?
No hay un número fijo. Monitorea el rendimiento de escritura al agregar índices. Si la latencia de INSERT o UPDATE crece más allá de tu SLA, tienes demasiados. Regla general: 5-10 índices por tabla para OLTP, más para tablas de reporte con mucha lectura.
¿Cuál es el costo de un índice faltante?
Un Seq Scan en una tabla de 10M filas puede tardar segundos. La misma consulta con un índice tarda milisegundos. El costo no es solo latencia sino también presión de CPU e I/O que afecta otras consultas.
¿Cuándo usar un índice de cobertura vs un índice compuesto?
Usa un índice de cobertura (INCLUDE) cuando quieres un Index Only Scan y las columnas extra no son parte del filtro u ordenamiento. Usa un índice compuesto cuando todas las columnas son parte del filtro u ordenamiento.
¿Cómo pruebo el rendimiento de índices en staging sin datos de producción?
Genera datos sintéticos con cardinalidad realista. Usa generate_series en PostgreSQL para crear filas de prueba. Compara el output de EXPLAIN ANALYZE antes y después de agregar el índice.
¿Pueden los índices perjudicar el rendimiento de SELECT?
Sí. Si el planificador elige un index scan cuando un sequential scan sería más rápido (ej: el índice coincide con pocas filas pero la tabla es pequeña), o si el índice está fragmentado y requiere más I/O que un scan directo. El planificador generalmente acierta, pero estadísticas desactualizadas pueden causar malas elecciones.
¿Cómo manejo índices durante una migración?
Crea índices con CREATE INDEX CONCURRENTLY después de la migración de datos, no antes. Pre-crear índices en una tabla vacía y luego cargar datos es más lento que cargar primero e indexar después. Para tablas grandes, considera crear el índice en una sesión en background.
¿Cuál es la diferencia entre CLUSTER y REINDEX?
REINDEX reconstruye la estructura de datos del índice. CLUSTER reordena físicamente las filas de la tabla para coincidir con un índice. CLUSTER es una operación única (PostgreSQL no mantiene el orden), mientras que REINDEX aborda el bloat que se acumula con el tiempo.
Recursos Relacionados
Réplicas de Lectura: Escala Lecturas Sin Tocar tu App
Guía de producción sobre réplicas de lectura: configuración de replicación streaming, enrutamiento de lectura/escritura (app, ORM y proxy), manejo del lag, monitoreo, failover y ajuste fino en PostgreSQL, MySQL y réplicas gestionadas en la nube.
GuideGuía Completa de Optimización de Consultas SQL
Detecta y corrige consultas SQL lentas: lee planes EXPLAIN, indexa bien, elimina consultas N+1 y aplica particionamiento, pooling y caché en PostgreSQL y MySQL.
RecipeOptimizacion de Queries e Indexing en PostgreSQL
Analiza y optimiza queries lentas en PostgreSQL usando EXPLAIN, indexing apropiado, partial indexes y reescritura de queries para reducir tiempo de ejecucion
RecipeEncontrar y eliminar filas duplicadas en SQL
Detecta y elimina registros duplicados en tablas SQL usando GROUP BY y HAVING, conservando la fila canónica de forma segura.
RecipeRecorrer datos jerárquicos con CTEs recursivas
Consulta estructuras en árbol o grafo en SQL usando expresiones comunes de tabla recursivas para recorrer relaciones padre-hijo.
RecipeConfigurar índices de búsqueda de texto completo
Configura índices de búsqueda de texto completo en PostgreSQL para consultar grandes columnas de texto con ranking y resaltado.