Problema que se suele ver
Consultas que crecen en tiempo a medida que la tabla aumenta. A menudo el culpable no es la base de datos, sino un índice mal diseñado o una consulta no sargable. Aquí verás cómo diagnosticar y corregirlo con ejemplos prácticos en PostgreSQL y MySQL.
1) Diagnóstico rápido con EXPLAIN
Siempre comienza con un EXPLAIN (o EXPLAIN ANALYZE en entornos de prueba). Mira si la consulta usa Seq Scan / table scan en lugar de Index Scan / index seek.
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2024-01-01';
-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2024-01-01';
Si ves "Seq Scan" o "ALL" (MySQL), la consulta está barriendo la tabla.
2) Índices compuestos: el orden importa
Si tu WHERE tiene varias columnas y además haces ORDER BY o SELECT solo un subconjunto, el orden de las columnas en el índice es crítico.
-- MAL: índice con orden invertido
CREATE INDEX idx_orders_created_customer ON orders (created_at, customer_id);
-- BUENO: para WHERE customer_id = ? AND created_at > ?
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
Explicación breve: un índice compuestos sirve para búsquedas que filtren por el primer prefijo de columnas. Si filtras por customer_id y luego por created_at, pon customer_id primero.
3) Index-only scans / covering indexes
Si la consulta solo necesita columnas presentes en el índice, algunas bases de datos (PostgreSQL, InnoDB en MySQL) pueden devolver resultados sin leer la fila completa.
-- PostgreSQL: haz el índice cubrir las columnas necesarias
CREATE INDEX idx_orders_cover ON orders (customer_id, created_at) INCLUDE (status);
-- MySQL (InnoDB) crea índice que incluye las columnas que vas a SELECT
ALTER TABLE orders ADD INDEX idx_orders_cover (customer_id, created_at, status);
Esto reduce I/O y mejora latencias para consultas frecuentes.
4) Evita consultas no-sargables
Funciones o operaciones sobre la columna impiden usar índices.
-- NO: evita esto si la columna está indexada
SELECT * FROM users WHERE LOWER(email) = 'juan@example.com';
-- SÍ: normaliza los datos o usa un índice expresivo
-- Opción A: almacenar email en minúsculas
-- Opción B (PostgreSQL): índice sobre la expresión
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
5) Índices parciales y expresiones
Si solo consultas filas con una condición frecuente (por ejemplo, status = 'active'), crea índices parciales/filtrados para reducir tamaño y costo de mantenimiento.
-- PostgreSQL: índice parcial
CREATE INDEX idx_orders_active_customer ON orders (customer_id, created_at)
WHERE status = 'active';
-- MySQL: emular índice parcial con columna computada (o mantener columna booleana)
ALTER TABLE orders ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 0;
UPDATE orders SET is_active = (status = 'active');
ALTER TABLE orders ADD INDEX idx_orders_active_customer (is_active, customer_id, created_at);
6) Estadísticas y planner
Asegúrate de mantener estadísticas actualizadas; un planner con estadísticas malas elegirá scans en lugar de índices.
-- PostgreSQL
ANALYZE orders;
-- o VACUUM ANALYZE orders;
-- MySQL (InnoDB) en general actualiza automáticamente, pero puedes usar:
ANALYZE TABLE orders;
7) Coste de los índices: escribe vs lee
No indexar todo: cada índice penaliza INSERT/UPDATE/DELETE. Balancea según R/W ratio. Para tablas muy write-heavy considera índices más pequeños (BRIN en PostgreSQL para datos cronológicos).
-- Ejemplo: BRIN para tablas append-only con timestamps muy correlacionados
CREATE INDEX brin_orders_created ON orders USING BRIN (created_at);
8) Cómo medir impacto (pautas)
- Captura EXPLAIN antes y después.
- Mide latencia p99 y throughput en staging con datos representativos.
- Usa herramientas: pg_stat_statements (Postgres), performance_schema / sys schema (MySQL).
-- PostgreSQL: consultar uso de índices
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes JOIN pg_class ON pg_stat_user_indexes.indexrelid = pg_class.oid
WHERE relname = 'orders';
9) Casos prácticos
Escenario: tienes una consulta lenta:
SELECT id, customer_id, total, status
FROM orders
WHERE customer_id = 123
AND status = 'active'
ORDER BY created_at DESC
LIMIT 50;
Solución efectiva:
-- Índice que cubre filtro + orden (Postgres con INCLUDE)
CREATE INDEX idx_orders_customer_status_created ON orders (customer_id, status, created_at DESC) INCLUDE (total);
-- Alternativa MySQL
ALTER TABLE orders ADD INDEX idx_orders_customer_status_created (customer_id, status, created_at);
Verifica con EXPLAIN que se produce un Index Scan y/o Index Only Scan. Si sigues viendo table scan, revisa selectividad de las columnas y estadísticas.
10) Pitfalls y recomendaciones rápidas
- No indexar columnas con alta cardinalidad cuando no se consultan por valor exacto.
- Evita índices redundantes: combina en uno si la consulta los usa juntos.
- Cuida orden en composite indexes: piensa en prefijos que usarán las consultas.
- Para consultas con rango + orden, coloca la columna de rango después de la columna de igualdad en el índice.
Si necesitas ayuda práctica: toma el EXPLAIN de tu consulta, la DDL de la tabla (CREATE TABLE) y las consultas frecuentes; con eso podré sugerir índices exactos y una estrategia de pruebas.
Consejo avanzado: en PostgreSQL, combina índices de tipo B-tree con un índice BRIN para particiones lógicas (por ejemplo, particionar por rango de fechas) y usa REINDEX CONCURRENTLY en mantenimiento programado para evitar locks prolongados. Y atención: nunca construyas indices basados en entradas de usuario sin parametrizar la consulta — eso además de ineficiente puede abrir puertas a SQL injection.
¿Quieres comentar?
Inicia sesión con Telegram para participar en la conversación