Il y a trois stratégies possibles pour faire une jointure :
Le plan d'exécution final comporte donc des noeuds de scan indexés ou séquentiels, des noeuds de jointure, plus d'autres noeuds intermédiaires comme des noeuds de tri, de fonctions d'agrégations ou de calculs. La plupart de ces noeuds procèdent en plus à des opérations de sélection (filtrage) ou de projection. Une des prérogatives de l'optimiseur est d'attacher les conditions de sélection (clause where) aux noeuds appropriés dans le plan d'exécution.
Voici un exemple d'une jointure sur deux tables sans utiliser la clé primaire. On utilise ici la table des enseignants : chaque enseignant d'une université est recruté dans une discipline. On veut les mettre en rapport avec les unités d'enseignement de leur discipline. Pour cela, on utilise la table discipline_unite_enseignement qui modélise la relation maillée entre unités d'enseignement et disciplines. En effet, une unité d'enseignement peut relever de plusieurs disciplines (c'est surtout vrai en ce qui concerne les sciences de la vie, où biologistes et biochimistes doivent souvent intervenir ensemble).
On a ajouté dans les exemples quelques informations pour mesurer la taille des tables concernées.
Exemple :
jeanperrin=# select count(*) from enseignant;
count
-------
140
(1 ligne)
jeanperrin=# select count(*) from discipline_unite_enseignement;
count
-------
284
(1 ligne)
jeanperrin=# explain select nom, id_unite_enseignement
jeanperrin-# from enseignant natural join discipline_unite_enseignement ;
QUERY PLAN
--------------------------------------------------------------------------
Merge Join (cost=9.39..85.02 rows=4942 width=23)
Merge Cond: ("outer".id_discipline = "inner".id_discipline)
-> Index Scan using discipline_unite_enseignem_pkey on
discipline_unite_enseignement
(cost=0.00..12.45 rows=284 width=8)
-> Sort (cost=9.39..9.74 rows=140 width=15)
Sort Key: enseignant.id_discipline
-> Seq Scan on enseignant (cost=0.00..4.40 rows=140 width=15)
(6 lignes)
Exemple : Exemples d'une jointure sur une clé primaire :
base_test=# explain select * from enseignant natural join statut;
QUERY PLAN
--------------------------------------------------------------------
Hash Join (cost=1.09..5.28 rows=7 width=522)
Hash Cond: ("outer".id_statut = "inner".id_statut)
-> Seq Scan on enseignant (cost=0.00..3.40 rows=140 width=452)
-> Hash (cost=1.07..1.07 rows=7 width=70)
-> Seq Scan on statut (cost=0.00..1.07 rows=7 width=70)
(5 lignes)
Pour comparer avec les autres possibilités de jointure, on peut modifier les valeurs d'environnement :
base_test=# set enable_hashjoin = off;
SET
base_test=# explain select * from enseignant natural join statut;
QUERY PLAN
--------------------------------------------------------------------------
Merge Join (cost=9.56..10.38 rows=7 width=522)
Merge Cond: ("outer".id_statut = "inner".id_statut)
-> Sort (cost=8.39..8.74 rows=140 width=452)
Sort Key: enseignant.id_statut
-> Seq Scan on enseignant (cost=0.00..3.40 rows=140 width=452)
-> Sort (cost=1.17..1.19 rows=7 width=70)
Sort Key: statut.id_statut
-> Seq Scan on statut (cost=0.00..1.07 rows=7 width=70)
(8 lignes)
base_test=# set enable_mergejoin = off;
SET
base_test=# explain select * from enseignant natural join statut;
QUERY PLAN
--------------------------------------------------------------------
Nested Loop (cost=0.00..37.12 rows=7 width=522)
Join Filter: ("inner".id_statut = "outer".id_statut)
-> Seq Scan on statut (cost=0.00..1.07 rows=7 width=70)
-> Seq Scan on enseignant (cost=0.00..3.40 rows=140 width=452)
(4 lignes)
La dernière variable d'environnement pour les jointures est enable_nestloop.
Exemple : Encore un exemple avec une fonction d'agrégats :
base_test=# explain select sum(nb_heures_faites)
from enseignant_element_enseignement
where id_enseignant <50;
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=1.36..1.36 rows=1 width=4)
-> Seq Scan on enseignant_element_enseignement (cost=0.00..1.34 rows=9 width=4)
Filter: (id_enseignant < 50)
(3 lignes)
Il faut noter que lorsqu'il s'agit de fonctions (d'agrégation ou pas) définies par l'utilisateur, et donc en SQL ou en PL/SQL ou dans un autre langage, la commande explain ne détaille pas le plan d'exécution pour les requêtes SQL internes aux fonctions.
D'après les exemples précédents, on voit que le SGBD choisit le meilleur plan d'exécution. Est-ce que cela dédouane le programmeur de tout effort à effectuer ?
Exemple :
base_test=# explain select id_enseignant
from enseignant_element_enseignement
where id_element_enseignement in
(select id_element_enseignement
from element_enseignement
where id_nature_enseignement = 3);
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on enseignant_element_enseignement
(cost=0.00..337.15 rows=14 width=4)
Filter: (subplan)
SubPlan
-> Materialize (cost=12.44..12.44 rows=3 width=4)
-> Seq Scan on element_enseignement
(cost=0.00..12.44 rows=3 width=4)
Filter: (id_nature_enseignement = 3)
(6 lignes)
base_test=# explain select id_enseignant
from enseignant_element_enseignement
natural join element_enseignement
where id_nature_enseignement = 3;
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=12.44..13.85 rows=1 width=12)
Hash Cond: ("outer".id_element_enseignement
= "inner".id_element_enseignement)
-> Seq Scan on enseignant_element_enseignement
(cost=0.00..1.27 rows=27 width=8)
-> Hash (cost=12.44..12.44 rows=3 width=4)
-> Seq Scan on element_enseignement
(cost=0.00..12.44 rows=3 width=4)
Filter: (id_nature_enseignement = 3)
(6 lignes)
base_test=# select count(*) from enseignant_element_enseignement;
count
-------
27
(1 ligne)
base_test=# select 27*12.44;
?column?
----------
335.88
(1 ligne)
La commande explain analyze permet de comparer les estimations de coûts faits par explain et les ressources réellement nécessitées par l'exécution de la requête. Les éléments chiffrés ne peuvent pas se comparer car les unités ne correspondent pas (l'unité de mesure est la milliseconde pour explain analyze),et car certaines actions sont prises en compte d'un côté et pas de l'autre (dans le cas d'un insert par exemple, le temps mis pour procéder à l'insertion n'est pas pris en compte dans les coûts estimés). Néanmoins, on peut comparer les différents ratios proposés par différents plans d'exécution.
Exemple :
base_test=# explain analyze select id_enseignant
from enseignant_element_enseignement
where id_element_enseignement in (
select id_element_enseignement
from element_enseignement
where id_nature_enseignement = 3);
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on enseignant_element_enseignement
(cost=0.00..337.15 rows=14 width=4)
(actual time=2.49..6.37 rows=11 loops=1)
Filter: (subplan)
SubPlan
-> Materialize
(cost=12.44..12.44 rows=3 width=4)
(actual time=0.05..0.11 rows=106 loops=27)
-> Seq Scan on element_enseignement
(cost=0.00..12.44 rows=3 width=4)
(actual time=0.10..1.19 rows=124 loops=1)
Filter: (id_nature_enseignement = 3)
Total runtime: 6.48 msec
(7 lignes)
base_test=# explain analyze select id_enseignant
from enseignant_element_enseignement natural join element_enseignement
where id_nature_enseignement = 3;
QUERY PLAN
----------------------------------------------------------------
Hash Join (cost=12.44..13.85 rows=1 width=12)
(actual time=1.31..1.42 rows=11 loops=1)
Hash Cond: ("outer".id_element_enseignement
= "inner".id_element_enseignement)
-> Seq Scan on enseignant_element_enseignement
(cost=0.00..1.27 rows=27 width=8)
(actual time=0.01..0.07 rows=27 loops=1)
-> Hash (cost=12.44..12.44 rows=3 width=4)
(actual time=1.25..1.25 rows=0 loops=1)
-> Seq Scan on element_enseignement
(cost=0.00..12.44 rows=3 width=4)
(actual time=0.06..1.05 rows=124 loops=1)
Filter: (id_nature_enseignement = 3)
Total runtime: 1.52 msec
(7 lignes)
Il est important aussi de ne pas interpréter les résultats de la commande explain comme étant des résultats valables pour toute table. Les estimations des coûts et donc les choix de plans d'exécution sont dépendants des tables. Ainsi, une petite table n'excédant pas une page-disque, même si elle est indexée, ne sera jamais scannée autrement que séquentiellement.