Probleme de jointure externe ...

Version imprimable

Bonjour,

Sauriez-vous pourquoi, postgresql se met a me faire un Right Join , alors que je lui ai demander de me faire un Left Join ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EXPLAIN ANALYSE
SELECT * FROM parametre_general     
	LEFT JOIN (
        SELECT * FROM param_AGENT
        WHERE pspec_ref_id = 94
        ) AS pspec ON pgen_id = pspec_pgen_id
      	WHERE pgen_id = 94
 
QUERY PLAN
"Merge Right Join  (cost=0.00..7.19 rows=2 width=57) (actual time=0.452..0.476 rows=1 loops=1)"
"  Merge Cond: (""outer"".pspec_pgen_id = ""inner"".pgen_id)"
"  ->  Index Scan using index_agent on param_agent  (cost=0.00..3.19 rows=7 width=12) (actual time=0.196..0.229 rows=2 loops=1)"
"        Index Cond: (pspec_ref_id = 94)"
"  ->  Index Scan using pk_parametre_general on parametre_general  (cost=0.00..3.96 rows=2 width=45) (actual time=0.060..0.076 rows=1 loops=1)"
"        Index Cond: (pgen_id = 94)"
"Total runtime: 1.189 ms"

Ce problème est d'autant plus surprenant que pour un id différent,
j'ai le bon comportement ...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXPLAIN ANALYSE
        SELECT * FROM parametre_general     
	LEFT OUTER JOIN (
        SELECT * FROM param_AGENT
        WHERE pspec_ref_id = 95
        ) AS pspec ON pspec_pgen_id = pgen_id
      	WHERE pgen_id = 94
 
 
QUERY PLAN
"Merge Left Join  (cost=3.29..7.30 rows=2 width=57) (actual time=0.724..0.761 rows=1 loops=1)"
"  Merge Cond: (""outer"".pgen_id = ""inner"".pspec_pgen_id)"
"  ->  Index Scan using pk_parametre_general on parametre_general  (cost=0.00..3.96 rows=2 width=45) (actual time=0.168..0.184 rows=1 loops=1)"
"        Index Cond: (pgen_id = 94)"
"  ->  Sort  (cost=3.29..3.31 rows=7 width=12) (actual time=0.392..0.405 rows=4 loops=1)"
"        Sort Key: param_agent.pspec_pgen_id"
"        ->  Index Scan using index_agent on param_agent  (cost=0.00..3.19 rows=7 width=12) (actual time=0.088..0.221 rows=7 loops=1)"
"              Index Cond: (pspec_ref_id = 95)"
"Total runtime: 1.487 ms"

J'ai reindexé, fait un vacuum analyse de la table , sans changement ... :/

Help ................

05/09/2005, 09h23
amenis

Bonjour,

Je viens de rentrer de vacances, et j'espère que si personne à répondu a cette question , c'est que vous y êtiez aussi ;)

je tente donc un +1

Bon on dirait que les 3 semaines de vacances ont été bénéfiques :

J'ai corrigé le problème de cette manière :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
EXPLAIN ANALYSE
SELECT * FROM parametre_general     
   LEFT JOIN (
        SELECT * FROM param_AGENT
        WHERE pspec_ref_id = 94
        AND pspec_pgen_id = 94
        ) AS pspec ON pgen_id = pspec_pgen_id
         WHERE pgen_id = 94
 
QUERY PLAN
"Nested Loop Left Join  (cost=0.00..10.40 rows=2 width=57) (actual time=0.507..0.572 rows=1 loops=1)"
"  ->  Index Scan using pk_parametre_general on parametre_general  (cost=0.00..3.96 rows=2 width=45) (actual time=0.171..0.182 rows=1 loops=1)"
"        Index Cond: (pgen_id = 94)"
"  ->  Index Scan using index_agent on param_agent  (cost=0.00..3.21 rows=1 width=12) (actual time=0.253..0.294 rows=1 loops=1)"
"        Index Cond: (pspec_ref_id = 94)"
"        Filter: (pspec_pgen_id = 94)"
"Total runtime: 1.236 ms"

Je comprends pas pourquoi le fait de doubler la contrainte :
"ON pgen_id = pspec_pgen_id" et "WHERE pgen_id = 94"
et "AND pspec_pgen_id = 94

fait que postgresql ne perd plus les pedales ...

06/09/2005, 09h59
Escandil

:arrow: :resolu: Stp :wink: