Historia de la pregunta
PostgreSQL introdujo declaraciones preparadas para eliminar la sobrecarga de análisis y planificación en consultas SQL que se ejecutan repetidamente. Las versiones tempranas siempre generaban planes de ejecución personalizados adaptados a valores específicos de parámetros, pero esto incurrió en costos significativos de CPU para consultas complejas. Para optimizar esto, PostgreSQL 9.2 implementó caché de planes con un mecanismo de plan genérico que reutiliza una única estructura de plan a través de múltiples ejecuciones. Si bien este enfoque reduce la latencia de planificación, trata todos los parámetros como marcadores de posición opacos durante la fase de planificación inicial.
El problema
La poda de particiones en PostgreSQL opera en dos etapas distintas: la poda en tiempo de plan, que ocurre durante la planificación de consultas cuando el planificador examina las restricciones de partición contra valores literales, y la poda en tiempo de ejecución, que filtra particiones durante la ejecución utilizando uniones por partición o filtrado de nodos de apéndice. Los planes genéricos generados para declaraciones preparadas carecen de valores de parámetros concretos en el momento de la planificación, lo que hace imposible la poda en tiempo de plan. Como resultado, el planificador genera un escaneo a través de todas las particiones independientemente de los valores de parámetros reales proporcionados durante la ejecución, lo que lleva a una degradación catastrófica del rendimiento en tablas particionadas grandes.
La solución
La resolución requiere forzar a PostgreSQL a generar planes personalizados que incorporen valores de parámetros reales durante la fase de planificación. Esto se logra configurando el parámetro de configuración plan_cache_mode en force_custom_plan para la sesión o consulta específica, eludiendo por completo la caché de planes genéricos. Alternativamente, las soluciones a nivel de protocolo incluyen usar el protocolo de consulta extendida con el mensaje Bind que contenga valores literales en lugar de parámetros, o emplear creadores de consultas del lado del cliente que inlinen valores literales para claves de partición mientras mantienen otros parámetros vinculados para prevenir inyecciones de SQL.
-- Forzar plan personalizado para esta sesión SET plan_cache_mode = force_custom_plan; -- O usar SQL dinámico con format() para incluir literales de forma segura EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Una plataforma de análisis de comercio de alta frecuencia experimentó picos de latencia severos cada mañana al consultar movimientos de precios intradía. La base de datos almacenaba datos ticks en una tabla particionada por la fecha de la sesión de trading, conteniendo más de 2,000 particiones que abarcan cinco años. La aplicación utilizaba declaraciones preparadas de JDBC con marcadores de posición ? para el parámetro de fecha para prevenir inyecciones de SQL y reducir la sobrecarga de análisis.
El equipo de desarrollo observó inicialmente que las consultas que filtraban datos de "hoy" estaban escaneando particiones históricas, consumiendo 45 segundos en lugar de los 300 milisegundos esperados. Esta degradación del rendimiento ocurrió porque el plan genérico no podía eliminar particiones irrelevantes durante la fase de planificación.
Un enfoque involucró la creación de una tabla no registrada separada para datos activos y la migración de registros cada noche. Esta estrategia habría eludido completamente la tabla particionada para consultas recientes, pero introdujo lógica ETL compleja y arriesgó la pérdida de datos durante caídas del sistema.
Otra propuesta sugirió desactivar globalmente las declaraciones preparadas en el grupo de conexiones de JDBC. Si bien esto habría restaurado la poda en tiempo de plan al exponer valores literales al planificador, los análisis revelaron un aumento del 40% en la utilización de CPU en el servidor de base de datos debido a la sobrecarga de análisis y planificación repetida.
El equipo también evaluó el uso de las capacidades de poda de particiones en tiempo de ejecución de PostgreSQL introducidas en la versión 11. Sin embargo, la poda en tiempo de ejecución solo elimina particiones después de que el ejecutor comienza a escanear, lo que significa que el planificador aún asignaba recursos para todas las particiones y producía órdenes de unión subóptimas que ignoraban los límites de partición.
Finalmente, el equipo eligió implementar un cambio de configuración a nivel de conexión. Configuraron el grupo de conexiones para detectar consultas dirigidas a tablas particionadas y ejecutar SET plan_cache_mode = force_custom_plan antes de despachar esas declaraciones específicas. Esto preservó los beneficios de seguridad de las consultas parametrizadas para filtros de entrada de usuario mientras aseguraba que los valores de las claves de partición fueran visibles para el planificador.
El resultado redujo la latencia de consultas a 280 milisegundos y disminuyó el uso general de CPU de la base de datos en un 15%, ya que el planificador ahora podía utilizar la exclusión de restricciones para eliminar 1,999 particiones antes de la ejecución. Esta optimización permitió que la plataforma de comercio cumpliera con sus estrictos requisitos de latencia matutina sin comprometer la integridad o seguridad de los datos.
¿Cómo decide PostgreSQL entre planes genéricos y personalizados cuando plan_cache_mode está configurado en auto?
En modo auto, PostgreSQL planifica y ejecuta la consulta utilizando un plan personalizado para las primeras cinco ejecuciones, acumulando el costo de planificación. Después de la quinta ejecución, compara el tiempo promedio de ejecución del plan genérico (estimado durante la primera ejecución) con el tiempo promedio de ejecución de los planes personalizados más su sobrecarga de planificación. Si el costo estimado del plan genérico es menor que el costo promedio del plan personalizado, el sistema cambia permanentemente al plan genérico para esa declaración preparada. Los candidatos a menudo omiten que esta comparación incluye la sobrecarga de planificación ahorrada al reutilizar el plan genérico, y que la decisión es permanente durante la vida útil de la declaración preparada a menos que se replantee explícitamente.
¿Cuál es la distinción entre la poda de particiones en tiempo de plan y en tiempo de ejecución en el contexto de declaraciones preparadas?
La poda en tiempo de plan ocurre durante la fase de planificación cuando el planificador puede probar que ciertas particiones no pueden contener filas relevantes en función de las restricciones de partición y valores literales en la consulta. La poda en tiempo de ejecución ocurre durante la ejecución cuando el ejecutor verifica las restricciones de partición contra los valores de parámetros reales utilizando el mecanismo de filtrado del ejecutor. Los planes preparados genéricos admiten la poda en tiempo de ejecución a partir de PostgreSQL 11, pero no pueden admitir la poda en tiempo de plan porque los valores de parámetros son desconocidos. Los candidatos confunden frecuentemente estos mecanismos, creyendo que la poda en tiempo de ejecución resuelve todos los problemas de particionamiento de declaraciones preparadas, sin darse cuenta de que la poda en tiempo de plan es crucial para una planificación de uniones eficiente y selección de índices.
¿Por qué podría fallar force_custom_plan al resolver problemas de poda de particiones en PostgreSQL versiones anteriores a 10?
Antes de la versión 10, PostgreSQL carecía de apoyo significativo para la poda de particiones en tiempo de ejecución en su totalidad, y las declaraciones preparadas no podían beneficiarse de la exclusión de restricciones incluso con planes personalizados si los parámetros eran pasados a través del protocolo de consulta extendida utilizando el mensaje Bind. El planificador trataba todos los parámetros vinculados como externos al proceso de planificación, requiriendo valores literales explícitos en la cadena de consulta misma para activar la exclusión de restricciones. Esta limitación histórica significa que en sistemas legados, incluso los planes personalizados escanearían todas las particiones, necesitando generación dinámica de SQL con EXECUTE ... USING con literales o concatenación de cadenas del lado del cliente con el escape adecuado, en lugar de enlazado moderno de parámetros a nivel de protocolo.