Optimización de Consultas

Una consulta lenta puede hacer inservible una aplicación en producción. EXPLAIN ANALYZE revela cómo PostgreSQL ejecuta la query internamente. El problema N+1, los índices faltantes, y el SELECT * innecesario son los culpables más comunes de mal rendimiento.

-- EXPLAIN ANALYZE: leer el plan de ejecución
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.nombre, COUNT(o.id) AS pedidos
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.activo = TRUE
GROUP BY u.id, u.nombre;

-- Salida típica a interpretar:
-- HashAggregate (cost=1250.00..1300.00 rows=500 width=40)
--   -> Hash Left Join (cost=500.00..1000.00 rows=10000 width=32)
--         Hash Cond: (o.user_id = u.id)
--         -> Seq Scan on orders (cost=0..300 rows=10000 width=8)
--         -> Hash (cost=450..450 rows=4000 width=30)
--               -> Index Scan on users (cost=0..450 rows=4000 width=30)
--                     Index Cond: (activo = TRUE)
-- Planning time: 2.5 ms
-- Execution time: 45.3 ms  <-- tiempo real

-- PROBLEMA N+1: el anti-patrón más común en ORMs
-- N+1: 1 query para obtener N usuarios +
--      N queries para obtener pedidos de cada uno = N+1 queries
-- Solución: 1 sola query con JOIN
SELECT
  u.nombre,
  COALESCE(json_agg(json_build_object(
    'id', o.id,
    'total', o.total,
    'fecha', o.fecha
  ) ORDER BY o.fecha DESC) FILTER (WHERE o.id IS NOT NULL),
  '[]') AS pedidos
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.nombre;

-- Seleccionar SOLO las columnas necesarias
-- MAL: SELECT * FROM orders (trae todas las columnas)
-- BIEN: SELECT id, total, fecha FROM orders

-- Índice parcial: solo en filas activas
CREATE INDEX idx_orders_activas ON orders(user_id, fecha)
WHERE estado != 'cancelado';

-- VACUUM ANALYZE: actualizar estadísticas del planificador
-- PostgreSQL usa estadísticas para decidir qué plan usar
VACUUM ANALYZE orders;
VACUUM ANALYZE users;

-- Ver tablas con más dead tuples (candidatas para VACUUM)
SELECT relname, n_dead_tup, n_live_tup,
       ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS pct_dead
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;