LeadFlow — Informe de Optimización SQL

Consultas problemáticas

↑ Seq Scans identificados

Tiempo medio antes

9.2s

Query más lenta del dashboard

Tiempo estimado después

<350ms

↓ 96% reducción

Índices propuestos

5 nuevos + 1 GIN JSONB

Consulta 1 — Resumen de pipeline por etapa

Búsqueda de leads por dominio + join con deals

9.2s — CRÍTICO

EXPLAIN ANALYZE

Antes

Después

-- EXPLAIN ANALYZE output (antes de optimizar)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM leads
JOIN deals ON leads.id = deals.lead_id
JOIN stages ON deals.stage_id = stages.id
WHERE LOWER(leads.email) LIKE '%@empresa.com%'
  AND deals.created_at > '2024-01-01'
ORDER BY deals.value DESC;

-- Resultado del plan:
-- Sort  (cost=24831.44..24856.44 rows=10000)  (actual time=9187.33..9193.41 rows=8723)
--   Sort Key: deals.value DESC
--   ->  Hash Join  (cost=2148.50..23981.44)  (actual time=145.82..9024.11 rows=8723)
--         ->  Seq Scan on deals  (cost=0.00..18234.00)  ⚠️ FULL TABLE SCAN
--               Filter: (created_at > '2024-01-01'::date)
--               Rows Removed by Filter: 34219
--         ->  Hash  (cost=1834.00..1834.00)
--               ->  Seq Scan on leads  (cost=0.00..1834.00)  ⚠️ FULL TABLE SCAN
--                     Filter: (lower(email) ~~ '%@empresa.com%')
--                     Rows Removed by Filter: 172341
-- Planning Time: 12.43 ms
-- Execution Time: 9187.33 ms  ← 9.2 segundos!

ANTES — 9.187ms

-- PROBLEMAS DETECTADOS:
-- 1. LOWER(email) impide uso de índice
-- 2. SELECT * carga columnas innecesarias
-- 3. LIKE '%...' no puede usar B-Tree
-- 4. Sin índice en deals.created_at
-- 5. Seq Scan en 180k + 95k filas

SELECT *
FROM leads
JOIN deals ON leads.id = deals.lead_id
JOIN stages ON deals.stage_id = stages.id
WHERE LOWER(leads.email) LIKE '%@empresa.com%'
  AND deals.created_at > '2024-01-01'
ORDER BY deals.value DESC;

DESPUES — ~85ms

-- SOLUCIONES APLICADAS:
-- 1. Índice funcional en LOWER(email)
-- 2. Índice en deals(created_at, lead_id)
-- 3. SELECT columnas específicas
-- 4. Filtrar leads ANTES del JOIN

SELECT
  l.id, l.name, l.email, l.company,
  d.value, d.status, d.created_at,
  s.name AS stage_name
FROM (
  SELECT id, name, email, company
  FROM leads
  WHERE LOWER(email) LIKE '%@empresa.com%'
) l
JOIN deals d ON l.id = d.lead_id
  AND d.created_at > '2024-01-01'
JOIN stages s ON d.stage_id = s.id
ORDER BY d.value DESC;

Consulta 2 — Top comerciales por revenue

JOIN implícito (producto cartesiano) + GROUP BY sin índice

6.8s — ALTO

ANTES — 6.813ms

-- JOIN implícito = producto cartesiano
-- luego se filtra: MUY ineficiente
-- Sin índice en deal_assignments(user_id)
-- Sin índice en deals(status)

SELECT u.name, u.email,
       SUM(d.value) as total_revenue,
       COUNT(*) as total_deals
FROM users u, deals d, deal_assignments da
WHERE u.id = da.user_id
  AND d.id = da.deal_id
  AND d.status = 'closed_won'
GROUP BY u.name, u.email
ORDER BY total_revenue DESC
LIMIT 10;

DESPUES — ~42ms

-- JOIN explícito + índices compuestos
-- Filtro status ANTES del GROUP BY
-- GROUP BY u.id (PK, más eficiente)

SELECT
  u.id, u.name, u.email,
  SUM(d.value) AS total_revenue,
  COUNT(d.id) AS total_deals
FROM users u
JOIN deal_assignments da ON u.id = da.user_id
JOIN deals d ON da.deal_id = d.id
  AND d.status = 'closed_won'
GROUP BY u.id, u.name, u.email
ORDER BY total_revenue DESC
LIMIT 10;

Consulta 3 — Actividad reciente por lead (Problema N+1)

N+1 queries: 200+ ejecuciones por carga de página en tabla de 2.3M filas

220s total — CRÍTICO

ANTES — N+1 pattern

-- Se ejecuta 200+ veces por carga de página
-- 1.1s × 200 leads = ~220s de espera total
-- (en paralelo aún 8-12s de carga)
-- Sin índice en events(lead_id, occurred_at)

