EXPLAIN
PostgreSQL réalise un plan de
requête pour chaque requête qu'il reçoit. Choisir le bon plan
correspondant à la structure de la requête et aux propriétés des données
est absolument critique pour de bonnes performances, donc le système
inclut un planificateur ou
optimiseur complexe qui tente de choisir les bons
plans. Vous pouvez utiliser la commande EXPLAIN pour voir quel
plan de requête le planificateur crée pour une requête particulière. La
lecture du plan est un art qui requiert de l'expérience pour le maîtriser,
mais cette section essaie de couvrir les bases.
Les exemples dans cette section sont tirés de la base de données pour les
tests de régression après avoir effectué un VACUUM
ANALYZE, avec les sources de la version de développement 9.3.
Vous devriez obtenir des résultats similaires si vous essayez les exemples
vous-même, mais vos estimations de coût et de nombre de lignes pourraient
légèrement varier parce que les statistiques d'ANALYZE
sont basées sur des échantillons aléatoires, et parce que les coûts sont
dépendants de la plateforme utilisée.
Les exemples utilisent le format de sortie par défaut
(« text ») d'EXPLAIN, qui est compact et
pratique pour la lecture. Si vous voulez utiliser la sortie
d'EXPLAIN avec un programme pour une analyse
ultérieure, vous devriez utiliser un des formats de sortie au format
machine (XML, JSON ou YAML) à la place.
EXPLAIN
La structure d'un plan de requête est un arbre de nœuds
de plan. Les nœuds de bas niveau sont les nœuds
de parcours : ils renvoient les lignes brutes d'une table. Il existe
différents types de nœuds de parcours pour les différentes méthodes
d'accès aux tables : parcours séquentiel, parcours d'index et
parcours d'index bitmap. Il y a également des ensembles de lignes qui ne
proviennent pas de tables, avec par exemple des clauses
VALUES ainsi que des fonctions renvoyant des ensembles
dans un FROM, qui ont leurs propres types de
nœuds de parcours. Si la requête requiert des jointures,
agrégations, tris ou d'autres opérations sur les lignes brutes, ce seront
des nœuds supplémentaires au-dessus des nœuds de parcours
pour réaliser ces opérations. Encore une fois, il existe plus d'une façon
de réaliser ces opérations, donc différents types de nœuds peuvent
aussi apparaître ici. La sortie d'EXPLAIN comprend une
ligne pour chaque nœud dans l'arbre du plan, montrant le type de
nœud basique avec les estimations de coût que le planificateur a
faites pour l'exécution de ce nœud du plan. Des lignes
supplémentaires peuvent apparaître, indentées par rapport à la ligne de
résumé du nœud, pour montrer les propriétés supplémentaires du
nœud. La première ligne (le nœud tout en haut) comprend le
coût d'exécution total estimé pour le plan ; c'est ce nombre que le
planificateur cherche à minimiser.
Voici un exemple trivial, juste pour montrer à quoi ressemble l'affichage.
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Puisque la requête n'a pas de clause WHERE, il faut
parcourir toutes les lignes de la table, c'est pourquoi le planificateur
a choisi d'utiliser un plan avec un simple parcours séquentiel. Les
nombres affichés entre parenthèses sont (de gauche à droite) :
Coût estimé du lancement. Cela correspond au temps passé avant que l'affichage de la sortie ne commence, par exemple le temps de faire un tri dans un nœud de tri ;
Coût total estimé. Cela suppose que le nœud du plan d'exécution est
exécuté entièrement, c'est-à-dire que toutes les lignes disponibles
sont récupérées. En pratique, un nœud parent peut arrêter la
récupération de toutes les lignes disponibles avant la fin
(voir l'exemple LIMIT ci-dessous) ;
Nombre de lignes estimé en sortie par ce nœud de plan. Encore une fois, on suppose que le nœud est exécuté entièrement.
Largeur moyenne estimée (en octets) des lignes en sortie par ce nœud du plan.
Les coûts sont mesurés en unités arbitraires déterminées par les
paramètres de coût du planificateur (voir Section 20.7.2). La pratique habituelle est
de mesurer les coûts en unité de récupération de pages disque ;
autrement dit, seq_page_cost est initialisé à
1.0 par convention et les autres paramètres de coût
sont relatifs à cette valeur. Les exemples de cette section sont exécutés
avec les paramètres de coût par défaut.
Il est important de comprendre que le coût d'un nœud de haut niveau inclut le coût de tous les nœuds fils. Il est aussi important de réaliser que le coût reflète seulement les éléments d'importance pour le planificateur. En particulier, le coût ne considère pas le temps dépensé dans la transmission des lignes de résultat au client, ce qui pourrait être un facteur important dans le temps réel passé ; mais le planificateur l'ignore parce qu'il ne peut pas le changer en modifiant le plan (chaque plan correct sortira le même ensemble de lignes).
La valeur rows est un peu difficile car il ne s'agit
pas du nombre de lignes traitées ou parcourues par le plan de nœuds, mais
plutôt le nombre émis par le nœud. C'est habituellement moins, reflétant
la sélectivité estimée des conditions de la clause
WHERE qui sont appliquées au nœud. Idéalement, les
estimations des lignes de haut niveau seront une approximation des
nombres de lignes déjà renvoyées, mises à jour, supprimées par la
requête.
Quand un UPDATE ou un DELETE affecte
une hiérarchie d'héritage, la sortie pourrait ressembler à ceci :
EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Update on parent (cost=0.00..24.59 rows=0 width=0)
Update on parent parent_1
Update on child1 parent_2
Update on child2 parent_3
Update on child3 parent_4
-> Result (cost=0.00..24.59 rows=4 width=14)
-> Append (cost=0.00..24.54 rows=4 width=14)
-> Seq Scan on parent parent_1 (cost=0.00..0.00 rows=1 width=14)
Filter: (f1 = 101)
-> Index Scan using child1_pkey on child1 parent_2 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child2_pkey on child2 parent_3 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child3_pkey on child3 parent_4 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)Dans cet exemple, le nœud Update doit prendre en compte les trois tables filles ainsi que la table parente mentionnée dans la requête. Donc il y a quatre sous-plans de parcours en entrée, un par table. Pour plus de clarté, le nœud Update est annoté pour afficher les tables cibles spécifiques à mettre à jour, dans le même ordre que les sous-plans correspondants.
Le Temps de planification (Planning time) affiché est
le temps qu'il a fallu pour générer le plan d'exécution de la requête
analysée et pour l'optimiser. Cela n'inclut ni le temps de réécriture ni
le temps d'analyse.
Pour revenir à notre exemple :
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Ces nombres sont directement dérivés. Si vous faites :
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
vous trouverez que tenk1 a 358 blocs disque et
10000 lignes. Le coût estimé est calculé avec (nombre de blocs lus *
seq_page_cost) + (lignes parcourues * cpu_tuple_cost). Par défaut,
seq_page_cost vaut 1,0 et
cpu_tuple_cost vaut 0,01. Donc le coût estimé est de
(358 * 1,0) + (10000 * 0,01), soit 458.
Maintenant, modifions la requête originale pour ajouter une condition
WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7001 width=244)
Filter: (unique1 < 7000)
Notez que l'affichage d'EXPLAIN montre la clause
WHERE appliquée comme une condition de
« filtre » rattachée au nœud de parcours séquentiel ;
ceci signifie que le nœud de plan vérifie la condition pour chaque ligne
qu'il parcourt et ne conserve que celles qui satisfont la condition.
L'estimation des lignes en sortie a baissé à cause de la clause
WHERE. Néanmoins, le parcours devra toujours visiter
les 10000 lignes, donc le coût n'a pas baissé ; en fait, il a un peu
augmenté(par 10000 * cpu_operator_cost pour être
exact) dans le but de refléter le temps CPU supplémentaire dépensé pour
vérifier la condition WHERE.
Le nombre réel de lignes que cette requête sélectionnera est 7000, mais
l'estimation rows est approximative. Si vous tentez de
dupliquer cette expérience, vous obtiendrez probablement une estimation
légèrement différente ; de plus, elle changera après chaque commande
ANALYZE parce que les statistiques produites par
ANALYZE sont prises à partir d'un extrait au hasard de
la table.
Maintenant, rendons la condition plus restrictive :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)Ici, le planificateur a décidé d'utiliser un plan en deux étapes : le nœud en bas du plan visite un index pour trouver l'emplacement des lignes correspondant à la condition de l'index, puis le nœud du plan du dessus récupère réellement ces lignes de la table. Récupérer séparément les lignes est bien plus coûteux que de les lire séquentiellement, mais comme toutes les pages de la table n'ont pas à être visitées, cela revient toujours moins cher qu'un parcours séquentiel (la raison de l'utilisation d'un plan à deux niveaux est que le nœud du plan du dessus trie les emplacements des lignes identifiées par l'index dans l'ordre physique avant de les lire pour minimiser les coûts des récupérations séparées. Le « bitmap » mentionné dans les noms de nœuds est le mécanisme qui s'occupe du tri).
Maintenant, ajoutons une autre condition à la clause
WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.04..229.43 rows=1 width=244)
Recheck Cond: (unique1 < 100)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
L'ajout de la condition stringu1 = 'xxx' réduit
l'estimation du nombre de lignes renvoyées, mais pas son coût, car il
faut toujours parcourir le même ensemble de lignes. Notez que la clause
stringu1 ne peut être appliquée comme une condition
d'index car l'index ne porte que sur la colonne
unique1. À la place, un filtre a été appliqué sur les
lignes récupérées par l'index. C'est pourquoi le coût a légèrement
augmenté pour refléter la vérification supplémentaire.
Dans certains cas, le planificateur préfèrera un plan « simple » d'index :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
Dans ce type de plan, les lignes de la table sont récupérées dans l'ordre
de l'index, ce qui les rend encore plus coûteuses à récupérer, mais il y
en a tellement peu que le coût supplémentaire pour trier l'ordre des
lignes n'est pas rentable. Vous verrez principalement ce type de plan
pour les requêtes qui ne récupèrent qu'une seule ligne, ou pour les
requêtes qui ont une condition ORDER BY qui correspond
à l'ordre de l'index, car cela ne nécessite aucune étape supplémentaire
pour satisfaire l'ORDER BY. Dans cet exemple, ajouter
ORDER BY unique1 ferait que l'optimiseur utilise le
même plan parce que l'index fournit déjà implicitement le tri requis.
L'optimiseur peut exécuter une clause ORDER BY de
plusieurs façons. L'exemple ci-dessus montre qu'une clause de tri peut
être effectué implicitement. L'optimiseur peut aussi ajouter une étape de
tri (sort) explicite :
EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=1109.39..1134.39 rows=10000 width=244)
Sort Key: unique1
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
Si une partie du plan garantit un ordre sur un préfixe des clés de tri
requises, alors l'optimiseur peut décider à la place d'utiliser une étape
de tri incrémental (incremental sort) :
EXPLAIN SELECT * FROM tenk1 ORDER BY four, ten LIMIT 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=521.06..538.05 rows=100 width=244)
-> Incremental Sort (cost=521.06..2220.95 rows=10000 width=244)
Sort Key: four, ten
Presorted Key: four
-> Index Scan using index_tenk1_on_four on tenk1 (cost=0.29..1510.08 rows=10000 width=244)
Comparé aux tris habituels, le tri incrémental permet de renvoyer les
lignes avant que l'ensemble du résultat ne soit trié, ce qui permet en
particulier des optimisations avec les requêtes utilisant la clause
LIMIT. Il peut aussi réduire l'utilisation de la
mémoire et la probabilité d'envoyer des tris sur disque, mais cela a un
coût : une surcharge pour répartir l'ensemble de lignes résultats
dans plusieurs groupes de tri.
S'il y a des index sur plusieurs colonnes référencées dans la clause
WHERE, le planificateur pourrait choisir d'utiliser
une combinaison binaire (AND et OR) des index :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
Index Cond: (unique2 > 9000)Mais ceci requiert de visiter plusieurs index, donc ce n'est pas nécessairement un gain comparé à l'utilisation d'un seul index et au traitement de l'autre condition par un filtre. Si vous variez les échelles de valeurs impliquées, vous vous apercevrez que le plan change en accord.
Voici un exemple montrant les effets d'un LIMIT :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.29..14.48 rows=2 width=244)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..71.27 rows=10 width=244)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
C'est la même requête qu'au-dessus, mais avec l'ajout de
LIMIT, ce qui fait que toutes les lignes ne seront pas
récupérées, et donc que le planificateur change sa façon de procéder.
Notez que le coût total ainsi que le nombre de lignes du nœud de parcours
d'index sont affichés comme si le nœud devait être exécuté entièrement.
Cependant, le nœud Limit s'attend à s'arrêter après avoir récupéré
seulement un cinquième de ces lignes, c'est pourquoi son coût total n'est
qu'un cinquième du coût précédent, ce qui est le vrai coût estimé de la
requête. Ce plan est préférable à l'ajout d'un nœud Limit au plan
précédent, car le Limit ne pourrait pas empêcher le coût de départ du
parcours d'index Bitmap, ce qui augmenterait le coût d'environ 25 unités
avec cette approche.
Maintenant, essayons de joindre deux tables, en utilisant les colonnes dont nous avons discuté :
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
Dans ce plan, nous avons un nœud de jointure en boucle imbriquée sur deux
parcours de tables en entrée. L'indentation des lignes de sommaire des
nœuds reflète la structure en arbre du plan. Le premier nœud, ou nœud
« externe », utilise le même parcours de bitmap que celui vu
précédemment, et donc ses coût et nombre de lignes sont les mêmes que ce
que l'on aurait obtenu avec SELECT ... WHERE unique1 <
10, car la même clause WHERE
unique1 < 10 est appliquée à ce nœud. La clause
t1.unique2 = t2.unique2 n'a pas encore d'intérêt, elle
n'affecte donc pas le nombre de lignes du parcours externe. Le nœud de
jointure en boucle imbriquée s'exécutera sur le deuxième nœud, ou nœud
« interne », pour chaque ligne obtenue du nœud externe. Les
valeurs de colonne de la ligne externe courante peuvent être utilisées
dans le parcours interne ; ici, la valeur
t1.unique2 de la ligne externe est disponible, et on
peut obtenir un plan et un coût similaires à ce que l'on a vu plus haut
pour le cas simple SELECT ... WHERE t2.unique2 =
.(Le coût estimé est ici un
peu plus faible que celui vu précédemment, en prévision de la mise en
cache des données durant les parcours d'index répétés sur
constantt2.) Les coûts du nœud correspondant à la boucle sont
ensuite initialisés sur la base du coût du parcours externe, avec une
répétition du parcours interne pour chaque ligne externe (ici 10 * 7,91),
plus un petit temps CPU pour traiter la jointure.
Dans cet exemple, le nombre de lignes en sortie de la jointure est
identique au nombre de lignes des deux parcours, mais ce n'est pas vrai
en règle générale car vous pouvez avoir des clauses
WHERE mentionnant les deux tables et qui, donc,
peuvent seulement être appliquées au point de jointure, et non pas aux
parcours d'index. Voici un exemple :
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.65..49.46 rows=33 width=488)
Join Filter: (t1.hundred < t2.hundred)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Materialize (cost=0.29..8.51 rows=10 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
Index Cond: (unique2 < 10)
La condition t1.hundred < t2.hundred ne peut être
testée dans l'index tenk2_unique2, elle est donc
appliquée au nœud de jointure. Cela réduit l'estimation du nombre de
lignes dans le nœud de jointure, mais ne change aucun parcours d'entrée.
Notez qu'ici le planificateur a choisi de matérialiser la relation interne
de la jointure en plaçant un nœud Materialize au-dessus. Cela signifie
que le parcours d'index de t2 ne sera réalisé qu'une
seule fois, même si le nœud de jointure par boucle imbriquée va lire dix
fois les données, une fois par ligne de la relation externe. Le nœud
Materialize conserve les données en mémoire lors de leur première
lecture, puis renvoie les données depuis la mémoire à chaque lecture
supplémentaire.
Quand vous utilisez des jointures externes, vous pouvez voir des nœuds de
plan de jointure avec à la fois des conditions « Join Filter »
et « Filter » simples attachées. Les conditions « Join
Filter » viennent des clauses de jointures externes
ON, pour qu'une ligne ne satisfaisant pas la condition
« Join Filter » puisse toujours être récupérée comme une ligne
NULL. Mais une condition « Filter » simple est appliquée après
la règle de jointure externe et supprime donc les lignes de manière
inconditionnelle. Dans une jointure interne, il n'y a pas de différence
sémantique entre ces types de filtres.
Si nous changeons un peu la sélectivité de la requête, on pourrait obtenir un plan de jointure très différent :
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=230.47..713.98 rows=101 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
-> Hash (cost=229.20..229.20 rows=101 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
Ici, le planificateur a choisi d'utiliser une jointure de hachage, dans
laquelle les lignes d'une table sont entrées dans une table de hachage en
mémoire, après quoi l'autre table est parcourue et la table de hachage
est sondée pour faire correspondre chaque ligne. Notez encore une fois
comment l'indentation reflète la structure du plan : le parcours
d'index bitmap sur tenk1 est l'entrée du nœud de
hachage, qui construit la table de hachage. C'est alors retourné au nœud
de jointure de hachage, qui lit les lignes depuis le plan du fils externe
et cherche dans la table de hachage pour chaque ligne.
Un autre type de jointure possible est la jointure d'assemblage, illustrée ici :
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=198.11..268.19 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Sort (cost=197.83..200.33 rows=1000 width=244)
Sort Key: t2.unique2
-> Seq Scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)
La jointure d'assemblage nécessite que les données en entrée soient triées
sur la clé de jointure. Dans ce plan, les données de
tenk1 sont triées grâce à l'utilisation d'un parcours
d'index pour visiter les lignes dans le bon ordre, mais un parcours
séquentiel suivi d'un tri sont préférables pour onek,
car il y a beaucoup plus de lignes à visiter dans cette table.
(Un parcours séquentiel suivi d'un tri bat fréquemment un parcours
d'index pour trier de nombreuses lignes, du fait des accès disques non
séquentiels requis par le parcours d'index.)
Une façon de rechercher des plans différents est de forcer le
planificateur à oublier certaines stratégies qu'il aurait trouvées moins
coûteuses en utilisant les options d'activation (enable)/désactivation
(disable) décrites dans la Section 20.7.1
(c'est un outil complexe, mais utile ; voir aussi la Section 14.3). Par exemple, si nous n'étions pas convaincus
qu'un parcours séquentiel suivi d'un tri soit la meilleure façon de
parcourir la table onek dans l'exemple précédent, nous
pourrions essayer
SET enable_sort = off;
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=0.56..292.65 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Index Scan using onek_unique2 on onek t2 (cost=0.28..224.79 rows=1000 width=244)
ce qui montre que le planificateur pense que le tri de
onek par un parcours d'index est plus coûteux
d'environ 12% par rapport à un parcours séquentiel suivi d'un tri. Bien
sûr, la question suivante est de savoir s'il a raison sur ce point. Nous
pourrions vérifier cela en utilisant EXPLAIN ANALYZE,
comme expliqué ci-dessous.
EXPLAIN ANALYZE
Il est possible de vérifier l'exactitude des estimations du planificateur
en utilisant l'option ANALYZE de
EXPLAIN. Avec cette option, EXPLAIN
exécute vraiment la requête, puis affiche le vrai nombre de lignes et les
vrais temps passés dans chaque nœud, avec ceux estimés par un simple
EXPLAIN. Par exemple, nous pourrions avoir un résultat
tel que :
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
Index Cond: (unique2 = t1.unique2)
Planning time: 0.181 ms
Execution time: 0.501 msNotez que les valeurs « temps réel » sont en millisecondes alors que les estimations de « coût » sont exprimées dans des unités arbitraires ; il y a donc peu de chances qu'elles correspondent. L'information qu'il faut généralement rechercher est si le nombre de lignes estimées est raisonnablement proche de la réalité. Dans cet exemple, les estimations étaient toutes rigoureusement exactes, mais c'est en pratique plutôt inhabituel.
Dans certains plans de requête, il est possible qu'un nœud de sous-plan
soit exécuté plus d'une fois. Par exemple, le parcours d'index interne
est exécuté une fois par ligne externe dans le plan de boucle imbriquée
ci-dessus. Dans de tels cas, la valeur loops renvoie
le nombre total d'exécutions du nœud, et le temps réel et les valeurs des
lignes affichées sont une moyenne par exécution. Ceci est fait pour que
les nombres soient comparables avec la façon dont les estimations de
coûts sont affichées. Multipliez par la valeur de
loops pour obtenir le temps total réellement passé
dans le nœud. Dans l'exemple précédent, le parcours d'index sur
tenk2 a pris un total de 0,220 milliseconde.
Dans certains cas, EXPLAIN ANALYZE affiche des
statistiques d'exécution supplémentaires après le temps et le nombre de
lignes de l'exécution d'un nœud du plan. Par exemple, les nœuds de tri et
de hachage fournissent des informations supplémentaires :
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 msLe nœud de tri donne la méthode de tri utilisée (en particulier, si le tri s'est effectué en mémoire ou sur disque) ainsi que la quantité de mémoire ou d'espace disque requis. Le nœud de hachage montre le nombre de paquets de hachage, le nombre de lots ainsi la quantité maximale de mémoire utilisée pour la table de hachage (si le nombre de lots est supérieur à un, il y aura également l'utilisation de l'espace disque impliqué, mais cela n'est pas montré dans cet exemple).
Un autre type d'information supplémentaire est le nombre de lignes supprimées par une condition de filtrage :
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
Filter: (ten < 7)
Rows Removed by Filter: 3000
Planning time: 0.083 ms
Execution time: 5.905 msCes nombres peuvent être particulièrement précieux pour les conditions de filtres appliquées aux nœuds de jointure. La ligne « Rows Removed » n'apparaît que si au moins une ligne parcourue, ou une ligne potentiellement appairée dans le cas d'un nœud de jointure, est rejetée par la condition de filtre.
Un cas similaire aux conditions de filtre apparaît avec des parcours d'index « avec perte ». Par exemple, regardez cette recherche de polygone contenant un point spécifique :
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on polygon_tbl (cost=0.00..1.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
Filter: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Filter: 4
Planning time: 0.040 ms
Execution time: 0.083 msLe planificateur pense (plutôt correctement) que cette table d'échantillon est trop petite pour s'embêter avec un parcours d'index, et utilise donc un parcours séquentiel dans lequel toutes les lignes sont rejetées par la condition de filtre. Mais si nous forçons l'utilisation d'un parcours d'index, nous voyons :
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Index Recheck: 1
Planning time: 0.034 ms
Execution time: 0.144 msL'index retourne une ligne candidate, qui est ensuite rejetée par une deuxième vérification de la condition de l'index. Cela arrive car un index GiST est « avec perte » pour les tests de contenance de polygone : il retourne en fait les lignes pour lesquelles les polygones chevauchent la cible, ce qui nécessite après coup un test de contenance exacte sur ces lignes.
EXPLAIN a une option BUFFERS qui
peut être utilisée avec ANALYZE pour obtenir encore
plus de statistiques d'exécution:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
Buffers: shared hit=15
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
Buffers: shared hit=7
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
Index Cond: (unique1 < 100)
Buffers: shared hit=2
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)
Index Cond: (unique2 > 9000)
Buffers: shared hit=5
Planning time: 0.088 ms
Execution time: 0.423 ms
Les nombres fournis par BUFFERS aident à identifier les
parties de la requête les plus intensives en termes d'entrées sorties.
Il faut garder en tête que comme EXPLAIN ANALYZE
exécute vraiment la requête, tous les effets secondaires se produiront
comme d'habitude, même si, quel que soit l'affichage de la requête, il
est remplacé par la sortie des données d'EXPLAIN. Si
vous voulez analyser une requête modifiant les données sans changer les
données en table, vous pouvez annuler les modifications après, par
exemple :
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Update on tenk1 (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)
-> Bitmap Heap Scan on tenk1 (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
Heap Blocks: exact=90
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)
Index Cond: (unique1 < 100)
Planning Time: 0.113 ms
Execution Time: 3.850 ms
ROLLBACK;
Comme vous pouvez le voir dans cet exemple, quand la requête contient une
commande INSERT, UPDATE ou
DELETE l'application des changements est faite au
niveau du nœud principal Insert, Update ou Delete du plan. Les nœuds du
plan sous celui-ci effectuent le travail de recherche des anciennes
lignes et/ou le calcul des nouvelles données. Ainsi, au-dessus, on peut
voir les mêmes tris de parcours de bitmap déjà vus précédemment, et leur
sortie est envoyée à un nœud de mise à jour qui stocke les lignes
modifiées. Il est intéressant de noter que bien que le nœud de
modification de données puisse prendre une part considérable sur le temps
d'exécution (ici, c'est la partie la plus gourmande), le planificateur
n'ajoute rien au coût estimé pour considérer ce travail. C'est dû au fait
que le travail à effectuer est le même pour chaque plan de requête
correct, et n'affecte donc pas les décisions du planificateur.
La phrase Planning time affichée par EXPLAIN
ANALYZE correspond au temps pris pour générer et optimiser le
plan de requêtes à partir de la requête analysée. Cela n'inclut pas
l'analyse syntaxique et la réécriture.
Le Temps total d'exécution donné par EXPLAIN
ANALYZE inclut le temps de démarrage et d'arrêt de l'exécuteur,
ainsi que le temps d'exécution de tous les triggers pouvant être
déclenchés, mais n'inclut pas les temps d'analyse, de réécriture ou de
planification. Le temps passé à exécuter les triggers
BEFORE, s'il y en a, est inclus dans le temps passé à
l'exécution des nœuds Insert, Update ou Delete associés, mais le temps
passé à exécuter les triggers AFTER n'est pas compté,
car les triggers AFTER sont déclenchés après
l'achèvement du plan entier. Le temps total passé dans chaque trigger
(que ce soit BEFORE ou AFTER) est
affiché séparément. Notez que les triggers de contrainte ne seront pas
exécutés avant la fin de la transaction et par conséquent ne seront pas
affichés du tout par EXPLAIN ANALYZE.
Il existe deux raisons importantes pour lesquelles les temps d'exécution
mesurés par EXPLAIN ANALYZE peuvent dévier de
l'exécution normale de la même requête. Tout d'abord, comme aucune ligne
n'est réellement envoyée au client, les coûts de conversion réseau et les
coûts de formatage des entrées/sorties ne sont pas inclus. Ensuite, le
surcoût de mesure induit par EXPLAIN ANALYZE peut être
significatif, plus particulièrement sur les machines avec un appel
système gettimeofday() lent. Vous pouvez utiliser
l'outil pg_test_timing pour mesurer le surcoût du calcul
du temps sur votre système.
Les résultats de EXPLAIN ne devraient pas être
extrapolés pour des situations autres que celles de vos tests en
cours ; par exemple, les résultats sur une petite table ne peuvent
être appliqués à des tables bien plus volumineuses. Les estimations de
coût du planificateur ne sont pas linéaires et, du coup, il pourrait bien
choisir un plan différent pour une table plus petite ou plus grande. Un
exemple extrême est celui d'une table occupant une page disque. Vous
obtiendrez pratiquement toujours un parcours séquentiel, que des index
soient disponibles ou non. Le planificateur réalise que cela va
nécessiter la lecture d'un seul bloc disque pour traiter la table dans
ce cas, il n'y a donc pas d'intérêt à étendre des lectures de blocs
supplémentaires pour un index. (Nous voyons cela arriver dans l'exemple
polygon_tbl au-dessus.)
Ici, ce sont des cas dans lesquels les valeurs réelles et estimées ne
correspondent pas vraiment, mais qui ne sont pas non plus totalement
fausses. Un tel cas peut se produire quand un nœud d'exécution d'un plan
est arrêté par un LIMIT ou autre chose avec un effet
similaire. Par exemple, dans la requête LIMIT
utilisée précédemment,
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..14.71 rows=2 width=244) (actual time=0.177..0.249 rows=2 loops=1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..72.42 rows=10 width=244) (actual time=0.174..0.244 rows=2 loops=1)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
Rows Removed by Filter: 287
Planning time: 0.096 ms
Execution time: 0.336 msles estimations de coût et de nombre de lignes pour le nœud de parcours d'index sont affichées comme s'ils devaient s'exécuter jusqu'à la fin. Mais en réalité le nœud Limit arrête la récupération des lignes après la deuxième ligne récupérée, et donc le vrai nombre de lignes n'est que de 2 et le temps d'exécution est moindre que ne le suggérait le coût estimé. Ce n'est pas une erreur d'estimation, juste une contradiction entre la façon dont l'estimation et les valeurs réelles sont affichées.
Les jointures d'assemblage ont également leurs artefacts de mesure qui
peuvent embrouiller une personne non avertie. Une jointure d'assemblage
arrêtera la lecture d'une entrée si l'autre entrée est épuisée et que la
prochaine valeur clé dans la première entrée est supérieure à la dernière
valeur clé de l'autre entrée ; dans un cas comme ça, il ne peut plus
y avoir de correspondance et il est donc inutile de parcourir le reste de
la première entrée. Cela a donc pour conséquence de ne pas lire
entièrement un des fils, avec des résultats similaires à ceux mentionnés
pour LIMIT. De même, si le fils externe (premier fils)
contient des lignes avec des valeurs de clé dupliquées, le fils externe
(second fils) est sauvegardé et les lignes correspondant à cette valeur
clé sont parcourues de nouveau. EXPLAIN ANALYZE compte
ces émissions répétées de mêmes lignes internes comme si elles étaient de
vraies lignes supplémentaires. Quand il y a de nombreux doublons
externes, le nombre réel de lignes affiché pour le nœud de plan du fils
interne peut être significativement plus grand que le nombre de lignes
qu'il y a vraiment dans la relation interne.
Les nœuds BitmapAnd et BitmapOr affichent toujours un nombre de lignes réelles à 0, du fait des limitations d'implémentation.
Habituellement, la sortie d'EXPLAIN affichera chaque
nœud de plan généré par le planificateur de requêtes. Néanmoins, il
existe des cas où l'exécuteur peut déterminer que certains nœuds n'ont
pas besoin d'être exécutés car ils ne produisent aucune ligne.
(Actuellement, ceci peut n'arriver qu'aux nœuds enfants du nœud
Append qui parcourt une table partitionnée.) Quand
cela arrive, ces nœuds sont omis de la sortie de la commande
EXPLAIN et une annotation Subplans Removed:
apparaît à la place.
N