¿Cómo se diagnostica y optimiza una consulta lenta en PostgreSQL?

Planteamiento y contexto aplicable

La tabla orders contiene 200 millones de filas y soporta 3000 inserciones o actualizaciones de estado por segundo. Alrededor del 2% de todos los pedidos son pending, aunque esa proporción varía sustancialmente entre inquilinos (tenants). Un panel de operaciones ejecuta la siguiente consulta 40 veces por segundo. Esta solicita los 50 pedidos pendientes más recientes para un inquilino en los últimos 30 días:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

La tabla ya cuenta con dos índices B-tree de una sola columna, orders(tenant_id) y orders(created_at). La monitorización de la aplicación muestra que el p95 de la consulta aumentó de 120 ms a 2,8 segundos, mientras que la CPU, la memoria y el recuento de conexiones de PostgreSQL se mantienen por debajo de su capacidad. Un plan capturado en una réplica a escala de producción estima 8000 filas, pero produce 420 000 filas antes de ordenar, accede a unos 120 000 búferes compartidos (shared buffers) y finalmente aplica una ordenación top-N para devolver 50 filas.

Esta pregunta apunta a PostgreSQL 18. El tamaño de la tabla, el rendimiento y las cifras del plan son supuestos de la entrevista utilizados para hacer que el razonamiento sea comprobable. El objetivo es mejorar esta lectura de alta frecuencia controlando al mismo tiempo el riesgo de compilación del índice, el almacenamiento y la amplificación de escritura. El particionamiento (sharding), el almacenamiento en caché y la ampliación de hardware quedan fuera de la primera pasada.

Qué evalúa el entrevistador

La primera señal es la confirmación de la carga de trabajo antes de realizar cambios de SQL. Una respuesta sólida separa la "ejecución individual más lenta" del "mayor costo acumulativo". Una consulta que promedia 80 ms con 1000 llamadas por segundo puede merecer atención antes que una consulta ocasional de cinco segundos. pg_stat_statements proporciona llamadas, tiempo total de ejecución y tiempo medio de ejecución. La monitorización o el rastreo de aplicaciones aún deben suministrar el p95 y los percentiles específicos de cada inquilino.

La segunda señal es leer un plan de ejecución como una cadena causal. La evidencia relevante incluye la brecha de cardinalidad de 8000 frente a 420 000, las filas emitidas por los nodos de escaneo, el loops de cada nodo, la actividad del búfer, el método de ordenación y si un predicado aparece en una condición de índice o en un Filter posterior al escaneo. Ver Seq Scan o ver que se utilizó un índice no determina si un plan es bueno.

La tercera señal es deducir el orden de las claves a partir de la forma de la consulta. tenant_id es un predicado de igualdad. created_at es tanto un predicado de rango como el orden solicitado. status='pending' es un estado de negocio fijo y poco frecuente. Un índice adecuado debe ingresar al rango pendiente de un inquilino, leer en orden de marca de tiempo y detenerse tan pronto como se encuentren 50 filas.

Finalmente, el entrevistador busca disciplina de validación y despliegue. Un índice consume almacenamiento e I/O de compilación, al tiempo que añade trabajo a las inserciones y transiciones de estado. Una respuesta completa compara planes en datos a escala de producción, cachés frías y calientes, diferentes tamaños de inquilinos, escrituras concurrentes y umbrales explícitos de reversión (rollback). Una sola ejecución local más rápida no es evidencia suficiente.

Preguntas para aclarar antes de responder

