Discussion :

[DeathMaker]

Bonjour,

J'ai un petit soucis de lenteur d'exécution d'une requête. J'ai une table contenant 20 000 enregistrements.
Ma requête contient quelques jointures, et PostgreSQL met environ 1200ms pour me donner le résultat.
Je ne sais pas si c'est correct comme temps de réponse, mais je trouve cela pas extraordinaire... J'ai passé ma requête au EXPLAIN, amélioré quelques éléments... mais la différence n'est pas flagrante: j'arrive à 1100ms. Les index sont bien placés, enfin, je pense...

En pièce jointe, vous trouverez mon MCD. Le modèle est assez simple:
Une dépense (depense_variable) a une origine (compte_bancaire - id_qui) et une arrivée (compte_bancaire - id_pour_qui).
Un compte_bancaire est détenu par une personne, pouvant être un utilisateur (du système).
Une dépense est d'un certain type (type_depense), et est effectuée au moyen (moyen_depense) d'une CB, chèque...

J'ai donc environ 20 000 dépenses dans la table depense_variable, et je souhaiterai affiché la liste des dépenses faites par les utilisateur du système. Voici la requête (j'ai remplacé la liste des champs par *, le résultat est sensiblement le même, de plus, j'ai besoin de tous les champs, sauf de la table utilisateur):

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
 
SELECT 	*
FROM depense_variable 
 
JOIN compte_bancaire qui ON depense_variable.id_qui = qui.id
JOIN personne quiproprietaire ON quiproprietaire.id = qui.id_personne 
 
JOIN compte_bancaire pourQui ON pourQui.id = depense_variable.id_pour_qui 
JOIN personne pourQuiproprietaire ON pourQuiproprietaire.id = pourQui.id_personne 
 
JOIN moyen_depense ON  moyen_depense.id = depense_variable.id_moyen 
JOIN type_depense ON  type_depense.id = depense_variable.id_pour_quoi 
 
WHERE  1 = 1 
AND ( 
depense_variable.archive != true OR ( 
depense_variable.archive is null ) 
) 
AND EXISTS ( 	SELECT 1 
		FROM utilisateur 
		WHERE utilisateur.id = qui.id_personne  )
order by depense_variable.quand ASC;

Cette requête mets donc en moyenne 1200ms. Voici le plan d'éxécution:

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
32
33
34
35
 
Merge Join  (cost=2177.83..2315.82 rows=9198 width=145)
  Merge Cond: (moyen_depense.id = depense_variable.id_moyen)
  ->  Sort  (cost=1.14..1.15 rows=6 width=12)
        Sort Key: moyen_depense.id
        ->  Seq Scan on moyen_depense  (cost=0.00..1.06 rows=6 width=12)
  ->  Materialize  (cost=2176.69..2291.67 rows=9198 width=133)
        ->  Sort  (cost=2176.69..2199.69 rows=9198 width=133)
              Sort Key: depense_variable.id_moyen
              ->  Hash Join  (cost=87.26..941.14 rows=9198 width=133)
                    Hash Cond: (depense_variable.id_pour_quoi = type_depense.id)
                    ->  Hash Join  (cost=85.14..812.54 rows=9198 width=118)
                          Hash Cond: (depense_variable.id_pour_qui = pourqui.id)
                          ->  Hash Join  (cost=79.00..679.93 rows=9198 width=96)
                                Hash Cond: (depense_variable.id_qui = qui.id)
                                ->  Seq Scan on depense_variable  (cost=0.00..439.96 rows=18397 width=74)
                                      Filter: ((archive <> true) OR (archive IS NULL))
                                ->  Hash  (cost=78.55..78.55 rows=36 width=22)
                                      ->  Hash Join  (cost=2.53..78.55 rows=36 width=22)
                                            Hash Cond: (qui.id_personne = quiproprietaire.id)
                                            ->  Seq Scan on compte_bancaire qui  (cost=0.00..75.52 rows=36 width=9)
                                                  Filter: (subplan)
                                                  SubPlan
                                                    ->  Seq Scan on utilisateur  (cost=0.00..1.02 rows=1 width=0)
                                                          Filter: (id = $0)
                                            ->  Hash  (cost=1.68..1.68 rows=68 width=13)
                                                  ->  Seq Scan on personne quiproprietaire  (cost=0.00..1.68 rows=68 width=13)
                          ->  Hash  (cost=5.24..5.24 rows=72 width=22)
                                ->  Hash Join  (cost=2.53..5.24 rows=72 width=22)
                                      Hash Cond: (pourqui.id_personne = pourquiproprietaire.id)
                                      ->  Seq Scan on compte_bancaire pourqui  (cost=0.00..1.72 rows=72 width=9)
                                      ->  Hash  (cost=1.68..1.68 rows=68 width=13)
                                            ->  Seq Scan on personne pourquiproprietaire  (cost=0.00..1.68 rows=68 width=13)
                    ->  Hash  (cost=1.50..1.50 rows=50 width=15)
                          ->  Seq Scan on type_depense  (cost=0.00..1.50 rows=50 width=15)

Je ne sais plus trop quoi optimiser... Mis à part que je n'ai pas encore mis le nez dans la configuration du serveur.
Y a t il quelque chose vous sautant au yeux ? Quelque chose à améliorer ? Peut être d'autre index à placer ?

Ou bien le temps de réponse vous semble correct ?

[SQLpro]

Virez l'étoile dans le SELECT et ne rapporter que les seules colonnes qui vous intérssent.
Changer cette expression avec OR :

depense_variable.archive != true OR (
depense_variable.archive IS NULL

par :

depense_variable.archive = false

Vérifiez que vous avez des index sous toutes les clefs primaires et étrangères.
Rajoutez un index qui combine id, archive et quand dans depense_variable.

Plus généralement, lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/quoi-indexer/

A +

[VASAPANCH]

Bonjour,

Je me demande si ce bout de code ne fait pas perdre en perf :

Code :

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

1
2
3
4
5
6
AND EXISTS 
(
    SELECT 1 
    FROM utilisateur 
    WHERE utilisateur.id = qui.id_personne  
)

Il est souvent préférable d'utiliser des tournures avec JOIN et test de réalisation plutôt que d'utiliser directement des tests d'existence (EXISTS) ou d'appartenance (IN, NOT IN).
J'ai fait une proposition à Xian sur une demande d'optimisation de requête : http://www.developpez.net/forums/d66...ation-requete/
Peut-être pourrez-vous vous en inspirer et voir si une tournure avec JOIN et test de réalisation n'est pas préférable ?
Je n'ai jamais regardé ce que cela donne pour EXISTS.

Sinon concernant le temps d'éxécution, il semble un peu élevé mais pas délirant. Seul le plan d'éxécution peut véritablement aider. Hélas, les plans d'éxécution ne me parle pas (en tout cas pas en français)...

Enfin, il est nécessaire que vos statistiques soient à jour pour que PG évalue le meilleur plan d'éxécution adapté à vos données.

Cordialement,