Checklist de Optimización de Consultas SQL

Overview

Cuando una consulta va lenta, la tentación es empezar a cambiar cosas al azar — un índice aquí, reescribir un join allá — y cruzar los dedos. Este checklist es lo contrario: un recorrido de arriba abajo por los sospechosos habituales, en el orden en que conviene revisarlos. Empieza por el plan (sección 1), porque una consulta que no has perfilado es una consulta sobre la que estás adivinando. Después índices, patrones N+1, joins, paginación, y al final la infraestructura — pooling y caché — antes de culpar a la base de datos.

Cada sección te dice qué ejecutar, cómo se ve un mal resultado y cuál suele ser la solución. Los ejemplos cubren PostgreSQL y MySQL; los conceptos se transfieren a cualquier motor relacional. Ten a mano la guía de Optimización de Consultas SQL para la teoría de fondo — este archivo es la hoja de triage.

1. EXPLAIN Plan Analysis

Empieza siempre aquí. EXPLAIN muestra la estrategia del planificador; EXPLAIN ANALYZE muestra lo que realmente pasó. La diferencia importa — las estimaciones de filas pueden desviarse órdenes de magnitud cuando las estadísticas de la tabla están viejas.

1.1 PostgreSQL EXPLAIN

-- EXPLAIN básico (plan estimado, no ejecuta la consulta)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN con estadísticas reales de ejecución (sí la ejecuta)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;

-- Timing detallado + salida legible por máquina
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

1.2 MySQL EXPLAIN

-- EXPLAIN básico
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- SHOW WARNINGS revela la consulta reescrita que MySQL ejecutó de verdad
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
SHOW WARNINGS;

1.3 Red Flags en el Plan

Estos tipos de escaneo casi siempre significan “te falta un índice” — compruébalo contra los conteos de filas reales antes de lanzar el CREATE INDEX:

Tipo de scan	Significado	Solución
Seq Scan (Postgres)	Escaneo completo de la tabla, sin índice	Índice en la columna del filtro
ALL (MySQL)	Escaneo completo de la tabla	Índice en la columna del filtro
Filesort	Paso extra de ordenación tras recuperar	Índice que coincida con el ORDER BY
Temporary	Tabla temporal materializada	Reescribe el GROUP BY o indexa
Nested Loop en tablas grandes	Join O(n·m)	Índice en la columna del join
Filas examinadas ≫ devueltas	Baja selectividad	Índice mejor/compuesto

1.4 Métricas que Vigilar

Métrica (PostgreSQL)	Bien	Ojo	Mal
Seq Scan rows	< 100	100–10.000	> 10.000
Sort rows	< 1.000	1.000–50.000	> 50.000
Hash Build rows	< 10.000	10.000–100.000	> 100.000
Buffers shared hit	> 90 %	50–90 %	< 50 %
Execution time	< 10ms	10–100ms	> 100ms

Si el plan se ve bien pero las estimaciones de filas de ANALYZE están muy lejos, las estadísticas están viejas — ejecuta ANALYZE orders; (y mantén sano el autovacuum) antes de añadir índices para compensar un planificador que trabaja con datos malos.

2. Index Strategy

Los índices son la solución a la mayoría de lecturas lentas — y un impuesto sobre cada escritura. El checklist de abajo cubre el ciclo de auditoría: verifica qué se usa, añade lo que falta, quita lo que es peso muerto.

2.1 Index Audit Checklist

- [ ] Revisa los índices existentes en la tabla
  - PostgreSQL: \d orders o SELECT * FROM pg_indexes WHERE tablename = 'orders';
  - MySQL: SHOW INDEX FROM orders;

- [ ] Verifica que la consulta realmente usa un índice
  - Ejecuta EXPLAIN — busca Index Scan, no Seq Scan
  - Índice en columnas del WHERE
  - Índice en columnas del JOIN
  - Índice en columnas del ORDER BY

- [ ] Añade los índices que falten (ejemplos en 2.2)
  - De una columna para igualdad simple
  - Compuesto para filtros multi-columna
  - Índice parcial para un subconjunto habitual
  - Covering index para evitar lecturas de la tabla

- [ ] Elimina índices sin uso
  - PostgreSQL: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
  - MySQL: SELECT * FROM sys.schema_unused_indexes;
  - Cada índice sin uso sigue costando escrituras y espacio

- [ ] Revisa la selectividad del índice
  - Alta selectividad (muchos valores distintos): buen candidato
  - Baja selectividad (booleano, enum de estado): normalmente saltar
  - SELECT COUNT(DISTINCT col)::float / COUNT(*) FROM orders;
  - Selectividad > 0.3 es un candidato decente

2.2 Index Creation Examples

-- Índice compuesto: columnas de igualdad primero, rango al final — el orden importa
CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, created_at);

