Non utilisation de l'index

**JUSTIN Loïc** · 20/04/2010, 16h46

Bonjour

voici ma requete

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
SELECT a.id_serv,a.id_app,sum(a.obj_sauve),sum(a.vol_sauve),
sum(a.nb_sauve),sum(a.err_sauve),sum(a.obj_rest),
sum(a.vol_rest),sum(a.nb_rest),sum(a.err_rest)
,a.id_dom_1,a.id_dom_2,a.id_dom_3,
a.id_dom_4,a.id_dom_5,a.id_dom_6,
a.id_dom_7,a.id_dom_8
FROM tab_stat_app_jour a,domaines b
WHERE a.date>=to_date('01/03/2010', 'DD/MM/YYYY')
AND a.date<to_date('01/03/2010', 'DD/MM/YYYY')+ '1 month'::interval
AND b.domaine='mon_domaine'
And (b.id_dom=a.id_dom_1
or b.id_dom=a.id_dom_2
or b.id_dom=a.id_dom_3
or b.id_dom=a.id_dom_4
or b.id_dom=a.id_dom_5
or b.id_dom=a.id_dom_6
or b.id_dom=a.id_dom_7
or b.id_dom=a.id_dom_8)
group by a.id_serv,a.id_app,
a.id_dom_1,a.id_dom_2,a.id_dom_3,
a.id_dom_4,a.id_dom_5,a.id_dom_6,
a.id_dom_7,a.id_dom_8;

J'ai essayé de mettre un index sur le champ date ou sur le champ id_serv.
Dans les deux cas, l'index n'est pas utilisé. Hors comme je commence à avoir pas mal de donnée, la durée du traitement evolue considerablement.
Je passe de 14mn à presque 30 mn maintenant
Que faire .....

**aserx** · 20/04/2010, 17h45

Bonjour,

Il y a peut-être des index plus judicieux à trouver.

Peux-tu poster le EXPLAIN de ta table ?

Cordialement,

**SQLpro** · 20/04/2010, 23h06

Vous avez un OR dans votre cluse WHERE portant sur la même table que votre colonne DATE. Dès lors aucun index ne peut être utilisé car le prédicat n'est pas "Sargable".

Revoyez votre modèle de donnée pour expurger cet immonde groupe de OR, car vu la répétition des colonnes (id_dom_1, id_dom_2, id_dom_3...) vous violez la première forme normale.

A +

**JUSTIN Loïc** · 21/04/2010, 08h52

Merci SQLpro pour ta réponse.

Par contre, je vais avoir du mal à modifier mon modèle de donnée.
Explication:
J'ai un serveur qui est rattaché à une application et cette application est rattaché au minimum deux domaines maximum huit.
Pour eviter la redondance des données, que j'ai créé cette table.
Car, un serveur peut evolué dans le temps. Ce qui veut dire changer d'application.

Par contre, je suis preneur sur toute piste me permettant de faire evoluer mon modéle de donnée.
J'espère avoir été suffisament claire.

**SQLpro** · 21/04/2010, 13h19

Eh bien justement, créé une table des dommaines de rattachement et fais une jointure avec. Du coup tu pourras mettre un index efficace.

