10 años de mejoras en el optimizador de PostgreSQL

• Al comparar la latencia de consultas en el percentil 90 con el Join Order Benchmark desde PostgreSQL 8 hasta 16, se confirma empíricamente una mejora sostenida del rendimiento en la cola a largo plazo
• Frente a PostgreSQL 8, en la versión 16 la latencia en la cola se redujo casi a la mitad, y entre las versiones 13 y 16 se mantuvo en general en un nivel estable
• Según el análisis de regresión, cada nueva versión mayor mostró en promedio una mejora de rendimiento del 15%, aunque un modelo lineal podría no describir bien el patrón real de cambio
• El experimento fijó las condiciones con GCC 13.2, Docker sobre Arch Linux, shared_buffers de 8GB y work_mem de 8MB para enfocarse en la calidad del optimizador de consultas
• Al interpretar la magnitud de las mejoras, hay que considerar no solo el optimizador, sino también cambios en el motor de ejecución como workers paralelos y compilación JIT

---

Configuración del benchmark de PostgreSQL 8 a 16

• El análisis cubre las versiones mayores 8 a 16 de PostgreSQL, un optimizador de consultas de código abierto
• Para el benchmark se usó Join Order Benchmark, un conjunto de consultas con joins complejos
  • Este benchmark fue introducido en el artículo “How Good are Query Optimizers, Really?”
• Cada versión de PostgreSQL fue compilada con GCC 13.2 dentro de un contenedor Docker con Arch Linux
• El entorno de medición se ajustó para observar la calidad del optimizador de consultas más que el rendimiento de índices o de I/O
  • shared_buffers se configuró en 8GB, lo suficientemente grande para contener toda la base de datos
  • work_mem se fijó en 8MB en todas las versiones
• Cada consulta se ejecutó una vez para calentar caché y luego se registró la latencia mediana de 5 ejecuciones adicionales
• Para cada versión mayor se utilizó la versión menor más reciente
  • Por ejemplo, para PostgreSQL 8 se evaluó la 8.4.22
  • Esas versiones menores normalmente salieron después de la nueva versión mayor, pero por lo general solo incluyen correcciones de errores y no nuevas funciones ni mejoras de rendimiento

Resultados e interpretación

• El rendimiento en la cola de PostgreSQL mejoró de forma importante en conjunto
  • Al comparar PostgreSQL 8 con 16, la latencia en la cola se redujo casi a la mitad
  • De PostgreSQL 13 a 16 se mantuvo, en términos generales, en un nivel estable
• El análisis de regresión se usó para verificar si la tendencia descendente entre el número de versión mayor y la latencia de las consultas era significativa, y para cuantificar la magnitud de la mejora por versión
  • Bajo una regresión lineal, cada nueva versión mayor mostró una mejora promedio de rendimiento del 15% en Join Order Benchmark
  • Aun así, un modelo lineal puede no ser adecuado para medir el patrón real del cambio
• No es fácil explicar todas las mejoras solo por el optimizador de consultas
  • Mejoras en el motor de ejecución, como workers paralelos y compilación JIT, también influyen en el rendimiento
  • Cómo cambiaron año con año los planes de consulta de JOB sigue siendo un tema para análisis aparte
• Al pasar de PostgreSQL 8 a 16, es posible que la latencia en la cola de la carga de trabajo se reduzca de forma considerable
• En las comparaciones de investigación, es importante considerar que el propio PostgreSQL sigue fortaleciéndose como línea base
  • Neo y Bao se compararon con PostgreSQL 11, pero estudios más recientes se comparan con PostgreSQL 14, 15 y 16
  • Aunque una técnica anterior mejore 30% frente a PostgreSQL y una técnica reciente mejore 25%, la técnica reciente podría estar comparándose contra un PostgreSQL más fuerte
• Los valores medidos originales pueden revisarse en raw data

[GN⁺]

Opiniones de Hacker News

