Discussion :

[mortimer.pw]

Bonjour,

Je travaille sur une BD 8.4.2 sous Cent-OS 5.2.

J'ai une requête qui est trés lente alors que le volume de données dans ma base est ridicule.
La requête doit me retourner la dernière ligne de travail de la personne '0000123' pour la veille.

Voilà la requête :

Code :

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

1
2
3
4
5
6
7
 
    SELECT T.*,A.*,D.libelle
    FROM Travail T LEFT OUTER JOIN Document D ON (CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1),Vue_Action A
    WHERE T.jour=CURRENT_DATE-1
    AND T.personne='0000123'
    AND T.id_action=A.id_act1||A.id_act2||A.id_act3||A.id_act4
    ORDER BY T.jour DESC,T.heure DESC LIMIT 1;

Je fais une jointure entre travail et action pour récupérer les paramètres liés à une action.
Je fais une jointure entre travail et document pour récupérer le libelle du document

Le volume des tables est :
travail : 82591 enregistrements
action : 20325 enregistrements
document : 34991 enregistrements

J'ai fait un explain mais je ne sais pas l'exploiter :

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
 
Limit  (cost=632614.91..632614.92 rows=1 width=367)
   ->  Sort  (cost=632614.91..632620.69 rows=2309 width=367)
         Sort Key: t.heure
         ->  Hash Join  (cost=2102.89..632603.37 rows=2309 width=367)
               Hash Cond: (a.id_act1 = ss1.id)
               ->  Hash Join  (cost=2101.30..632570.04 rows=2309 width=355)
                     Hash Cond: (a.id_act2 = ss2.id)
                     ->  Hash Join  (cost=2093.88..632530.86 rows=2309 width=346)
                           Hash Cond: (a.id_act3 = ss3.id)
                           ->  Nested Loop  (cost=2081.16..632486.40 rows=2309 width=333)
                                 ->  Hash Join  (cost=2081.16..631828.23 rows=2309 width=317)
                                       Hash Cond: ((t.id_action)::text = ((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text)|| (a.id_act4)::text))
                                       ->  Nested Loop Left Join  (cost=965.91..630170.27 rows=95 width=235)
                                             Join Filter: (CASE WHEN (lastalpha(t.doc) = 0::numeric) THEN (t.doc)::text ELSE substr((t.doc)::text, 1, (length((t.doc)::text) - 1)) END = substr((d.doc)::text, 1, (length((d.doc)::text) - 1)))
                                             ->  Index Scan using travail1 on travail t  (cost=0.01..66.19 rows=62 width=219)
                                                   Index Cond: ((jour = (('now'::text)::date - 1)) AND (personne = '0000781'::bpchar))
                                             ->  Materialize  (cost=965.90..1315.81 rows=34991 width=25)
                                                   ->  Seq Scan on document d  (cost=0.00..930.91 rows=34991 width=25)
                                       ->  Hash  (cost=1054.56..1054.56 rows=4855 width=82)
                                             ->  Seq Scan on action a  (cost=0.00..1054.56 rows=4855 width=82)
                                                   Filter: ((id_type1 = 'G'::bpchar) AND (id_type2 = 1::numeric) AND (to_char((('now'::text
)::date)::timestamp with time zone, 'YYYYMMDD'::text) <= (fin_action)::text))
                                 ->  Index Scan using pk_ss_action4 on ss_action4 ss4  (cost=0.00..0.27 rows=1 width=21)
                                       Index Cond: (ss4.id = a.id_act4)
                           ->  Hash  (cost=7.32..7.32 rows=432 width=17)
                                 ->  Seq Scan on ss_action3 ss3  (cost=0.00..7.32 rows=432 width=17)
                     ->  Hash  (cost=4.41..4.41 rows=241 width=13)
                           ->  Seq Scan on ss_action2 ss2  (cost=0.00..4.41 rows=241 width=13)
               ->  Hash  (cost=1.26..1.26 rows=26 width=16)
                     ->  Seq Scan on ss_action1 ss1 (cost=0.00..1.26 rows=26 width=16)
(29 rows)

J'espère fournir suffisamment d'éléments.

Pouvez-vous m'aider ?

[teach]

tu as oublié un let join ou INNER join pour la vue

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT T.*,A.*,D.libelle
FROM Travail T LEFT OUTER JOIN Document D 
ON (CASE WHEN LastAlpha(T.doc)=0 
         THEN T.doc 
         ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) 
     END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1)
LEFT JOIN Vue_Action A
ON T.id_action=A.id_act1||A.id_act2||A.id_act3||A.id_act4
WHERE T.jour=CURRENT_DATE-1
AND T.personne='0000123'
ORDER BY T.jour DESC,T.heure DESC 
LIMIT 1;

[mortimer.pw]

Bonjour Teach,

J'ai testé et le temps de réponse ne change pas.

[estofilo]

Quelle est la définition de la vue et y'a-t-il un index sur action.fin_action?

[mortimer.pw]

Bonjour Estofilo,

La définition de la vue est la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE VIEW Vue_Action AS
	SELECT a.id_act1,ss1.libelle AS lib1,a.id_act2,ss2.libelle AS lib2,a.id_act3,ss3.libelle AS lib3,a.id_act4,ss4.libelle AS lib4
	FROM action a,ss_action1 ss1,ss_action2 ss2,ss_action3 ss3,ss_action4 ss4
	WHERE a.id_type1='G'
	AND a.id_type2=1
	AND to_char('now'::text::date::timestamp with time zone,'YYYYMMDD'::text)<=a.fin_action::text
	AND a.id_act1=ss1.id
	AND a.id_act2=ss2.id
	AND a.id_act3=ss3.id
	AND a.id_act4=ss4.id;

Et il y a une primary key sur la table action :

Code :

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

1
2
 
ALTER TABLE action ADD CONSTRAINT pk_action PRIMARY KEY(id_act1,id_act2,id_act3,id_act4,fin_action);

[estofilo]

La clause ci-dessous parait problématique:

Code :

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

	AND to_char('now'::text::date::timestamp WITH time zone,'YYYYMMDD'::text)<=a.fin_action::text

A supposer que la colonne fin_action soit de type date, il serait bon de remplacer la clause par

Code :

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

AND current_date <= fin_action

et de mettre un index sur fin_action s'il n'y en a pas. Ca donnerait à l'optimiseur une chance d'éviter le "Seq scan" sur cette table. Un ANALYZE sur la table après coup est aussi nécessaire.