Instructions préparées - partie 2
(sujet préalablement traité avec la version 10)
La préparation des instructions peut permettre daller plus loin que léconomie des parses pour les requêtes souvent exécutées. Elle permet dans certains cas déconomiser la phase doptimisation avec lutilisation de plans génériques. Cest toujours le cas avec les instructions ne comportant pas de paramètres. Si linstruction 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 quil est adopté, le plan générique est utilisé pour toute nouvelle exécution de linstruction 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 dexé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) dun index B-Tree, effectuée en parallèle. Ce plan est relativement adapté pour $1=0 même sil napporte ici aucun gain par rapport à un balayage complet en parallèle de la table (seq scan) . Mais le problème est surtout quil est inutile et même néfaste avec toute autre valeur du paramètre $1. Les exécutions pour $1=1 sachevaient en effet en moins de 2ms avant ladoption 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 dexécution en raison de ladoption dun 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 dinstructions 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 davoir beaucoup de requêtes pouvant tirer parti de lindex only scan, il ne semble pas indispensable dindexer 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 lindexation est très positive. Nous navons 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 à lexé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. Sil ny a aucun doute sur le fait quun 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