• He usado Postgres durante 15 años y pasé la mayor parte de mi carrera modelando y resolviendo problemas de optimización matemática; en este tema, creo que hay tres puntos clave.
  Todo problema de optimización necesita datos de costos, y mientras más y mejores datos haya, mejor. Postgres ha tenido mejoras como estadísticas entre columnas, pero todavía quedan grandes huecos, como la latencia de llamadas al sistema. La latencia de leer una página desde disco varía mucho de un sistema a otro, pero Postgres no la mide directamente y depende de valores de configuración. También faltan estadísticas de claves foráneas, así que los joins que siguen claves foráneas no deberían terminar con malos planes, pero a veces todavía pasa.
  En particular, para consultas grandes y costosas se necesita planificación diferida o planes para escenarios alternativos. Hoy el plan queda fijado antes de la ejecución, pero los conteos de filas o estimaciones de cardinalidad obtenidos en las primeras etapas de ejecución podrían mejorar mucho el plan posterior.
  El aprendizaje automático también es un área con margen de mejora, pero los intentos que he visto hasta ahora no me han impresionado. Más que usar aprendizaje automático para el plan en sí, habría que usarlo para el descubrimiento y la estimación de costos. Hay que crear mejores modelos de costos y hacer que el motor de optimización aproveche esos datos.

  • Me gustaría escuchar más opiniones. Por ejemplo, me sorprende que la latencia de llamadas al sistema esté tan arriba en la lista. Creo que la visión general en la comunidad de bases de datos es que el modelo de costos está más o menos bien, y que la estimación de cardinalidad es lo realmente malo.
    Sobre la planificación diferida/alternativa, me pregunto si la ejecución adaptativa de consultas es una vía razonable. Se puede hacer que la información de las primeras etapas de ejecución influya en el plan posterior, pero me preocupa que si se eligen mal los primeros joins, algo bastante común, sea difícil recuperarse sin algo como Yannakakis/SIPs.
    Sobre “aprendizaje automático para optimización de consultas”, claramente tengo sesgos. Aun así, todos los enfoques de “aprendizaje automático para planificación” que he visto terminan usando internamente aprendizaje automático para descubrimiento/estimación de costos. Estos enfoques intentan equilibrar los datos que recolectan, es decir, la exploración, con la calidad de los planes que producen, es decir, la explotación. Curiosamente, si se usa aprendizaje automático de una forma completamente separada de la planificación, las estimaciones se vuelven más precisas, pero los planes de consulta reales empeoran: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
    Tengo intereses en esta área, así que tomen mi opinión con cautela.
  • Los planes alternativos suenan realmente bien. Hace poco vi un plan de consulta que estimaba que una subconsulta produciría unas 1,000 filas, así que puso un loop anidado sobre un index scan; en realidad eran alrededor de 1,000 millones de filas.
    Todavía no sé por qué la estimación estuvo tan equivocada, pero si al superar cierto umbral de filas se pudiera cambiar de un loop anidado a un hash join, creo que ayudaría mucho a evitar planes catastróficos.
  • Me pregunto qué significa exactamente que falten estadísticas de claves foráneas. Postgres, como muchas bases de datos relacionales, no crea automáticamente índices en claves foráneas, y supongo que eso ya lo sabes.
    ¿Te refieres a problemas con el orden de los joins?
  • Me pregunto si consideran que MSSQL es mejor en esta parte.

• El optimizador de consultas de Postgres intenta reducir la cantidad de páginas leídas desde disco y la cantidad de páginas escritas a disco como resultados intermedios. Por eso, configurar shared buffers lo bastante grande como para contener todos los datos y hacer benchmarks del optimizador de consultas parece incorrecto.
  En ese caso se termina midiendo la velocidad del optimizador de consultas y del procesador de joins, no la calidad de los planes de consulta generados. De hecho, no sería sorprendente que los planes generados por cada versión fueran todos iguales y que solo se haya medido la velocidad de ejecución.

  • No es así. Lo que se optimiza no son solo las páginas leídas desde disco, sino un costo que también incluye cosas como el uso de CPU.
    El costo está en unidades arbitrarias pensadas para correlacionarse con el tiempo transcurrido, no con la cantidad de lecturas de disco, así que comparar planes con todo en RAM es perfectamente válido. Por convención, leer una página desde disco se escala a 1.0, pero eso no es lo mismo que decir que “el optimizador minimiza la cantidad de lecturas de páginas de disco”. También podrían haber definido 1 ms como 1.0 en una máquina arbitraria.
  • Definitivamente es posible que los planes sean parecidos y que se hayan medido mejoras del motor de ejecución. Join Order Benchmark está diseñado para probar la calidad del optimizador.
    El optimizador de PG intenta reducir no solo la cantidad de páginas leídas desde disco, sino también el número de tuplas que examina la CPU, la cantidad de evaluaciones de predicados, etc.; todos esos números se combinan en un “costo” que se vuelve la función que el optimizador minimiza.
    Medir rendimiento con caché fría y con caché caliente puede dar resultados distintos, y este experimento claramente es un escenario de caché caliente. Pero la caché fría también tiene el problema mencionado. Con el tamaño de datos de Join Order Benchmark, el efecto de ahorrar algunas operaciones de I/O gracias a las mejoras en los B-tree de PG podría dominar sobre las mejoras basadas en CPU.
    Como referencia, el plan de la consulta de latencia P90 cambió de usar loop join y merge join en PG 8.4 a usar hash join en PG 16, y esa consulta ya no es la consulta P90. Esto puede verse al menos como parte de la evidencia de mejoras en el optimizador.

