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.