AQO - Optimisation adaptative des requêtes PostgreSQL

Lors de l'exécution de requêtes, les SGBD modernes utilisent un modèle d'optimisation des coûts - basé sur les coefficients stockés dans les fichiers de configuration et les statistiques collectées, le «coût» d'obtention et le volume des ensembles de lignes résultants sont calculés. Lorsque les requêtes sont répétées, le coût et la sélectivité sont recalculés. Vous pouvez exécuter la requête et voir les valeurs réelles de ces paramètres, cependant, dans le processus de replanification (standard), l'optimiseur de SGBD n'utilise en aucune façon ces informations.



Mais que se passe-t-il si l'optimiseur enregistre les valeurs réelles de coût, de sélectivité et d'autres paramètres nécessaires pour exécuter une requête et, lors de sa nouvelle exécution, est guidé non seulement par les statistiques collectées standard, mais également par celles enregistrées après l'exécution précédente?



Cela s'appelle l'optimisation adaptative des requêtes et est prometteur. Certains SGBD utilisent déjà de telles technologies.



Société Postgres Professional travaillant depuis plusieurs années sur l'extension AQO pour PostgreSQL, qui implémente (sous une forme ou une autre) l'optimisation adaptative. Le travail est toujours en cours, mais il y a déjà quelque chose à tester.



Tout d'abord, examinons de plus près le domaine de l'optimisation des requêtes.

Pourquoi le planificateur peut choisir un plan sous-optimal

La requête SQL peut être effectuée de différentes manières. Par exemple, lorsqu'il y a une jointure de deux tables, cela peut être fait de plusieurs manières différentes - en utilisant des boucles imbriquées, la fusion, le hachage. Plus il y a de tables qui participent à la requête, plus il y a de variations dans leurs jointures. La tâche du planificateur est de choisir le plan d'exécution de la requête avec le coût le plus bas parmi de nombreuses variantes.



Comme déjà mentionné, dans leur travail, les planificateurs de nombreux SGBD utilisent des informations statistiques collectées automatiquement ou manuellement. Le planificateur calcule un coût estimé sur la base de ces statistiques.



En général, les planificateurs de SGBD modernes fonctionnent bien dans la plupart des situations. Cependant, dans certains cas, le plan choisi peut être très loin d'être optimal.



Par exemple,le manque d'informations statistiques à jour conduit au fait que le planificateur est guidé dans son travail par des données (très probablement) incorrectes sur le nombre de lignes dans les tables jointes. Une sous-estimation (ou surestimation) excessive de la cardinalité conduit au choix de méthodes non optimales d'accès aux données dans les tableaux.



Une autre raison importante pourrait être l' absence d'index requis . En l'absence d'index, le planificateur dispose d'un choix limité de méthodes d'accès aux données.



Utilisation de conditions dépendantes (corrélées)peut également affecter négativement le fonctionnement du SGBD. Le planificateur (par défaut) suppose que toutes les conditions d'une requête sont indépendantes les unes des autres, c'est-à-dire que la valeur d'une condition n'affecte en aucune façon l'autre. Ce n'est pas toujours le cas. Si des conditions dépendantes sont utilisées (par exemple, code postal et ville), le planificateur calculera également le mauvais coût et la cardinalité des connexions.



Le planificateur peut également être affecté par l' utilisation de fonctions dans l'environnement . Une fonction pour le planificateur est une «boîte noire», il ne sait pas combien de lignes la fonction renverra, ce qui peut également entraîner un coût de plan erroné.

Façons d'influencer le travail de l'ordonnanceur

Des statistiques à jour sont une condition indispensable au bon travail de l'ordonnanceur. Tout d'abord, assurez-vous que le système est configuré pour collecter régulièrement des informations statistiques.

Il existe plusieurs façons de remédier aux situations décrites ci-dessus et d'aider le planificateur à choisir les plans d'exécution de requête les plus optimaux.



Sans index, l'ordonnanceur n'a qu'une seule façon d'obtenir des données: l'analyse séquentielle des tables (et ce n'est pas toujours mauvais et coûteux). Dans certains cas, la création des index nécessaires peut aider à accélérer l'accès aux données - vous n'avez pas à analyser la table entière. Mais l'utilisation d'index (la recherche du nécessaire, la création, la maintenance) n'est pas gratuite. Idéalement, ils devraient être utilisés exactement là où ils sont nécessaires. Et où ce n'est pas nécessaire - ne l'utilisez pas.



Lorsque vous utilisez des conditions de jointure corrélées dans des requêtes, vous pouvez générer des statistiques étendues- «dites» clairement à l'optimiseur que les conditions sont liées les unes aux autres. Pour ce faire, l'administrateur de base de données (ou le développeur) doit bien connaître ses données et surveiller les conditions dépendantes dans les requêtes, car le nombre de combinaisons de colonnes dépendantes est difficile à prédire à l'avance. Des statistiques avancées devront être créées manuellement pour chacune de ces options.



