[2/4] Optimisation des requêtes : du parsing aux plans d’exécution

Chaque requête SQL suit un véritable parcours interne. Connaitre ce processus est la clé pour comprendre les optimisations de performances. Et quel hasard, c’est l’idée de cet article !

1. Cycle d’exécution d’une requête

Pour qu’une requête soit exécutée, elle passe par 4 étapes : Parser, rewriter, planner, executor.

1.1 Le parser

Son rôle est de traduire la requête SQL écrite sous forme de texte, en une structure de données interne que PostgreSQL peut comprendre et manipuler. Le parser va aussi valider la syntaxe de votre requête. Et pour faire ces actions, il va exécuter trois opérations :

• Analyse lexicale (tokenisation) Il découpe la requête SQL en “tokens” (mots-clés, noms de tables, opérateurs, etc.).
• Analyse syntaxique : Il vérifie si la requête respecte la grammaire du SQL, comme un compilateur le ferait avec du code source.
• Génération d’un arbre syntaxique (parse tree) c’est une représentation de la requête qui sert de base pour les étapes suivantes.

1.2 Le rewriter

Le rewriter peut être vu comme un mapper : il prend l’arbre syntaxique généré par le parser et applique des règles de réécriture pour produire un ou plusieurs nouveaux arbres. Ces règles sont souvent internes dictées par PostgreSQL mais peuvent être définies par l’utilisateur si l’envie vous en prend.

Le cas d’usage principal est la réécriture des vues.
Lorsque vous créez une vue :

CREATE VIEW v AS SELECT …;

PostgreSQL crée automatiquement une règle INSTEAD OF SELECT qui réécrit toute requête sur la vue en une requête équivalente portant sur les tables sous-jacentes.

Par exemple :

SELECT * FROM vue;

est réécrit en :

SELECT … FROM tables réelles …

Ce qu’il faut retenir, c’est que le rewriter ne modifie pas le sens logique de la requête, mais peut transformer sa structure. Il ne réécrit pas les requêtes dans un but d’optimisation, mais pour répondre à un besoin structurel : l’implémentation des vues, des règles utilisateur, ou certaines garanties de sécurité.

1.3 Le Planner

C’est l’étape centrale d’optimisation du traitement des requêtes SQL. C’est lui qui transforme une requête SQL valide en plan d’exécution optimal.

• Prend une requête logique (issue du rewriter)
• Transformation logique : le planner commence par simplifier la requête (ex : pousser les conditions dans les sous-requêtes, fusionner les WHERE inutiles, etc.).
• Génération des plans : Il explore différentes stratégies d’exécution : ordres de jointure, types de jointure, parcours de table (seq scan, index scan) et créé des plans pour chacune de ses méthodes.
• Estimation des coûts : chaque plan est évalué selon les statistiques sur les tables (pg_stats), la sélectivité des filtres, le coût des lectures disque, l’utilisation mémoire/CPU. Pour chaque plan est donc attribué un coût qui est exprimé en unités et non en temps réel.
• Principaux paramètres : seq_page_cost (lecture séquentielle), random_page_cost (lecture aléatoire), cpu_tuple_cost (traitement d’une ligne), cpu_operator_cost (évaluation d’une condition).
• Le coût total = coût de démarrage (initialisation) + coût d’exécution (pour toutes les lignes).
• Les calculs prennent en compte le nombre de pages à lire, le nombre de tuples estimés, et le coût CPU par tuple ou opération. (C’est ce coût que vous retrouverez dans le explain)
• Pour finir, il choisit le meilleur plan, celui avec le coût total estimé le plus bas

1.4 L’executor

C’est la dernière étape de traitement d’une requête SQL dans PostgreSQL. Son nom est assez explicite mais dans le doute : c’est lui qui exécute concrètement le plan choisi par le planner.

2. Les statistiques

Ok, on y voit plus clair sur le processus. On vient de voir que le Planner génère des plans et calcul les coûts de chaque plan en se basant sur les statistiques de la base de données. Mais de quoi parle-t-on quand on parle de statistiques ?

Ce sont en fait des tables du catalogue système, c’est-à-dire les métadonnées internes au SGBD. Concrètement de simples tables que vous pouvez aller requêter comme n’importe quelle table (je vous invite d‘ailleurs à le faire).

Voici quelques tables clés utilisées dans PostgreSQL par exemple :

Je vous invite à tester sur vos DB pour aller explorer ces différentes tables.