• El artículo menciona el compilador JIT de PostgreSQL, pero hasta ahora solo he visto que empeora el rendimiento de las consultas. Lo tengo en la checklist de instalación para desactivarlo.

  • Un cliente tuvo los peores problemas de rendimiento después de migrar a Postgres. Curiosamente, solo ocurría en Docker y en la configuración del servidor de pruebas, no en las máquinas de los desarrolladores. Los desarrolladores ejecutaban Postgres con Homebrew.
    Resultó que Homebrew instalaba Postgres sin soporte JIT, y en las máquinas de los desarrolladores cierta consulta terminaba en 200 ms, pero en entornos con JIT activado tardaba 4–5 segundos. Como no usamos Postgres en mucha profundidad, nos tomó un tiempo encontrar la causa; desde entonces siempre desactivamos JIT y no miramos atrás.
  • El compilador JIT es excelente para consultas analíticas.
    En PostgreSQL también se puede configurar el umbral para activar JIT, así que puedes subir el criterio a partir del cual se enciende JIT.
  • El JIT de pg muestra bastante bien que LLVM no es particularmente bueno para JIT, y se vuelve peor porque Postgres no tiene una caché persistente y compartida de consultas.
    Si pudiera compilar de forma asíncrona para consultas futuras, creo que sería menos dañino. De hecho, los JIT comunes, en especial los backends de optimización, funcionan más cerca de ese modelo.
  • ¿Postgres no puede compilar una consulta con JIT una vez y luego ejecutar varias veces la consulta compilada?

• Es interesante, pero el esquema de numeración de versiones de Postgres cambió en la v10. 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1 y 8.0 son, en la práctica, versiones mayores separadas
  También sería interesante ver cómo cambió el rendimiento en esas versiones

  • Aun así, se agradece que desde la v9.0 hasta la 9.6 mantuvieran la compatibilidad de sistema de archivos entre versiones mayores, lo que permitía actualizaciones in-place más rápidas cambiando solo los binarios
    Quizás eso los haya limitado, pero las actualizaciones anuales que requieren más downtime o reindexación no son precisamente agradables, y puede ser una razón por la que muchos sitios posponen la actualización hasta el fin del soporte de la versión anterior. En especial para usuarios de AWS RDS
    Las actualizaciones con replicación lógica posteriores a la v10 tienen ventajas en términos de disponibilidad, pero si el esquema no es relativamente simple, son proyectos grandes con costos inevitables y riesgos importantes
  • Totalmente de acuerdo. Interpreté los números de versión al estilo semver y elegí la versión más reciente de cada versión mayor, pero eso no coincide con la forma en que PostgreSQL tradicionalmente ha manejado los números de versión mayor
    Por ejemplo, PG 8.2 y 8.1 son versiones mayores distintas, pero yo las interpreté como si fueran versiones menores. La principal razón para hacerlo así fue reducir la cantidad de versiones que tenía que probar, y coincido en que un análisis más completo debería probar cada versión mayor real

• Dijeron “por supuesto, no toda esta mejora se debe al optimizador de consultas”, pero sería interesante ver si hubo cambios en los planes de ejecución según la versión

• Me recuerda a la ley de Proebsting: https://proebsting.cs.arizona.edu/law.html

  • Lo bueno de la optimización de compiladores es que puede mejorar el rendimiento sin tocar físicamente el CPU existente. Año tras año se exprime más rendimiento de una máquina que alguien diseñó, y acumulado se vuelve grande
    Imagina cuál sería el impacto ambiental de optimizar el rendimiento de Python en un 1%. ¿Cuánto CO2 atmosférico reduciría? Probablemente sea más que la huella ambiental combinada de uno mismo, la familia y los amigos. Tal vez incluso comparable con la de toda la ciudad donde vives. Todo porque alguien dedicó tiempo a implementar algunos trucos de operaciones a nivel de bits
  • No sé por qué. Esa ley parece decir que las mejoras de rendimiento en software no importan mucho, mientras que este artículo trata de que las mejoras en Postgres fueron considerables
    ¿Será porque 15% se considera una cifra baja? En este contexto no es baja en absoluto. Es menor que el 60% de la ley enlazada, y si se divide tipo 15/10 sería todavía menor, pero no hay que comparar el rendimiento de Postgres con las mejoras de hardware. Para igualar una mejora de rendimiento del 1% en lo que aquí se mide haría falta una mejora de hardware enorme
    No creo que esa ley sea tan ridícula como dicen algunos, pero trata sobre tiempos de compilación de lenguajes de programación. No compararía algo relativamente poco importante como eso con el almacenamiento y consumo de datos, que podría considerarse una de las cosas más importantes de la informática
  • En este caso, el investigador compiló todas las versiones de PostgreSQL con el mismo GCC 13.2 y las probó en el mismo sistema operativo
  • Parece una “ley” bastante débil. ¿Fue hecha como chiste? Sus fundamentos son números de origen incierto, del tipo “supongamos que”, y la conclusión también está bastante errada. Parece insinuar que una optimización que mejora en 4% anual el rendimiento de mucho software en todo el mundo es una pérdida de tiempo
    Como comparación solo se presenta la ley de Murphy. Me da curiosidad cuál es la diferencia entre el costo de desarrollar hardware más rápido y el costo de seguir mejorando compiladores. Dependiendo de cómo se compare el retorno de inversión, por ejemplo en dólares por punto porcentual de mejora de rendimiento, esta “ley” podría tener cierto peso
    En cambio, este artículo sobre Postgres parece mostrar rendimientos decrecientes en la optimización, lo que refuta la premisa de esa “ley” de que los beneficios son constantes año tras año. Al mismo tiempo, también podría confirmar la insinuación de Proebsting de que, a largo plazo, la optimización es una mala inversión

