On utilise pour les exemples une base de données qui modélise les enseignements à l'U.F.R. La première table qu'on regarde est la table ue_ee qui modélise une relation maillée entre des unités d'enseignements et les éléments d'enseignements qui les composent. Cette relation est maillée car une élément d'enseignement peut être une composante de plusieurs unités d'enseignements différentes.
Exemple :
jeanperrin=# \d ue_ee
Table "public.ue_ee"
Colonne | Type | Modifications
-------------------------+---------+---------------
id_element_enseignement | integer | not null
id_unite_enseignement | integer | not null
maj_auto | boolean | default false
nb_gpe | real | default 0
Index: ue_ee_pkey primary key btree
(id_unite_enseignement, id_element_enseignement)
Foreign Key constraints: ue_ee_del_cascade_ue
FOREIGN KEY (id_unite_enseignement)
REFERENCES unite_enseignement(id_unite_enseignement)
ON UPDATE NO ACTION ON DELETE CASCADE,
ue_ee_del_cascade_ee
FOREIGN KEY (id_element_enseignement)
REFERENCES element_enseignement(id_element_enseignement)
ON UPDATE NO ACTION ON DELETE CASCADE
La clé primaire de cette table est une clé composée (id_element_enseignement, id_unite_enseignement).
Voici un premier exemple de requête.
jeanperrin=# explain select * from ue_ee;
QUERY PLAN
--------------------------------------------------------
Seq Scan on ue_ee (cost=0.00..9.71 rows=571 width=13)
(1 row)
Un second exemple, avec une condition de sélection :
Exemple :
jeanperrin=# explain select * from ue_ee where nb_gpe>2;
QUERY PLAN
---------------------------------------------------------
Seq Scan on ue_ee (cost=0.00..11.14 rows=190 width=13)
Filter: (nb_gpe > 2::double precision)
(2 rows)
Le coût total estimé augmente : les 571 enregistrements devront être
parcourus, avec en plus le temps nécessaire au test de la condition
where.
On utilise un filtre qui limite le nombre de lignes retournées (190), mais on utilise toujours un scan séquentiel.
Exemple :
jeanperrin=# explain select * from ue_ee where id_unite_enseignement<18 ;
QUERY PLAN
---------------------------------------------------------
Seq Scan on ue_ee (cost=0.00..11.14 rows=190 width=13)
Filter: (id_unite_enseignement < 18)
Même résultat estimé...car utilisation d'un filtre!
Un exemple en utilisant un des attributs de la clé primaire de la table :
Exemple :
jeanperrin=# explain select * from ue_ee where id_unite_enseignement=18 ;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using ue_ee_pkey on ue_ee (cost=0.00..10.45 rows=3 width=13)
Index Cond: (id_unite_enseignement = 18)
(2 rows)
Le nombre d'enregistrements visité, grâce à l'utilisation de l'index, chute à 3.
Le scan de la table est effectué en utilisant un index sur la clé primaire. Cela améliore le temps d'exécution par rapport aux deux requêtes précédentes.
Et maintenant, en utilisant complètement la clé primaire :
Exemple :
jeanperrin=# explain select * from ue_ee
where id_unite_enseignement=12
and id_element_enseignement=80;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using ue_ee_pkey on ue_ee (cost=0.00..5.57 rows=1 width=13)
Index Cond: ((id_unite_enseignement = 12)
AND (id_element_enseignement = 80))
(2 rows)
L'utilisation de l'index permet d'obtenir un résultat bien meilleur qu'un examen complet de la table, même sans condition (premier exemple).
Enfin, un exemple qui combine scan indexé et filtrage des résultats :
Exemple :
jeanperrin=# explain select * from ue_ee
where id_unite_enseignement=18 and nb_gpe>3;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using ue_ee_pkey on ue_ee (cost=0.00..10.46 rows=1 width=13)
Index Cond: (id_unite_enseignement = 18)
Filter: (nb_gpe > 3::double precision)
(3 rows)