Voici un exemple de ce qu’on trouve dans pg_stats, qui est une vue dérivée de pg_statistic :

• null_frac: fraction de valeurs NULL
• n_distinct: nombre de valeurs distinctes. Pour comprendre le négatif, le mieux est de faire un exemple : Disons que nous avons une table clients avec 1 000 lignes.
• n_distinct = 5 Cela signifie qu’il y a exactement 5 valeurs différentes dans la colonne.
• n_distinct = -0.5 Cela signifie qu’il y a environ 0.5 × 1 000 = 500 valeurs différentes. Le nombre de valeurs distinctes est estimé à 50% du nombre total de lignes.
• most_common_vals: valeurs les plus fréquentes
• histogram_bounds: histogrammes de distribution des valeurs. Exemple : pour un champ age, l’histogram_bounds peut être {18, 25, 55, 75}, cela signifie que PostgreSQL a observé des valeurs de la colonne et sont comprises entre 18 et 75. Et que chaque segment : [18, 25], [25, 55],[55, 75] est censé contenir à peu près le même nombre de lignes. L’optimiseur se sert ensuite de ces bornes pour estimer combien de lignes satisfont une condition donnée.

Prenons un exemple pour voir comment ces statistiques sont utilisés:

SELECT * FROM client WHERE ville = ‘Paris’;

Le Planer va consulter pg_stats :

Si ‘Paris’ est dans most_common_vals, il connaît sa fréquence.

• S’il est très fréquent (ex : 60% des lignes), le Planer peut choisir un Seq Scan.
• S’il est rare (ex : 0.5%), il choisira probablement un Index Scan.

Ces statistiques et leurs valeurs sont donc cruciales pour un bon calcul de plan et donc une requête performante.

Les valeurs sont importantes mais leur exactitude l’est tout autant, il va donc y avoir un gros challenge autour de la mise à jour des statistiques. De base, ces mise à jour, ou recalculs sont lancés automatiquement via l’autovacuum à intervalles réguliers, si une certaine proportion des lignes a été modifiée (INSERT, UPDATE, DELETE).

La fréquence dépend de deux paramètres :

• autovacuum_analyze_threshold (par défaut : 50 lignes)
• autovacuum_analyze_scale_factor (par défaut : 0.1, soit 10%)

Exemple : pour une table de 10 000 lignes, l’analyse est lancée automatiquement si supérieur à 50 + (0.1 × 10 000) = 1 050 lignes modifiées

On peut aussi lancer ce recalcul manuellement avec :

ANALYZE; -- Analyse toutes les tables de la base
ANALYZE ma_table; -- Analyse une seule table
VACUUM ANALYZE ma_table; -- Cumulable avec VACUUM

En bonnes pratiques :

• Laissez autovacuum activé (c’est la meilleure solution dans 95% des cas)
• Si vous avez de grosses modifications par batch, migration… lancez un ANALYZE manuel après ces opérations, cela vous assurera d’avoir les statistiques à jour.
• Si les plans ne suivent pas vos attentes (voir plus bas), regardez si les statistiques sont obsolètes, et relancez leurs calculs. Si elles ne sont jamais remises à jour, vous pouvez changer les paramètres autovacuum_analyze_scale_factor et autovacuum_analyze_threshold pour les déclencher plus régulièrement (par exemple sur des tables très grosses dont les statistiques sont changeantes).

3. Les prepared statements

On a donc un processus en plusieurs étapes avec un système de choix optimisé grâce aux statistiques. Ce processus a quand même beaucoup d’étapes et de logique, qui peuvent paraître redondantes si je fais 10, 100, 1000 fois la même requête à quelques paramètres près.

Il n’existe pas de cache automatique global de plans dans PostgreSQL (contrairement à Oracle ou SQL Server). Il existe un cache shared_buffers qui permet à PostgreSQL de renvoyer les données récemment accédées pour éviter le temps d’accès disque, mais cela ne nous aide pas dans le cas d’une application qui exécute régulièrement, à intervalles espacés, la même requête.

Pour faire cette optimisation, il faut utiliser les Prepared Statements. Voilà un exemple pour mieux comprendre :

PREPARE select_user_by_age(INT) AS
SELECT * FROM users WHERE age = $1;
EXECUTE select_user_by_age(30);
EXECUTE select_user_by_age(45);

