PREPARE — prépare une instruction pour exécution
PREPAREnom
[ (type_données
[, ...] ) ] ASinstruction
PREPARE
crée une instruction préparée. Une instruction
préparée est un objet côté serveur qui peut être utilisé pour optimiser les
performances. Quand l'instruction PREPARE
est exécutée,
l'instruction spécifiée est lue, analysée et réécrite. Quand une
commande EXECUTE
est lancée par la suite, l'instruction
préparée est planifiée et exécutée. Cette division du travail évite une analyse
répétitive tout en permettant au plan d'exécution de dépendre des valeurs
spécifiques du paramètre.
Les instructions préparées peuvent prendre des paramètres : les valeurs
sont substituées dans l'instruction lorsqu'elle est exécutée. Lors de la
création de l'instruction préparée, faites référence aux paramètres suivant
leur position, $1
, $2
, etc. Une liste
correspondante des types de données des paramètres peut être spécifiée si
vous le souhaitez. Quand le type de donnée d'un paramètre n'est pas indiqué
ou est déclaré comme inconnu (unknown
), le type est
inféré à partir du contexte dans lequel le paramètre est référencé en premier
(si possible). Lors de l'exécution de l'instruction, indiquez les valeurs
réelles de ces paramètres dans l'instruction EXECUTE
.
Référez-vous à EXECUTE
pour plus d'informations à ce sujet.
Les instructions préparées sont seulement stockées pour la durée
de la session en cours. Lorsque la session se termine, l'instruction préparée
est oubliée et, du coup, elle doit être recréée avant d'être utilisée de
nouveau. Ceci signifie aussi qu'une seule instruction préparée ne peut pas
être utilisée par plusieurs clients de bases de données simultanément ;
néanmoins, chaque client peut créer sa propre instruction préparée à
utiliser. Les instructions préparées peuvent être supprimées manuellement
en utilisant la commande DEALLOCATE
.
Les instructions préparées sont principalement intéressantes quand une seule session est utilisée pour exécuter un grand nombre d'instructions similaires. La différence de performances est potentiellement significative si les instructions sont complexes à planifier ou à réécrire, par exemple, si la requête implique une jointure de plusieurs tables ou requiert l'application de différentes règles. Si l'instruction est relativement simple à planifier ou à réécrire mais assez coûteuse à exécuter, l'avantage de performance des instructions préparées est moins net.
nom
Un nom quelconque donné à cette instruction préparée particulière. Il doit être unique dans une session et est utilisé par la suite pour exécuter ou désallouer cette instruction préparée.
type_données
Le type de données d'un paramètre de l'instruction préparée. Si le type
de données d'un paramètre particulier n'est pas spécifié ou est spécifié
comme étant inconnu (unknown
), il sera inferré à
partir du contexte dans lequel le paramètre est référencé en premier.
Pour référencer les paramètres de l'instruction préparée, utilisez
$1
, $2
, etc.
instruction
Toute instruction SELECT
, INSERT
,
UPDATE
, DELETE
, MERGE
ou VALUES
.
Une requête préparée peut être exécutée soit avec un plan générique soit avec un plan personnalisé. Un plan générique est le même pour toutes les exécutions alors qu'un plan personnalisé est généré pour une exécution spécifique en utilisant les valeurs des paramètres données lors de son appel. L'utilisation d'un plan générique évite une charge supplémentaire pour la planification mais, dans certaines situations, un plan personnalisé sera bien plus efficace à exécuter parce que le planificateur pourra utiliser sa connaissance des valeurs des paramètres. (Bien sûr, si la requête préparée n'a pas de paramètres, ceci n'a pas d'intérêt et un plan générique sera toujours utilisé.)
Par défaut (autrement dit, quand plan_cache_mode est
configuré à auto
), le serveur choisira automatiquement
l'utilisation d'un plan générique ou personnalisé pour une requête préparée
utilisant des paramètres. La règle actuelle pour ceci est que les cinq
premières exécutions sont réalisées avec des plans personnalisés et le coût
moyen estimé pour ces plans est calculé. Un plan générique est créé et son
estimation de coût est comparé au coût moyens des cinq plans personnalisés.
Les exécutions suivantes utilisent le plan générique si son coût n'est pas
tellement plus élevé que le coût moyen des plans personnalisés pour éviter
de perdre du temps sur la planification.
Cette heuristique peut être surchargée, en forçant le serveur à utiliser
soit des plans génériques soit des plans personnalisés, en configurant
plan_cache_mode
à, respectivement,
force_generic_plan
ou
force_custom_plan
. Ce paramètre est principalement utile
si l'estimation de coût du plan générique est très mauvais, autorisant son
choix même si le coût réel est bien plus important que celui d'un plan
personnalisé.
Pour examiner le plan de requête que PostgreSQL
utilise pour une instruction préparée, utilisez EXPLAIN
,
par exemple :
EXPLAIN EXECUTEnom
(valeurs_parametres
);
Si un plan générique est utilisé, il contiendra des symboles
$
, alors qu'un plan
personnalisé contiendra les valeurs fournies pour les paramètres.
n
Pour plus d'informations sur la planification de la requête et les statistiques récupérées par PostgreSQL dans ce but, voir la documentation de ANALYZE.
Bien que le but principal d'une requête préparée est déviter une analyse et
une planification répétée, PostgreSQL forcera une
nouvelle analyse et une nouvelle planification de la requête à chaque fois
que les objets de la base utilisés dans la requête auront vus leur
définition modifiée (requête DDL) or their planner statistics have
been updated depuis la dernière utilisation de la
requête préparée. De plus, si la valeur de search_path
change d'une exécution à l'autre, la requête sera de nouveau analysée d'après
la nouvelle valeur du paramètre search_path
. (Ce dernier
comportement est nouveau depuis PostgreSQL 9.3.) Ces
règles font d'une requête préparée l'équivalent sémantique de la soumission
sans fin de la même requête, avec de meilleures performances si aucun objet
n'est modifié, tout spécialement si le meilleur plan reste le même au travers
des utilisations. Un exemple d'un cas où l'équivalence sémantique n'est pas
parfaite est que, si la requête fait référence à une table dont le nom n'est pas
qualifié du nom du schéma et qu'une nouvelle table de même nom est créée dans
un schéma apparaissant avant dans le paramètre search_path
,
aucune nouvelle analyse n'intervient vu qu'aucun objet de la requête n'a été
modifié. Néanmoins, si une autre modification force une nouvelle analyse, la
nouvelle table sera référencée dans les utilisations suivantes.
Vous pouvez voir toutes les instructions préparées disponibles dans la session
en exécutant une requête sur la vue système pg_prepared_statements
.
Crée une instruction préparée pour une instruction INSERT
,
puis l'exécute :
PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
Crée une instruction préparée pour une instruction SELECT
,
puis l'exécute :
PREPARE usrrptplan (int) AS SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2; EXECUTE usrrptplan(1, current_date);
Dans cet exemple, notez que le type de données du deuxième paramètre n'est
pas indiqué, donc il est déduit du contexte dans lequel
$2
est utilisé.
Le standard SQL inclut une instruction PREPARE
mais il
est seulement utilisé en SQL embarqué. Cette version de l'instruction
PREPARE
utilise aussi une syntaxe quelque peu différente.