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:

-- En PostgreSQL: 
log_min_duration_statement = 500 

-- O para una sola sesión: 
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.nombre, c.correo_electrónico,      
 COUNT(oi.id) AS número_de_artículos,      
 SUM(oi.cantidad * oi.unit_price) como 
calculated_totalFROM pedidos 
oJOIN clientes c ON c.id = o.
customer_idJOIN artículos_pedido oi ON oi.order_id = o.
idJOIN productos p ON p.id = oi.
product_idWHERE o.created_at >= '2025-01-01'
  AND o.status IN ('completado', 'enviado')
  AND c.region = 
'asia-pacific'GROUP BY o.id, o.total, c.name, c.
emailORDER BY o.created_at 
DESCLIMIT 50;

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

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

Escaneo secuencial en pedidos o  (coste=0,00..185 432,00 filas=892 145 ancho=48)
  Filtro: (created_at >= '2025-01-01' Y status = CUALQUIERA(...))
  Filas eliminadas por el filtro: 3241876
  Búferes: coincidencias compartidas=12 lecturas=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:

-- Índice compuesto que coincide con la 
cláusula WHERE CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Índice en la unión de clave externa (¡a menudo se pasa por alto!)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- índice global

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) como 
calculated_totalFROM recent_orders 
roJOIN customers c ON c.id = ro.
customer_idJOIN order_items oi ON oi.order_id = ro.
idGROUP BY ro.id, ro.total, c.name, c.email, ro.
created_atORDER 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