Choix entre 2 requêtes : performance ? [Résolu]

[kimaidou]

Bonjour,

Difficile de décrire dans le titre ma question.

Voici le MCD utilisé pour créer ma bdd
http://img137.imageshack.us/i/mcdp.png

Voici les tables :

Code :

1
2
3
4
 
personne (id_u, u_nom, p_prenom, p_tel1, p_tel2, p_email, p_datenais, p_lieunais, #couleur, #fruit, #legume)  
liste (id_li, li_nom) 
item (id_it, it_nom, #id_li)

La table liste contient 3 lignes avec le nom des listes : "fruit", "légumes", "couleurs"
La table item contient tous les items des listes, par ex "banane", "violet", "courgette".

Les 3 colonnes couleur, fruit et legume de ma table personne sont des clés étrangères liées à la table item. Chaque personne a donc un unique fruit, un unique légume et une unique couleur

Pour récupérer dans une requête des informations sur les clients, et récupérer les intitulés, j'ai 2 possibilités:

Requête 1

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT
u_nom, p_prenom,
c.it_nom AS couleur,
f.it_nom AS fruit,
l.it_nom AS legume
FROM personne p ,
(item NATURAL JOIN liste) AS c,
(item f NATURAL JOIN liste) AS f,
(item l NATURAL JOIN liste) AS l
WHERE 
p.couleur = c.id_it
AND p.fruit = f.id_it
AND p.legume = l.id_it

Requête 2

Code :

1
2
3
4
5
6
7
 
SELECT
u_nom, p_prenom,
(SELECT it_nom FROM item NATURAL JOIN liste WHERE id_it = p.couleur) AS couleur,
(SELECT it_nom FROM item NATURAL JOIN liste WHERE id_it = p.fruit) AS fruit,
(SELECT it_nom FROM item NATURAL JOIN liste WHERE id_it = p.legume) AS legume
FROM personne p

Je ne sais pas du tout laquelle est la mieux.

* La première utilise 3 appels à la même table "item" (+jointure vers "liste") avec des alias différents et a besoin de 3 clauses WHERE
Il est simple d'ajouter des clauses WHERE comme

Code :

AND c.it_nom LIKE 'bl%'

* La 2ème utilise des SELECT imbriqués. Je ne sais pas comment ajouter une clause WHERE , par exemple sur la couleur..

Qu'est ce qui sera le mieux à l'usage, sachant que je veux utiliser les 2 tables item et list pour des colonnes d'autres tables que "personne" ?

Merci d'avance

[kimaidou]

Pour info, la première requête peut être écrite plus "joliment" ainsi

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
u_nom, p_prenom,
c.it_nom AS couleur,
f.it_nom AS fruit,
l.it_nom AS legume
FROM personne p 
INNER JOIN (item c NATURAL JOIN liste) AS c ON p.couleur = c.id_it
INNER JOIN (item f NATURAL JOIN liste) AS f ON p.fruit = f.id_it
INNER JOIN (item l NATURAL JOIN liste) AS l ON p.legume = l.id_it
 
WHERE 
 
c.it_nom LIKE 'bl%'

[kimaidou]

J'ai testé les coûts via un EXPLAIN :

Requête 1 (avec les INNER JOIN et sans WHERE)

Code :

Hash JOIN  (cost=221.33..261.85 rows=330 width=160)

Requête 2 (avec les SELECT imbriqués)

Code :

Seq Scan ON personne p  (cost=0.00..16395.48 rows=330 width=76)

Je ne suis pas spécialiste des coûts, mais il me semble que la 1ère est bien plus performante ?

[estofilo]

A quoi servent les jointures avec la table liste? Il me semble que si elles n'étaient pas là, le résultat serait le même. Si c'est le cas autant les enlever.

[kimaidou]

Envoyé par estofilo

A quoi servent les jointures avec la table liste? Il me semble que si elles n'étaient pas là, le résultat serait le même. Si c'est le cas autant les enlever.

Entièrement d'accord ! Je ne sais pas pourquoi je traine la table liste, vu que les texte des items sont dans item. Cela aurait pu servir si je souhaitais avoir le nom de la liste dans une colonne. Mais ici c'est inutile

Maintenant, voici les 2 nouvelles requêtes et les coûts associés:

Requête 1

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT
u_nom, p_prenom,
c.it_nom AS couleur,
f.it_nom AS fruit,
l.it_nom AS legume
FROM personne p 
INNER JOIN item c ON p.couleur = c.id_it
INNER JOIN item f ON p.fruit = f.id_it
INNER JOIN item l ON p.legume = l.id_it

Code :

"Hash Join  (cost=108.30..135.21 rows=330 width=160)"

Requête 2

Code :

1
2
3
4
5
6
SELECT
u_nom, p_prenom,
(SELECT it_nom FROM item WHERE id_it = p.couleur) AS mycouleur,
(SELECT it_nom FROM item WHERE id_it = p.fruit) AS myfruit,
(SELECT it_nom FROM item WHERE id_it = p.legume) AS mylegume
FROM personne p

Code :

"Seq Scan on personne p  (cost=0.00..8198.20 rows=330 width=76)"

Au final, quelle est la meilleure méthode ? La plus propre, celle qui permettra de réaliser des requêtes plus complexes sans difficulté ? La plus performante ?

[CinePhil]

Je suppose que les colonnes couleur, fruit et legume sont indexées puisque ce sont des clés étrangères ?

Si le cost est exprimé en secondes, 108 secondes avec seulement 330 lignes, il y a un problème quelque part !

La première requête est meilleur sur le plan conceptuel mais je suis étonné de son résultat par rapport à la seconde.

[kimaidou]

Envoyé par CinePhil

Je suppose que les colonnes couleur, fruit et legume sont indexées puisque ce sont des clés étrangères ?

Non, je n'avais pas indexé

Si le cost est exprimé en secondes, 108 secondes avec seulement 330 lignes, il y a un problème quelque part !

Non, les coûts donnés, si je me souviens bien, ne sont pas donnés en seconde.
Je recherche dans la doc...
http://doc.postgresql.fr/9.0/sql-explain.html nous dit qu'ils sont "mesurés en unités de récupération de pages sur le disque". Il y a en fait le temps de démarrage, et le temps d'exécution totale.
Il n'y a pas non plus 330 lignes (seulement 10).

Donc dans mon cas, même sans index, le temps d'execution total était donc en fait bien meilleur avec la requête 1 (135) qu'àvec la 2 (8198)

La première requête est meilleur sur le plan conceptuel mais je suis étonné de son résultat par rapport à la seconde.

Et depuis l'ajout d'index,
Requête 1:
"Nested Loop (cost=0.00..63.55 rows=3 width=160)"
Requête 2:
"Seq Scan on personne p (cost=0.00..75.44 rows=3 width=76)"

On est donc encore gagnant avec la 1, comme quoi quand c'est mieux au niveau conceptuel, c'est souvent mieux au niveau performances.

Merci de m'avoir obligé à réfléchir !