Hints dans PostgreSQL¶

Qu’est-ce qu’un Hint ?¶

Un Hint est un indice qu’on fournit à l’optimiseur de requêtes, afin de l’aider à choisir un meilleur plan d’exécution que celui qu’il aurait choisi lui même. Ils ne sont pas supportés sous PostgreSQL. Nous allons nous intéresser aux hints tels qu’ils sont implémentés sous Oracle, et expliquer pourquoi les développeurs de PostgreSQL les refusent à l’heure actuelle (version 9.1).

Pourquoi un hint ?¶

Parce que parfois l’optimiseur ne choisit pas le bon plan. Par exemple, l’optimiseur va se tromper sur l’estimation de la sélectivité d’un filtre, et choisir un parcours d’index pour récupérer ce qu’il pense être quelques enregistrements, alors qu’en réalité, plusieurs millions d’entre eux vont être ramenés. On peut aussi vouloir prévenir au début d’une requête qu’on ne veut récupérer que les premiers enregistrements.

Les indices servent donc en théorie à signaler au moteur des informations qu’il ne peut pas connaître.

Où est le problème alors ?¶

Le problème, c’est que les hints, tels qu’ils sont, permettent d’optimiser une requête dans un contexte précis, et rien de plus. Si la volumétrie change, les cardinalités entre deux tables sont modifiées, le plan forcé par un hint va non seulement devenir non-optimal, mais peut devenir réellement catastrophique. Un hint est donc habituellement bon pendant les quelques jours, ou quelques semaines où il a été posé, mais devient un risque à long terme.

Un autre problème est davantage lié au fonctionnement de la communauté PostgreSQL: quand on pose un hint, c’est qu’on rencontre une limitation de l’optimiseur. Les développeurs préfèrent avoir un retour d’un utilisateur rencontrant cette limitation et la corriger, que de laisser chaque utilisateur placer des hints sans aucune rétroaction.

Poser des hints « à la Oracle » (c’est-à-dire forçant le plan d’exécution) impose aussi aux développeurs d’appendre à lire la syntaxe ésotérique des hints (les 70, si nécessaire), de maintenir les hints, et de gérer leur mise en place et suppression. Les hints enlèvent une bonne part de l’intérêt de SQL, qui est un langage décrivant la requête, pas son exécution. Par ailleurs, les hints se cachant dans les commentaires, ne génèrent aucun message, aucune trace, de la part du moteur. Une erreur de syntaxe de hint sera silencieusement ignorée, ce qui rend les tests sur eux très difficiles.

Les Hints, tels qu’on les voit à l’heure actuelle, ne sont donc qu’une solution à court terme.

Les hints dans PostgreSQL¶

Il existe quelques mécanismes pour influencer le choix de plan sous PostgreSQL:

les enable_* qui permettent d’activer ou désactiver tel ou tel nœud d’exécution d’un plan. Mais ils ne sont à utiliser qu’en phase de débuggage ;
les réécritures: on peut influencer le moteur par la façon dont une requête est écrite (jointure, exists, in… ;
l’ajout d’opérateurs non-relationnels comme LIMIT, qui trompent l’optimiseur ;
pour l’équivalent de first rows, all rows, on utilise le paramètre cursor_tuple_fraction ;
from_collapse_limit et join_collapse_limit peuvent être utilisés pour forcer les ordres de jointure.

Les développeurs refusent pour le moment d’ajouter les hints d’Oracle, et les arguments cités précédemment sont tout à fait recevables. Il est par contre envisagé de fournir les hints ne décrivant pas comment exécuter la requête, mais permettant de donner à l’optimiseur des informations qu’il ne possède pas. L’un des principaux problèmes actuels, par exemple, est très visible dans une requête de ce genre:

Récupérer la liste des villes du département ALLIER :

marc=# EXPLAIN ANALYZE SELECT * from villes where departement='ALLIER';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on villes  (cost=0.00..799.86 rows=225 width=32) (actual time=0.263..8.932 rows=320 loops=1)
   Filter: (departement = 'ALLIER'::text)
 Total runtime: 9.217 ms
(3 rows)

Jusqu’ici tout va bien: l’optimiseur estime 225 villes, il en récupère 320, il n’y a donc pas de souci.

Quelles sont les villes du département ALLIER, et dont le code postal commence par 03 ? Nous savons que ce sont les même.

marc=# EXPLAIN ANALYZE SELECT * from villes where departement='ALLIER' and codepos like '03%';
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on villes  (cost=0.00..897.24 rows=2 width=32) (actual time=0.270..8.629 rows=320 loops=1)
   Filter: ((codepos ~~ '03%'::text) AND (departement = 'ALLIER'::text))
 Total runtime: 8.922 ms
(3 rows)

Pas le moteur: il pense récupérer 2 enregistrements, il en récupère 320. Le problème est bien évidemment que le code postal est corrélé au département, ce que ne sait pas PostgreSQL. Cette table n’est pas en troisième forme normale (le département dépend directement du code postal), ce qui est la vraie source du problème ici. Mais ce problème de corrélation peut apparaître dans une table correctement normalisée.

On pourrait donc tout à fait imaginer des hints d’un type totalement différent des hints Oracle. Ici on pourrait par exemple indiquer dans la déclaration de la table qu’il y a une corrélation (chiffrable entre -1 et 1 par exemple s’il s’agit d’un facteur de corrélation statistique) entre departement et codepos. Ou au moins un hint au niveau de la requête, indiquant cette corrélation. Mais il y a consensus sur le refus de hints indiquant la façon d’exécuter la requête.

Cette corrélation peut être indiquée au moteur depuis la version 10 avec l’ordre CREATE STATISTICS.

D’autres types de hints sont envisagés, comme de pouvoir indiquer au moteur qu’une jointure va générer beaucoup ou peu d’enregistrements (les contraintes d’unicité, not null, foreign keys participent déjà à ce mécanisme).