Instructions préparées - partie 2

(sujet préalablement traité avec la version 10)

      La préparation des instructions peut permettre d’aller plus loin que l’économie des parses pour les requêtes souvent exécutées. Elle permet dans certains cas d’économiser la phase d’optimisation avec l’utilisation de plans génériques. C’est toujours le cas avec les instructions ne comportant pas de paramètres. Si l’instruction comprend des paramètres, un plan générique peut parfois être utilisé après la 5ème exécution.
      La table utilisée est GEANTS. La 1ère requête testée est une simple recherche via la clé primaire.
      Démonstration avec PostgreSQL 12 devel :

select version(); version ---------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 12devel (Debian 12~~devel~20180828.0458-1~140.gitee0e274.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-4) 8.2.0, 64-bit (1 ligne) create table geants( idg integer generated always as identity primary key, pw int, clan int, pm int, tutelaire int, ere varchar(10), taille int ); CREATE TABLE insert into geants(pw, clan, pm, tutelaire, ere, taille) select case when random() < 0.001 then random()*10000000 else 0 end, 1, case when random() < 0.001 then 1 else 2 end, case when random() < 0.001 then 1 else 2 end, case when random() < 0.001 then 'TAUREAU' else 'LICORNE' end, 250 + random()*250 from generate_series(1,10000000); INSERT 0 10000000 create index geants_i1 on geants(pw); CREATE INDEX vacuum analyze geants; VACUUM PREPARE selpk(int) AS SELECT * FROM geants WHERE idg = $1; PREPARE EXPLAIN ANALYZE EXECUTE selpk(123456); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using geants_pkey on geants (cost=0.43..8.45 rows=1 width=32) (actual time=0.109..0.125 rows=1 loops=1) Index Cond: (idg = 123456) Planning Time: 1.008 ms Execution Time: 0.206 ms ... Planning Time: 0.363 ms Execution Time: 0.184 ms ... Planning Time: 0.287 ms Execution Time: 0.156 ms ... Planning Time: 0.285 ms Execution Time: 0.154 ms ... Planning Time: 0.283 ms Execution Time: 0.155 ms EXPLAIN ANALYZE EXECUTE selpk(123456); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using geants_pkey on geants (cost=0.43..8.45 rows=1 width=32) (actual time=0.058..0.075 rows=1 loops=1) Index Cond: (idg = $1) Planning Time: 0.283 ms Execution Time: 0.495 ms ... Planning Time: 0.044 ms Execution Time: 0.181 ms ... Planning Time: 0.060 ms Execution Time: 0.225 ms ... Planning Time: 0.034 ms Execution Time: 0.134 ms ... Planning Time: 0.044 ms Execution Time: 0.184 ms

      Tout se passe comme prévu. Après la 5ème exécution, un plan générique est utilisé. Cela peut être identifié par le fait que nous obtenons idg=$1 au lieu de idg=valeur dans la représentation affichée par la commande EXPLAIN. Une fois qu’il est adopté, le plan générique est utilisé pour toute nouvelle exécution de l’instruction préparée jusqu’à sa désaffectation, sauf action DDL concernant les tables de la requête ou modification du chemin de recherche (search_path)
      La crainte au sujet des plans génériques est de ne pas toujours avoir le meilleur plan possible dans le contexte d’exécution de la requête. Cette fois, nous allons tester une requête comprenant un filtrage sur la colonne pw. La valeur 0 est surreprésentée par rapport aux autres valeurs possibles au niveau de cette colonne.
      Démonstration :

