Práctica: Rewrite de Queries Problemáticas

Concepto clave

El rewrite de queries es el proceso de transformar una consulta SQL problemática en una versión más eficiente que produzca el mismo resultado. En aplicaciones de alta concurrencia, una sola query mal escrita puede convertirse en un cuello de botella que afecta a cientos o miles de usuarios simultáneos. Imagina una autopista donde un solo conductor mal estacionado bloquea múltiples carriles: el rewrite sería como redirigir el tráfico por rutas alternativas más fluidas.

La clave está en entender que PostgreSQL ejecuta tu SQL, no necesariamente de la forma más óptima. Tu trabajo como ingeniero backend es escribir queries que el optimizador pueda transformar eficientemente. Esto implica reconocer patrones problemáticos como N+1 queries, subqueries correlacionadas innecesarias, y operaciones en el lado incorrecto de los joins. El objetivo final es reducir el tiempo de ejecución y el consumo de recursos sin alterar la lógica de negocio.

Cómo funciona en la práctica

Vamos a trabajar con un escenario real: una plataforma de e-commerce con alta concurrencia. La base de datos tiene tablas orders (pedidos), order_items (ítems del pedido), y products (productos). Los desarrolladores reportan lentitud en el dashboard de administración que muestra los pedidos recientes con sus productos.

Paso 1: Identificar la query problemática usando EXPLAIN ANALYZE. Encontramos esta consulta:

SELECT o.id, o.customer_name, o.created_at,
       (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) as item_count,
       (SELECT SUM(oi.quantity * p.price) FROM order_items oi 
        JOIN products p ON oi.product_id = p.id 
        WHERE oi.order_id = o.id) as total_amount
FROM orders o
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

Paso 2: Analizar los problemas. Esta query tiene dos subqueries correlacionadas que se ejecutan por cada fila de orders, causando un patrón N+1. Para 50 pedidos, se ejecutan 101 queries (1 principal + 50 para item_count + 50 para total_amount).

Paso 3: Rediseñar usando joins y agregaciones. Transformamos las subqueries correlacionadas en joins que procesan todos los datos en una sola pasada.

Codigo en accion

Aquí está el rewrite completo con explicaciones:

-- QUERY ORIGINAL (PROBLEMÁTICA)
SELECT o.id, o.customer_name, o.created_at,
       (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) as item_count,
       (SELECT SUM(oi.quantity * p.price) FROM order_items oi 
        JOIN products p ON oi.product_id = p.id 
        WHERE oi.order_id = o.id) as total_amount
FROM orders o
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- QUERY OPTIMIZADA (REWRITE)
SELECT o.id, o.customer_name, o.created_at,
       COALESCE(oi_stats.item_count, 0) as item_count,
       COALESCE(oi_stats.total_amount, 0) as total_amount
FROM orders o
LEFT JOIN (
    SELECT oi.order_id,
           COUNT(*) as item_count,
           SUM(oi.quantity * p.price) as total_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    GROUP BY oi.order_id
) oi_stats ON o.id = oi_stats.order_id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

La query optimizada ejecuta solo 2 operaciones principales: un scan de la tabla orders y un join con la subquery agregada. En pruebas con 10,000 pedidos y 50,000 ítems, el tiempo de ejecución bajó de 850ms a 45ms.

Errores comunes

• Usar SELECT * en subqueries grandes: Extraer columnas innecesarias aumenta el volumen de datos procesados. Siempre lista solo las columnas necesarias.
• Ignorar el orden de los joins en queries complejas: PostgreSQL optimiza mejor cuando las tablas más pequeñas o más filtradas van primero. Usa EXPLAIN para verificar el plan de ejecución.
• Olvidar COALESCE en LEFT JOINs: Cuando no hay datos coincidentes, obtendrás NULLs que pueden romper tu aplicación. Usa COALESCE(column, valor_por_defecto).
• Usar funciones en el lado izquierdo de condiciones WHERE: WHERE UPPER(name) = 'JUAN' no puede usar índices. Mejor: WHERE name = 'Juan' o WHERE name ILIKE 'juan'.
• No considerar la paginación eficiente: LIMIT/OFFSET se vuelve lento en offsets grandes. Para dashboards, usa WHERE id > último_id_visto LIMIT 50.

Checklist de dominio

1. ¿Identificaste y eliminaste todas las subqueries correlacionadas reescribiéndolas como joins?
2. ¿Verificaste con EXPLAIN ANALYZE que el nuevo plan de ejecución tiene menos operaciones secuenciales?
3. ¿Aseguraste que los índices existentes se utilizan efectivamente en la nueva query?
4. ¿Probarste la query con volúmenes de datos similares a producción?
5. ¿Documentaste el cambio y la mejora de performance para el equipo?
6. ¿Consideraste el impacto en la concurrencia (bloqueos, tiempo de ejecución)?
7. ¿Validaste que los resultados son idénticos entre la versión original y la optimizada?

Optimiza una query de reporte de ventas con múltiples filtros

Tienes una query de reporte que está causando timeouts en producción durante horas pico. Tu tarea es analizarla y reescribirla para mejor performance.

1. Crea una base de datos de prueba con estas tablas:

   CREATE TABLE sales (
       id SERIAL PRIMARY KEY,
       product_id INTEGER,
       quantity INTEGER,
       price DECIMAL(10,2),
       sale_date DATE,
       region VARCHAR(50),
       salesperson_id INTEGER
   );
   
   CREATE TABLE products (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       category VARCHAR(50)
   );
   
   CREATE TABLE salespeople (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       department VARCHAR(50)
   );

2. Inserta datos de prueba (10,000 ventas, 100 productos, 50 vendedores).
3. Analiza esta query problemática:

   SELECT 
       p.name,
       p.category,
       (SELECT COUNT(*) FROM sales s WHERE s.product_id = p.id AND s.sale_date BETWEEN '2024-01-01' AND '2024-03-31') as q1_sales,
       (SELECT SUM(s.quantity * s.price) FROM sales s WHERE s.product_id = p.id AND s.region = 'North' AND s.sale_date >= '2024-01-01') as north_revenue,
       (SELECT sp.name FROM salespeople sp WHERE sp.id = (SELECT s.salesperson_id FROM sales s WHERE s.product_id = p.id ORDER BY s.quantity DESC LIMIT 1)) as top_salesperson
   FROM products p
   WHERE p.category IN ('Electronics', 'Clothing')
   ORDER BY p.name;

4. Identifica al menos 3 problemas de performance en la query original.
5. Reescribe la query usando joins y agregaciones apropiadas.
6. Ejecuta EXPLAIN ANALYZE en ambas versiones y compara los resultados.
7. Documenta la mejora en tiempo de ejecución y el nuevo plan de consulta.

• Piensa en cómo convertir las subqueries correlacionadas en joins con GROUP BY
• Considera usar CTEs (WITH clauses) para organizar las agregaciones intermedias
• Asegúrate de que los filtros de fecha y región se apliquen temprano en el proceso

Evalua tu comprension

Completa el quiz interactivo de arriba para ganar XP.