requête lente sur volume de données réduit

**mortimer.pw** · 10/03/2010, 11h26

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** · 10/03/2010, 11h51

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** · 10/03/2010, 12h10

Bonjour Teach,

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

**estofilo** · 10/03/2010, 12h40

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

**mortimer.pw** · 10/03/2010, 12h57

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** · 10/03/2010, 13h14

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.

**mortimer.pw** · 10/03/2010, 13h48

La colonne fin_action est de type numeric.

J'ai créé l'index :

Code :

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

CREATE INDEX action_fin_action ON action(fin_action);

J'ai fait le Analyze.

Pas de changement.

En supprimant la relation avec la table document, c'est beaucoup plus rapide.

Code :

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

1
2
3
4
5
6
7
 
    SELECT T.*,A.*
    FROM Travail T,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;

En gardant la relation mais en supprimant l'appel à la Fonction LastAlpha c'est également plus rapide.

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 T.doc=D.doc,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;

Si cela peut vous aider.

**estofilo** · 10/03/2010, 15h07

Envoyé par mortimer.pw

La colonne fin_action est de type numeric.

J'ai créé l'index :

Code :

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

CREATE INDEX action_fin_action ON action(fin_action);

J'ai fait le Analyze.

Pas de changement.

C'est logique, parce que la création d'un index va de pair avec l'autre suggestion qui est de réécrire le test de date en utilisant des vraies dates, c'est-à-dire ni des champs texte convertis au vol, ni des champs numériques convertis au vol. L'un sans l'autre n'apportera rien.

**mortimer.pw** · 10/03/2010, 15h15

Excusez-moi mais : Comment faire pour comparer la date système (type date) à ma fin_action (type numeric) sans utiliser de "conversion au vol" ?

Je ne peux pas modifier la structure de ma table.

**estofilo** · 10/03/2010, 17h24

Envoyé par mortimer.pw

Excusez-moi mais : Comment faire pour comparer la date système (type date) à ma fin_action (type numeric) sans utiliser de "conversion au vol" ?

Je ne peux pas modifier la structure de ma table.

Je pensais à une modification du type de la colonne, effectivement. Là, toutes ces conversions de type sont contre-productives.

**mortimer.pw** · 11/03/2010, 07h35

Bonjour Estofilo,

Ne pensez-vous pas que le problème puisse venir de la recherche du document (relation Travail Document) ?

**estofilo** · 11/03/2010, 14h22

Il faudrait faire EXPLAIN ANALYZE plutôt que EXPLAIN simple, ça donnerait les temps d'exécutions des différentes phases.

**mortimer.pw** · 12/03/2010, 07h39

Bonjour Estofilo,

Voila le Explain analyze :

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
33
34
35
36
37
38
39
 