PREPARE selpwpm(int) AS SELECT avg(taille) FROM geants WHERE pw = $1; PREPARE EXPLAIN EXECUTE selpwpm(1); QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=22.40..22.41 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.43..21.56 rows=333 width=4) Index Cond: (pw = 1) EXECUTE selpwpm(1); avg ----- (1 ligne) Temps : 1,354 ms Temps : 1,270 ms Temps : 1,263 ms Temps : 1,350 ms Temps : 1,234 ms Temps : 1,496 ms EXPLAIN EXECUTE selpwpm(0); QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.69..137021.70 rows=1 width=32) -> Gather (cost=137021.47..137021.68 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136021.47..136021.48 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.75 rows=4163089 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false (10 lignes) Temps : 98,635 ms EXPLAIN EXECUTE selpwpm(0); QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.69..137021.70 rows=1 width=32) -> Gather (cost=137021.47..137021.68 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136021.47..136021.48 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.75 rows=4163089 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false (10 lignes) Temps : 4,371 ms EXPLAIN EXECUTE selpwpm(0); QUERY PLAN ------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=13662.66..13662.67 rows=1 width=32) -> Gather (cost=13662.44..13662.65 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=12662.44..12662.45 rows=1 width=32) -> Parallel Index Scan using geants_i1 on geants (cost=0.43..12276.64 rows=154322 width=4) Index Cond: (pw = $1) (6 lignes) Temps : 1,240 ms EXECUTE selpwpm(0); avg ---------------------- 375.0323006258066073 (1 ligne) Durée : 2297,591 ms (00:02,298) explain SELECT avg(taille) FROM geants WHERE pw = 0; QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.69..137021.70 rows=1 width=32) -> Gather (cost=137021.47..137021.68 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136021.47..136021.48 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.75 rows=4163089 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false SELECT avg(taille) FROM geants WHERE pw = 0; avg ---------------------- 375.0323006258066073 (1 ligne) Durée : 1312,166 ms (00:01,312) explain EXECUTE selpwpm(1); QUERY PLAN ------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=13662.66..13662.67 rows=1 width=32) -> Gather (cost=13662.44..13662.65 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=12662.44..12662.45 rows=1 width=32) -> Parallel Index Scan using geants_i1 on geants (cost=0.43..12276.64 rows=154322 width=4) Index Cond: (pw = $1) (6 lignes) Temps : 1,096 ms EXECUTE selpwpm(1); avg ----- (1 ligne) Temps : 169,332 ms Temps : 168,055 ms Temps : 170,208 ms ...


      Nous finissons par obtenir un plan générique basé une utilisation classique (index scan) d’un index B-Tree, effectuée en parallèle. Ce plan est relativement adapté pour $1=0 même s’il n’apporte ici aucun gain par rapport à un balayage complet en parallèle de la table (seq scan) . Mais le problème est surtout qu’il est inutile et même néfaste avec toute autre valeur du paramètre $1. Les exécutions pour $1=1 s’achevaient en effet en moins de 2ms avant l’adoption du plan générique et en plus de 160ms après son adoption.
      Un rapport de plus de 1 à 100, cela incite fortement à réfléchir. Utiliser des instructions préparées ne donne pas toujours des performances optimales lors de la phase d’exécution en raison de l’adoption d’un plan générique.
      Quelles sont les pistes pour éviter ce problème si nous devons exécuter dans la même session des requêtes nécessitant des plans différents en fonction du contexte ?
      Nous pouvons bien sûr ne pas utiliser d’instructions préparées et/ou de bind variables lorsque les valeurs sont ou pourront être distribuées de manière très inégale dans une colonne utilisée au niveau de la clause de filtrage ou de jointure. Attention alors à ne pas rendre le code vulnérable aux injections SQL.
      Il existe heureusement d'autres solutions. PostgreSQL 12 introduit un paramètre plan_cache_mode permettant de contrôler directement l'utilisation des plans mis en cache. Démonstration :

set plan_cache_mode=force_custom_plan; SET explain EXECUTE selpwpm(1); QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=22.40..22.41 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.43..21.56 rows=333 width=4) Index Cond: (pw = 1) (3 lignes) EXECUTE selpwpm(1); avg ----- (1 ligne) Temps : 1,346 ms Temps : 0,979 ms Temps : 1,277 ms set plan_cache_mode=force_generic_plan; explain EXECUTE selpwpm(1); QUERY PLAN ------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=13662.62..13662.63 rows=1 width=32) -> Gather (cost=13662.40..13662.61 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=12662.40..12662.41 rows=1 width=32) -> Parallel Index Scan using geants_i1 on geants (cost=0.43..12276.60 rows=154321 width=4) Index Cond: (pw = $1) (6 lignes) set plan_cache_mode=auto; explain EXECUTE selpwpm(1); QUERY PLAN ------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=13662.62..13662.63 rows=1 width=32) -> Gather (cost=13662.40..13662.61 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=12662.40..12662.41 rows=1 width=32) -> Parallel Index Scan using geants_i1 on geants (cost=0.43..12276.60 rows=154321 width=4) Index Cond: (pw = $1) (6 lignes) PREPARE selpkbis(int) AS SELECT * FROM geants WHERE idg = $1; PREPARE set plan_cache_mode=force_generic_plan; SET EXPLAIN ANALYZE EXECUTE selpkbis(123456); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using geants_pkey on geants (cost=0.43..8.45 rows=1 width=32) (actual time=0.101..0.118 rows=1 loops=1) Index Cond: (idg = $1) Planning Time: 0.324 ms Execution Time: 0.208 ms


      Le paramètre plan_cache_mode offre bien un contrôle direct sur l'utilisation des plans mis en cache pour les instructions préparées.
      La valeur par défaut, auto, suit la règle des 5 exécutions et le comportement présenté au début de la page.
      En le positionnant avec la valeur force_custom_plan avant d'exécuter selpwpm avec la valeur 1, nous avons forcé le calcul d'un plan ad-hoc et évité le problème rencontré précédemment.
      A l'inverse, il est possible d'adopter immédiatement un plan générique sans attendre 5 exécutions en positionnant le paramètre à force_generic_plan.

      La solution peut aussi parfois se situer au niveau de la définition du schéma. Ici, à moins éventuellement d’avoir beaucoup de requêtes pouvant tirer parti de l’index only scan, il ne semble pas indispensable d’indexer les valeurs 0 pour la colonne pw.
      Démonstration :

