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.

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.

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, BUFFERS, SETTINGS) EXECUTE stmt(1)

                   QUERY PLAN
--------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4)
         (actual time=0.001..0.002 rows=1 loops=1)
 Settings: random_page_cost = '1.1'
 Planning Time: 0.032 ms
 Execution Time: 0.078 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;