• ¿Qué métrica de latencia sufrió una regresión? El p95 por inquilino, el p95 global, la latencia media y el tiempo total de base de datos implican diferentes prioridades. Establezca cuándo comenzó la regresión y si coincide con el crecimiento de datos, la distribución de parámetros, un despliegue o cambios en las estadísticas.
• ¿Cuál es la tasa de pedidos pendientes y la distribución de inquilinos? Un índice parcial resulta atractivo si pending se mantiene entre el 1% y el 2% de las filas. Su ventaja de tamaño se reduce si la mitad de la tabla está pendiente. Un promedio también oculta el sesgo entre inquilinos muy grandes y pequeños.
• ¿La consulta contiene siempre el literal status='pending'? Un índice parcial solo se puede utilizar cuando el planificador puede demostrar que la condición de la consulta implica el predicado del índice. Un parámetro de estado genérico puede impedir esa demostración.
• ¿Qué columnas y garantías de consistencia se requieren? Devolver textos grandes, JSON o diez tablas unidas inflaría rápidamente un índice cubridor (covering index). Confirme las columnas que este endpoint de lista realmente necesita.
• ¿Qué tan intensiva en escritura es la tabla y qué tipo de despliegue se permite? Con 3000 escrituras por segundo, se deben medir el ancho del índice y el costo de transición. La producción puede requerir CREATE INDEX CONCURRENTLY, junto con una ventana de compilación más larga, escaneos adicionales y un procedimiento para limpiar un índice no válido tras un fallo.
• ¿Puede ejecutarse el plan real en una réplica a escala de producción? EXPLAIN ANALYZE ejecuta la sentencia. Incluso un SELECT puede generar una carga sustancial, mientras que las sentencias que modifican datos ejecutan sus efectos secundarios. Use una réplica, parámetros acotados o primero un EXPLAIN simple.

Estructura de respuesta en 30 segundos