set plan_cache_mode=auto; SET \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-------------+-------+--------------+--------+--------+------------- public | geants_i1 | index | postgres | geants | 214 MB | public | geants_pkey | index | postgres | geants | 214 MB | drop index geants_i1; DROP INDEX create index geants_i1 on geants(pw) where pw <> 0; CREATE INDEX \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-------------+-------+--------------+--------+--------+------------- public | geants_i1 | index | postgres | geants | 232 kB | public | geants_pkey | index | postgres | geants | 214 MB | PREPARE selpwpm(int) AS SELECT avg(taille) FROM geants WHERE pw = $1; PREPARE EXPLAIN EXECUTE selpwpm(1); QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=22.25..22.26 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.29..21.41 rows=333 width=4) Index Cond: (pw = 1) (3 lignes) EXECUTE selpwpm(1); avg ----- (1 ligne) Temps : 1,455 ms Temps : 1,844 ms Temps : 1,291 ms Temps : 1,455 ms Temps : 1,321 ms Temps : 1,241 ms Temps : 1,038 ms ... EXPLAIN EXECUTE selpwpm(0); QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.19..137021.20 rows=1 width=32) -> Gather (cost=137020.97..137021.18 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136020.97..136020.98 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4163055 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false (10 lignes) EXPLAIN EXECUTE selpwpm(0); QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.19..137021.20 rows=1 width=32) -> Gather (cost=137020.97..137021.18 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136020.97..136020.98 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4163055 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false EXPLAIN EXECUTE selpwpm(0); QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.19..137021.20 rows=1 width=32) -> Gather (cost=137020.97..137021.18 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136020.97..136020.98 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4163055 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false EXECUTE selpwpm(0);EXECUTE selpwpm(0); avg ---------------------- 375.0323006258066073 (1 ligne) Durée : 1293,079 ms (00:01,293) explain SELECT avg(taille) FROM geants WHERE pw = 0; QUERY PLAN -------------------------------------------------------------------------------------------- Finalize Aggregate (cost=137021.19..137021.20 rows=1 width=32) -> Gather (cost=137020.97..137021.18 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=136020.97..136020.98 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4163055 width=4) Filter: (pw = 0) JIT: Functions: 8 Inlining: false Optimization: false explain EXECUTE selpwpm(1); QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=22.25..22.26 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.29..21.41 rows=333 width=4) Index Cond: (pw = 1) EXECUTE selpwpm(1); avg ----- (1 ligne) Temps : 1,346 ms Temps : 1,343 ms Temps : 1,298 ms


      Dans notre exemple, la modification de l’indexation est très positive. Nous n’avons pas dû renoncer à la préparation de la requête, nous avons gagné 200Mo au niveau stockage ET nous obtenons des performances égales ou supérieures à l’exécution par rapport à celles obtenues précédemment.

Conclusion

      Les instructions préparées permettent d’économiser la phase de parse et apportent bien les bénéfices attendus. S’il n’y a aucun doute sur le fait qu’un plan générique soit toujours optimal, indépendamment du contexte, alors elles peuvent être utilisées sans restriction pour économiser la phase de planification.
      Attention tout de même aux limitations possibles des plans génériques. Il existe différentes méthodes pour amener le moteur à ne jamais devoir exécuter une requête avec un plan générique inadapté.

Mise à jour : 28/08/2018