Description

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 utilisé (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. L'instruction préparée peut être supprimés 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.

Paramètres

Notes

Les instructions préparées peuvent utiliser des plans génériques plutôt que de planifier à chaque fois pour chaque valeur fournie à EXECUTE. La planification survient immédiatement pour les requêtes préparées sans paramètre ; dans les autres cas, cela survient après que cinq ou plus d'exécutions ont produit des plans dont le coût estimé moyen (incluant l'optimisation) est plus important que le coût du plan générique. Une fois qu'un plan générique est choisi, il est utilisé pendant toute la vie de la requête préparée. Utiliser EXECUTE avec des valeurs rares dans des colonnes contenant de nombreuses valeurs dupliquées peut générer des plans personnalisés bien moins coûteux que le plan générique, même en prenant en compte le coût d'optimisation, à tel point que le plan générique ne sera jamais utilisé.

Un plan générique suppose que chaque valeur fournie à EXECUTE est une des valeurs distinctes de la colonne et que les valeurs de la colonne sont uniformément distribuées. Par exemple, si les statistiques enregistrent trois valeurs distinctes, un plan générique suppose qu'une comparaison d'égalité sur cette colonne correspondra à un tiers des lignes traitées. Les statistiques sur les colonnes autorisent aussi les plans génériques à calculer précisément la sélectivité des colonnes uniques. Les comparaisons sur des colonnes distribuées non uniformément et la spécification des valeurs inexistantes affectent le coût moyen du plan, et de ce fait si et quand un plan générique est choisi.

Pour examiner le plan de requête que PostgreSQL utilise pour une instruction préparée, utilisez EXPLAIN, autrement dit EXPLAIN EXECUTE. Si un plan générique est utilisé, il contiendra des symboles $n, alors qu'un plan personnalisé contiendra les valeurs fournies pour les paramètres. Les estimations de nombre de lignes dans le plan générique reflètent la sélectivité calculée pour les paramètres.

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) 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.

Exemples

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);
   

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é.

Compatibilité

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.

Voir aussi