Instructions préparées - partie 2
(sujet mis à jour avec la version 12 devel)
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 :
select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu (Debian 10.1-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 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.064..0.066 rows=1 loops=1)
Index Cond: (idg = 123456)
Planning time: 0.496 ms
Execution time: 0.111 ms
...
Planning time: 0.236 ms
Execution time: 0.087 ms
...
Planning time: 0.202 ms
Execution time: 0.075 ms
...
Planning time: 0.151 ms
Execution time: 0.060 ms
...
Planning time: 0.143 ms
Execution time: 0.057 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.041..0.045 rows=1 loops=1)
Index Cond: (idg = $1)
Planning time: 0.200 ms
Execution time: 0.081 ms
...
Planning time: 0.034 ms
Execution time: 0.110 ms
...
Planning time: 0.019 ms
Execution time: 0.071 ms
..
Planning time: 0.019 ms
Execution time: 0.066 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=23.32..23.33 rows=1 width=32)
-> Index Scan using geants_i1 on geants (cost=0.43..22.49 rows=333 width=4)
Index Cond: (pw = 1)
EXECUTE selpwpm(1);
avg
-----
(1 ligne)
Temps : 0,904 ms
Temps : 0,918 ms
Temps : 0,892 ms
Temps : 0,901 ms
Temps : 0,880 ms
Temps : 0,900 ms
...
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
(6 lignes)
Temps : 0,979 ms
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
(6 lignes)
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=11778.11..11778.12 rows=1 width=32)
-> Gather (cost=11777.89..11778.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=10777.89..10777.90 rows=1 width=32)
-> Parallel Index Scan using geants_i1 on geants (cost=0.43..10462.24 rows=126262 width=4)
Index Cond: (pw = $1)
(6 lignes)
EXECUTE selpwpm(0);
avg
----------------------
374.9978873202780817
(1 ligne)
Durée : 1675,495 ms (00:01,675)
explain SELECT avg(taille) FROM geants WHERE pw = 0;
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
(6 lignes)
SELECT avg(taille) FROM geants WHERE pw = 0;
avg
----------------------
374.9978873202780817
(1 ligne)
Durée : 1110,265 ms (00:01,110)
explain EXECUTE selpwpm(1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=11778.11..11778.12 rows=1 width=32)
-> Gather (cost=11777.89..11778.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=10777.89..10777.90 rows=1 width=32)
-> Parallel Index Scan using geants_i1 on geants (cost=0.43..10462.24 rows=126262 width=4)
Index Cond: (pw = $1)
(6 lignes)
EXECUTE selpwpm(1);
avg
-----
(1 ligne)
Temps : 22,359 ms
Temps : 22,060 ms
Temps : 22,055 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 1ms avant l’adoption du plan générique et en plus de 22ms après son adoption.
Un rapport de 1 à 22, cela incite fortement à réfléchir au moment de coder. 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.
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 :
\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;
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=23.17..23.18 rows=1 width=32)
-> Index Scan using geants_i1 on geants (cost=0.29..22.34 rows=333 width=4)
Index Cond: (pw = 1)
EXECUTE selpwpm(1);
avg
-----
(1 ligne)
Temps : 0,977 ms
Temps : 0,936 ms
Temps : 0,949 ms
Temps : 0,905 ms
Temps : 0,972 ms
Temps : 0,877 ms
...
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
EXPLAIN EXECUTE selpwpm(0);
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
EXECUTE selpwpm(0);
avg
----------------------
374.9978873202780817
(1 ligne)
Durée : 1122,132 ms (00:01,122)
explain SELECT avg(taille) FROM geants WHERE pw = 0;
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=137019.11..137019.12 rows=1 width=32)
-> Gather (cost=137018.89..137019.10 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=136018.89..136018.90 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..125613.33 rows=4162222 width=4)
Filter: (pw = 0)
explain EXECUTE selpwpm(1);
QUERY PLAN
---------------------------------------------------------------------------------
Aggregate (cost=23.17..23.18 rows=1 width=32)
-> Index Scan using geants_i1 on geants (cost=0.29..22.34 rows=333 width=4)
Index Cond: (pw = 1)
EXECUTE selpwpm(1);
avg
-----
(1 ligne)
Temps : 0,932 ms
Temps : 0,882 ms
Temps : 0,924 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 : 22/01/2018