-- Índice parcial PostgreSQL: solo las filas que filtrarás de verdad
CREATE INDEX idx_orders_active_customer
  ON orders(customer_id)
  WHERE status = 'active';

-- Covering index PostgreSQL: las columnas INCLUDE se almacenan, no se buscan
CREATE INDEX idx_orders_customer_cover
  ON orders(customer_id)
  INCLUDE (total, status, created_at);

-- Índice compuesto MySQL
CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, created_at);

-- Índice funcional MySQL (8.0+)
CREATE INDEX idx_orders_lower_email
  ON orders((LOWER(email)));

-- Índice de expresión PostgreSQL
CREATE INDEX idx_orders_lower_email
  ON orders(LOWER(email));

Y no indexar las FKs a ciegas: PostgreSQL no indexa automáticamente el lado referenciador de una clave foránea, una causa clásica de joins lentos y deletes en cascada eternos.

3. N+1 Query Detection

El patrón N+1 es silencioso en un test de consulta única — solo aparece como cientos de roundtrips con datos reales. Una consulta trae una lista; el código dispara luego una consulta por fila para el dato relacionado.

3.1 El Patrón

Problema:
  1 consulta para traer 100 pedidos
  100 consultas para traer el nombre de cada cliente
  Total: 101 consultas

Solución:
  1 consulta con JOIN, o 2 consultas con IN (...) por lotes

3.2 Detección

-- PostgreSQL: registra cada consulta durante una ventana controlada
ALTER SYSTEM SET log_min_duration_statement = 0;
SELECT pg_reload_conf();

-- MySQL: captura todo brevemente
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0;

-- En el log, busca la misma forma de consulta repetida
-- con parámetros distintos — ese es tu N+1

No dejes long_query_time = 0 encendido — inundar el log de un servidor de producción es una buena forma de crear la avería que intentabas depurar. Restáuralo tras la prueba.

3.3 Cómo Arreglarlo

-- Antes: 1 consulta + N búsquedas (generadas por el ORM)
-- Query 1: SELECT * FROM orders WHERE status = 'active';
-- Query 2..101: SELECT * FROM customers WHERE id = ?;

-- Después: un solo JOIN
SELECT
  o.id,
  o.total,
  o.created_at,
  c.name AS customer_name,
  c.email AS customer_email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active';

-- O por lotes cuando el JOIN no encaja (p. ej. servicios distintos)
SELECT id, customer_id, total FROM orders WHERE status = 'active';
SELECT id, name, email FROM customers WHERE id IN (1, 2, 3, 4, 5, ...100);

3.4 ORM-Specific Fixes

# Django: select_related (JOIN) para FK, prefetch_related para M2M/reverso
# Antes (N+1):
orders = Order.objects.filter(status='active')
for order in orders:
    print(order.customer.name)  # una consulta extra por pedido

# Después (un solo JOIN):
orders = Order.objects.filter(status='active').select_related('customer')
for order in orders:
    print(order.customer.name)  # sin consultas extra

# Después (por lotes con prefetch_related para FK reverso):
customers = Customer.objects.prefetch_related('order_set').all()
for customer in customers:
    for order in customer.order_set.all():  # sin consultas extra
        print(order.total)

# Rails: includes / preload / eager_load
# Antes (N+1):
orders = Order.where(status: 'active')
orders.each { |o| puts o.customer.name }

# Después:
orders = Order.includes(:customer).where(status: 'active')
orders.each { |o| puts o.customer.name }

4. Join Optimization

Los joins son donde las buenas consultas van a morir — normalmente por una columna de join sin indexar o un filtro aplicado demasiado tarde. Revisa esto antes de reescribir la consulta entera.

4.1 Join Checklist

- [ ] Verifica que las columnas del join están indexadas en ambos lados
  - PostgreSQL no indexa las FKs automáticamente — revisa \d child_table

- [ ] Usa el tipo de join correcto
  - INNER JOIN: solo filas coincidentes (default, el más rápido)
  - LEFT JOIN: todas las de la izquierda + coincidencias — caro con tablas grandes a la derecha
  - CROSS JOIN: producto cartesiano — nunca por accidente

- [ ] Filtra pronto
  - El WHERE debe reducir filas antes del join
  - Lleva los filtros a subqueries o CTEs por claridad

- [ ] No selecciones lo que no necesitas
  - SELECT solo las columnas necesarias, no SELECT *
  - Los covering indexes evitan lecturas extra de la tabla

- [ ] Revisa el orden del join
  - El filtro más selectivo se aplica primero
  - El optimizador suele acertar — fuerza el orden solo con evidencia

4.2 Examples

-- Antes: une todas las filas de customers antes de filtrar
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'active' AND o.created_at > '2026-01-01';

-- Después: filtra orders primero, luego une el resultado pequeño
SELECT c.name, active_orders.total
FROM customers c
INNER JOIN (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'active' AND created_at > '2026-01-01'
) active_orders ON c.id = active_orders.customer_id;

