pg_hint_plan¶
Dalibo recommande une utilisation restreinte de pg_hint_plan.
Warning
Dans le cadre de notre offre de support, l’utilisation de pg_hint_plan est limitée :
seuls les hints de type GUC sont admis dans le cadre des contrats de support Dalibo,
sauf accord explicite de Dalibo pour des cas spécifiques clairement identifiés.
Les hints de type GUC permettent de positionner des paramètres comme join_collapse_limit ou work_mem pour une
requête, soit en ajoutant un commentaire au texte de la requête, soit en insérant une ligne dans la table hint_plan.hints,
les requêtes étant alors identifiées par leur query_id. C’est cette dernière méthode qui nous intéresse plus particulièrement ici,
puisqu’elle a l’avantage de ne pas avoir à modifier le code applicatif. Pour l’autre méthode, se référer à la
documentation officielle.
Les autres types de hints sont considérés comme une mauvaise pratique par Dalibo, et ne sont considérés qu’en toute dernière extrémité. Voir aussi cette page sur l’utilisation des hints en général.
Installation¶
Cette extension est empaquetée pour Debian, RHEL, et leurs dérivées. Pour installer la version adaptée à PostgreSQL 17 :
ou, pour les dérivées RHEL :
Configuration¶
La configuration suivante doit être ajoutée :
Il sera nécessaire de redémarrer l’instance après.
Ensuite, pour chaque base en ayant besoin :
Paramétrage spécifique à une requête¶
Nous créons une base pgbench pour l’exemple :
La requête suivante utilise un Parallel Index Only Scan avec deux workers :
EXPLAIN (SETTINGS, VERBOSE, COSTS OFF) SELECT aid FROM pgbench_accounts WHERE aid % 5 = 0;
QUERY PLAN
---------------------------------------------------------------------------------------
Gather
Output: aid
Workers Planned: 2
-> Parallel Index Only Scan using pgbench_accounts_pkey on public.pgbench_accounts
Output: aid
Filter: ((pgbench_accounts.aid % 5) = 0)
Query Identifier: 23581355037094665
L’option VERBOSE de EXPLAIN permet de récupérer le query_id (dernière ligne).
Nous souhaitons n’autoriser qu’un seul worker pour cette requête. Cela se fait de la façon suivante :
INSERT INTO hint_plan.hints (query_id, application_name, hints)
VALUES (23581355037094665, '', 'Set(max_parallel_workers_per_gather 1)');
Nous voyons que cela fonctionne :
EXPLAIN (SETTINGS, VERBOSE, COSTS OFF) SELECT aid FROM pgbench_accounts WHERE aid % 5 = 0;
QUERY PLAN
---------------------------------------------------------------------------------------
Gather
Output: aid
Workers Planned: 1
-> Parallel Index Only Scan using pgbench_accounts_pkey on public.pgbench_accounts
Output: aid
Filter: ((pgbench_accounts.aid % 5) = 0)
Query Identifier: 23581355037094665
Notez néanmoins que l’option SETTINGS n’affiche pas le paramétrage spécifique. C’est une limitation.
Pour cette même requête, nous souhaitons maintenant augmenter la valeur de random_page_cost,
car les performances de notre stockage cloud sont très mauvaises pour les accès non-séquentiels :
UPDATE hint_plan.hints SET hints = 'Set(max_parallel_workers_per_gather 1) Set(random_page_cost 8)'
WHERE query_id = 23581355037094665;
On voit que ça fonctionne, il y a toujours un seul worker, et on passe sur un Parallel Seq Scan :
EXPLAIN (SETTINGS, VERBOSE, COSTS OFF) SELECT aid FROM pgbench_accounts WHERE aid % 5 = 0;
QUERY PLAN
----------------------------------------------------
Gather
Output: aid
Workers Planned: 1
-> Parallel Seq Scan on public.pgbench_accounts
Output: aid
Filter: ((pgbench_accounts.aid % 5) = 0)
Query Identifier: 23581355037094665
Bonnes pratiques¶
Il est important de documenter l’utilisation de pg_hint_plan. Pour chaque nouvelle ligne ajoutée à la table
hint_plan.hints, il faut (idéalement) documenter la date d’ajout, le texte de la requête associée au query_id, et les
raisons qui expliquent le choix du hint.
En l’absence de documentation, pg_stat_statements permettra
(si la requête est suffisamment fréquente pour ne pas avoir été évincée) de retrouver le texte normalisé de la requête,
ce qui est déjà ça. Quoi qu’il en soit, nous recommandons de toujours laisser pg_stat_statements dans shared_preload_libraries,
quitte à désactiver la collecte des métriques en positionnant le paramètre pg_stat_statements.track à off si l’on souhaite
éviter tout risque de surcharge d’une instance de production. Réactiver la collecte peut alors se faire à chaud, sans avoir
à redémarrer l’instance.
Avec pg_stat_statements chargée, il n’est pas nécessaire de positionner compute_query_id,
la configuration minimale sera alors la suivante :