Guía completa de optimización de consultas SQL (PostgreSQL)

Esta guía práctica te muestra cómo diagnosticar y optimizar consultas SQL en PostgreSQL (muchos conceptos aplican a otros RDBMS). Cubriremos: creación de esquema de ejemplo, análisis con EXPLAIN/EXPLAIN ANALYZE, índices (simples, compuestos, covering), anti-patrones comunes y soluciones prácticas paso a paso.

1. Caso práctico: esquema y datos

Usaremos un esquema sencillo de comercio con tablas users, products y orders. Crea la base y carga algunos datos de ejemplo:

-- Esquema mínimo
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku TEXT NOT NULL,
  price NUMERIC(10,2)
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL,
  status TEXT NOT NULL,
  placed_at TIMESTAMPTZ DEFAULT now()
);

-- Índices iniciales básicos (FKs ya tienen índices implícitos en algunas DBs)
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (product_id);

Inserta datos masivos para simular carga y que el optimizador tenga trabajo real:

-- Insertar datos (ejemplo simplificado; en producción usar COPY o scripts en batches)
INSERT INTO users (email)
SELECT 'user' || g || '@example.com' FROM generate_series(1,100000) g;

INSERT INTO products (sku, price)
SELECT 'SKU' || g, (random()*100)::numeric(10,2) FROM generate_series(1,1000) g;

INSERT INTO orders (user_id, product_id, quantity, status, placed_at)
SELECT (random()*99999)::int + 1, (random()*999)::int + 1, (random()*5)::int + 1,
       (ARRAY['pending','paid','cancelled','shipped'])[floor(random()*4 + 1)],
       now() - ((random()*365)::int || ' days')::interval
FROM generate_series(1,1000000);

ANALYZE; -- importante: actualizar estadísticas

2. Cómo leer EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra el plan estimado; EXPLAIN ANALYZE ejecuta la consulta y muestra tiempos reales. Siempre compara ambos para entender discrepancias.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.*) AS orders_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.placed_at > now() - interval '30 days'
GROUP BY u.id, u.email
ORDER BY orders_count DESC
LIMIT 50;

Salida típica (simplificada): busca Seq Scan, Index Scan, Hash Join, tiempos (actual time) y filas estimadas vs reales. Si las filas estimadas difieren mucho de las reales, el plan puede ser subóptimo.

3. Problemas comunes y soluciones

3.1 Seleccionar columnas innecesarias (SELECT *)

SELECT * obliga a leer más datos, evita índices covering y aumenta I/O. Pide solo lo que necesitas.

-- Evitar:
SELECT * FROM orders WHERE user_id = 123;
-- Mejor:
SELECT id, product_id, quantity, placed_at FROM orders WHERE user_id = 123;

3.2 No tener índices adecuados

Un índice en orders(user_id) ayuda búsquedas por usuario, pero si filtras por fecha y usuario, un índice compuesto será mejor:

-- Índice compuesto para consultas por usuario y fecha (ordenado):
CREATE INDEX idx_orders_user_placed_at ON orders (user_id, placed_at DESC);

-- Para PostgreSQL 11+ puedes añadir columnas cubiertas
CREATE INDEX idx_orders_cover ON orders (user_id, placed_at DESC) INCLUDE (product_id, quantity);

El índice compuesto permite que la consulta utilice index-only scans si las columnas solicitadas están cubiertas por el índice.

3.3 Usar funciones sobre columnas en WHERE

Hacer WHERE date_trunc('day', placed_at) = '2026-02-01' impide el uso de índices en placed_at. Reescribe a rangos:

-- Malo (no usa índice):
SELECT * FROM orders WHERE date_trunc('day', placed_at) = '2026-02-01'::date;

-- Bueno (usa índice):
SELECT * FROM orders WHERE placed_at >= '2026-02-01'::date AND placed_at < '2026-02-02'::date;

3.4 ORs que evitan índices

OR entre columnas puede descartar índices. Emplea UNION ALL o índices compuestos según el caso.

-- Evitar OR que hace Seq Scan:
SELECT * FROM orders WHERE user_id = 123 OR product_id = 456;

-- Alternativa (más controlable):
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE product_id = 456 AND user_id <> 123;

3.5 JOINs ineficientes: orden y tipo

El optimizador elige el orden de JOINs. A veces transformar subconsultas o añadir índices específicos para la columna JOIN arregla problemas. Prefiere JOINs con condiciones sargables (que usan índices).

4. Antes y después: ejemplo real

