Optimización de consultas SQL: de lentas a rápidas

Técnicas prácticas para detectar y arreglar consultas SQL lentas con planes EXPLAIN, estrategias de indexación y reestructuración de consultas.

Publicado el 17 de diciembre de 2020Actualizado el 9 de septiembre de 20265 min de lectura

sql database performance postgresql optimization

Toda aplicación lenta tiene una consulta lenta escondida en algún lugar. El código de la aplicación puede estar bien optimizado, el servidor puede tener recursos de sobra, pero una sola consulta SQL mal escrita que hace un escaneo completo de una tabla con un millón de filas puede convertirse en el cuello de botella de todo el sistema. La consulta funcionaba bien con 1000 filas en desarrollo. Se desploma a escala de producción.

Encontrar y corregir estas consultas es una de las habilidades de mayor impacto que puede tener un ingeniero backend. El patrón es casi siempre el mismo: leer el plan de EXPLAIN, agregar el índice correcto y reestructurar la consulta.

Cómo leer los planes de EXPLAIN

EXPLAIN ANALYZE es la herramienta más importante para el rendimiento de SQL. Muestra cómo la base de datos ejecuta realmente tu consulta, no cómo crees que la ejecuta.

sqlsql

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2020-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

Esto es lo que hay que buscar en la salida:

Seq Scan — Escaneo completo de la tabla. Aceptable en tablas pequeñas, desastroso en las grandes.
Index Scan / Index Only Scan — Uso de un índice. Esto es lo que se busca.
Nested Loop — Une filas iterando una por una. Eficiente para conjuntos de resultados pequeños.
Hash Join — Construye una tabla hash para el join. Mejor para conjuntos de datos grandes.
Sort — Paso de ordenamiento explícito. Puede volcarse a disco si work_mem es demasiado pequeño.
Actual time — Tiempo de ejecución real por nodo. El número más alto es tu cuello de botella.

sqlsql

-- ❌ Seq Scan on a million-row table — every row is read
-- Seq Scan on orders (cost=0.00..35421.00 rows=1000000)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 990000
-- Planning Time: 0.2ms
-- Execution Time: 2340ms
 
-- ✅ After adding an index — only relevant rows are read
-- Index Scan using idx_orders_status on orders (cost=0.42..825.00 rows=10000)
--   Index Cond: (status = 'pending')
-- Planning Time: 0.3ms
-- Execution Time: 12ms

Estrategias de indexación

Un índice es una estructura de datos ordenada que permite a la base de datos encontrar filas sin escanear toda la tabla. El índice correcto convierte una consulta de 2 segundos en una de 10 ms. El índice equivocado desperdicia espacio en disco y ralentiza las escrituras.

sqlsql

-- Single column index — for exact match and range queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at);
 
-- Composite index — column order matters
-- This index serves: WHERE status = X AND created_at > Y
-- It does NOT efficiently serve: WHERE created_at > Y (alone)
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);
 
-- Partial index — indexes only rows matching a condition
-- Much smaller than a full index when most data is filtered out
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Reglas para elegir índices

sqlsql

-- ❌ Index on a low-cardinality column — not useful
-- A boolean column has 2 values. The index doesn't help.
CREATE INDEX idx_users_active ON users (is_active);
 
-- ✅ Partial index on the rare case — small and effective
CREATE INDEX idx_users_inactive
  ON users (email)
  WHERE is_active = false;
-- Only indexes the 2% of users who are inactive

El orden de las columnas en un índice compuesto sigue la regla «igualdad primero, rango después»:

sqlsql

-- Query: WHERE status = 'shipped' AND created_at > '2020-06-01'
-- ❌ Range column first — index partially used
CREATE INDEX idx_wrong ON orders (created_at, status);
 
-- ✅ Equality column first — index fully utilized
CREATE INDEX idx_right ON orders (status, created_at);

Reestructuración de consultas

A veces la solución no es un índice sino una consulta diferente. Los patrones habituales que causan problemas de rendimiento tienen alternativas bien conocidas.

Evitar SELECT *

sqlsql

-- ❌ Fetches all 30 columns — most are unused
SELECT * FROM users WHERE department = 'engineering';
 
-- ✅ Only fetch what you need — enables index-only scans
SELECT id, name, email FROM users WHERE department = 'engineering';