«Primero correlacionaría el p95 de la aplicación con las llamadas de pg_stat_statements, el tiempo total de base de datos y los inquilinos lentos, descartando al mismo tiempo esperas de bloqueos y dependencias externas. Luego ejecutaría EXPLAIN (ANALYZE, BUFFERS) con parámetros representativos en una réplica a escala de producción e inspeccionaría las filas estimadas frente a las reales, los bucles, los búferes y el nodo de ordenación. Aquí, los dos índices de una sola columna todavía producen 420 000 candidatos antes de ordenar. Debido a que la consulta siempre apunta a un estado pendiente poco frecuente, probaría un índice cubridor parcial en (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending', que puede leer las primeras 50 filas en orden. Si el estado debe estar parametrizado, compararía un índice completo en (tenant_id, status, created_at DESC). Validaría el p95, el trabajo de búfer, el tamaño del índice y la latencia de escritura en diferentes tamaños de inquilinos, cachés frías y calientes, y escrituras concurrentes antes de compilar concurrentemente con umbrales de reversión».

Análisis detallado paso a paso

Paso 1: Priorizar a partir de la carga de trabajo real

Mapee la ruta, el inquilino, el rango de parámetros y el p95 de la aplicación a una consulta normalizada de base de datos. Si pg_stat_statements está habilitado, comience con el consumo acumulativo de recursos:

SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time encuentra el tiempo de base de datos acumulado a través de llamadas frecuentes, mean_exec_time resalta ejecuciones individuales costosas y calls muestra el multiplicador. La vista no expone el p95 ni explica por qué un inquilino o parámetro en particular es lento, por lo que debe conservar los percentiles y las cohortes de parámetros del lado de la aplicación. Si la latencia se debe principalmente a esperas de bloqueos, colas de conexiones, red o una llamada downstream, cambiar únicamente el plan de consulta no solucionará la latencia de extremo a extremo.

Paso 2: Recopilar evidencia de ejecución real de forma segura

Inspeccione primero la estructura con un EXPLAIN simple. Luego, en una réplica a escala de producción o en un entorno controlado, ejecute:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Lea hacia arriba desde los nodos más profundos con un tiempo real sustancial. Trate actual rows × loops como parte del trabajo total de un nodo. Buffers: shared read registra los bloques que tuvieron que leerse desde el almacenamiento; shared hit significa que los bloques ya estaban en los búferes compartidos, pero esos aciertos aún consumen CPU y ancho de banda de memoria. Una ordenación respaldada en disco reporta una ordenación externa e I/O de bloques temporales, lo que motiva a investigar su tamaño de entrada y el presupuesto de memoria.

La estimación de 8000 filas frente a 420 000 filas reales es un error de 52,5 veces. Las estadísticas pueden estar desactualizadas, o las estadísticas de una sola columna pueden no representar la correlación entre tenant_id y status. Ejecute un ANALYZE con el alcance adecuado y mida de nuevo. Si una correlación estable afecta materialmente el plan, pruebe estadísticas extendidas para esas columnas. Las estadísticas extendidas conllevan costos de recolección y planificación, así que créelas solo para columnas fuertemente relacionadas que mejoren una estimación importante.

Paso 3: Deducir el índice a partir de la forma de la consulta

El planificador podría combinar los índices de una sola columna existentes con BitmapAnd, pero un resultado de mapa de bits no conserva la ordenación de un B-tree. Aún puede visitar muchas páginas del heap y ordenar. Alternativamente, el planificador puede elegir un índice y filtrar el otro predicado después. Tener ambos índices solo genera rutas candidatas; no produce una ruta adaptada para WHERE + ORDER BY + LIMIT.

Para un estado pendiente fijo y poco común, compare primero un índice parcial:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

El primer índice almacena solo pedidos pendientes y, por lo tanto, debería ser más pequeño bajo la distribución indicada. Después de ubicar la igualdad del inquilino, created_at restringe el rango de 30 días y suministra el orden descendente, permitiendo que el escaneo se detenga después de 50 filas. id y total_cents son columnas de carga útil (payload) en INCLUDE; no participan en la búsqueda ni en la ordenación y simplemente hacen posible una lectura de tipo index-only.

El índice multicolumna completo se adapta a un estado parametrizado o a varios estados que utilizan el mismo patrón de consulta. Las columnas de igualdad iniciales en el B-tree reducen el rango antes del rango de tiempo y la ordenación. «Poner siempre la columna más selectiva primero» es una regla demasiado simplista; la igualdad, el rango, la ordenación y la reutilización a través de plantillas de consulta reales determinan juntos el orden de las claves.

Paso 4: Declarar los límites de los índices parciales y cubridores

Un índice parcial solo es utilizable si el planificador puede demostrar que la condición de la consulta incluye status='pending'. En una sentencia preparada genérica escrita como status = $2, ese parámetro no puede implicar el predicado para cada valor posible, por lo que el planificador puede ignorar el índice parcial. Una consulta de operaciones dedicada puede preservar el literal, o el diseño puede usar el índice multicolumna completo. Verifique la decisión con la plantilla de consulta real en lugar de inferirla a partir de la definición del índice.

INCLUDE no garantiza un Index Only Scan en cada ejecución. PostgreSQL aún debe verificar la visibilidad MVCC. Cuando una página del heap carece del bit all-visible, el escaneo visita el heap. Las inserciones y cambios de estado frecuentes en una tabla con alta actividad hacen que dichas visitas sean más probables. Si el plan todavía reporta muchos Heap Fetches, compare un índice más estrecho sin columnas de carga útil. Los índices anchos también aumentan el uso de disco y caché, y añaden trabajo de mantenimiento a cada escritura afectada.

Paso 5: Validar beneficios y costos

Compare los planes de antes y después utilizando los mismos parámetros representativos: inquilinos muy grandes, medianos y pequeños; inquilinos con muchas filas pendientes y casi ninguna; caché fría y caché caliente. Registre las distribuciones de latencia, filas reales, búferes, comportamiento de ordenación, I/O temporal, Heap Fetches y tamaño del índice. Un único tiempo transcurrido es sensible a la caché y la concurrencia, mientras que el trabajo del plan explica por qué cambió el resultado.

Luego, realice pruebas de carga de inserciones y transiciones de pending → paid a la proporción de producción. Un pedido completado elimina una entrada del índice parcial; el índice completo actualiza su clave de estado. Ambos imponen trabajo de escritura. Los criterios de aceptación pueden requerir un p95 de lectura dentro del objetivo y sin una regresión sustancial en p99, una gran reducción en filas candidatas y trabajo de bloques compartidos, así como p95 de escritura, volumen de WAL, almacenamiento y retraso de replicación dentro del presupuesto.

Antes del despliegue, confirme el espacio disponible en disco y la monitorización para compilaciones concurrentes. CREATE INDEX CONCURRENTLY permite que las inserciones, actualizaciones y eliminaciones continúen, pero tarda más y puede dejar un índice no válido tras un fallo. Una vez compilado, verifique que la plantilla de consulta de producción realmente elija la nueva ruta y observe un ciclo pico completo. Si la latencia de escritura o replicación supera su umbral, retire la nueva ruta de consulta y elimine el nuevo índice según el procedimiento operativo. Mantenga los índices antiguos hasta que el reemplazo haya superado una ventana de estabilidad y ninguna otra carga de trabajo dependa de ellos.

Respuesta de muestra de alta calidad

«Primero confirmaría que este SQL merece prioridad. La monitorización de la aplicación me da el p95 y los inquilinos lentos, mientras que pg_stat_statements proporciona las llamadas, el tiempo total de ejecución y el tiempo medio de ejecución. Si se ejecuta 40 veces por segundo y tiene un rango alto en el tiempo acumulado de base de datos, capturaría la plantilla de consulta real y los inquilinos representativos, y luego ejecutaría EXPLAIN (ANALYZE, BUFFERS) en una réplica a escala de producción.

El problema central en este plan es el escaneo amplio: el planificador estima 8000 filas, 420 000 filas llegan realmente a la ordenación top-N y la consulta toca unos 120 000 búferes. Los índices de una sola columna pueden combinar filtros, pero no crean directamente un rango ordenado para tenant_id + pending + created_at DESC. Actualizaría las estadísticas y mediría de nuevo. Si la correlación entre inquilino y estado sigue causando el error de estimación, probaría estadísticas extendidas.

Debido a que la consulta de operaciones siempre solicita el estado pendiente poco frecuente, probaría un índice parcial en (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending'. Este limita la población indexada, ingresa al rango de tiempo ordenado de un inquilino y puede detenerse después de 50 filas. Si la aplicación parametriza el estado y consulta varios valores, compararía el índice completo en (tenant_id, status, created_at DESC). INCLUDE solo habilita un posible Index Only Scan; las páginas calientes aún pueden requerir visitas al heap, por lo que inspeccionaría Heap Fetches.

La validación cubriría inquilinos grandes, medianos y pequeños, cachés frías y calientes, y escrituras concurrentes. Compararía el p95 y p99, filas candidatas, búferes, I/O temporal, tamaño del índice, WAL, latencia de escritura y retraso de replicación. Compilaría concurrentemente en producción, confirmaría que la plantilla real use el nuevo plan y observaría un ciclo pico completo. Si la ganancia de lectura es débil o la ruta de escritura supera el presupuesto, retiraría la ruta y eliminaría el nuevo índice en lugar de ocultar un plan no explicado detrás de más hardware».

Errores comunes

• Añadir un índice en cuanto el SQL parece lento → La frecuencia, los parámetros y el tipo de espera siguen sin conocerse, por lo que el equipo puede optimizar una consulta de baja prioridad → Correlacione primero los percentiles de la aplicación, pg_stat_statements y los parámetros reales.
• Tratar cada Seq Scan como un defecto → Un escaneo secuencial puede ser más económico para una tabla pequeña o una consulta que devuelve una gran fracción de ella → Compare las filas reales, los búferes y el costo total frente a las alternativas.
• Comprobar solo si el plan dice Index Scan → Un escaneo de índice aún puede leer cientos de miles de entradas y visitar repetidamente el heap → Inspeccione actual rows × loops, Filter, Buffers y Heap Fetches.
• Ignorar las brechas entre filas estimadas y reales → Una cardinalidad incorrecta puede provocar uniones, escaneos y ordenaciones deficientes → Actualice las estadísticas y pruebe estadísticas extendidas para columnas correlacionadas estables.
• Asumir que varios índices de una sola columna equivalen a un índice multicolumna → Las combinaciones de mapas de bits generalmente pierden el orden requerido y pueden visitar muchas páginas del heap → Deduzca una clave a partir de la igualdad, el rango, el orden y el LIMIT.
• Poner todas las columnas devueltas en INCLUDE → El inflado (bloat) del índice reduce la eficiencia de la caché y amplifica las escrituras → Cubra únicamente columnas estrechas requeridas por una consulta de alto valor.
• Compilar un índice parcial sin probar la plantilla de consulta → Un predicado parametrizado puede no implicar el predicado del índice en el momento de la planificación → Ejecute EXPLAIN en la misma forma preparada que se usa en producción.
• Ejecutar EXPLAIN ANALYZE en un SQL arbitrario de la base de datos primaria → Esto ejecuta la sentencia; una lectura pesada genera carga y una escritura realiza efectos secundarios → Utilice primero EXPLAIN simple y obtenga evidencia real en una réplica o dentro de una transacción controlada.
• Reportar una sola ejecución que bajó de 2,8 segundos a un número menor → La caché, los parámetros y la concurrencia pueden crear una ganancia accidental → Compare distribuciones, trabajo del plan y un ciclo pico completo.

Preguntas de seguimiento y respuestas

Pregunta de seguimiento 1: ¿Por qué un escaneo secuencial puede ser más rápido que un escaneo de índice?

Cuando una consulta lee una gran fracción de una tabla, el acceso secuencial evita gran parte del acceso aleatorio necesario para recorrer un índice y recuperar páginas dispersas del heap. Una tabla pequeña puede ocupar solo unas pocas páginas, lo que hace que un escaneo directo también sea más económico. Compare los búferes y el tiempo total transcurrido en datos reales en lugar de calificar un plan por el nombre del nodo.

Pregunta de seguimiento 2: ¿Por qué son insuficientes los índices separados de (tenant_id) y (created_at)?

El planificador puede elegir un índice y filtrar después, o combinar ambos con BitmapAnd. Un mapa de bits recopila ubicaciones de tuplas candidatas y no conserva el orden de B-tree de created_at, por lo que comúnmente lee muchas páginas del heap y luego ordena. El índice multicolumna coloca la igualdad de inquilino, el rango de tiempo y la ordenación en una sola ruta de acceso ordenada, lo que permite que LIMIT 50 se detenga anticipadamente.

Pregunta de seguimiento 3: ¿Por qué PostgreSQL podría no usar nunca el índice parcial?

El planificador debe reconocer durante la planificación que la condición de la consulta implica el predicado del índice. status='pending' coincide directamente; status=$2 no puede garantizar una coincidencia para cada parámetro. Una expresión escrita de manera diferente, una distribución de datos modificada o una estimación de costos que haga que el acceso al heap parezca costoso también pueden hacer que se seleccione otra ruta. Aplique EXPLAIN a la plantilla preparada real y use el índice multicolumna completo cuando el estado deba permanecer genérico.

Pregunta de seguimiento 4: ¿Qué pasa si la estimación sigue siendo errónea por un factor de 50 después de ANALYZE?

Verifique la cobertura de muestreo, el objetivo de estadísticas por columna (statistics target) y si los datos cambiaron abruptamente. Si tenant_id y status están fuertemente correlacionados, las estadísticas de una sola columna aproximan los predicados tratándolos como independientes. Pruebe estadísticas extendidas de dependencias o de MCV (most common values) para ese grupo de columnas. Las estadísticas extendidas mejoran la estimación; no crean una ruta de acceso faltante, por lo que debe validar el índice y la forma de la consulta por separado.

Pregunta de seguimiento 5: Las lecturas mejoran, pero el p95 de escritura aumenta. ¿Cómo decide?

Regrese a los SLO y a la carga de trabajo total: cuantifique el tiempo de base de datos ahorrado en las lecturas, los usuarios afectados por la regresión de escritura y los cambios en WAL, almacenamiento y retraso de replicación. Si solo un estado fijo y poco frecuente necesita aceleración, un índice parcial estrecho puede superar a un índice cubridor completo. Si las columnas de carga útil causan bloat, elimine INCLUDE y acepte cierto acceso al heap. Cuando se exceda el presupuesto de escritura, retire el plan de índices y busque una ruta de acceso más pequeña mediante el alcance de la consulta, garantías de paginación o el modelo de datos.