comportement étrange d'une jointure ...

[amenis]

Bonjour,

En partant d'une requête qui s'execute en 4s (je ne l'affiche pas , car elle est grosse) , et en lui rajoutant des contraintes supplémentaires dans les clauses de jointure , celle-ci met du coup un temps infini ... (j'ai attendu 2 min , elle n'était tjs pas fini).

Pour résumer , au départ j'ai une requête du style :
SELECT * FROM table AS a
JOIN table AS b
ON a.f1 = b.f1

Je rajoute une contrainte :

SELECT * FROM table AS a
JOIN table AS b
ON a.f1 = b.f1
AND a.f2 = b.f2

cette dernière déconne complet, comment est-ce possible ?
Notez , qu'il s'agit d'une jointure sur elle-même ...

[amenis]

Pour apporter quelques précisions ...
Cette table contient :
- 100 champs
- Une clée primaire sur 7 champs (c'est pas moi )

Pour résumer , au départ j'ai une requête du style :
SELECT * FROM table AS a
JOIN table AS b
ON a.pk1 = b.pk1
AND a.pk2 = b.pk2
AND a.pk3 = b.pk3
AND a.pk4 = b.pk4
AND a.pk5 = b.pk5

En rajoutant une contrainte sur un champ supplémentaire de la clée primaire , le temps d'éxecution explose ...
Y'a quelque chose qui m'échappe

[kryskool]

Bonjour

a part la clé primaire ta table possède t'elle un index, si non en crée un qui va bien, si oui vérifie avec l'explain plan pour voir si celui si est utilisé

KrysKool.

[amenis]

Merci,

J'ai fait l'explain des 2 requêtes , et effectivemment elles n'ont rien a voir l'une avec l'autre.

Mais j'ai beaucoup de mal à m'y retrouver

Ce que je peux dire , c'est que
bgp_agent_prenom
bgp_agent_nom
bgp_agent_mois
bgp_agent_numero_homonyme
bgp_agent_site

Font partis de la clée primaire et donc un index multiple existe sur ces champs.

Dans la premiere requête marche , elle fait un hash join ????, alors que dès que je rajoute bgp_agent_numero_homonyme dans la condition de jointure , il n'y a plus de Hash join , tout est fait dans le join filter ....
Une idée ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Nested Loop  (cost=4.84..47.08 rows=1 width=233)
  ->  Hash JOIN  (cost=4.84..29.94 rows=1 width=162)
        Hash Cond: ((("outer".bgp_agent_nom)::text = ("inner".bgp_agent_nom)::text) 
        				AND (("outer".bgp_agent_prenom)::text = ("inner".bgp_agent_prenom)::text)
        				AND ("outer".bgp_agent_mois = ("inner".bgp_agent_mois + 1)))
        JOIN Filter: ("outer".bgp_agent_tr <> "inner".bgp_agent_tr)
        ->  Seq Scan ON bgp_agent ba2  (cost=0.00..25.00 rows=5 width=105)
              Filter: ((bgp_agent_tr <> 0::numeric) AND (57 = bgp_agent_site_id))
        ->  Hash  (cost=4.83..4.83 rows=1 width=118)
              ->  INDEX Scan USING pk_bgp_agent ON bgp_agent ba1  (cost=0.00..4.83 rows=1 width=118)
                    INDEX Cond: ((bgp_agent_type_exercice = 'P'::bpchar) 
                    				AND ((bgp_agent_exercice)::text = '2005'::text)
                    				AND (bgp_agent_site_id = 57))
                    Filter: (bgp_agent_tr <> 0::numeric)
  ->  INDEX Scan USING pk_bgp_grade ON bgp_grade bg1  (cost=0.00..17.07 rows=5 width=104)
        INDEX Cond: ((bg1.bgp_grade_id)::text = ("outer".bgp_agent_grade)::text)

En rajoutant bgp_agent_numero_homonyme dans les conditions de jointure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Nested Loop  (cost=0.00..47.10 rows=1 width=233)
  ->  Nested Loop  (cost=0.00..29.96 rows=1 width=162)
        JOIN Filter: ((("inner".bgp_agent_nom)::text = ("outer".bgp_agent_nom)::text) 
        					AND (("inner".bgp_agent_prenom)::text = ("outer".bgp_agent_prenom)::text)
        					AND ("inner".bgp_agent_numero_homonyme = "outer".bgp_agent_numero_homonyme)
        					AND ("inner".bgp_agent_mois = ("outer".bgp_agent_mois + 1))
        					AND ("inner".bgp_agent_tr <> "outer".bgp_agent_tr))
        ->  INDEX Scan USING pk_bgp_agent ON bgp_agent ba1  (cost=0.00..4.83 rows=1 width=118)
              INDEX Cond: ((bgp_agent_type_exercice = 'P'::bpchar)
              				AND ((bgp_agent_exercice)::text = '2005'::text)
              				AND (bgp_agent_site_id = 57))
              Filter: (bgp_agent_tr <> 0::numeric)
        ->  Seq Scan ON bgp_agent ba2  (cost=0.00..25.00 rows=5 width=105)
              Filter: ((bgp_agent_tr <> 0::numeric) AND (57 = bgp_agent_site_id))
  ->  INDEX Scan USING pk_bgp_grade ON bgp_grade bg1  (cost=0.00..17.07 rows=5 width=104)
        INDEX Cond: ((bg1.bgp_grade_id)::text = ("outer".bgp_agent_grade)::text)

[amenis]

Bon en rajoutant un index unique sur les 7 champs de la clée primaire.
Et en rajoutant bgp_agent_numero_homonyme dans la condition de jointure j'obtiens le
même genre d'explain que la première requête.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Nested Loop  (cost=5.03..466.60 rows=1 width=233)
  ->  Hash JOIN  (cost=5.03..449.45 rows=1 width=162)
        Hash Cond: ((("outer".bgp_agent_nom)::text = ("inner".bgp_agent_nom)::text)
        				AND (("outer".bgp_agent_prenom)::text = ("inner".bgp_agent_prenom)::text)
        				AND ("outer".bgp_agent_numero_homonyme = "inner".bgp_agent_numero_homonyme)
        				AND ("outer".bgp_agent_mois = ("inner".bgp_agent_mois + 1)))
        JOIN Filter: ("outer".bgp_agent_tr <> "inner".bgp_agent_tr)
        ->  INDEX Scan USING index_bgp_agent ON bgp_agent ba2  (cost=0.00..441.94 rows=110 width=105)
              INDEX Cond: (57 = bgp_agent_site_id)
              Filter: (bgp_agent_tr <> 0::numeric)
        ->  Hash  (cost=5.02..5.02 rows=1 width=118)
              ->  INDEX Scan USING pk_bgp_agent ON bgp_agent ba1  (cost=0.00..5.02 rows=1 width=118)
                    INDEX Cond: ((bgp_agent_type_exercice = 'P'::bpchar) 
                    				AND ((bgp_agent_exercice)::text = '2005'::text)
                    				AND (bgp_agent_site_id = 57))
                    Filter: (bgp_agent_tr <> 0::numeric)
  ->  INDEX Scan USING pk_bgp_grade ON bgp_grade bg1  (cost=0.00..17.07 rows=5 width=104)
        INDEX Cond: ((bg1.bgp_grade_id)::text = ("outer".bgp_agent_grade)::text)

Par contre en voulant rajouter bgp_agent_exercice dans la condition de jointure (qui fait aussi parti de la clée primaire et de l'index),
Il recommence le même délire qu'avant le rajout de l'index unique

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Nested Loop  (cost=0.00..28.21 rows=1 width=233)
  ->  Nested Loop  (cost=0.00..11.06 rows=1 width=162)
        JOIN Filter: ((("inner".bgp_agent_nom)::text = ("outer".bgp_agent_nom)::text)
        					AND (("inner".bgp_agent_prenom)::text = ("outer".bgp_agent_prenom)::text)
        					AND ("inner".bgp_agent_numero_homonyme = "outer".bgp_agent_numero_homonyme)
        					AND ("inner".bgp_agent_mois = ("outer".bgp_agent_mois + 1))
        					AND ("inner".bgp_agent_tr <> "outer".bgp_agent_tr))
        ->  INDEX Scan USING pk_bgp_agent ON bgp_agent ba1  (cost=0.00..5.02 rows=1 width=118)
              INDEX Cond: ((bgp_agent_type_exercice = 'P'::bpchar) 
              				 AND ((bgp_agent_exercice)::text = '2005'::text)
              				 AND (bgp_agent_site_id = 57))
              Filter: (bgp_agent_tr <> 0::numeric)
        ->  INDEX Scan USING index_bgp_agent ON bgp_agent ba2  (cost=0.00..6.02 rows=1 width=105)
              INDEX Cond: ((57 = bgp_agent_site_id) 
              				 AND ('2005'::text = (bgp_agent_exercice)::text))
              Filter: (bgp_agent_tr <> 0::numeric)
  ->  INDEX Scan USING pk_bgp_grade ON bgp_grade bg1  (cost=0.00..17.07 rows=5 width=104)
        INDEX Cond: ((bg1.bgp_grade_id)::text = ("outer".bgp_agent_grade)::text)

Et dire que j'ai encore 2 champs à rajouter dans la condition de jointure

[Jedei]

Bonjour,
Sans regarder en détail ta requête, les index ne sont utilisés sous PostgreSQL que lorsque tous les champs qui les composent sont inclus dans la requête. Après chaque création ou modification d'index il faut réaliser un ANALYZE pour que le planificateur mette à jour ses statistiques.
D'autre part, peut-être serait-il préférable d'utiliser une syntaxe du type INNER JOIN pour éviter des ambiguités, ou regarder tout simplement avec des clauses WHERE ce que ça donne.
Ceci dit, je pense quand même que ton problème vient de la constitution des index.