Nouveautés du planificateur de requêtes de Postgres 16

Nouveautés du planificateur de requêtes de PostgreSQL 16

• PostgreSQL 16 introduit de nombreuses améliorations du planificateur de requêtes, ce qui permet à beaucoup de requêtes SQL de s’exécuter plus rapidement que dans les versions précédentes.
• On peut voir ces améliorations du planificateur dans les notes de version de PG16, mais comme chaque version de PostgreSQL comporte de nombreux changements, il est difficile de fournir une explication détaillée pour chacun d’eux.
• Cet article de blog propose une analyse approfondie de 10 améliorations apportées au planificateur de requêtes de PostgreSQL 16, avec une comparaison des sorties du planificateur entre PG15 et PG16 ainsi que des exemples des changements effectués.

10 améliorations du planificateur de requêtes de PostgreSQL 16

• Tri incrémental : introduit pour la première fois dans PostgreSQL 13, le tri incrémental exploite le fait qu’une partie de l’ensemble de résultats est déjà triée sur une ou plusieurs colonnes de tête, et n’effectue alors le tri que sur les colonnes restantes.
• Agrégations utilisant des données triées : le planificateur de requêtes de PostgreSQL 16 tente désormais de construire des plans qui alimentent les nœuds d’agrégation avec des lignes déjà dans l’ordre trié.
• Mémoïsation : introduit pour la première fois dans PostgreSQL 14, le nœud de plan de mémoïsation agit comme une couche de cache pour éviter les recherches de valeurs dupliquées.
• Anti-join : PostgreSQL 16 permet de hacher la plus petite table lors de l’exécution d’un anti-join.
• Jointure de hachage parallèle : PostgreSQL 16 prend en charge les jointures de hachage parallèles pour les types de jointure FULL et RIGHT.
• Optimisation des fonctions de fenêtre : PostgreSQL 16 permet d’utiliser des fonctions de fenêtre plus rapides en mode ROWS qu’en mode RANGE.
• Optimisation des fonctions de fenêtre toujours croissantes : PostgreSQL 16 étend l’optimisation à des fonctions de fenêtre telles que ntile(), cume_dist() et percent_rank().
• Élimination de jointure sur les tables partitionnées : PostgreSQL 16 autorise l’optimisation de suppression de LEFT JOIN sur les tables partitionnées.
• Utilisation de Limit pour les requêtes DISTINCT : le planificateur de requêtes de PostgreSQL 16 n’inclut pas de nœud de plan pour dédupliquer les résultats lorsqu’il peut détecter que toutes les lignes contiennent la même valeur.
• Assouplissement des règles pour Merge Join : lorsqu’il évalue un Merge Join, le planificateur de requêtes de PostgreSQL 16 vérifie désormais qu’au moins une colonne de tête est correctement triée, au lieu d’exiger une correspondance exacte de l’ordre des lignes.

L’avis de GN⁺

• Les améliorations du planificateur de requêtes de PostgreSQL 16 jouent un rôle important dans l’augmentation des performances de la base de données. En particulier, des fonctionnalités comme le tri incrémental et la mémoïsation permettent d’exécuter plus efficacement des requêtes complexes.
• Ces améliorations seront très utiles aux développeurs et administrateurs de bases de données qui utilisent PostgreSQL, en particulier dans les systèmes manipulant de grands volumes de données, où les gains de performance devraient être perceptibles.
• Les efforts continus d’innovation et d’amélioration de la communauté PostgreSQL stimulent l’évolution des technologies de bases de données open source, offrant ainsi de meilleures solutions de gestion des données aux utilisateurs et aux entreprises.

[GN⁺]

Avis sur Hacker News

• Certains estiment qu’il serait souhaitable que le planificateur de requêtes de Postgres puisse replanifier une requête en cours d’exécution. Le manque d’informations sur la distribution des données peut conduire à des plans de requête inefficaces, avec un impact important sur le temps d’exécution. Si une requête progresse plus lentement que prévu, une fonctionnalité permettant de la replanifier à partir des informations d’avancement actuelles serait utile. Cependant, comme Postgres prend en charge les requêtes en streaming, modifier le plan en cours d’exécution exigerait des changements d’infrastructure considérables.
• Un utilisateur indique utiliser explain.dalibo.com et www.pgexplain.dev comme outils de visualisation de requêtes. Les deux fournissent des résultats de sortie similaires.
• Certains soulignent que l’amélioration du planificateur de requêtes est un aspect important d’une base de données, mais qu’on le remarque surtout lorsqu’il ne fonctionne pas comme on le souhaite. Une expérience partagée indique que, dans les versions récentes de Postgres, le compilateur JIT (Just-In-Time) ne semble pas avoir des heuristiques d’activation très robustes et peut ralentir les petits jeux de données, au point qu’il peut être préférable de désactiver le JIT.
• Un commentaire se demande à quelle fréquence les changements sont réellement efficaces sur des requêtes réelles, en particulier la modification consistant à « utiliser Limit au lieu de DISTINCT lorsque c’est possible », et si ce cas se présente vraiment en pratique. Il demande aussi si les développeurs de Postgres disposent d’informations à ce sujet.
• Certains aimeraient disposer d’un « mode strict » pour les tests d’applications. Dans ce mode, une erreur serait renvoyée s’il n’existe aucun index permettant d’améliorer une requête, et il serait possible de créer les index nécessaires via une commande du type CREATE INDICES FOR <sql>. Un mode de création automatique d’index est aussi proposé pour le développement et l’usage interactif.
• Un utilisateur raconte qu’un de ses amis travaille comme DBA Microsoft pour des PME et affirme qu’on ne peut pas faire de travail sérieux avec Postgres. Il dit aussi avoir été surpris d’apprendre que Postgres n’aurait pas de planificateur de requêtes, ce qui est incorrect. Le commentaire s’interroge sur la crédibilité de l’affirmation selon laquelle MSSQL pourrait gérer des charges plus importantes que Postgres.
• Une question est posée sur les raisons pour lesquelles Postgres n’implémente pas de hints.
• Un commentaire demande pourquoi la fonctionnalité annoncée par Citus Data a été publiée ailleurs que sur postgresql.org, et s’il s’agit d’une fonctionnalité payante ou d’une extension open source.
• Une question est posée sur le moment où Postgres peut utiliser un index pour accélérer une requête IS NOT DISTINCT FROM.
• Un commentaire a été signalé et marqué comme flagged.