AQO - Optimización de consulta adaptativa PostgreSQL

Al realizar consultas, los DBMS modernos utilizan el modelo de optimización de costos, basado en los coeficientes almacenados en los archivos de configuración y las estadísticas recopiladas, calculan el "precio" de recepción y el volumen de los conjuntos de filas resultantes. Cuando las consultas se repiten, el costo y la selectividad se recalculan. Puede ejecutar una consulta y ver los valores reales de estos parámetros, sin embargo, el optimizador DBMS no utiliza esta información durante la reprogramación (estándar).



Pero, ¿qué pasaría si el optimizador guardara los valores reales de costo, selectividad y otros parámetros necesarios para ejecutar una consulta y, al ejecutarla nuevamente, se guiara no solo por las estadísticas recopiladas estándar, sino también por las guardadas después de la ejecución anterior?



Esto se llama optimización de consulta adaptativa y es prometedor. Algunos DBMS ya usan tales tecnologías.



Empresa Postgres Professional durante varios años trabajando en la extensión AQO para PostgreSQL, que implementa (de alguna forma) optimización adaptativa. El trabajo todavía está en marcha, pero ya hay algo para probar.



Primero, echemos un vistazo más de cerca al área temática de optimización de consultas.

Por qué el planificador podría elegir un plan subóptimo

La consulta SQL se puede realizar de diferentes maneras. Por ejemplo, cuando se unen dos tablas, esto se puede hacer de varias maneras diferentes: utilizando bucles anidados, fusión, hashing. Cuantas más tablas participan en la consulta, más variaciones hay en sus uniones. La tarea del planificador es elegir el plan de ejecución de la consulta con el menor costo entre muchas variaciones.



Como ya se mencionó, en su trabajo, los programadores de muchos DBMS utilizan información estadística recopilada de forma automática o manual. El planificador calcula un costo estimado basado en estas estadísticas.



En general, los planificadores DBMS modernos funcionan bien en la mayoría de las situaciones. Sin embargo, en algunos casos, el plan elegido puede estar muy lejos de ser óptimo.



Por ejemplo,La falta de información estadística relevante lleva al hecho de que el planificador se enfoca en (incorrectamente) datos incorrectos sobre el número de filas en las tablas unidas. La subestimación excesiva (o exageración) de la cardinalidad lleva a la elección de métodos no óptimos para acceder a los datos en las tablas.



Otra razón importante podría ser la falta de índices requeridos . En ausencia de índices, el planificador tiene una elección limitada de métodos de acceso a datos.



Uso de condiciones dependientes (correlacionadas)También puede afectar negativamente el funcionamiento del DBMS. El planificador (por defecto) supone que todas las condiciones en una consulta son independientes entre sí, es decir, el valor de una condición no afecta a la otra de ninguna manera. Este no es siempre el caso. Si se utilizan condiciones dependientes (por ejemplo, código postal y ciudad), el planificador también calculará el costo incorrecto y la cardinalidad de las conexiones.



El planificador también puede verse afectado por el uso de funciones en el entorno . Una función para el planificador es un "recuadro negro", no sabe cuántas filas devolverá la función, lo que también puede conducir a un costo erróneo del plan.

Formas de influir en el planificador

Las estadísticas reales son una condición indispensable para el trabajo adecuado del planificador. En primer lugar, asegúrese de que el sistema esté configurado para recopilar regularmente información estadística.

Hay varias formas de remediar las situaciones descritas anteriormente y ayudar al planificador a elegir los planes de ejecución de consultas más óptimos.



Sin índices, el planificador solo tiene una forma de obtener datos: escaneos de tablas secuenciales (y esto no siempre es malo y costoso). En algunos casos, la creación de los índices necesarios ayuda a acelerar el acceso a los datos, sin necesidad de escanear toda la tabla. Pero el uso de índices (la búsqueda de lo necesario, la creación, el mantenimiento) no es gratuito. Idealmente, deberían usarse exactamente donde se necesitan. Y donde no es necesario, no lo use.



Al usar condiciones de unión correlacionadas en consultas, puede generar estadísticas extendidas- explícitamente "preguntar" al optimizador que las condiciones están relacionadas entre sí. Para hacer esto, el administrador de la base de datos (o desarrollador) necesita conocer bien sus datos y monitorear las condiciones dependientes en las consultas, ya que es difícil predecir el número de combinaciones de columnas dependientes de antemano. Las estadísticas extendidas deberán crearse manualmente para cada opción.



