Optimisation requête SQL

Bonjour,

Voila je suis entrain de transposer une base MySQL vers PostgreSQL et n'ayant pas encore trop l'habitude de la fonction explain sous pgsql je voulais avoir votre avis sur cette sortie:
Ma requete SQL est:

Code:

---

1 2 3 4 5

explain SELECT timbre.id, timbre.ref, timbre.prix, variete.nom AS nomvariete, couleur.nom AS nomcouleur FROM timbre JOIN couleur ON couleur.id = timbre.couleur JOIN variete ON variete.id = timbre.variete WHERE timbre.ref = '262'

---

j'ai des index sur les champs id de chaque table

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Hash Join (cost=2.18..3.25 rows=1 width=145)   Hash Cond: (couleur.id = timbre.couleur)   -> Seq Scan on couleur (cost=0.00..1.04 rows=4 width=65)   -> Hash (cost=2.17..2.17 rows=1 width=86)   -> Hash Join (cost=1.07..2.17 rows=1 width=86)   Hash Cond: (variete.id = timbre.variete)   -> Seq Scan on variete (cost=0.00..1.06 rows=6 width=24)   -> Hash (cost=1.06..1.06 rows=1 width=68)   -> Seq Scan on timbre (cost=0.00..1.06 rows=1 width=68)   Filter: (ref = '262'::bpchar)

---

Merci d'avance de toutes vos remarques.

Deux remarques:

1. Apparemment il y a tellement peu de contenu dans les 3 tables que les index ne sont pas utilisés.

2. Il vaut mieux faire EXPLAIN ANALYZE qui permet de comparer les estimations avec les vraies durées et nombres de lignes.

Votre "timbre.ref" est un littéral ou un nombre ?

A +

effectivement on fait un modèle avec peu de donnée, on va essayer de charger la base pour que les résultat soit plus cohérent.

@SQLpro:
c'est un littéral car certains valeurs comporte que des chiffres mais il peut y avoir des lettres et le problème est que durant une époque il n'y avait pas de nomenclature sur ce champ résultat on peut se retrouver avec des valeurs du style 'A831' ou '456' ou '596J' voir carrément des '45H65P'.
Donc totalement impossible d'arriver a normaliser ce champ, la seule chose de normalisé, est la valeur qui est unique et au maximum composé de 10 chiffres et/ou lettres.

par contre heureusement c'est la seule anomalie de la base, tout le reste a pu être normaliser

En rajoutant un peu de donnée on obtient ca sur cette requete:

Code:

---

1 2 3 4 5

EXPLAIN ANALYZE SELECT timbre.id, timbre.ref, timbre.prix, variete.nom AS nomvariete, couleur.nom AS nomcouleur FROM timbre JOIN couleur ON couleur.id = timbre.couleur JOIN variete ON variete.id = timbre.variete WHERE timbre.ref = '4'

---

explain:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

QUERY PLAN   Hash Join (cost=2.23..12.46 rows=41 width=102) (actual time=0.373..1.039 rows=41 loops=1)   Hash Cond: (timbre.variete = variete.id)   -> Hash Join (cost=1.09..10.77 rows=41 width=84) (actual time=0.138..0.658 rows=41 loops=1)   Hash Cond: (timbre.couleur = couleur.id)   -> Seq Scan on timbre (cost=0.00..9.11 rows=41 width=25) (actual time=0.030..0.408 rows=41 loops=1)   Filter: (ref = '4'::bpchar)   Rows Removed by Filter: 368   -> Hash (cost=1.04..1.04 rows=4 width=65) (actual time=0.028..0.028 rows=4 loops=1)   Buckets: 1024 Batches: 1 Memory Usage: 1kB   -> Seq Scan on couleur (cost=0.00..1.04 rows=4 width=65) (actual time=0.006..0.015 rows=4 loops=1)   -> Hash (cost=1.06..1.06 rows=6 width=24) (actual time=0.067..0.067 rows=6 loops=1)   Buckets: 1024 Batches: 1 Memory Usage: 1kB   -> Seq Scan on variete (cost=0.00..1.06 rows=6 width=24) (actual time=0.030..0.043 rows=6 loops=1)   Total runtime: 1.386 ms

---

S'ils n'existent pas, créez ces trois index :

Code:

---

1 2 3 4 5 6 7 8

CREATE INDEX X_T_TBR_REF_VCIP ON timbre (ref, variete, couleur, id, prix);   CREATE INDEX X_T_VAR_ID_N ON variete (id, nom);   CREATE INDEX X_T_COU_ID_N ON couleur (id, nom);

---

Prévoyez un facteur de remplissage de l'ordre de 80 à 90 %

NOTA : testez l'index X_T_TBR_REF_VCIP en permutant variete, couleur au besoin (nous n'avons pas les cardinalités de vos tables).

A +

Pour les cardinalités, les tables s'articulent ainsi.

La table couleur et variete c'est du 1 pour 1 entre id et nom.

Pour la table timbre:
Id: numero unique type serial
ref: litteral non unique
variete: id de la table variete (a priori 26 éléments finaux dans la table)
couleur: id de la table couleur ( 692 éléments)
prix: a considérer comme quasi unique
mais il manque encore beaucoup de champs sur cette table

Donc, couleur en premier et non pas variété.

A +

Salut
sqlpro, peut-on savoir pourquoi utiliser des colonnes dans la clé des indexes alors qu'elles ne sont ni dans le where ni dans la jointure?
Je présume que cela vient du plan d’exécution, mais je ne sais pas encore bien lire ces plans.:oops:
Merci d'avance.

Citation:

---

Envoyé par alassanediakite

peut-on savoir pourquoi utiliser des colonnes dans la clé des indexes alors qu'elles ne sont ni dans le where ni dans la jointure?

---

Elle sont dans le select. Les mettre dans l'index permet d'éviter d'aller chercher les données dans la table : l'index à lui seul contient toutes les données nécessaires pour répondre à la requête.

C'est un index "couvrant"

Salut
Merci de la réponse.
Il y a-t-il une fréquence d'utilisation de ces colonnes ou de cette requête qui justifie une telle solution sachant la lenteur des mise à jour (ajout, suppression, modification) qui en découlera?
@+

bonsoir en ce qui me concerne cet index me va très bien car l'utilisation de la table est très particulier.
Un update massif une fois par mois durant la maintenance de la base apres ca ne sera que de la lecture 100% du temps.

Par contre effectivement un index couvrant sur une base qui est régulièrement et/ou en permanence mise a jour c'est pas top top en terme de perf.

Apres dans mon cas vu le gain que ce genre d'index peut avoir par rapport a la masse de donnée, je pense que l'opération est largement acceptable d'avoir des perfs en berne sur les requêtes de mise à jour qui seront plutôt rare et faites en plus en mode déconnecté.