Guía completa de optimización de consultas SQL para desarrolladores

Objetivo: darte técnicas prácticas y comprobables para acelerar consultas SQL en OLTP y OLAP, entender planes de ejecución y aplicar cambios seguros en producción.

Índice

• Cómo leer un plan de ejecución (EXPLAIN)
• Índices: tipos y cuándo usarlos
• Escritura de consultas sargables
• Joins, subconsultas y CTEs: cuándo elegir
• Agregaciones, window functions y cobertura
• Estadísticas, caché de planes y parameter sniffing
• Particionado, tablas temporales y denormalización
• Monitoreo y herramientas prácticas
• Ejemplo práctico paso a paso
• Checklist rápido y siguiente paso

1) Cómo leer un plan de ejecución (EXPLAIN)

Primero: siempre ejecuta el plan real. En Postgres usa EXPLAIN (ANALYZE, BUFFERS, VERBOSE), en MySQL EXPLAIN FORMAT=JSON, en SQL Server usa el plan de ejecución estimado y real.

-- Postgres example
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.id;

Busca en el plan:

• Operación más costosa (Seq Scan vs Index Scan vs Hash Join vs Merge Join)
• Filtrado: dónde se aplica el WHERE (push-down)
• Estimaciones vs resultados reales (rows, loops). Grandes discrepancias indican estadísticas obsoletas
• Lecturas físicas/lógicas (buffers) para entender I/O

2) Índices: tipos y cuándo usarlos

Reglas prácticas:

• Indexa columnas usadas en JOIN, WHERE y ORDER BY
• Un índice compuesto es útil si la consulta filtra por la primera columna y luego por la segunda
• Evita índices en columnas con muy baja cardinalidad (ej. boolean) salvo si acompañan a otras columnas
• Considera índices parciales (Postgres) para condiciones comunes
• Los índices de cobertura (incluyendo columnas con INCLUDE) evitan back-reads

-- Postgres: índice compuesto y de cobertura
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
-- índice que también cubre total para evitar heap fetch
CREATE INDEX idx_orders_cov ON orders (customer_id, created_at) INCLUDE (total);

-- Índice parcial: sólo pedidos activos del último año
CREATE INDEX idx_recent_active_orders ON orders (customer_id, created_at)
WHERE status = 'completed' AND created_at >= now() - interval '1 year';

3) Escritura de consultas sargables

Sargable = Search ARGument ABLE. Evita operaciones que impidan usar índices:

• No pongas funciones sobre columnas en WHERE (ej. WHERE DATE(created_at) = ...)
• Evita LIKE '%abc%' si puedes usar trigram o índices invertidos
• Reescribe OR con UNION ALL si conviene y hay índices distintos

-- Mal (no sargable)
SELECT * FROM users WHERE LOWER(email) = 'foo@exa.com';
-- Mejor (usa índice functional o normaliza a lowercase en otra columna)
SELECT * FROM users WHERE email = 'foo@exa.com';

-- Si necesitas case-insensitive en Postgres
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

4) Joins, subconsultas y CTEs: cuándo elegir

Reglas rápidas:

• JOINs son generalmente más eficientes que subconsultas correlacionadas
• EXISTS suele ser mejor que IN cuando la subconsulta es grande
• CTEs en Postgres antes de v12 eran materialized y podían impedir optimizaciones; en v12+ puedes usar WITH inlining (depende de versión)

-- Prefiere EXISTS para cortocircuitar
SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 100
);

-- Evita subconsulta que recalcula para cada fila
-- Mal
SELECT c.*, (SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) as spent
FROM customers c;
-- Mejor usando JOIN + GROUP BY o una subconsulta previa aggregada

5) Agregaciones, window functions y cobertura

Para agregaciones grandes considera:

• Agregar índices que permitan agrupación rápida (index-only scans)
• Usar pre-aggregaciones (materialized views) para tablas históricas
• Window functions no siempre pueden ser reemplazadas por GROUP BY; evalúa costos

-- Agregación con pre-aggregation
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT date_trunc('month', created_at) as ym, customer_id, SUM(total) as total
FROM orders
GROUP BY 1,2;

-- Window example
SELECT order_id, customer_id, total,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders;

6) Estadísticas, caché de planes y parameter sniffing

Mantén estadísticas actualizadas: si la estimación de filas es mala, el optimizador elegirá planes inadecuados.

