Hints

Un cadeau empoisonné

      L’optimiseur (planner) choisit pour vous le meilleur plan d’exécution pour chaque requête en fonction de statistiques sur les données et autres informations. Il est libre de ses choix sauf si vous le contraignez à faire ce ce que vous voulez. Certains SGBD disposent pour cela de "hints", qu’on peut traduire par "indications" en français. Mais ce ne sont pas des indications, ce sont des directives données à l’optimiseur : "PASSE PAR TEL INDEX !", "UTILISE DES BOUCLES IMBRIQUEES !" etc.
      PostgreSQL ne dispose pas nativement d’un tel système et ses développeurs se font donc régulièrement tarabuster à ce sujet. Ils ont bien raison de ne pas céder. J’utilise Oracle Database depuis très longtemps et j’ai souvent eu à résoudre des problème liés à des hints mal positionnés. Il y a tellement de moyens plus souples pour résoudre un problème de plan d’exécution, à commencer par une adaptation des statistiques. Il est rarissime que les hints soient indispensables et fréquent qu’ils détériorent les temps de réponse sur le long terme. Jonathan Lewis est sans doute le meilleur expert Oracle du CBO (cost based optimizer) et il est CONTRE le positionnement de hints dans le code applicatif. Par conséquent oubliez les ! Fin de l’article.

Oui mais ...

      Oui mais...le PostgreSQL "Enterprise" d’EDB a des hints ! C’est quand même bien la preuve que c’est utile ! Hmmmm peut-être...mais c’est peut-être aussi la preuve qu’il faut avoir la même liste de fonctionnalités que les autres, même si la fonctionnalité en question est plus nuisible qu’utile.
      Oui mais...rien ne peut vous convaincre. Il vous FAUT les hints pour ne pas vivre avec la peur d’être coincé si le planner devient fou. Nous allons donc montrer dans la suite de cet article que PostgreSQL dispose en pratique de cette fonctionnalité. Cela passe par une extension dont l’installation est très simple :

cd /tmp git clone https://github.com/ossc-db/pg_hint_plan.git cd /tmp/pg_hint_plan make make install

      Je reprends l’exemple de cet article au cours duquel nous avons vu que le plan utilisant le B-Tree était inefficace lors de la recherche sur 10% des données. Nous allons forcer le plan pour obliger PostgreSQL à balayer complètement la table :

select version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) load 'pg_hint_plan'; LOAD create table population(id serial, nom_marital text, masse smallint); CREATE TABLE insert into population(nom_marital, masse) select CASE WHEN random() < 0.1 THEN 'LEFEVRE' ELSE upper(md5(random()::text)) END, trunc(random() * 50 + 50) from generate_series(1,20000000,1); INSERT 0 20000000 create index bt_nmr on population(nom_marital); CREATE INDEX Temps : 251284,358 ms analyze population; ANALYZE Temps : 547,186 ms \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------------+----------+--------------+------------+------------- public | population | table | postgres | 1260 MB | public | population_id_seq | séquence | postgres | 8192 bytes | (2 lignes) \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------+-------+--------------+------------+---------+------------- public | bt_nmr | index | postgres | population | 1057 MB | (1 ligne) explain select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN --------------------------------------------------------------------------------------- Aggregate (cost=260268.57..260268.58 rows=1 width=32) -> Bitmap Heap Scan on population (cost=69211.56..255298.56 rows=1988000 width=2) Recheck Cond: (nom_marital = 'LEFEVRE'::text) -> Bitmap Index Scan on bt_nmr (cost=0.00..68714.56 rows=1988000 width=0) Index Cond: (nom_marital = 'LEFEVRE'::text) (5 lignes) Temps : 0,654 ms select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4423899835696956 (1 ligne) Temps : 5629,823 ms Temps : 5617,879 ms Temps : 5629,656 ms explain /*+ SeqScan(population)*/ select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN --------------------------------------------------------------------------- Aggregate (cost=416206.95..416206.96 rows=1 width=32) -> Seq Scan on population (cost=0.00..411236.95 rows=1988000 width=2) Filter: (nom_marital = 'LEFEVRE'::text) (3 lignes) Temps : 1,070 ms /*+ SeqScan(population)*/ select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4423899835696956 (1 ligne) Temps : 4516,048 ms Temps : 4533,856 ms Temps : 4533,084 ms

Oui mais ... (bis)

      Voilà il suffit de charger la librairie pg_hint_plan puis de préfixer notre requête par le hint /*+ SeqScan(population)*/ pour obtenir le résultat souhaité. Bravo, nous avons gagné 1s1 sur un cas particulier ! Mais il y a un hic. Imaginez que la table accueille les données d’autres régions françaises et que la part des LEFEVRE tombe à 1% du total. Nous allons continuer à forcer un balayage complet de la table. Le hint n’est en effet PAS un conseil c’est un ORDRE.
      Oui mais quand même il a été bien utile ce hint non ? Et bien pas tant que ça. Ici je mens à PostgreSQL. Pas sur le contenu de la table car le planner sait que les LEFEVRE représentent 10% des données. Mais il ne sait pas que ma RAM est suffisante pour stocker toutes les données de ma base. Les I/O aléatoires ne sont donc pas plus coûteuses que les I/O séquentielles. Et si je donnais cette précieuse information au planner ?

set random_page_cost=1; SET postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4423899835696956 (1 ligne) Temps : 1963,659 ms Temps : 1961,370 ms Temps : 1955,968 ms

Que s’est-il passé ?!?

      Ma requête avec hint est battue à plate couture : nous obtenons le résultat plus de 2 fois plus rapidement ! PostgreSQL considère par défaut que les I/O aléatoires (random_page_cost) sont 4 fois plus coûteuses que les I/O séquentielles (seq_page_cost), il estime en effet que les I/O aléatoires sont 40 fois plus lentes par nature mais que nous allons demander des données en cache dans 90% des cas. Je lui dis en ramenant random_page_cost à 1 qu’il peut considérer que tout est en cache, il choisit donc de passer par l’index de manière classique (index scan).
      Si vous êtes vraiment fanatique des hints vous pouvez bien sûr obtenir le "bon" plan (qui n’est bon que parce que le cas est particulier !) en le forçant :

set random_page_cost=4; SET Temps : 0,438 ms explain /*+ IndexScan(population bt_nmr)*/ select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN ------------------------------------------------------------------------------------------ Aggregate (cost=738059.71..738059.72 rows=1 width=32) -> Index Scan using bt_nmr on population (cost=0.56..733110.63 rows=1979633 width=2) Index Cond: (nom_marital = 'LEFEVRE'::text) (3 lignes) /*+ IndexScan(population bt_nmr)*/ select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4423899835696956 (1 ligne) Temps : 1972,872 ms Temps : 1949,823 ms Temps : 1972,365 ms

Conclusion

      Les hints sont très rarement intéressants mais ils sont parfaitement utilisables avec PostgreSQL. Ne le dites à personne ;) !

Mise à jour : 28/03/2017