par Guillaume Lelarge.

Obtenir un plan d'exécution


Un plan d'exécution PostgreSQL est récupéré en plaçant la commande explain au début d'une requête SQL. Néanmoins, il existe une limitation importante : il n'est pas possible de récupérer un plan d'exécution de cette façon avec les requêtes SQL comprenant des paramètres liés (par exemple $1, $2, etc.). Elles doivent d'abord être préparées :

PREPARE req(int) AS SELECT $1;

Notez que PostgreSQL utilise la notation $n pour les paramètres liés. La couche d'abstraction de la base de données peut vous le cacher pour que vous puissiez utiliser des points d'interrogation comme définis dans le standard SQL.

Il est possible d'obtenir le plan d'exécution de la requête préparée ainsi :

EXPLAIN EXECUTE req(1);

Jusqu'à PostgreSQL 9.1, le plan d'exécution était déjà créé lors de l'appel à prepare et pouvait donc ne pas prendre en considération les valeurs réelles pour des paramètres liés lors du execute. À partir de PostgreSQL 9.2, la création du plan d'exécution est repoussé jusqu'à l'exécution et peut donc prendre en considération les valeurs réelles des paramètres liés.

Remarque

Les plans d'exécution des requêtes sans paramètres liés s'obtiennent directement :

EXPLAIN SELECT 1;

Voici un plan d'exécution :

                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)

La sortie comprend des informations similaires aux plans d'exécution d'Oracle montrés dans tout le livre : le nom de l'opération (« Result »), le coût relatif, l'estimation du nombre de lignes et la taille attendue de la ligne.

Notez que PostgreSQL affiche deux valeurs de coût. Le premier est le coût de démarrage (c'est-à-dire le coût pour récupérer la première ligne), le second est le coût total pour l'exécution si toutes les lignes sont récupérées. Le plan d'exécution Oracle affiche seulement la deuxième valeur.

La commande explain de PostgreSQL a deux options. L'option VERBOSE fournit des informations supplémentaires comme le nom complètement qualifié de la table. VERBOSE a généralement peu d'intérêt.

La deuxième option est ANALYZE. Bien qu'elle soit très utilisée, je ne recommande pas d'avoir l'habitude de l'utiliser automatiquement car elle force l'exécution de la requête. C'est généralement sans risque pour les requêtes select mais cela provoque la modification des données pour les requêtes insert, update et delete. Pour éviter le risque de modifier vos données, vous pouvez l'englober dans une transaction et faire une annulation après coup.

Attention

explain analyze exécute la requête même s'il s'agit d'un insert, d'un update ou d'un delete.

L'option ANALYZE exécute la requête et enregistre la durée réelle et le nombre de lignes réel. Ces informations sont importantes pour trouver la cause de mauvaises estimations de cardinalité (estimation du nombre de lignes) :

BEGIN;
EXPLAIN ANALYZE EXECUTE req(1);

                   QUERY PLAN
--------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
         (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.020 ms

ROLLBACK;

Notez que le plan est formaté pour mieux tenir sur la page. PostgreSQL affiche les « vraies » valeurs sur la même ligne que les valeurs estimées.

Le nombre de lignes est la seule valeur affichée dans les deux parties (réel et estimation). Ceci vous permet de trouver rapidement les estimations erronées de cardinalité.

Enfin, les requêtes préparées doivent être fermées :

DEALLOCATE req;

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license