Discussion :

[everest31]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[estofilo]

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.

[SQLpro]

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

A +

[everest31]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SQLpro]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[everest31]

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