Al crear una función, puede especificar el costo aproximado de ejecución y / o una estimación del número de líneas producidas por la función. En la versión 12 , se hizo posible utilizar funciones auxiliares para mejorar las estimaciones del planificador en función de los argumentos. Este también es un método manual, que no siempre da el mejor resultado.



Si todo lo demás falla, puede reescribir manualmente la solicitud, por ejemplo, utilizando vistas materializadas, expresiones de tabla comunes (CTE). O para aclarar los requisitos del área temática y, posiblemente, reescribir radicalmente la lógica de la solicitud.



Y no hay otra forma de “sugerencias” al planificador - optimización de consultas adaptativa ( una daptive q uery o ptimization). La idea detrás de este método es que después de ejecutar la consulta, la información estadística real se almacena y, cuando la consulta dada (o similar) se ejecuta nuevamente, el optimizador puede confiar en ella.



El DBMS Postgres Pro Enterprise es una extensión para la optimización de consultas adaptativas llamada AQO. Esta extensión se publica en github: github.com/postgrespro/aqo , también se puede probar con Vanilla PostgreSQL, más sobre eso a continuación.

Como funciona el módulo

El módulo AQO utiliza el aprendizaje automático en su trabajo. Puede leer más sobre el principio de funcionamiento en el artículo de Oleg Ivanov Uso de Machine Learning para aumentar el rendimiento de PostgreSQL y con más detalle en la presentación Adaptive Query Optimization (informe en YouTube ).



La esencia de este método se describe brevemente a continuación:



para evaluar el costo, el planificador necesita una evaluación de la cardinalidad y, a su vez, se necesita una evaluación de la selectividad de las condiciones.



Para condiciones simples (como "atributo = constante" o "atributo> constante"), el planificador tiene un modelo mediante el cual estima la selectividad. Para hacer esto, usa información estadística: el número de valores de atributos únicos, histogramas, etc.

Para las condiciones que se componen de elementos simples que usan conectivos lógicos, el planificador usa fórmulas fáciles de calcular:

• sel (no A) = 1 - sel (A)
• sel (no A) = 1 - sel (A)
• sel (A y B) = sel (A) * sel (B)
• sel (A o B) = sel (no (no A y no B)) = 1 - (1 - sel (A)) * (1 - sel (B))

Estas fórmulas suponen la independencia (falta de correlación) de las condiciones A y B, razón por la cual obtenemos estimaciones incorrectas en el caso cuando se viola esta suposición.

AQO complica la fórmula: introduce su propio coeficiente para cada condición simple. Usando el aprendizaje automático (usando la regresión del vecino más cercano), AQO selecciona estos coeficientes para que la selectividad calculada por la fórmula coincida mejor con la selectividad real que AQO observó previamente.



Para esto, el módulo guarda:

• , ;
• .

En su trabajo, AQO distingue entre condiciones hasta constantes. Esto permite reducir la complejidad del problema que se está resolviendo y, además, en la mayoría de los casos, la información aún no se pierde: AQO no "ve" el valor de la constante, pero "ve" la selectividad de la condición.

Una situación en la que se produce una pérdida, estas son condiciones que son evaluadas por una constante independientemente de valores específicos. Por ejemplo, para algunas condiciones, el planificador no puede hacer estimaciones razonables y elige la constante predeterminada (por ejemplo, la selectividad de la condición "expresión1 = expresión2" siempre se evalúa como 0.005 y "expresión1> expresión2" como 1/3).



Por lo tanto, AQO mejora la estimación de selectividad de condiciones complejas (y, como consecuencia, la estimación de costos, que puede conducir a la elección de un plan de ejecución más adecuado).

Instalar el módulo

Para probar la funcionalidad del módulo en Vanilla PostgreSQL, debe usar un parche especial y luego ensamblar el sistema a partir del código fuente. Lea más en el archivo README en github.



Si se utiliza Postgres Pro Enterprise, la instalación del módulo AQO continuará en modo estándar:



shared_preload_libraries = 'aqo'



después de eso, puede crear una extensión en la base de datos requerida.

Preparando la base de datos