-- En el ORM (Node.js), por cada lead:
SELECT *
FROM events
WHERE lead_id = $1
ORDER BY occurred_at DESC
LIMIT 5;

-- Ejecutado para: lead_id 1, 2, 3 ... 200
-- = 200 round-trips a la DB

DESPUES — 1 query con LATERAL

-- 1 sola query para TODOS los leads
-- LATERAL JOIN = subquery correlacionada eficiente
-- Con índice (lead_id, occurred_at DESC)
-- ~95ms para 200 leads

SELECT l.id, l.name, l.email,
       e.type, e.occurred_at, e.metadata
FROM leads l
JOIN LATERAL (
  SELECT type, occurred_at, metadata
  FROM events
  WHERE lead_id = l.id
  ORDER BY occurred_at DESC
  LIMIT 5
) e ON TRUE
WHERE l.id = ANY($1)  -- array de 200 IDs
ORDER BY l.id, e.occurred_at DESC;

Plan de Índices — 6 creaciones

idx_leads_lower_email Funcional B-Tree

Permite búsquedas case-insensitive sin Seq Scan. Reduce leads/consulta 1 de 9.2s a ~85ms.

CREATE INDEX CONCURRENTLY idx_leads_lower_email ON leads(LOWER(email));

idx_deals_created_lead Compuesto B-Tree

Cubre el filtro de fecha + FK de join. Elimina Seq Scan en tabla deals (95k filas).

CREATE INDEX CONCURRENTLY idx_deals_created_lead ON deals(created_at DESC, lead_id);

idx_deals_status_value Parcial B-Tree

Solo indexa deals con status='closed_won'. Menor tamaño, mayor selectividad para consulta 2.

CREATE INDEX CONCURRENTLY idx_deals_status_value ON deals(value DESC) WHERE status = 'closed_won';

idx_deal_assignments_user_deal Compuesto B-Tree

Cubre ambas FK de deal_assignments (210k filas). Elimina Hash Join costoso en consulta 2.

CREATE INDEX CONCURRENTLY idx_deal_assignments_user_deal ON deal_assignments(user_id, deal_id);

idx_events_lead_occurred Compuesto — LATERAL key

Indispensable para el LATERAL JOIN. PostgreSQL puede hacer Index Scan + LIMIT sin tocar la tabla completa.

CREATE INDEX CONCURRENTLY idx_events_lead_occurred ON events(lead_id, occurred_at DESC);

idx_events_metadata_gin GIN JSONB

Para futuras búsquedas sobre events.metadata JSONB (@>, ?, ??| operators). Preparación para analytics avanzados.

CREATE INDEX CONCURRENTLY idx_events_metadata_gin ON events USING GIN(metadata);

Queries de Monitoring — pg_stat_statements

Top 10 consultas más lentas

SELECT
  LEFT(query, 80) AS query_preview,
  calls,
  ROUND(total_exec_time::NUMERIC, 2) AS total_ms,
  ROUND(mean_exec_time::NUMERIC, 2) AS mean_ms,
  ROUND(stddev_exec_time::NUMERIC, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

Tablas con más Seq Scans (índices faltantes)

SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  ROUND(
    seq_tup_read::FLOAT / NULLIF(seq_scan, 0)
  ) AS avg_rows_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 50
  AND schemaname = 'public'
ORDER BY seq_tup_read DESC
LIMIT 10;

Índices no utilizados (candidatos a eliminar)

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(
    pg_relation_size(indexrelid)
  ) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Mantenimiento periódico recomendado

-- Actualizar estadísticas tras bulk imports
ANALYZE VERBOSE events;
ANALYZE VERBOSE leads;
ANALYZE VERBOSE deals;

-- Vacuum + análisis (sin bloquear tabla)
VACUUM ANALYZE events;

-- Verificar uso de índices nuevos (24h post-deploy)
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename IN ('events', 'leads', 'deals')
ORDER BY idx_scan DESC;

Impacto estimado del plan

⚡

Reducción tiempo de carga

96%

10s → <400ms

🗄️

Carga en CPU de base de datos

-78%

Seq Scans eliminados

📡

Round-trips al servidor

200 → 3

N+1 resuelto con LATERAL

Plan de implementación

Día 1 — Crear índices (CONCURRENTLY)

6 CREATE INDEX CONCURRENTLY sin bloquear tablas en producción. Estimado: 15-30min en tabla events (2.3M filas). Verificar con pg_stat_progress_create_index.

Día 2 — Deploy queries optimizadas

Reemplazar consultas 1, 2 y 3 en el ORM (Node.js). Para la consulta 3, pasar de N queries individuales a 1 LATERAL JOIN con array de IDs.

Día 3 — Validación y monitoring

Verificar tiempos de carga (<500ms), confirmar uso de índices en pg_stat_user_indexes, activar slow query log (log_min_duration_statement = 200ms), configurar alertas.

Informe de Optimización SQL — LeadFlow