Lors de la création d'une fonction, vous pouvez spécifier le coût d'exécution approximatif et / ou une estimation du nombre de lignes renvoyées par la fonction. Dans la version 12 , il est devenu possible d'utiliser des fonctions d'assistance pour améliorer les estimations du planificateur basées sur des arguments. Il s'agit également d'une méthode manuelle, qui ne donne pas toujours le meilleur résultat.



Si tout le reste échoue, vous pouvez réécrire manuellement la demande, par exemple, en utilisant des vues matérialisées, des expressions de table communes (CTE). Ou pour clarifier les exigences du domaine et, éventuellement, réécrire radicalement la logique de requête.



Et il y a une autre façon de « soupçon » le planificateur - l' optimisation des requêtes d' adaptation ( une daptive q uery o ptimization). L'idée derrière cette méthode est qu'après l'exécution de la requête, les informations statistiques réelles sont stockées et, lorsque la requête donnée (ou similaire) est à nouveau exécutée, l'optimiseur peut s'y fier.



Le SGBD Postgres Pro Enterprise est une extension pour l'optimisation adaptative des requêtes appelée AQO... Cette extension est publiée sur github: github.com/postgrespro/aqo , vous pouvez l'essayer avec vanilla PostgreSQL, plus à ce sujet ci-dessous.

Comment fonctionne le module

Le module AQO utilise l'apprentissage automatique dans son travail. Vous pouvez en savoir plus sur le principe de fonctionnement dans un article d'Oleg Ivanov Utilisation du machine learning pour augmenter la productivité de PostgreSQL et plus en détail dans la présentation Optimisation adaptative des requêtes (rapport sur YouTube ).



L'essence de cette méthode est brièvement décrite ci-dessous:



Pour estimer le coût, le planificateur a besoin d'une estimation de la cardinalité, et pour cela, à son tour, une estimation de la sélectivité des conditions est nécessaire.



Pour des conditions simples (telles que «attribut = constante» ou «attribut> constante»), l'ordonnanceur dispose d'un modèle par lequel il estime la sélectivité. Pour ce faire, il utilise des informations statistiques: nombre de valeurs d'attributs uniques, histogrammes, etc.

Pour les conditions composées d'éléments simples utilisant des connecteurs logiques, le planificateur applique des formules facilement calculées:

• sel (pas A) = 1 - sel (A)
• sel (pas A) = 1 - sel (A)
• sel (A et B) = sel (A) * sel (B)
• sel (A ou B) = sel (pas (pas A et pas B)) = 1 - (1 - sel (A)) * (1 - sel (B))

Ces formules supposent l'indépendance (non-corrélation) des conditions A et B, en raison de laquelle nous obtenons des estimations incorrectes dans le cas où cette hypothèse est violée.

AQO complique la formule: introduit son propre coefficient pour chaque condition simple. En utilisant l'apprentissage automatique (en utilisant la régression du plus proche voisin), AQO sélectionne ces coefficients de sorte que la sélectivité calculée par la formule corresponde le mieux à la sélectivité réelle qu'AQO a observée précédemment.



Pour cela, le module enregistre:

• , ;
• .

Dans ses travaux, l'AQO distingue les conditions jusqu'à des constantes. Cela permet de réduire la complexité du problème à résoudre, et d'ailleurs, dans la plupart des cas, l'information n'est toujours pas perdue: AQO ne «voit» pas la valeur de la constante, mais il «voit» la sélectivité de la condition.

La situation dans laquelle la perte se produit toujours est les conditions qui sont évaluées en tant que constante quelles que soient les valeurs spécifiques. Par exemple, pour certaines conditions, le planificateur ne peut pas faire d'estimations raisonnables et choisit une constante par défaut (par exemple, la sélectivité de la condition "expression1 = expression2" est toujours évaluée à 0,005 et "expression1> expression2" est toujours évaluée à 1/3).



Ainsi, AQO améliore l'estimation de la sélectivité des conditions complexes (et, par conséquent, l'estimation des coûts, ce qui peut conduire au choix d'un plan d'exécution plus adéquat).

Installation du module

Pour essayer les fonctionnalités du module sur PostgreSQL vanilla, vous devez utiliser un correctif spécial, puis construire le système à partir des sources. Pour en savoir plus, lisez le fichier README sur github.



Si Postgres Pro Enterprise est utilisé, l'installation du module AQO se fera en mode standard:



shared_preload_libraries = 'aqo'