Limit  (cost=643219.80..643219.81 rows=1 width=367) (actual time=30633.571..30633.572 rows=1 loops=1)
   ->  Sort  (cost=643219.80..643878.07 rows=263307 width=367) (actual time=30633.569..30633.569 rows=1 loops=1)
         Sort Key: t.heure
         Sort Method:  top-N heapsort  Memory: 25kB
         ->  Merge Join  (cost=632566.01..641903.27 rows=263307 width=367) (actual time=30633.309..30633.462 rows=43 loops=1)
               Merge Cond: ((((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text) || (a.id_act4)::text)) = ((t.id_action)::text))
               ->  Sort  (cost=1668.72..1680.86 rows=4855 width=132) (actual time=364.533..364.702 rows=726 loops=1)
                     Sort Key: (((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text) || (a.id_act4)::text))
                     Sort Method:  quicksort  Memory: 4222kB
                     ->  Hash Join  (cost=49.88..1371.46 rows=4855 width=132) (actual time=1.566..234.961 rows=14447 loops=1)
                           Hash Cond: (a.id_act1 = ss1.id)
                           ->  Hash Join  (cost=48.29..1303.12 rows=4855 width=120) (actual time=1.500..194.138 rows=14447 loops=1)
                                 Hash Cond: (a.id_act3 = ss3.id)
                                 ->  Hash Join  (cost=35.57..1223.65 rows=4855 width=107) (actual time=1.115..177.392 rows=14447 loops=1)
                                       Hash Cond: (a.id_act4 = ss4.id)
                                       ->  Hash Join  (cost=7.42..1128.74 rows=4855 width=91) (actual time=0.299..159.552 rows=14447 loops=1)
                                             Hash Cond: (a.id_act2 = ss2.id)
                                             ->  Seq Scan on action a  (cost=0.00..1054.56 rows=4855 width=82) (actual time=0.065..132.866 rows=14447 loops=1)
                                                   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))
                                             ->  Hash  (cost=4.41..4.41 rows=241 width=13) (actual time=0.223..0.223 rows=241 loops=1)
                                                   ->  Seq Scan on ss_action2 ss2  (cost=0.00..4.41 rows=241 width=13) (actual time=0.007..0.091 rows=241 loops=1)
                                       ->  Hash  (cost=16.40..16.40 rows=940 width=21) (actual time=0.803..0.803 rows=940 loops=1)
                                             ->  Seq Scan on ss_action4 ss4  (cost=0.00..16.40 rows=940 width=21) (actual time=0.009..0.347 rows=940 loops=1)
                                 ->  Hash  (cost=7.32..7.32 rows=432 width=17) (actual time=0.374..0.374 rows=432 loops=1)
                                       ->  Seq Scan on ss_action3 ss3  (cost=0.00..7.32 rows=432 width=17) (actual time=0.008..0.172 rows=432 loops=1)
                           ->  Hash  (cost=1.26..1.26 rows=26 width=16) (actual time=0.046..0.046 rows=26 loops=1)
                                 ->  Seq Scan on ss_action1 ss1 (cost=0.00..1.26 rows=26 width=16) (actual time=0.014..0.031 rows=26 loops=1)
               ->  Sort  (cost=630897.29..630924.41 rows=10847 width=235) (actual time=30268.120..30268.133 rows=43 loops=1)
                     Sort Key: ((t.id_action)::text)
                     Sort Method:  quicksort  Memory: 31kB
                     ->  Nested Loop Left Join  (cost=965.91..630170.27 rows=10847 width=235) (actual time=768.841..30267.651 rows=43 loops=1)
                           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) (actual time=0.068..0.420 rows=43 loops=1)
                                 Index Cond: ((jour = (('now'::text)::date - 1)) AND (personne = '0000123'::bpchar))
                           ->  Materialize  (cost=965.90..1315.81 rows=34991 width=25) (actual time=0.001..13.739 rows=34991 loops=43)
                                 ->  Seq Scan on document d  (cost=0.00..930.91 rows=34991 width=25) (actual time=0.010..31.729 rows=34991 loops=1)
Total runtime: 30636.099 ms
(37 rows)

**estofilo** · 12/03/2010, 10h26

L'essentiel du temps semble passé ici:

Code :

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

1
2
3
 
->  Nested Loop LEFT JOIN  (cost=965.91..630170.27 rows=10847 width=235) (actual time=768.841..30267.651 rows=43 loops=1)
    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)))

La fonction lastalpha est censée être rapide? Et dans t.doc et d.doc ce sont des chaînes de caractères de grande taille?

**CinePhil** · 12/03/2010, 11h20

Quand je vois la définition de la vue et la requête, je me dis qu'il y a un problème de modélisation !

On peut avoir la structure des tables Travail, action, Document ?
Que contiennent les tables ss_action1 à 4 ?
Pourquoi sont-elles liées toutes les 4 à la table action ?

Pourquoi les conditions de jointure dans la requête ne portent pas sur des clés primaires et étrangères simples ?

Ne peux-tu accéder qu'à la vue ou as-tu accès aux tables qui sont requêtées par la vue ? Ne sont-ce d'ailleurs que des tables ?

J'ai remis à la norme en vigueur depuis 1992 les jointures de la vue et de la requête :

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
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
INNER JOIN ss_action1 ss1 ON a.id_act1 = ss1.id
INNER JOIN ss_action2 ss2 ON a.id_act2 = ss2.id
INNER JOIN ss_action3 ss3 ON a.id_act3 = ss3.id
INNER JOIN ss_action4 ss4 ON a.id_act4 = ss4.id
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;
 
 
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)
INNER 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;

Encore un truc : éviter le SELECT * !
Lire à ce sujet "La guerre des étoiles" !

requête lente sur volume de données réduit

Requêtes PostgreSQL

Discussions similaires

Partager

Partager