Veamos un ejemplo concreto de cómo funciona el módulo AQO en una base de datos de demostración . Utilizaremos una gran base de datos que contiene información sobre vuelos para el año de septiembre de 2016 a septiembre de 2017.



Primero, crea una extensión:

CREATE EXTENSION aqo;

A continuación, desactive el procesamiento de consultas paralelas para que la visualización de planes paralelos no distraiga de la tarea principal:



max_parallel_workers_per_gather = 0;



para que el planificador PostgreSQL tenga más opciones para unir tablas, cree dos índices:

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

Al analizar los resultados, nos centraremos en el valor de BUFFERS como la cantidad de páginas que necesita leer para completar el trabajo. También veremos el tiempo de ejecución (pero el tiempo en un sistema cargado y en una computadora portátil doméstica puede variar mucho).



Aumente la memoria caché del búfer y work_mem para que todo el trabajo se realice en RAM:



shared_buffers = '256MB';

work_mem = '256MB';

Usando el Módulo AQO

Formularemos una solicitud: se requiere recibir pasajeros que volaron en clase ejecutiva desde una fecha determinada y llegaron tarde con no más de una hora.

Ejecutemos la consulta sin usar AQO (en adelante, parte de la información que no afecta la comprensión de la operación del módulo se ha eliminado de los planes):

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

Y veamos el plan resultante: en este caso, el planificador consideró el plan óptimo, en el cual, primero, usando un escaneo de mapa de bits ( ), obtenemos un conjunto de filas de la tabla de vuelos, que tenemos (un nodo ) con un conjunto de filas de la tabla ticket_flights obtenida usando el índice escanear ( ). El resultado resultante se usará como el conjunto de filas externo para la unión de bucle anidado final (nodo ). El conjunto interno para esta unión se obtiene utilizando un escaneo de índice exclusivo de la tabla tickets ( ). La operación más "voluminosa" es obtener el conjunto de filas interno para el bucle anidado: se leen 106 205 buffers.



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





Este plan se puede llamar relativamente bueno, ya que una unión de bucle anidado procesa un número relativamente pequeño de filas en un conjunto externo.



Ahora realicemos un experimento y veamos cómo cambiará (o no) el plan propuesto dependiendo del cambio en las fechas de la solicitud. Las fechas se seleccionan de manera que aumenten secuencialmente el rango de filas de la tabla de vuelos que satisfacen la condición, lo que conduce a un error de planificación al evaluar la cardinalidad de acceso a esta tabla. El plan anterior muestra que con la primera fecha, el optimizador casi no se equivoca en cardinalidad ( ). Sustituyamos las siguientes fechas en la solicitud una por una:Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 2017-04-01
• 2017-01-01
• 2016-08-01

Y mira el resultado:





Para resumir, sin usar el módulo AQO, el planificador funciona de la siguiente manera:



Veamos el resultado nuevamente:

Cuando AQO está habilitado, el planificador se da cuenta rápidamente de que necesita cambiar de una conexión de bucle anidado y usar un índice a una unión hash y un escaneo secuencial.

Resumir

Existen ventajas y desventajas al usar el módulo AQO para la optimización de consultas adaptativas.



Una de las ventajas de usar un módulo es que no tiene que realizar un seguimiento de las condiciones dependientes en las consultas. En algunos casos, la velocidad de ejecución de consultas puede aumentar. Y hay diferentes modos de usar el módulo. Por ejemplo, puede usar AQO para optimizar solo ciertos tipos de consultas.



Entre las desventajas del módulo, se pueden destacar los costos generales adicionales para la capacitación y el almacenamiento de estadísticas en las estructuras del módulo. Y la información estadística recopilada por el módulo no se transmite a las réplicas.



El módulo AQO no es una bala de plata contra todos los posibles problemas de programación. Por ejemplo, en algunas situaciones, el módulo puede reemplazar las estadísticas extendidas (si no se crea a mano) o no prestará atención a las estadísticas irrelevantes. Pero el módulo no creará los índices necesarios y, además, no reescribirá el texto de la consulta.



Por lo tanto, no debe incluir un módulo para todas las solicitudes. Los candidatos de optimización ideales para AQO son consultas en las que el error del planificador al calcular la cardinalidad de los nodos da como resultado un mal plan. Y, por alguna razón, no es posible influir en el refinamiento de esta estimación.