Après cela, vous pouvez créer une extension dans la base de données requise.

Préparation de la base de données

Regardons un exemple concret du fonctionnement du module AQO dans une base de données de démonstration . Nous utiliserons une grande base de données contenant des informations sur les vols pour l'année de septembre 2016 à septembre 2017.



Commencez par créer une extension:

CREATE EXTENSION aqo;

Ensuite, désactivez le traitement des requêtes parallèles afin que l'affichage des plans parallèles ne distrait pas de la tâche principale:



max_parallel_workers_per_gather = 0;



pour que le planificateur PostgreSQL ait plus d'options pour joindre des tables, créez deux index:

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

Lors de l'analyse des résultats, nous nous concentrerons sur la valeur de BUFFERS comme le nombre de pages à lire pour faire le travail. Examinons également le temps d'exécution (mais le temps sur un système chargé et sur un ordinateur portable à la maison peut être très différent).



Augmentons le cache tampon et work_mem pour que tout le travail soit effectué en RAM:



shared_buffers = '256MB';

work_mem = '256MB';

Utilisation du module AQO

Formulons une demande: vous devez obtenir des passagers qui ont volé en classe affaires à partir d'une certaine date et qui sont arrivés avec un retard ne dépassant pas une heure.

Nous exécutons la demande sans utiliser AQO (ci-après, une partie des informations qui n’affectent pas la compréhension du fonctionnement du module a été supprimée des plans):

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';

Et voyons le plan résultant: dans ce cas, le planificateur a considéré le plan optimal, dans lequel, d'abord, en utilisant un scan bitmap ( ), nous obtenons un ensemble de lignes de la table des vols, que nous avons haché (un nœud ) avec un ensemble de lignes de la table ticket_flights obtenu à l'aide de l'index scan ( ). Le résultat résultant sera utilisé comme l'ensemble de lignes externe pour la jointure de boucle imbriquée finale (nœud ). L'ensemble interne de cette jointure est obtenu à l'aide d'un balayage d'index exclusif de la table tickets ( ). L'opération la plus «volumineuse» consiste à obtenir un ensemble de lignes interne pour la boucle imbriquée - 106 205 tampons y sont lus.



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





Ce plan peut être qualifié de relativement bon, car une boucle imbriquée rejoint un nombre relativement faible de lignes dans l'ensemble externe.



Maintenant, faisons une expérience et voyons comment le plan proposé changera (ou ne changera pas) en fonction du changement des dates de la demande. Les dates sont sélectionnées de manière à augmenter séquentiellement la plage de lignes de la table Vols qui satisfont à la condition, ce qui conduit à une erreur de planification dans l'évaluation de la cardinalité d'accès à cette table. Dans le plan ci-dessus, vous pouvez voir qu'avec la première date, l'optimiseur ne se trompe presque pas dans la cardinalité ( ). Remplaçons une à une les dates suivantes dans la requête:Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 01/04/2017
• 01/01/2017
• 01/08/2016

Et voyons le résultat:





Si vous collectez un bref résumé, sans utiliser le module AQO, le planificateur fonctionne comme suit:



Regardons à nouveau le résultat:

Avec AQO activé, le planificateur se rend compte rapidement qu'il doit passer d'une jointure de boucle imbriquée et d'une utilisation d'index à une jointure par hachage et une analyse séquentielle.

Résumer

L'utilisation du module AQO pour l'optimisation adaptative des requêtes présente à la fois des avantages et des inconvénients.



L'un des avantages de l'utilisation d'un module est que vous n'avez pas à suivre les conditions dépendantes dans les requêtes. Dans certains cas, la vitesse d'exécution des requêtes peut augmenter. Et il existe différents modes d'utilisation du module. Par exemple, vous pouvez utiliser AQO pour optimiser uniquement certains types de requêtes.



Parmi les inconvénients du module, on peut distinguer des frais généraux supplémentaires pour la formation et la sauvegarde des statistiques dans les structures du module. Et les informations statistiques collectées par le module ne sont pas transférées vers des répliques.



Le module AQO n'est pas une solution miracle contre tous les problèmes de planification possibles. Par exemple, dans certaines situations, le module peut remplacer les statistiques étendues (si vous ne les créez pas à la main), ou ne fera pas attention aux statistiques non pertinentes. Mais le module ne créera pas les index nécessaires et, de plus, ne réécrira pas le texte de la requête.



Par conséquent, vous ne devez pas activer le module pour toutes les demandes. Les candidats d'optimisation idéaux pour AQO sont des requêtes où l'erreur du planificateur dans le calcul de la cardinalité des nœuds se traduit par un mauvais plan. Et, pour une raison quelconque, il n'est pas possible d'influencer le raffinement de cette estimation.