• Este análisis me resulta un poco confuso. No sé cómo identificaron en los datos una tendencia descendente que no se ve en la gráfica
  La mediana parece bajar un poco en las primeras versiones y luego volver a subir en las versiones más recientes. Como el R² es muy bajo, la correlación no se siente convincente. Básicamente, parece que la latencia de cola mejoró y lo demás depende del entorno

  • Soy el autor del blog
    La interpretación de que “la latencia de cola mejoró y lo demás depende del entorno” me parece válida, aunque conservadora. Por supuesto, en muchas aplicaciones, quizá en la mayoría, la latencia de cola es muy importante. Además, la latencia de cola también es el objetivo principal de los ingenieros del optimizador: reducir el tiempo de ejecución de las consultas que más tardan

• ¿Cómo se ve la optimización de consultas? Me da curiosidad si se optimiza a nivel de SQL o a nivel de algoritmos

  • Dejando fuera PostgreSQL, en la mayoría de las bases de datos que he usado, casi toda la optimización ocurre a nivel de algoritmos. Es decir, se elige el mejor algoritmo y el orden de ejecución para una consulta específica
    Parece ser porque varias consultas SQL distintas pueden convertirse en la misma “instrucción” o plan de ejecución, y porque la propia semántica de SQL no deja mucho margen para optimizar a nivel de lenguaje
    Como se dijo en otra respuesta, una de las decisiones importantes es si un escaneo completo de tabla puede reemplazarse por una búsqueda en índice o un escaneo de índice
    Por ejemplo, si se necesita un escaneo completo de tabla y para cada fila hay que hacer un cálculo considerable para decidir si se incluye en el conjunto de resultados, el optimizador puede convertir el escaneo completo de tabla en un escaneo paralelo de tabla y fusionar los resultados de cada tarea paralela
    Cuando escribes código de alto rendimiento para un compilador, tienes que saber cómo el optimizador del compilador transforma el código fuente en código máquina. Así puedes favorecer código que el optimizador maneja bien y evitar patrones que produzcan código máquina más lento. Al final, el optimizador está programado para detectar ciertos patrones y transformarlos
    Con los optimizadores de consultas y los planes de ejecución pasa lo mismo. Hay que aprender qué patrones puede manejar el optimizador de consultas de la base de datos que usas para generar planes de ejecución eficientes
  • Describe todas las formas en que se puede ejecutar SQL y luego elige el plan más rápido. Por ejemplo, si buscas la fila de un usuario cuyo user_id es xx, decide si leer toda la tabla y filtrar, o usar una estructura de datos dedicada
    Con un índice, se puede encontrar en tiempo logarítmico respecto al número de filas. Además de eso, se pueden hacer muchas otras cosas, como elegir el orden de los joins, elegir la estrategia de join y empujar las condiciones de filtro hacia el origen. Ese es el amplio ámbito de la optimización de SQL
  • Visto a muy alto nivel, el objetivo del planificador de consultas es minimizar el costo de leer datos desde el disco. Reúne estadísticas de columnas precalculadas, como el número de filas y la cantidad de valores únicos, para estimar cuántas filas coincidirá la consulta
    Con esta información decide el orden de los joins, selecciona índices, etc. Los joins pueden ejecutarse con varios algoritmos, como hash, loop y merge. La opción más barata depende de factores como si un lado cabe en la memoria de trabajo, si ambos lados ya están ordenados, por ejemplo gracias a un escaneo de índice, y cosas por el estilo
  • La optimización de consultas consiste en elegir el algoritmo que entrega los resultados solicitados por SQL

• Parece que el sitio está caído, así que en su lugar se puede ver esto: https://web.archive.org/web/20240417050840/https://rmarcus.i...