Cuando la consulta solo necesita columnas presentes en el índice, PostgreSQL puede resolverla por completo a partir del índice sin tocar la tabla: un «index-only scan».

Subconsulta vs. JOIN

sqlsql

-- ❌ Correlated subquery — runs once per row in the outer query
SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
 
-- ✅ JOIN with aggregation — single pass
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

EXISTS vs. IN para conjuntos grandes

sqlsql

-- ❌ IN with subquery — materializes entire result set
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
 
-- ✅ EXISTS — stops at first match per row
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

Detección del problema N+1

El problema N+1 es el error de rendimiento más común en aplicaciones que usan ORMs. Una consulta carga N filas padre y luego N consultas separadas cargan cada hijo.

tstypescript

// ❌ N+1 — 1 query for users + N queries for orders
const users = await db.query('SELECT * FROM users LIMIT 100');
for (const user of users) {
  user.orders = await db.query(
    'SELECT * FROM orders WHERE user_id = $1',
    [user.id]
  );
}
// Total: 101 queries
 
// ✅ Single query with JOIN
const usersWithOrders = await db.query(`
  SELECT u.*, json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id
  LIMIT 100
`);
// Total: 1 query

En los ORMs, usa carga anticipada (eager loading) para evitar el N+1:

tstypescript

// ❌ Prisma — lazy loading triggers N+1
const users = await prisma.user.findMany();
// Accessing users[0].orders triggers another query
 
// ✅ Prisma — include prevents N+1
const users = await prisma.user.findMany({
  include: { orders: true },
});

Paginación bien hecha

La paginación basada en offset se degrada de forma lineal. La página 1000 escanea y descarta 999 páginas de resultados.

sqlsql

-- ❌ Offset pagination — gets slower with higher pages
-- Page 1000 scans 100,000 rows to return 100
SELECT * FROM orders ORDER BY created_at DESC
OFFSET 99900 LIMIT 100;
 
-- ✅ Cursor-based pagination — constant performance
-- Uses the last seen value as the starting point
SELECT * FROM orders
WHERE created_at < '2020-12-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 100;

La paginación basada en cursor requiere una clave de ordenamiento indexada y única. Para columnas no únicas, combínala con la clave primaria:

sqlsql

-- Cursor for non-unique sort column
SELECT * FROM orders
WHERE (created_at, id) < ('2020-12-15T10:30:00Z', 'abc-123')
ORDER BY created_at DESC, id DESC
LIMIT 100;

Monitoreo de consultas lentas

No esperes a que los usuarios reporten lentitud. Monitorea el rendimiento de las consultas de forma proactiva.

sqlsql

-- PostgreSQL: enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '200';
-- Logs every query taking longer than 200ms
 
-- Find the heaviest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

La extensión pg_stat_statements es fundamental. Registra estadísticas de ejecución de cada patrón de consulta, de modo que puedas encontrar las consultas que consumen más tiempo total de base de datos, incluso si cada ejecución individual es rápida.

Conclusiones clave

Ejecuta primero EXPLAIN ANALYZE — nunca adivines por qué una consulta es lenta cuando la base de datos puede decírtelo
Indexa las columnas de igualdad antes que las de rango — el orden de las columnas en un índice compuesto afecta directamente su utilidad
Los índices parciales ahorran espacio y mejoran el rendimiento — indexa solo las filas que realmente consultas
Elimina las consultas N+1 — usa JOINs o carga anticipada en lugar de subconsultas por fila
Usa paginación basada en cursor — la paginación por offset se degrada a escala, los cursores se mantienen constantes
Monitorea con pg_stat_statements — encuentra las consultas que consumen más tiempo total antes de que se conviertan en un problema

Optimización de consultas SQL: de lentas a rápidas

Cómo leer los planes de EXPLAIN

Estrategias de indexación

Reglas para elegir índices

Reestructuración de consultas

Evitar SELECT *

Subconsulta vs. JOIN

EXISTS vs. IN para conjuntos grandes

Detección del problema N+1

Paginación bien hecha

Monitoreo de consultas lentas

Conclusiones clave

Publicaciones relacionadas

Errores de diseño de bases de datos y cómo corregirlos

Materialized Views en Postgres: cambiar actualidad por velocidad

Soft Deletes: la función que cuesta más de lo que ahorra