La requête SQL est analysée, planifiée et compilée une fois lors du prepapre, puis dans les deux sélections le plan est réutilisé avec différents paramètres. On gagne donc le temps de recalcul du plancher.

On a aussi un gain de sécurité, le plan est préparé une fois avec un paramètre typé. La valeur $1 est transmise comme donnée, mais ne sera jamais interprétée comme du SQL. Si on envoie ‘1 OR 1=1’ on aura juste une erreur de type, l’injection ne marchera pas.

Voilà pour les grandes lignes, mais lorsque l’on creuse dans la documentation, on découvre une subtilité sur ce fonctionnement. Un prepared statement peut être exécuté de deux façons différentes : via un generic plan ou un custom plan.

Un generic plan est le même plan pour toutes les exécutions, là où le custom plan est généré pour chaque exécution en fonction des paramètres.

L’usage du plan générique est le cas que nous avons vu plus haut (et celui auquel on s’attend), il évite le temps de calcul des étapes de parser / rewrite / plan, mais dans certains cas, le plan custom sera plus performant. En effet, le planner peut utiliser ses connaissances grâce aux statistiques et changer de stratégie selon le paramètre passé au prepare statement. (exemple vu plus haut d’un param plus ou moins filtrant, le paramètre très filtrant utilisera l’index, et le moins filtrant un sequential scan).

Comment Postgres choisit entre ses deux options ?

Par défaut, (si plan_cache_mode est set sur auto), postgres va tester pour les cinq premières requêtes en custom, calculer la moyenne des coûts estimés puis un generic plan est créé et son coût comparé à la moyenne des custom plan. Cela détermine si les prochaines exécutions utilisent ou non le generic plan.

Il est possible de vérifier quel plan est utilisé avec la commande Explain; s’il contient des symboles $***n***, c’est que le plan est générique.

Concrètement, votre optimisation ne sera active qu’après 5 exécutions de votre prepare statement.

Les prepared statements PostgreSQL créés via PREPARE durent le temps de la session (connexion). Comme nous utilisons souvent un pool de connexions, l’optimisation ne se fera quasiment jamais. Ce prepare est donc très adapté si vous faites des batchs ou énormément d’appels à la même requête avec des paramètres différents dans la même méthode.

Bonus : dans PL/pgSQL (le langage procédural intégré à PostgreSQL), les plans sont implicitement préparés et mis en cache à plus long terme (durée de vie du plan = durée de vie du code fonctionnel). Cela peut donc être intéressant de faire vos propres fonctions postgres dans certains cas.

Et Hibernate dans tout ça ?

Côté Hibernate, on utilise aussi le terme prepared statement, mais il n’a pas la même signification.

Query q = session.createQuery("FROM Utilisateur WHERE age = :age");
q.setParameter("age", 30);

Si vous passez par JPA, c’est le même fonctionnement qui sera utilisé.

Hibernate génère une requête SQL avec des ? à la place des paramètres nommés :

SELECT * FROM utilisateurs WHERE age = ?

• Hibernate appelle Connection.prepareStatement(…) (JDBC).
• JDBC crée un PreparedStatement ****Java, qui encapsule la requête SQL paramétrée (?)
• Le driver JDBC PostgreSQL transforme cette requête en protocole binaire PostgreSQL :
• Client → Server: Parse (nom = “”, requête SQL = “SELECT * FROM … WHERE age = $1”)
• Client → Server: Bind (valeurs = [30])
• Client → Server: Execute
• Client → Server: Sync

Pour résumer, les valeurs sont transmises séparément du SQL. Comme précédemment, cela empêche les injections, mais pas au même niveau ni via le même système que précédemment.

Si vous utilisez Hibernate avec les prepared statements, il n’y aura pas de gain de performance sur le plan d’exécution. Il faudra attendre que JDBC dépasse son propre seuil d’appel pour la même requête avec des paramètres différents avant de demander à Postgres d’utiliser son prepared statement. Et là, il faudra encore attendre cinq autres requêtes pour que Postgres valide le gain de réutiliser un plan générique.

On ne profite donc que rarement de cette optimisation.

Conclusion

On a vu comment Postgres construit et optimise ses requêtes, ainsi que comment, en tant qu’utilisateurs, nous pouvons influencer ces étapes. On retiendra surtout l’importance des tables de statistiques.

Dans le prochain article, on approfondira la notion des plans d’exécution générés par le planner et les optimisations que l’on peut appliquer au niveau des requêtes.