El problema con las queries lentas

La mayoría de los problemas de rendimiento en aplicaciones web tienen un denominador común: la base de datos. Una query que funciona perfectamente con 1.000 filas puede convertirse en un cuello de botella al llegar a 100.000. Saber diagnosticar lo que PostgreSQL está haciendo internamente es una habilidad fundamental para cualquier desarrollador backend.

Esta guía cubre las herramientas y técnicas más efectivas para encontrar y resolver queries lentas.

EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra el plan de ejecución de una query. EXPLAIN ANALYZE ejecuta la query de verdad y muestra tanto el plan como los tiempos reales de ejecución.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

Un resultado típico se ve así:

Sort  (cost=1200.45..1201.10 rows=260 width=120) (actual time=18.342..18.390 rows=260 loops=1)
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 64kB
  ->  Seq Scan on orders  (cost=0.00..1189.00 rows=260 width=120) (actual time=0.041..17.801 rows=260 loops=1)
        Filter: (customer_id = 42)
        Rows Removed by Filter: 48740
Planning Time: 0.210 ms
Execution Time: 18.501 ms

El punto clave aquí es Seq Scan: PostgreSQL está leyendo cada fila de la tabla y descartando las que no coinciden. Eso es exactamente lo que queremos evitar en tablas grandes.

Seq Scan vs Index Scan

Tipo de escaneoCuándo ocurreRendimiento
Seq ScanSin índice utilizable o el planificador cree que es más baratoLento en tablas grandes
Index ScanHay un índice que cubre el filtro u ordenamientoRápido para queries selectivas
Bitmap Index ScanMúltiples condiciones, selectividad moderadaBueno para rangos
Index Only ScanTodas las columnas necesarias están en el índiceMuy rápido

Cuando ves Seq Scan en una tabla grande, la primera pregunta es: ¿hay un índice en la columna del WHERE?

-- Crear un índice en customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Volver a ejecutar EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

Después de agregar el índice, deberías ver Index Scan y una caída drástica en el tiempo de ejecución.

pg_stat_statements

EXPLAIN ANALYZE es útil cuando ya sabes cuál query es lenta. pg_stat_statements te ayuda a encontrar las queries lentas desde el principio.

Activar la extensión:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

También necesitas agregarlo en postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Luego consultar la vista:

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS media_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Esto te da las 10 queries con mayor tiempo acumulado de ejecución. Es la forma más rápida de identificar cuáles están generando más carga en producción.

Consejos prácticos de indexación

Los índices compuestos respetan el orden de las columnas. Un índice en (customer_id, created_at) sirve para queries que filtran solo por customer_id, o por ambas columnas juntas. No ayuda si filtras únicamente por created_at.

-- Útil para: WHERE customer_id = ? AND created_at > ?
-- También útil para: WHERE customer_id = ?
-- No útil para: WHERE created_at > ?
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

Los índices parciales reducen el tamaño del índice y el costo de mantenimiento cuando solo consultas un subconjunto de filas:

-- Indexar solo usuarios activos
CREATE INDEX idx_users_active_email ON users(email) WHERE active = true;

Los índices de expresión permiten indexar valores calculados:

-- Si frecuentemente consultas WHERE lower(email) = ?
CREATE INDEX idx_users_lower_email ON users(lower(email));

Ejemplos en Ruby on Rails

Diagnosticar N+1 queries

El problema clásico de Rails: cargar una lista de posts y luego consultar la base de datos una vez por post para obtener el autor.

# N+1 — ejecuta 1 + N queries
posts = Post.all
posts.each { |post| puts post.author.name }

# Solución con includes — ejecuta 2 queries
posts = Post.includes(:author).all
posts.each { |post| puts post.author.name }

Usa la gema bullet en desarrollo para detectar automáticamente las N+1 queries.

Usar pluck para evitar cargar objetos completos

# Carga objetos ActiveRecord completos — lento y costoso en memoria
user_ids = User.where(active: true).map(&:id)

# Usa pluck — devuelve un array simple, mucho más rápido
user_ids = User.where(active: true).pluck(:id)

Ejecutar EXPLAIN directamente desde Rails

Desde Rails 7.1, el método .explain acepta opciones directamente, sin necesidad de gems extras:

# EXPLAIN básico
User.where(active: true).explain
# => EXPLAIN SELECT "users".* FROM "users" WHERE "users"."active" = true

# EXPLAIN ANALYZE con opciones (Rails 7.1+)
User.where(active: true).explain(:analyze, :verbose, :buffers)
# => EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT ...

Esto muestra el plan de ejecución real con tiempos, buffers y detalles — directo en la consola de Rails, sin SQL manual.

Herramientas útiles

pgBadger analiza los archivos de log de PostgreSQL y genera reportes HTML detallados con queries lentas, frecuencia y tiempos de espera por locks. Es invaluable para investigar incidentes en producción.

pgbadger /var/log/postgresql/postgresql.log -o reporte.html

auto_explain es un módulo de PostgreSQL que registra automáticamente los planes de ejecución de queries lentas. Agrega esto a postgresql.conf:

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000  -- registrar queries más lentas de 1 segundo
auto_explain.log_analyze = true

Esto te da los planes de ejecución en los logs sin modificar el código de la aplicación — muy útil en staging o producción cuando no puedes ejecutar EXPLAIN ANALYZE de forma interactiva.

Debugar queries en PostgreSQL no es magia — es proceso. Con EXPLAIN ANALYZE, pg_stat_statements y buenas prácticas de indexación, puedes identificar y resolver la mayoría de los problemas de rendimiento antes de que se conviertan en incidentes en producción.

Referencias