Toute table T comptant N colonne (C1, C2, C3...) et comportant une clef (sur les colonnes (Cx, Cy, Cz...), peut être remplacée par deux tables T1 et T2 plus petites qui partagent la même clef : T1 (Cx, Cy, Cz, C1) T2 (Cx, Cy, Cz, C2, C3, C4)

S'il existe des colonnes de groupe répétitifs, alors il faut ajouter dans la seconde table une colonne supplémentaire participant à la clef pour pallier à la répétition :
T1 (Cx, Cy, Cz, C1) T2 (Cx, Cy, Cz, Cn, C2, C3, C4)

A +

**JUSTIN Loïc** · 22/04/2010, 14h31

Bonjour,

voici l'avancé de mes reflexions
Creations de deux tables en lieu et place de celle existante
La 1ere

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
mabase=# \d tab_stat_app_jour_dom1
Table "public.tab_stat_app_jour_dom1"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 id_serv   | integer |
 date      | date    |
 obj_sauve | integer |
 vol_sauve | integer |
 nb_sauve  | integer |
 err_sauve | integer |
 obj_rest  | integer |
 vol_rest  | integer |
 nb_rest   | integer |
 err_rest  | integer |
 id_app    | integer |
 id_dom_1  | integer |

et la deuxieme

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mabase=# \d tab_stat_app_jour_dom2
Table "public.tab_stat_app_jour_dom2"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id_serv  | integer |
 date     | date    |
 id_app   | integer |
 id_dom_2 | integer |
 id_dom_3 | integer |
 id_dom_4 | integer |
 id_dom_5 | integer |
 id_dom_6 | integer |
 id_dom_7 | integer |
 id_dom_8 | integer |

ma nouvelle requete est

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT a.id_serv,a.id_app,sum(a.obj_sauve),sum(a.vol_sauve),
sum(a.nb_sauve),sum(a.err_sauve),sum(a.obj_rest),
sum(a.vol_rest),sum(a.nb_rest),sum(a.err_rest)
,a.id_dom_1
FROM tab_stat_app_jour_dom1 a,domaines b
WHERE a.date>=to_date('01/03/2010', 'DD/MM/YYYY')
AND a.date<to_date('01/03/2010', 'DD/MM/YYYY')+ '1 month'::interval
AND b.domaine='mon_domaine'
And b.id_dom=a.id_dom_1
group by a.id_serv

j'ai ensuite essayé de créer un index sur le champ date ou le champ id_serv sur la table tab_stat_app_jour_dom1 mais l'index n'est toujours pas utilisé.
A mon avis j'ai loupé quelque chose

**estofilo** · 22/04/2010, 17h16

Pour les questions de performance de requêtes, il faut poster le EXPLAIN ANALYZE pour avoir les éléments d'analyse.

**SQLpro** · 22/04/2010, 17h52

Non, la première table ne doit pas contenir d'id_dom
et la deuxième table n'est pas bonne !

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
 
mabase=# \d tab_stat_app_jour_dom2
TABLE "public.tab_stat_app_jour_dom2"
  COLUMN  |  Type   | Modifiers
----------+---------+-----------
 id_serv  | integer |
 date     | date    |
 id_app   | integer |
 id_dom_2 | integer |
 id_dom_3 | integer |
 id_dom_4 | integer |
 id_dom_5 | integer |
 id_dom_6 | integer |
 id_dom_7 | integer |
 id_dom_8 | integer |

Il faut la construire comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
mabase=# \d tab_stat_app_jour_dom2
TABLE "public.tab_stat_app_jour_dom2"
  COLUMN  |  Type   | Modifiers
----------+---------+-----------
 id_serv  | integer |
 date     | date    |
 id_app   | integer |
 id_n     | integer |
 id_dom   | integer |

et rajouter à votre clef la colonne id_n

Dès lors votre requête ferait quelque chose comme cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT a.id_serv, a.id_app, sum(a.obj_sauve),sum(a.vol_sauve),
       sum(a.nb_sauve), sum(a.err_sauve), sum(a.obj_rest),
       sum(a.vol_rest), sum(a.nb_rest), sum(a.err_rest),
       a.id_dom
FROM   tab_stat_app_jour_dom1 a
       INNER JOIN domaines b ON b.id_dom=a.id_dom_1
       INNER JOIN 
WHERE  a.date>=to_date('01/03/2010', 'DD/MM/YYYY')
   AND a.date<to_date('01/03/2010', 'DD/MM/YYYY')+ '1 month'::interval
   AND b.domaine='mon_domaine'
   AND a.id_n = 1
GROUP BY a.id_serv, a.id_app, a.id_dom_1

désolé de ne pas pouvoir vous en dire plus car vous n'avez pas posté le DDL de vos table ni de jeu d'essais.

A +

**JUSTIN Loïc** · 23/04/2010, 10h36

Code :

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

1
2
3
4
5
6
 id_serv |    date    | obj_s | vol_s | nb_s | err_s | id_app | id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8
---------+------------+-------+-------+------+-------+--------+-----+-----+-----+-----+-----+-----+-----+-----
   35598 | 2010-03-01 | 30507 |  4579 |    1 |     0 |    760 | 305 |  60 | 103 | 107 | 306 | 345 | 357 | 360
   35598 | 2010-03-02 | 33566 |  4724 |    1 |     0 |    760 | 305 |  60 | 103 | 107 | 306 | 345 | 357 | 360
   35598 | 2010-03-03 | 34228 |  4873 |    1 |     0 |    760 | 305 |  60 | 103 | 107 | 306 | 345 | 357 | 360
   35598 | 2010-03-04 | 34739 |  4995 |    1 |     0 |    760 | 305 |  60 | 103 | 107 | 306 | 345 | 357 | 360

Je vais donc expliquer à quoi tout cela correspond.
Tous les jours, il y a des sauvegardes qui sont effectuées pour un serveur (id_serv). On recupere le nombre d'objets sauvegardes (obj_s), le volume sauvegarde (vol_s), le nombre de sauvegarde (nb_s) et le nombre d'erreur de sauvegarde (err_s) qui ont été remontés au cours de la journée.
Ce serveur est rattaché une application (id_app) et cette application est rattaché entre deux à huit domaines (id1 à id8)
Dans la table domaines. On trouve la traduction du id_dom
Ensuite, une fois par mois je dois calculer les valeurs mensuelles par serveur . C'est d'ailleurs à cela que devra servir la requete

**SQLpro** · 26/04/2010, 23h40

vous n'avez toujours pas posté le DDL de vos table ni de jeu d'essais.

exemple :

Code :

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

1
2
3
CREATE TABLE ...
 
INSERT INTO ...

A +

**JUSTIN Loïc** · 27/04/2010, 13h46

Voici la création des tables

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
40
41
42
43
44
45
######################### creation table :tab_stat_app_jour
CREATE TABLE tab_stat_app_jour(
  id_serv integer,
  date date,
  obj_sauve integer,
  vol_sauve integer,
  nb_sauve integer,
  err_sauve integer,
  obj_rest integer,
  vol_rest integer,
  nb_rest integer,
  err_rest integer,
  id_app integer,
  id_dom_1 integer,
  id_dom_2 integer,
  id_dom_3 integer,
  id_dom_4 integer,
  id_dom_5 integer,
  id_dom_6 integer,
  id_dom_7 integer,
  id_dom_8 integer
);
GRANT ALL ON TABLE tab_stat_app_jour TO apache;
GRANT ALL ON TABLE tab_stat_app_jour TO admindb;
CREATE INDEX tab_stat_app_jour_idx
                        ON tab_stat_app_jour (id_serv);
 
######################### creation table : domaines
CREATE TABLE domaines (
id_dom      int2 PRIMARY KEY,
domaine     varchar(75),
type_dom    int2,
l_domaine   varchar(40),
site_ges    varchar(2),
visible   int2,
check_baf   int2,
methode     int2
);
 
GRANT ALL ON domaines TO apache;
GRANT ALL ON domaines TO admindb;
 
CREATE SEQUENCE seq_domaines increment 1;
GRANT ALL ON seq_domaines TO admindb;
GRANT ALL ON seq_domaines TO apache;

Ci-joint un jeu d'essai

**JUSTIN Loïc** · 10/06/2010, 14h46

Juste une petite question, il me semble avoir ajouté ce que l'on me demandait. Malgrés cela, ça va faire plus d'un mois que je n'ai plus de nouvelle. C'est encore pas bon, ce que j'ai mis

Sachant que j'ai mis la derniere version des tables. Car, je n'ai rien compris pour les modifier.

**estofilo** · 10/06/2010, 19h31

Envoyé par JUSTIN Loïc

Juste une petite question, il me semble avoir ajouté ce que l'on me demandait.

Pour la question "Pourquoi tel index n'est pas utilisé" qui est la question initiale, il faut poster l'explain analyze de la requête, et ça c'est resté sans réponse.

**JUSTIN Loïc** · 11/06/2010, 12h02

Explain analyze n'est pas le bon. Car ce n'est pas cela qui me provoque la derive du temps de traitement. Pour le traitement journalier, je fais la somme des données journalieres par serveur. Ensuite, je verifie pour chaque serveur que le traitement n'a pas déja eu lieu et c'est ce second traitement qui me pose probleme. Car quand il n'y a pas beaucoup de donnée cela va mais au bout d'un mois la durée de traitement s'allonge.
voici l'explain analyze

QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on tab_stat_app_jour (cost=0.00..30.00 rows=1 width=8) (actual time=95.139..121.643 rows=1 loops=1)
Filter: ((id_serv = 108394) AND ((date)::text ~~ '2010-06-07'::text) AND (id_app = 1446))
Total runtime: 121.786 ms

Là, le temp de traitement est correcte car j'ai reinitialisé les tables.

**estofilo** · 11/06/2010, 12h29

Tu parlais qu'une requête qui durait dans les 15mn mais celle-là prend 0.5s ??
Le but étant de savoir à quoi faire sont passées toutes ces minutes, cet explain n'est pas forcément une bonne base d'analyse.

**JUSTIN Loïc** · 11/06/2010, 15h55

Je viens de créer à nouveau un index sur ma table tab_stat_app_jour
Et là, l'index est bien utilisé.

Index Scan using tab_stat_app_jour_idx on tab_stat_app_jour (cost=0.00..1003.79 rows=1 width=8) (actual time=0.084..0.133 rows=1 loops=1)
Index Cond: (id_serv = 108394)
Filter: (((date)::text ~~ '2010-06-07'::text) AND (id_app = 1446))
Total runtime: 0.194 ms

Encore merci pour votre aide

Non utilisation de l'index

Requêtes PostgreSQL

Discussions similaires

Partager

Partager