Entrevista de datos: ¿Cómo se diagnostica un plan genérico de PostgreSQL para una consulta parametrizada?

Planteamiento y caso de uso

Una API que utiliza sentencias preparadas presenta latencia de cola larga después de su lanzamiento: los inquilinos pequeños son rápidos, mientras que los inquilinos grandes reciben repentinamente escaneos secuenciales. Explique cómo elige PostgreSQL entre planes personalizados y genéricos, cómo compararlos con EXPLAIN (GENERIC_PLAN), cómo verificar estadísticas, sesgo de parámetros, almacenamiento en caché y plan_cache_mode, y cómo solucionar el problema sin romper las transacciones o el pool de conexiones.

Qué evalúa el entrevistador

• Separar el costo de planificación, ejecución y serialización de resultados.
• Comprender que un plan genérico ignora los valores concretos de los parámetros, mientras que un plan personalizado puede aprovechar la selectividad.
• Usar EXPLAIN ANALYZE de forma segura sin enviar efectos secundarios de escritura a producción.
• Combinar estadísticas, índices, pools de conexiones y parametrización para encontrar la regresión.
• Elegir auto, force_generic_plan o force_custom_plan a partir de la evidencia.

Preguntas a aclarar primero

• ¿Se ejecuta la consulta a través de una sentencia preparada, un ORM o un proxy, y se reutilizan las conexiones?
• ¿Están los parámetros sesgados, con diferencias significativas en el tamaño del inquilino y la temperatura de los datos?
• ¿Se encuentra la regresión en la planificación, la ejecución, la espera de bloqueos, la E/S o la serialización?
• ¿Se le permite cambiar índices, objetivos de estadísticas, SQL, el pool o configuraciones a nivel de sesión?

Respuesta en treinta segundos

Comience con EXPLAIN (GENERIC_PLAN) para ver el plan que no depende de los valores de los parámetros, y luego use valores representativos con EXPLAIN ANALYZE EXECUTE para inspeccionar los planes personalizados y las filas reales. Un plan genérico ahorra trabajo de planificación pero puede seguir siendo ineficiente cuando la selectividad está sesgada. Verifique las estadísticas y el comportamiento de la caché de planes, evalúe la planificación, la ejecución y la latencia de cola, luego cambie plan_cache_mode o la consulta en una sesión controlada y valide cada conexión del pool.

Respuesta detallada paso a paso

1. Separar la planificación de la ejecución

El planificador elige los escaneos y joins a partir del SQL, las estadísticas y los parámetros; el ejecutor lee páginas, filtra filas y devuelve resultados. El tiempo de reloj de la aplicación por sí solo no puede demostrar que un plan genérico sea la causa.

2. Explicar un plan personalizado

Un plan personalizado se genera para los parámetros actuales y puede aprovechar la selectividad. Un inquilino pequeño puede favorecer un escaneo por índice, mientras que un inquilino grande puede favorecer un escaneo secuencial o un orden de join diferente; el costo es la planificación repetida.

3. Explicar un plan genérico

Un plan genérico utiliza marcadores de posición e ignora los valores actuales. Amortiza la sobrecarga de planificación, pero un plan puede ser deficiente para la mayoría de los valores cuando la distribución está muy sesgada. EXPLAIN (GENERIC_PLAN) no se puede combinar con ANALYZE.

4. Inspeccionar primero el plan genérico

EXPLAIN (GENERIC_PLAN)
SELECT sum(amount)
FROM invoices
WHERE tenant_id = $1 AND status = $2;

Inspeccione el tipo de escaneo, las filas estimadas, las condiciones de índice, el orden de join y el costo total. Agregue conversiones explícitas (casts) cuando los tipos de parámetros no se puedan inferir, para que un problema de tipos no se confunda con un problema de planificación.

5. Inspeccionar planes personalizados representativos

En un entorno aislado, ejecute EXPLAIN (ANALYZE, BUFFERS) EXECUTE con valores de inquilinos de diferentes tamaños. Compare las filas estimadas y reales, los aciertos y lecturas compartidas, el tiempo de planificación, el tiempo de ejecución y las ordenaciones en disco; no compare únicamente los números de costo.

6. Comprobar estadísticas y distribución

Confirme que autovacuum o un ANALYZE manual cubran los cambios recientes, y luego inspeccione la cardinalidad, la correlación y los valores más comunes. Un objetivo de estadísticas más alto puede ayudar a una columna sesgada, pero mida tanto la precisión como la sobrecarga de la planificación.

7. Elegir el límite de reparación

plan_cache_mode=force_custom_plan a nivel de sesión puede probar si los planes personalizados eliminan la regresión; force_generic_plan se adapta a consultas estables con planificación costosa. Las soluciones a largo plazo pueden ser un índice, una división de consultas, tipos explícitos o evitar sentencias preparadas innecesarias en el ORM, no solo un cambio de configuración global.

8. Validar el pool y el despliegue

Un pool hace que las configuraciones de sesión, la vida útil de las sentencias preparadas y las diferencias de versiones de PostgreSQL sean importantes. Durante el despliegue canary, compare p95/p99, tiempo de planificación, aciertos de buffer y tasa de errores por percentil de parámetros, tamaño de inquilino, instancia de pool y nodo de base de datos, con un interruptor de reversión preparado.

Compensaciones y límites

Un plan genérico ahorra trabajo de planificación pero pierde selectividad respecto a los valores actuales; un plan personalizado puede desperdiciar CPU de planificación en consultas cortas frecuentes. EXPLAIN ANALYZE ejecuta la sentencia, por lo que las sentencias de escritura necesitan una transacción con reversión (rollback) o una réplica de solo lectura. El costo del plan es una estimación, no milisegundos; las estadísticas se muestrean y los planes pueden cambiar con los datos, las versiones de PostgreSQL y ANALYZE.

Plan de despliegue y evidencia

1. Registre el texto de la consulta, los tipos de parámetros, el modo de pool, la versión de PostgreSQL y el comportamiento de la caché de planes.
2. Establezca una línea base de planes GENERIC_PLAN y ANALYZE para valores de parámetros representativos.
3. Compruebe la antigüedad de las estadísticas, el error de estimación, los aciertos de índice, la E/S y el tiempo de planificación.
4. Pruebe plan_cache_mode en una sola conexión o sesión canary, no cambiando la configuración global primero.
5. Acepte basándose en p95/p99, CPU de planificación, lecturas compartidas, esperas de bloqueo y errores, conservando un interruptor de reversión.

Errores comunes y seguimiento

Error 1: Eliminar un plan genérico tras ver un escaneo secuencial

Un escaneo secuencial puede ser correcto para un resultado grande. Compare primero las filas reales, la E/S y la latencia de cola para valores representativos.

Error 2: Tratar el costo como tiempo real

El costo es una estimación relativa del planificador. Combine el tiempo real de ANALYZE, los buffers y las métricas de producción.

Error 3: Ejecutar EXPLAIN ANALYZE de escritura directamente en producción

ANALYZE ejecuta la sentencia. Valide las escrituras en una transacción con rollback o en una réplica aislada.

Error 4: Elevar únicamente los objetivos de estadísticas

Objetivos más altos añaden trabajo de análisis y planificación y pueden no solucionar problemas del pool o de tipos de parámetros. Demuestre el beneficio con una prueba de rendimiento (benchmark).

Error 5: Ignorar los límites de sesión del pool

plan_cache_mode a nivel de sesión y las sentencias preparadas pueden afectar solo a algunas conexiones. Cubra cada conexión del pool y la política de reciclaje antes del lanzamiento.