Consulta inicial (lenta):

-- Consulta que cuenta ventas recientes por producto
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.sku, COUNT(o.*) AS sales
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
WHERE o.placed_at > now() - interval '90 days'
GROUP BY p.id, p.sku
ORDER BY sales DESC
LIMIT 100;

Problemas detectados: seq scans en orders, estimaciones erradas, lectura masiva de páginas.

Optimización aplicada:

-- 1) Índice compuesto para filtrar por product_id y placed_at
CREATE INDEX idx_orders_product_placed ON orders (product_id, placed_at DESC);

-- 2) Ejecutar ANALYZE para actualizar estadísticas
ANALYZE orders;

-- 3) Reescribir la consulta para evitar LEFT JOIN cuando buscamos solo orders recientes
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.sku, COALESCE(t.sales, 0) AS sales
FROM products p
LEFT JOIN (
  SELECT product_id, COUNT(*) AS sales
  FROM orders
  WHERE placed_at > now() - interval '90 days'
  GROUP BY product_id
) t ON t.product_id = p.id
ORDER BY sales DESC
LIMIT 100;

Resultados esperados: uso de Index Scan en orders para el rango de fechas y agrupación más rápida; reducción significativa de I/O y tiempo real en EXPLAIN ANALYZE.

5. Técnicas avanzadas

• Index-only scans: Diseña índices que cubran las columnas consultadas para evitar acceder a la tabla.
• Partial indexes: Si consultas frecuentes filtran por un valor de columna (ej. status='paid'), crea índices parciales: CREATE INDEX ON orders (product_id) WHERE status = 'paid';
• BRIN indexes: Para tablas append-only muy grandes con orden natural (timestamps), un BRIN puede ser muy barato.
• Materialized views: Para agregaciones costosas que se consultan frecuentemente, usa materialized views y refresca periódicamente.
• Partitioning: Particiona tablas muy grandes por rango (fecha) para eliminar I/O en particiones antiguas.

6. Medir correctamente

No asumas ganancias sin medir. Usa:

• EXPLAIN (ANALYZE, BUFFERS) para ver tiempos y lecturas de páginas
• pg_stat_statements para identificar consultas frecuentes y costosas
• Pruebas con datos representativos y carga similar a producción

7. Errores críticos que debes evitar

1. No actualizar estadísticas después de grandes cambios (usar ANALYZE o autovacuum correctamente configurado).
2. Crear demasiados índices sin evaluar impacto en INSERT/UPDATE/DELETE.
3. Confiar en estimaciones del optimizador sin validar con EXPLAIN ANALYZE.
4. Usar LIMIT/OFFSET para paginación en tablas grandes — usar paginación basada en cursor (seek method) es mejor.
5. Ignorar parámetros de configuración del servidor (work_mem, shared_buffers, maintenance_work_mem) que afectan a joins y ordenaciones.

8. Check-list rápido para optimizar una consulta

• Ejecuta EXPLAIN ANALYZE y revisa si hay Seq Scan, Hash Join costoso o demasiada lectura de buffers.
• Verifica estadísticas con ANALYZE y revisa pg_stats.
• Asegúrate de que las columnas en WHERE/JOIN/ORDER BY tengan índices adecuados.
• Evita operaciones no-sargables (funciones en columnas, casts innecesarios).
• Considera reescribir la consulta (CTEs, subconsultas, agregaciones previas) para limitar el conjunto de datos antes del JOIN.
• Mide: compara tiempos antes/después y observa impacto en DML (INSERT/UPDATE).

9. Configuración y observabilidad

Optimizar no es solo índices. Ajusta parámetros del servidor para tu carga:

• work_mem para joins y ordenaciones en memoria
• shared_buffers para caché de páginas
• effective_cache_size para que el planificador estime caché disponible

Activa pg_stat_statements para ver las consultas más caras y agrega alertas sobre latencia. Mantén un ciclo de monitoreo, optimización y tests de regresión.

Para entornos MySQL/MariaDB muchos de los mismos principios aplican: índices compuestos, evitar funciones en columnas, usar EXPLAIN y analizar motores (InnoDB).

Consejo avanzado: si tienes consultas con patrones de acceso muy estables, considera cachear resultados a nivel de aplicación o usar materialized views con refresh incremental; y vigila el coste de mantenimientos de índices en escrituras.

Próximo paso sugerido: habilita pg_stat_statements, reúne las top-10 queries por tiempo total y aplica la metodología vista aquí (EXPLAIN → índice o reescritura → medir).