-- Lo mismo, más legible como CTE
WITH active_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'active' AND created_at > '2026-01-01'
)
SELECT c.name, ao.total
FROM customers c
INNER JOIN active_orders ao ON c.id = ao.customer_id;

5. Pagination Patterns

OFFSET parece gratis hasta la página 500 — cada página lee y descarta todas las filas anteriores. La paginación keyset (cursor) lee solo lo que devuelve.

5.1 Avoid OFFSET

-- Problema: escanea 10.020 filas, devuelve 20 — 10.000 desperdiciadas
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Keyset: arranca donde terminó la última página, lee exactamente 20 filas
SELECT * FROM orders
WHERE created_at < '2026-06-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

5.2 Keyset Implementation

-- Página 1
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Página 2: el (created_at, id) de la última fila se convierte en el cursor
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-06-15 10:30:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- El índice debe coincidir exactamente con el orden de la ordenación
CREATE INDEX idx_orders_created_id
  ON orders(created_at DESC, id DESC);

La contrapartida: keyset renuncia a los saltos aleatorios de página (no hay “ir a la página 50”). Si la UI realmente necesita páginas numeradas, precomputa los límites o cachea el resultado — no hagas pagar a la base de datos el OFFSET en cada petición.

6. Connection Pooling

Las consultas lentas no siempre son consultas lentas — a veces son consultas rápidas esperando 30s por una conexión libre. Revisa el agotamiento del pool antes de culpar al planificador.

6.1 Pool Configuration

Parámetro	Recomendado	Por qué
Pool size	~(core × 2) + spindles	Más no es más rápido — regla clásica de dimensionado de PostgreSQL
Min idle	= pool size	Mantén conexiones calientes
Max lifetime	30 min	Evita conexiones viejas/zombis
Connect timeout	30s	Falla rápido ante agotamiento
Idle timeout	10 min	Cierra conexiones realmente ociosas
Validation query	SELECT 1	Chequeo de salud antes de entregar

6.2 PostgreSQL: PgBouncer

# pgBouncer — transaction pooling para apps que abren/cierran seguido
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
min_pool_size = 5
server_idle_timeout = 600
server_lifetime = 3600

pool_mode = transaction es el default correcto para la mayoría de apps — pero recuerda que rompe funciones a nivel de sesión (prepared statements entre consultas, SET de sesión). Usa modo session si dependes de eso.

6.3 Application-Level Pooling

# Configuración de pool en SQLAlchemy
engine = create_engine(
    'postgresql://user:pass@localhost/mydb',
    pool_size=20,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800,   # recicla antes de que el servidor mate la conexión
    pool_pre_ping=True,  # descarta conexiones muertas de forma transparente
)

7. Query Caching

Cuando la consulta ya es rápida, la ejecución más barata es la que nunca ocurre — cachea resultados antes de volver a tocar el plan.

7.1 Cache Layers

Capa	TTL	Cuándo usarla
Aplicación	5–60s	Lecturas calientes que toleran desfase
Redis/Memcached	1–60min	Compartido entre instancias
Base de datos	Sesión/refresh	Vistas materializadas, result cache
CDN	1–24h	Respuestas de API, datos semiestáticos

7.2 PostgreSQL Materialized Views

-- Agregación cara, calculada una sola vez
CREATE MATERIALIZED VIEW order_summary AS
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(total) AS total_spent,
  MAX(created_at) AS last_order_date
FROM orders
GROUP BY customer_id;

-- Indexala como una tabla
CREATE INDEX idx_order_summary_customer
  ON order_summary(customer_id);

-- REFRESH bloquea las lecturas...
REFRESH MATERIALIZED VIEW order_summary;

-- ...salvo que refresques CONCURRENTLY (necesita un índice único)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

8. Slow Query Log Triage

Los índices arreglan una consulta; el slow log encuentra las consultas que vale la pena arreglar. Actívalo, ordena por tiempo total (no por tiempo de una ejecución) y trabaja la lista desde arriba.

8.1 Activar el Log

-- PostgreSQL: registra todo lo que pase de 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

-- MySQL: la misma idea
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

8.2 Ordenar los Culpables (pg_stat_statements)

-- Instala una vez por base de datos
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top consultas por tiempo total — el coste real, no la ejecución más lenta
SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 0) AS total_ms,
  left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Prioriza total_ms: una consulta de 50ms llamada 2.000 veces por minuto cuesta más que un informe de 5s que corre de noche. El equivalente en MySQL es performance_schema.events_statements_summary_by_digest.

See Also

• Guía Completa de Optimización de Consultas SQL
• Indexación en Bases de Datos — Crear Índices Eficaces
• Read Replicas: Escalar Lecturas sin Cambiar la Aplicación
• Guía Completa de PostgreSQL Tuning
• Documentación de PostgreSQL EXPLAIN
• Documentación de pg_stat_statements