Cómo redujimos el tiempo de ejecución de una consulta de base de datos de 10 segundos a 50 ms

El problema: un panel de control que tardaba 10 segundos en cargarse

El panel de análisis de un cliente tardaba más de 10 segundos en cargarse. Los usuarios lo abandonaban. El director técnico estaba entrando en pánico. El equipo de backend decía: «Funciona bien en el entorno local». ¿Te suena?

Así es exactamente cómo localizamos el problema, lo solucionamos y redujimos el tiempo de respuesta a 50 ms. No hay ningún truco: solo una depuración sistemática.

Paso 1: Identificar la consulta lenta

Antes de hacer conjeturas, activamos el registro de consultas lentas de PostgreSQL para detectar cualquier consulta que durara más de 500 ms:

-- In postgresql.conf
log_min_duration_statement = 500

-- Or for a single session:
SET log_min_duration_statement = 500;

El culpable quedó claro de inmediato: una sola consulta que unía cuatro tablas tardaba 10,2 segundos:

SELECT o.id, o.total, c.name, c.email,
       COUNT(oi.id) as item_count,
       SUM(oi.quantity * oi.unit_price) as calculated_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= '2025-01-01'
  AND o.status IN ('completed', 'shipped')
  AND c.region = 'asia-pacific'
GROUP BY o.id, o.total, c.name, c.email
ORDER BY o.created_at DESC
LIMIT 50;

Paso 2: EXPLAIN ANALYZE — Descubre qué hace realmente PostgreSQL

Al ejecutar EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) se puso de manifiesto el verdadero problema:

Seq Scan on orders o  (cost=0.00..185432.00 rows=892145 width=48)
  Filter: (created_at >= '2025-01-01' AND status = ANY(...))
  Rows Removed by Filter: 3241876
  Buffers: shared hit=12 read=142891

Escaneo secuencial de 4 millones de filas. PostgreSQL estaba leyendo toda la tabla «orders» desde el disco porque no había ningún índice en «created_at» ni en «status». El valor de «Buffers: read=142891» confirmaba una E/S de disco masiva.

Paso 3: La solución: índice compuesto + índice parcial

Un índice de una sola columna sobre «created_at» sería útil, pero un índice compuesto diseñado específicamente para este patrón de consulta concreto es mucho mejor:

-- Composite index matching the WHERE clause
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Index on the foreign key join (often missed!)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- covering index

La cláusula «WHERE status IN (...)» del índice lo convierte en un índice parcial: solo indexa las filas que realmente consultamos, lo que lo mantiene pequeño y rápido. La cláusula «INCLUDE» del segundo índice lo convierte en un índice de cobertura: PostgreSQL puede responder a la consulta solo a partir del índice, sin necesidad de acceder a la tabla.

Paso 4: Reestructuración de consultas con CTE

La consulta original calculaba los totales sobre todo el conjunto de resultados antes de aplicar LIMIT. La hemos reestructurado para filtrar primero y luego calcular los totales:

WITH recent_orders AS (
  SELECT o.id, o.total, o.customer_id, o.created_at
  FROM orders o
  WHERE o.created_at >= '2025-01-01'
    AND o.status IN ('completed', 'shipped')
  ORDER BY o.created_at DESC
  LIMIT 50
)
SELECT ro.id, ro.total, c.name, c.email,
       COUNT(oi.id) as item_count,
       SUM(oi.quantity * oi.unit_price) as calculated_total
FROM recent_orders ro
JOIN customers c ON c.id = ro.customer_id
JOIN order_items oi ON oi.order_id = ro.id
GROUP BY ro.id, ro.total, c.name, c.email, ro.created_at
ORDER BY ro.created_at DESC;

Esto recupera primero solo 50 registros y, a continuación, realiza la unión. En lugar de realizar la agregación sobre millones de filas, estamos realizando la unión sobre 50.

El resultado

Sistema métrico	Antes	Después de
Tiempo de consulta	10 200 ms	47 ms
Filas escaneadas	4 134 021	50
Lecturas del disco	142 891 búferes	12 amortiguadores
Índice utilizado	Ninguno (búsqueda secuencial)	idx_status_pedidos_creados

Lecciones aprendidas

1. Comprueba siempre EXPLAIN ANALYZE en el entorno de producción, no solo en el local. Las bases de datos locales tienen cachés activas y conjuntos de datos pequeños.

2. Las columnas de clave externa necesitan índices. Django y Rails los crean automáticamente, pero en los esquemas SQL sin procesar suelen faltar.

3. La cláusula LIMIT no evita los escaneos completos de la tabla cuando se combina con GROUP BY. Reestructura la consulta para aplicar primero el filtro.

4. Utiliza la opción «CONCURRENTLY» al crear índices en el entorno de producción para evitar el bloqueo de la tabla.

La consulta más costosa es aquella que nadie ha analizado. Incorpora EXPLAIN ANALYZE a tu proceso de revisión de código para cualquier consulta que afecte a más de 10 000 filas.
— alokknight Ingeniería