Discussion :

[mortimer.pw]

Bonjour à tous,

Nous avons un problème sur une requête qui met 3 heures à s'éxecuter.

Nous utilisons une BD PostgreSQL 8.2.
Chaque week-end :
vacuumdb -a -f -z
reindexdb -a
reindexdb -s

Tables et volume :
TEMPS=7000000
ACTION=25000
DOCUMENT=4500000

La requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select * from sch1.temps TPS,sch1.action ACT,sch2.document DOC 
where DOC.id_etat1 in ('AV','RA') and DOC.id_etat2='EX' and DOC.id_trt='1' 
and TPS.id_act1=DOC.id_act1 and TPS.id_act2=DOC.id_act2 and TPS.id_act3=DOC.id_act3 
and TPS.id_doc1=DOC.id_doc1 and TPS.id_doc2=DOC.id_doc2 
and TPS.id_type_trt_ptg ='NT' and TPS.id_type_ptg_sst='S' 
and ACT.id_act1=TPS.id_act1 AND ACT.id_act2=TPS.id_act2 
and ACT.id_act3=TPS.id_act3 and ACT.id_act4=TPS.id_act4 
and ACT.date_fin_valide>=DOC.date_envoi 
and ACT.date_deb_valide<=DOC.date_envoi and ACT.envoi_com = TRUE

Le Explain :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
"Nested Loop  (cost=0.00..21810.41 rows=1 width=930)"
"  Join Filter: (((TPS.id_doc1)::text = (DOC.id_doc1)::text) AND ((TPS.id_doc2)::text = (DOC.id_doc2)::text) AND (act.date_fin_valide >= DOC.date_envoi) AND (act.date_deb_valide <= DOC.date_envoi))"
"  ->  Nested Loop  (cost=0.00..21783.79 rows=1 width=447)"
"        ->  Seq Scan on action act  (cost=0.00..1036.92 rows=1394 width=193)"
"              Filter: envoi_com"
"        ->  Index Scan using temps_idx1 on temps TPS  (cost=0.00..14.86 rows=1 width=254)"
"              Index Cond: ((act.id_act1 = TPS.id_act1) AND (act.id_act2 = TPS.id_act2) AND (act.id_act3 = TPS.id_act3) AND (act.id_act4 = TPS.id_act4) AND (TPS.id_type_trt_ptg = 'NT'::bpchar) AND (TPS.id_type_ptg_sst = 'S'::bpchar))"
"  ->  Index Scan using document_idx2 on document DOC  (cost=0.00..26.59 rows=1 width=483)"
"        Index Cond: ((TPS.id_act1 = DOC.id_act1) AND (TPS.id_act2 = DOC.id_act2) AND (TPS.id_act3 = DOC.id_act3) AND (DOC.id_etat2 = 'EX'::bpchar))"
"        Filter: ((id_etat1 = ANY ('{AV,RA}'::bpchar[])) AND (id_trt = '1'::bpchar))"

Pouvez-vous m'aider ?
La requête est-elle bien construite ?
Comment lire le Explain ?

Merci.

[kain_tn]

Pour commencer, je t'invite à réécrire ta requête en utilisant des INNER JOIN afin de séparer les conditions de jointures des conditions de ta clause WHERE...

[SQLpro]

Voici votre requête récrite :

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
SELECT * 
 
FROM   sch1.temps TPS
 
       INNER JOIN sch1.action ACT
             ON     ACT.id_act1=TPS.id_act1 
                AND ACT.id_act2=TPS.id_act2 
                AND ACT.id_act3=TPS.id_act3 
                AND ACT.id_act4=TPS.id_act4 
 
       INNER JOIN sch2.document DOC 
             ON     TPS.id_act1 = DOC.id_act1
                AND TPS.id_act2 = DOC.id_act2 
                AND TPS.id_act3 = DOC.id_act3 
                AND TPS.id_doc1 = DOC.id_doc1 
                AND TPS.id_doc2 = DOC.id_doc2
 
WHERE DOC.id_etat1 IN ('AV','RA') 
  AND DOC.id_etat2 = 'EX' 
  AND DOC.id_trt = '1' 
  AND TPS.id_type_trt_ptg ='NT' 
  AND TPS.id_type_ptg_sst='S'
  AND ACT.envoi_com = TRUE
 
--> jointure triangulaire ! 
  AND ACT.date_fin_valide>=DOC.date_envoi 
  AND ACT.date_deb_valide<=DOC.date_envoi

1) vous avez une jointure circulaire. DOC =(join)> TPS =(join)> ACT =(join)> DOC
Ceci est généralement signe d'un modèle de données mal foutu. En effet des circularité dans l'information, à moins qu'elle ne soit asynchrone (ce qui semble être votre cas) sont un erreur dans le MCD !

2) avez vous des index sur les colonnes du filtre WHERE, à savoir :
a) un index multicolonne sur DOC (id_etat2, id_trt, id_etat1) exactement dans cet ordre
b) un index multicolonne sur TPS (id_type_trt_ptg, id_type_ptg_sst) exactement dans cet ordre
c) un index sur ACT (date_deb_valide, date_fin_valide, envoi_com)
d) des index sur les colonnes des jointures :
ACT (id_act1, id_act2, id_act3, id_act4)
TPS (id_act1, id_act2, id_act3, id_act4, id_doc1, id_doc2)
DOC (id_act1, id_act2, id_act3, id_doc1, id_doc2)

A +