Detectar y corregir consultas lentas

Esta es la pregunta culminante de la entrevista: el entrevistador le entrega una consulta lenta y un plan de EXPLAIN ANALYZE y le pide que la diagnostique. Está evaluando un método, no trucos memorizados.

Una buena respuesta sigue una lista de comprobación en voz alta: medir, leer el plan, encontrar el coste dominante, formular una hipótesis, proponer una solución y verificarla. Esta lección desarrolla esa lista paso a paso.

Mantenga un enfoque sistemático y explique su razonamiento; eso es lo que le hará obtener una valoración de nivel sénior.

Nunca se base solo en el SQL. Obtenga el plan real con EXPLAIN (ANALYZE, BUFFERS).

ANALYZE proporciona los tiempos y el número de filas reales; BUFFERS muestra si está accediendo a la caché o leyendo del disco. Juntos indican si la consulta está limitada por la CPU, por la E/S o simplemente está haciendo demasiado trabajo.

Ejecútelo un par de veces; la primera ejecución puede incluir el coste de una caché fría y distorsionar el tiempo.

No lea el plan de arriba abajo buscando al azar. Encuentre el nodo donde realmente se invierte más tiempo.

Calcule el tiempo propio de cada nodo: su actual time total menos el tiempo de sus hijos, multiplicado por loops. El nodo con la mayor proporción es su objetivo; todo lo demás es ruido.

En una entrevista, diga: el 80 por ciento del tiempo de ejecución está en este Seq Scan, así que me concentro ahí. Optimizar cualquier otra cosa sería un esfuerzo desperdiciado.

En el nodo dominante, compare las filas estimadas con las filas reales. Una diferencia grande significa que el planificador está a ciegas y probablemente eligió un plan incorrecto (un algoritmo de join o un método de acceso equivocado).

El ejemplo muestra una subestimación de 1000 veces. Antes de rediseñar nada, actualice las estadísticas; este único comando suele corregir el plan sin coste.

ANALYZE recalcula las estadísticas de las columnas; VACUUM ANALYZE también limpia las tuplas obsoletas y actualiza el mapa de visibilidad.

El error corregible más frecuente: una función o una conversión envuelve la columna en WHERE, por lo que no se puede usar el índice y el motor realiza un escaneo secuencial.

El ejemplo fuerza un escaneo completo porque aplica DATE() a cada fila. Reescríbalo como un predicado de rango sobre la columna sin envolver (forma sargable) y se activará el índice sobre created_at.

La misma idea se aplica a WHERE lower(email)=...: almacene los datos normalizados, consulte la columna sin envolver o cree un índice sobre la expresión.

Si el nodo dominante es un Seq Scan con un filtro muy selectivo, o un Nested Loop con un número enorme de loops sobre una clave interna sin índice, la solución suele ser crear un índice.

Añada un índice sobre la columna filtrada o de join. El ejemplo crea uno sobre customer_id para que el join pueda cambiar de escaneos secuenciales a escaneos mediante índice, y el planificador pueda elegir un plan mucho más barato.

Verifíquelo volviendo a ejecutar EXPLAIN ANALYZE; no dé por hecho que el índice ayudó.

SELECT * extrae todas las columnas del disco y las transmite por la red, además de impedir los escaneos que solo usan el índice, porque rara vez el índice contiene todas las columnas.

Seleccione únicamente las columnas que necesite. Esto reduce el tamaño de las filas, disminuye la E/S y puede permitir un escaneo que solo use un índice de cobertura.

Si un entrevistador incluye SELECT * deliberadamente, quiere que lo detecte. Reducir la lista de columnas suele producir una mejora rápida y real en tablas anchas.

Si un nodo Sort o Hash muestra uso de disco (Sort Method: external merge Disk: 25000kB o Batches: > 1), la operación superó el límite de work_mem y desbordó al disco.

Opciones: aumente work_mem para la sesión, reduzca el número de filas que llegan al ordenamiento o al hash (filtre antes), o añada un índice que proporcione el orden necesario para no tener que ordenar.

Este es un diagnóstico preciso y propio de un nivel sénior, justo el tipo de respuesta que los entrevistadores valoran.

Preste atención a Rows Removed by Filter: 9500000. La consulta leyó diez millones de filas y descartó casi todas: es un desperdicio de trabajo clásico.

Soluciones: añada un índice para que el filtro se aplique durante el acceso (no después), haga que el predicado sea más selectivo o adelante el filtrado en la consulta para que asciendan menos filas por el árbol.

El principio es hacer el menor trabajo posible: filtre lo antes y lo más barato que pueda.

Repita esto en la entrevista y no perderá el rumbo:

• Mida con EXPLAIN (ANALYZE, BUFFERS).
• Localice el nodo que consume más tiempo.
• Compare las filas estimadas con las reales y corrija primero las estadísticas obsoletas.
• Compruebe la sargabilidad y elimine las funciones de las columnas filtradas.
• Indexe los filtros selectivos y las claves de join.
• Reduzca las columnas y evite SELECT *.
• Vigile los desbordamientos al disco y la obtención excesiva de filas.
• Verifique volviendo a ejecutar el plan.

Explique en voz alta un ejemplo completo. El plan muestra un Seq Scan sobre una tabla orders de 50 millones de filas, con el filtro customer_id = 42, Rows Removed by Filter cercano a 50 millones y una estimación que coincide aproximadamente con el valor real.

Diagnóstico: filtro selectivo, sin índice; el coste dominante es el escaneo. Solución: CREATE INDEX ON orders(customer_id). Vuelva a ejecutar la consulta: el plan cambia a un Index Scan y el tiempo baja de varios segundos a menos de un milisegundo.

Ese ciclo de medir, diagnosticar, corregir y verificar es la plantilla de respuesta para cualquier pregunta sobre consultas lentas.

Una consulta filtra con WHERE YEAR(order_date) = 2026 y el plan muestra un Seq Scan completo a pesar de que ya existe un índice B-Tree sobre order_date. ¿Cuál es la mejor primera solución?

Ahora dispone de un método repetible para abordar preguntas sobre consultas lentas:

• Siempre mida con EXPLAIN (ANALYZE, BUFFERS) y concéntrese en el nodo dominante.
• Corrija primero las estadísticas obsoletas cuando las estimaciones y los valores reales difieran.
• Haga que los predicados sean sargables, añada índices para filtros selectivos y claves de unión, y evite SELECT *.
• Aborde los desbordamientos a disco y la recuperación excesiva de datos; después, verifique el nuevo plan.

Exponer la lista de comprobación, proponer un cambio concreto y volver a ejecutar el plan para demostrarlo: esa es la respuesta de un perfil sénior.