• Postgres: ANALYZE, autovacuum
• MySQL: ANALYZE TABLE y revisar histograms en versiones recientes
• SQL Server: statistics y recompute

Parameter sniffing: en SQL Server un plan óptimo para un parámetro puede ser malo para otro. Soluciones: OPTION(RECOMPILE), hints, o plan guides.

7) Particionado, tablas temporales y denormalización

Cuando la escala supera lo que un índice puede acelerar:

• Particiona por rango (fecha) para queries que filtran por periodo
• Usa tablas temporales o tablas de trabajo para descomponer consultas complejas en pasos
• La denormalización o tablas agregadas pueden ser la solución para reportes

8) Monitoreo y herramientas prácticas

• Postgres: pg_stat_statements para identificar queries más costosas
• MySQL: performance_schema y slow query log
• SQL Server: Query Store, Extended Events
• Usa APMs y métricas de I/O, CPU, latencia de red

9) Ejemplo práctico: optimizando una consulta real

Escenario: tablas simplificadas

-- esquema simplificado
CREATE TABLE customers (id bigint PRIMARY KEY, name text);
CREATE TABLE orders (id bigint PRIMARY KEY, customer_id bigint, created_at timestamptz, total numeric, status text);
CREATE TABLE order_items (id bigint PRIMARY KEY, order_id bigint, product_id bigint, quantity int, price numeric);

Consulta inicial (lenta):

SELECT c.id, c.name, SUM(oi.quantity * oi.price) as spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= '2024-01-01' AND o.status = 'completed'
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.price) > 1000
ORDER BY spent DESC LIMIT 100;

Problemas comunes detectables en el plan:

• Seq Scan en orders u order_items
• Hash Join generando gran uso de memoria
• Estimaciones de rows muy diferentes a los reales

Optimización paso a paso:

1. Crear índices útiles

   CREATE INDEX idx_orders_completed_created ON orders (status, created_at, customer_id);
   CREATE INDEX idx_order_items_order ON order_items (order_id);
       

2. Agregar un índice de cobertura si la lectura del total puede evitar heap fetch

   -- Postgres: include total no aplica para order_items, pero sí en orders si se consulta
   CREATE INDEX idx_orders_cov ON orders (status, created_at, customer_id) INCLUDE (total);
       

3. Reescribir la consulta usando agregación temprana (pre-aggregate order_items)

   WITH oi_sum AS (
     SELECT order_id, SUM(quantity * price) as order_spent
     FROM order_items
     GROUP BY order_id
   )
   SELECT c.id, c.name, SUM(oi.order_spent) as spent
   FROM customers c
   JOIN orders o ON o.customer_id = c.id AND o.status = 'completed' AND o.created_at >= '2024-01-01'
   JOIN oi_sum oi ON oi.order_id = o.id
   GROUP BY c.id, c.name
   HAVING SUM(oi.order_spent) > 1000
   ORDER BY spent DESC LIMIT 100;
       

4. Volver a ejecutar EXPLAIN ANALYZE y comparar: la agregación temprana reduce filas que pasan por el JOIN

10) Checklist rápido

• Revisa EXPLAIN y compara estimado vs real
• ¿Se puede crear un índice compuesto que cubra la consulta?
• ¿Se están usando funciones sobre columnas en WHERE? Evítalas
• ¿Las estadísticas están actualizadas?
• ¿El plan cambia por parámetros (parameter sniffing)?
• ¿La consulta puede dividirse en pasos con tablas temporales o CTEs materializados?

Consejo avanzado: crea un entorno de pruebas que reproduzca la cardinalidad real (muestras) y automatiza comparaciones de EXPLAIN (herramientas como explain.depesz.com o diffs JSON) antes de desplegar cambios de índices en producción. Ten cuidado con índices nuevos: mejor medir el impacto en writes y espacio.

Advertencia: cambiar índices en bases de datos con alto tráfico requiere planeación: crea índices CONCURRENTLY (Postgres) o durante ventanas de mantenimiento, y monitoriza latencia de commits y uso de I/O.

Siguiente paso: aplica estas técnicas a una consulta lenta en tu sistema, captura su EXPLAIN (con ANALYZE), prueba al menos dos alternativas (índice vs reescritura) y mide latencia y I/O antes de elegir.