PostgreSQL: encuentra el índice correcto con EXPLAIN

1. Recorta el problema hasta una consulta

Una API lenta puede estar esperando conexiones, serializando demasiados datos o ejecutando cientos de consultas pequeñas. Antes de crear un índice, identifica la consulta que consume tiempo y el número de veces que se ejecuta. Conserva parámetros representativos sin copiar datos sensibles a los logs.

Imagina una lista de trabajos por workspace y estado, ordenados del más reciente al más antiguo. El patrón incluye filtro, orden y límite. Ese conjunto es el que debes optimizar. Probar únicamente un SELECT por identificador no dice nada sobre la pantalla que realmente está fallando. Reproduce el problema en el entorno de pruebas autorizado con una distribución de datos representativa.

2. Distingue estimaciones de ejecución real

EXPLAIN muestra el plan estimado. EXPLAIN ANALYZE ejecuta la sentencia y añade observaciones reales; úsalo con cuidado incluso en SELECT que invoquen funciones. En escrituras, una transacción con rollback no garantiza revertir efectos externos de funciones. Empieza con lecturas controladas y un presupuesto de tiempo.

-- En staging autorizado: $1 y $2 son parámetros del driver.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 50;

Compara filas estimadas y reales, repeticiones de cada nodo y buffers. El costo del plan no está expresado en milisegundos. Los tiempos de nodos superiores incluyen trabajo de sus hijos: sumarlos sin más cuenta trabajo dos veces. Una diferencia grande de cardinalidad puede señalar estadísticas insuficientes o una distribución sesgada.

Documentación: PostgreSQL: cómo interpretar EXPLAIN ↗

3. Haz que el índice responda al acceso

Para este patrón, un candidato es un B-tree que empiece por tenant_id y status, seguido de created_at e id. Las dos primeras columnas corresponden a igualdades y las siguientes al orden. Es una hipótesis de trabajo, no una receta para todas las consultas de la tabla. Verifica también las pantallas que filtran de otra manera.

-- Ejecutar fuera de un bloque de transacción.
CREATE INDEX CONCURRENTLY jobs_tenant_status_created_id_idx
ON jobs (tenant_id, status, created_at DESC, id DESC);

El índice ocupa espacio y añade trabajo a inserciones y cambios. No agregues todas las columnas como cobertura por reflejo. Compara el plan y el comportamiento de escritura antes y después. Un escaneo secuencial puede ser razonable si la consulta necesita gran parte de la tabla.

Documentación: PostgreSQL: índices multicolumna ↗ · PostgreSQL: CREATE INDEX y construcción concurrente ↗

4. Usa un orden estable al paginar

created_at puede repetirse. Añadir un identificador único como desempate permite describir una posición sin ambigüedad. En orden descendente, la página siguiente pide tuplas menores que la última recibida. Este ejemplo asume que created_at e id no admiten NULL y que mantienen su valor durante la navegación.

SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Valida el cursor y vincúlalo a filtros y orden. El tenant sigue viniendo de la sesión autorizada, nunca del cursor. Si se cambian fechas o filtros mientras navegas, el conjunto puede variar: esta técnica evita el costo del desplazamiento profundo, pero no crea una fotografía transaccional entre solicitudes.

5. Revisa el beneficio bajo carga

Una lectura aislada después de calentar caché no representa todas las solicitudes. Compara varias ejecuciones y percentiles del endpoint, con concurrencia razonable y tamaños de tenant distintos. Observa conexiones ocupadas, tiempos de bloqueo, CPU, lecturas físicas y volumen devuelto al cliente.

Registra la versión del esquema, el plan y las condiciones del ensayo. Si no puedes reproducir el volumen de forma segura, documenta esa limitación en la decisión. No conviertas una medición pequeña en una promesa de capacidad.

6. Deja una migración verificable

La entrega incluye revisar que el índice sea válido, que el plan relevante pueda usarlo y que no haya una regresión en escrituras. Define una forma de revertir con el equipo que opera la base. Evita combinar el cambio con otras migraciones difíciles de separar, porque perderías claridad al atribuir el resultado.

Vuelve a observar cuando crezcan los datos o cambie la consulta. El índice correcto hoy puede dejar de ser suficiente si el filtro más frecuente cambia. Un proveedor administrado facilita operaciones, pero no conoce el contrato de tu endpoint: esa parte del diseño sigue siendo responsabilidad de la aplicación.