Advices, hints
(sujet préalablement traité avec la version 9.6)
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" ou "advices", qu’on peut traduire par "indications" ou "conseils" en français. Mais ce ne sont en fait pas des indications, ce sont des directives données à l’optimiseur : "PASSE PAR TEL INDEX !", "UTILISE DES BOUCLES IMBRIQUEES !" etc.
PostgreSQL ne disposait pas nativement d’un tel système et ses développeurs se faisaient donc régulièrement tarabuster à ce sujet. Ils avaient bien raison d'être réticents. 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...les SGBD "Enterprise" ont tous 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 (advices) pour ne pas vivre avec la peur d’être coincé si le planner devient fou. Cela passe depuis PostgreSQL 19 par un module officiel, pg_plan_advice.
Un index B-Tree peut devenir inefficace lors de la recherche sur X% des données, en fonction aussi du taux de corrélation (clustering factor). Nous allons forcer le plan pour obliger PostgreSQL à balayer complètement la table :
select version();
version
-------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 19devel (Debian 19~~devel-3~20260422.2015.gdbf217c1c7c.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 ligne)
load 'pg_plan_advice';
LOAD
create table geants(id integer generated by default as identity, nmg text, masse smallint);
CREATE TABLE
insert into geants(nmg, masse) select CASE WHEN random() <= 0.02 THEN 'GOMGOR' ELSE upper(md5(random()::text)) END, trunc(random() * 50 + 50) from generate_series(1,20000000,1);
INSERT 0 20000000
create index bt_nmg on geants(nmg);
CREATE INDEX
analyse geants;
ANALYZE
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Persistence | Méthode d'accès | Taille | Description
--------+---------------+----------+--------------+-------------+-----------------+------------+-------------
clan | geants | table | postgres | permanent | heap | 1284 MB |
clan | geants_id_seq | séquence | postgres | permanent | | 8192 bytes |
(2 lignes)
\di+
List of indexes
Schéma | Nom | Type | Propriétaire | Table | Persistence | Méthode d'accès | Taille | Description
--------+--------+-------+--------------+--------+-------------+-----------------+---------+-------------
clan | bt_nmg | index | postgres | geants | permanent | btree | 1077 MB |
(1 ligne)
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=266664.96..266664.97 rows=1 width=32)
-> Gather (cost=266664.74..266664.95 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=265664.74..265664.75 rows=1 width=32)
-> Parallel Bitmap Heap Scan on geants (cost=15176.75..265226.54 rows=175279 width=2)
Recheck Cond: (nmg = 'GOMGOR'::text)
-> Bitmap Index Scan on bt_nmg (cost=0.00..15071.58 rows=420669 width=0)
Index Cond: (nmg = 'GOMGOR'::text)
Generated Plan Advice:
BITMAP_HEAP_SCAN(geants)
GATHER(geants)
(11 lignes)
select avg(masse) from geants where nmg='GOMGOR';
avg
---------------------
74.5050851177383072
(1 ligne)
Temps : 438,597 ms
Temps : 450,522 ms
Temps : 463,642 ms
SET pg_plan_advice.advice = 'SEQ_SCAN(geants)';
SET
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
-------------------------------------------------------------------------------------------
Finalize Aggregate (cost=271616.77..271616.78 rows=1 width=32)
-> Gather (cost=271616.55..271616.76 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=270616.55..270616.56 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..270178.35 rows=175279 width=2)
Filter: (nmg = 'GOMGOR'::text)
Supplied Plan Advice:
SEQ_SCAN(geants) /* matched */
Generated Plan Advice:
SEQ_SCAN(geants)
GATHER(geants)
(11 lignes)
select avg(masse) from geants where nmg='GOMGOR';
avg
---------------------
74.5050851177383072
(1 ligne)
Temps : 291,551 ms
Temps : 266,380 ms
Temps : 277,253 ms
set enable_seqscan = off;
SET
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=266664.96..266664.97 rows=1 width=32)
-> Gather (cost=266664.74..266664.95 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=265664.74..265664.75 rows=1 width=32)
-> Parallel Bitmap Heap Scan on geants (cost=15176.75..265226.54 rows=175279 width=2)
Disabled: true
Recheck Cond: (nmg = 'GOMGOR'::text)
-> Bitmap Index Scan on bt_nmg (cost=0.00..15071.58 rows=420669 width=0)
Index Cond: (nmg = 'GOMGOR'::text)
Supplied Plan Advice:
SEQ_SCAN(geants) /* matched, failed */
Generated Plan Advice:
BITMAP_HEAP_SCAN(geants)
GATHER(geants)
(14 lignes)
Oui mais ... (bis)
Voilà, il suffit de charger la librairie pg_plan_advice (cela peut être fait via shared_preload_libraries au niveau cluster) puis d'affecter des consignes à un paramètre pg_plan_advice.advice pour obtenir le résultat souhaité. Il est à noter que l'ensemble des advices (hints) obtenu via explain (pg_plan_advice) sera suffisant pour déterminer les grandes lignes du plan d'exécution. Cela permet en pratique d'obtenir l'équivalent des outlines (puis des baselines à un plan) d'Oracle Database. Fournir l'ensemble des advices via pg_plan_advice.advice permet en gros de livrer le plan en même temps que la requête.
En tous cas, bravo l'advice, nous avons gagné entre 150 et 200ms sur un cas particulier ! Mais il y a un hic. Imaginez que la table accueille les données d’autres clans de géants et que la part des GOMGOR tombe à 0,1% du total. Nous allons continuer à forcer un balayage complet de la table. L'advice n’est en effet PAS un conseil c’est un ORDRE, sauf s'il est impossible à appliquer, par exemple s'il contredit un enable_XXXXX comme le montre la fin de la démonstration (SEQ_SCAN(geants) /* matched, failed */)
Oui mais quand même il a été bien utile cet advice non ? Et bien pas tant que ça. Ici je mens à PostgreSQL. Pas sur le contenu de la table car le planner sait que les GOMGOR représentent 2% 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 enable_seqscan = on;
SET
set random_page_cost=1;
SET
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
-------------------------------------------------------------------------------------------
Finalize Aggregate (cost=271616.77..271616.78 rows=1 width=32)
-> Gather (cost=271616.55..271616.76 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=270616.55..270616.56 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..270178.35 rows=175279 width=2)
Filter: (nmg = 'GOMGOR'::text)
Supplied Plan Advice:
SEQ_SCAN(geants) /* matched */
Generated Plan Advice:
SEQ_SCAN(geants)
GATHER(geants)
(11 lignes)
SET pg_plan_advice.advice = '';
SET
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=175336.50..175336.51 rows=1 width=32)
-> Gather (cost=175336.28..175336.49 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=174336.28..174336.29 rows=1 width=32)
-> Parallel Index Scan using bt_nmg on geants (cost=0.56..173898.08 rows=175279 width=2)
Index Cond: (nmg = 'GOMGOR'::text)
Generated Plan Advice:
INDEX_SCAN(geants clan.bt_nmg)
GATHER(geants)
(9 lignes)
select avg(masse) from geants where nmg='GOMGOR';
avg
---------------------
74.5050851177383072
(1 ligne)
Temps : 170,260 ms
Temps : 169,435 ms
Temps : 169,802 ms
Que s’est-il passé ?!?
La requête avec advice est battue à plate couture ! Nous avons encore gagné autour de 100ms : nous obtenons le résultat en 170ms contre 270ms précédemment. 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 ou advices, 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
SET pg_plan_advice.advice = 'INDEX_SCAN(geants clan.bt_nmg) GATHER(geants)';
SET
explain (plan_advice) select avg(masse) from geants where nmg='GOMGOR';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=682305.62..682305.63 rows=1 width=32)
-> Gather (cost=682305.40..682305.61 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=681305.40..681305.41 rows=1 width=32)
-> Parallel Index Scan using bt_nmg on geants (cost=0.56..680867.20 rows=175279 width=2)
Index Cond: (nmg = 'GOMGOR'::text)
Supplied Plan Advice:
INDEX_SCAN(geants clan.bt_nmg) /* matched */
GATHER(geants) /* matched */
Generated Plan Advice:
INDEX_SCAN(geants clan.bt_nmg)
GATHER(geants)
(12 lignes)
select avg(masse) from geants where nmg='GOMGOR';
avg
---------------------
74.5050851177383072
(1 ligne)
Temps : 170,283 ms
Temps : 168,596 ms
Temps : 167,299 ms
Conclusion
Les hints ou advices sont très rarement intéressants mais ils sont parfaitement utilisables avec PostgreSQL. Ne le dites à personne ;) !
Mise à jour : 23/04/2026