Optimización de JOINs y funciones ventana en PostgreSQL

En bases de datos reales las consultas con varios JOINs y funciones ventana pueden ser lentas si no se diseñan correctamente. Aquí tienes una guía práctica para identificar cuellos de botella y optimizar consultas comunes sin perder legibilidad.

Escenario y esquema de ejemplo

Supongamos un modelo sencillo de comercio: clientes, pedidos y lineas de pedido.

CREATE TABLE customers (id serial PRIMARY KEY, name text, country text);
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int REFERENCES customers(id), created_at timestamptz);
CREATE TABLE order_items (id serial PRIMARY KEY, order_id int REFERENCES orders(id), product_id int, qty int, price numeric);

-- índices basicos
CREATE INDEX ON orders(customer_id);
CREATE INDEX ON order_items(order_id);

Consulta naif: JOINs + función ventana

Queremos listar, por cliente, su total de ventas y el rank de clientes por ventas en el último trimestre:

SELECT c.id, c.name,
       SUM(oi.qty * oi.price) AS total_sales,
       RANK() OVER (ORDER BY SUM(oi.qty * oi.price) DESC) AS sales_rank
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 >= now() - interval '3 months'
GROUP BY c.id, c.name
ORDER BY total_sales DESC
LIMIT 100;

Problemas habituales con esta consulta:

• El planner puede hacer sort y aggregation costosos si no hay filtros selectivos o índices que ayuden.
• La función ventana aplicada sobre la agregación obliga a materializar o reordenar los resultados.
• Si las tablas son grandes, el JOIN antes de filtrar por fecha puede multiplicar filas innecesariamente.

Pasos prácticos para optimizar

1) Usa EXPLAIN ANALYZE y EXPLAIN (ANALYZE, BUFFERS)

Siempre parte de medir. Mira tiempos, lecturas de disco y si hay Sort, Hash Join o Seq Scan costosos.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT ...;

2) Filtra temprano y evita multiplicación de filas

Aplica filtros en la tabla que reduzca más filas antes de hacer JOINs. Usa subconsultas o CTEs materializados en casos específicos.

WITH recent_orders AS (
  SELECT id, customer_id
  FROM orders
  WHERE created_at >= now() - interval '3 months'
)
SELECT c.id, c.name,
       SUM(oi.qty * oi.price) AS total_sales,
       RANK() OVER (ORDER BY SUM(oi.qty * oi.price) DESC) AS sales_rank
FROM customers c
JOIN recent_orders ro ON ro.customer_id = c.id
JOIN order_items oi ON oi.order_id = ro.id
GROUP BY c.id, c.name
ORDER BY total_sales DESC
LIMIT 100;

Si recent_orders reduce mucho el conjunto, el planner hará menos trabajo.

3) Crear índices útiles

Para este patrón, un índice compuesto en orders por created_at y customer_id ayuda:

CREATE INDEX idx_orders_created_customer ON orders (created_at DESC, customer_id);
-- y en order_items si consultas por order_id y product_id
CREATE INDEX idx_order_items_order ON order_items (order_id);

El índice por created_at permite un filtro eficiente del rango temporal y, si es selectivo, evita lectores masivos.

4) Reescribir JOINs con LATERAL para agregaciones por grupo

Si quieres top N por cliente con agregaciones costosas, usar LATERAL puede evitar un GROUP BY global:

SELECT c.id, c.name, s.total_sales
FROM customers c
CROSS JOIN LATERAL (
  SELECT SUM(qty * price) AS total_sales
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = c.id
    AND o.created_at >= now() - interval '3 months'
) s
ORDER BY s.total_sales DESC
LIMIT 100;

Esto calcula por cliente y permite paralelismo o índices por cliente en subconsultas.

5) Ventanas: agrupa primero, luego aplica OVER

En muchos casos es mejor materializar la agregación y luego correr la función ventana sobre ese resultado reducido:

WITH sales_per_customer AS (
  SELECT c.id, c.name, SUM(oi.qty * oi.price) AS total_sales
  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 >= now() - interval '3 months'
  GROUP BY c.id, c.name
)
SELECT *, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM sales_per_customer
ORDER BY total_sales DESC
LIMIT 100;

Materializar la agregación reduce la cantidad de filas sobre las que se calcula la ventana.

6) Ajustes de configuración y memoria

Si ves Sorts externos o spills a disco en EXPLAIN, aumenta temporalmente work_mem para la sesión cuando ejecutes agregaciones grandes:

SET work_mem = '256MB';
-- Ejecuta la consulta de análisis
RESET work_mem;

No subas work_mem en exceso en servidores con muchas conexiones concurrencias: cada sesión puede usar esa memoria.

7) Materialized views y mantenimiento

Para agregaciones pesadas que no necesitan datos en tiempo real, usa materialized views y refresca periódicamente:

CREATE MATERIALIZED VIEW mv_sales_quarter AS
SELECT customer_id, SUM(qty * price) AS total_sales
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= date_trunc('quarter', now())
GROUP BY customer_id;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_quarter;

Luego consultas la vista y aplicas funciones ventana sobre un conjunto ya reducido.

Checklist rápido de diagnóstico

• Usa EXPLAIN ANALYZE para ver si hay Seq Scan, Hash Join, o Sort costosos.
• Afina índices: columnas de filtro, ordenamiento y join.
• Filtra lo antes posible; considera CTEs o subconsultas si reducen filas.
• Materializa agregaciones grandes si no requieren frescura inmediata.
• Ajusta work_mem con cuidado para evitar spills a disco.

Ejemplo final: combinación de técnicas

Consulta optimizada usando CTE materializado, índice y ventana posterior:

-- Índice sugerido
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

WITH sales_per_customer AS MATERIALIZED (
  SELECT o.customer_id, SUM(oi.qty * oi.price) AS total_sales
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.created_at >= now() - interval '3 months'
  GROUP BY o.customer_id
)
SELECT c.id, c.name, spc.total_sales,
       RANK() OVER (ORDER BY spc.total_sales DESC) AS sales_rank
FROM sales_per_customer spc
JOIN customers c ON c.id = spc.customer_id
ORDER BY spc.total_sales DESC
LIMIT 100;

Nota: PostgreSQL no soporta la palabra clave MATERIALIZED en CTEs antes de la versión X; si tu versión no la soporta, crea una tabla temporal o materialized view.

Para terminar: siempre mide antes y después de cada cambio, protege tus consultas dinámicas contra inyección y no aumentes parámetros de memoria sin calcular el impacto en concurrencia. Siguiente paso avanzado: usa pg_stat_statements para encontrar tus consultas más costosas y perfila con EXPLAIN (ANALYZE, BUFFERS, VERBOSE) para afinar índices y ajustes de planner.