Optimisation de requête

**koven** · 05/09/2016, 16h29

Bonjour à tous,

Je souhaiterai optimiser une requête postegre qui prend plusieurs dixaines de seconde à être exécutée.. Ci après 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
select
     f.timestamp::date as date,
       user_id,
       activity_type,
       f.container_id as group_id,
       (
          select
              string_agg(distinct("userId"), ',') as group_owners
            from
              jusers_groups_copy g
            where
              g.place_id = f.container_id
              and state like 'owner'
        ) as group_owners
     from
       fact_activity f
     where
       f.container_type like '700'
       and f.timestamp::date < to_date('2016-09-05', 'YYYY-MM-DD')
     group by
      date, user_id, activity_type, group_id
     order by
      date, user_id, activity_type, group_id

Etant complètement nouveau sur postgre, j'ai utilisé un explain et un analyse mais il m'est compliqué de comprendre le résultat :

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
"Group  (cost=7029.62..651968.20 rows=17843 width=27) (actual time=431.017..4513.973 rows=11483 loops=1)"
"  Buffers: shared hit=139498 read=411, temp read=255 written=255"
"  ->  Sort  (cost=7029.62..7074.90 rows=18111 width=27) (actual time=430.630..667.098 rows=54660 loops=1)"
"        Sort Key: ((f."timestamp")::date), f.user_id, f.activity_type, f.container_id"
"        Sort Method: external merge  Disk: 2008kB"
"        Buffers: shared hit=1702 read=411, temp read=255 written=255"
"        ->  Seq Scan on fact_activity f  (cost=0.00..5748.76 rows=18111 width=27) (actual time=0.107..188.827 rows=54660 loops=1)"
"              Filter: ((container_type ~~ '700'::text) AND (("timestamp")::date < to_date('2016-09-05'::text, 'YYYY-MM-DD'::text)))"
"              Rows Removed by Filter: 125414"
"              Buffers: shared hit=1691 read=411"
"  SubPlan 1"
"    ->  Aggregate  (cost=36.12..36.13 rows=1 width=5) (actual time=0.315..0.318 rows=1 loops=11483)"
"          Buffers: shared hit=137796"
"          ->  Seq Scan on jusers_groups_copy g  (cost=0.00..36.09 rows=11 width=5) (actual time=0.041..0.266 rows=13 loops=11483)"
"                Filter: ((state ~~ 'owner'::text) AND (place_id = f.container_id))"
"                Rows Removed by Filter: 1593"
"                Buffers: shared hit=137796"
"Total runtime: 4536.074 ms"

J'ai constaté que la durée de la requête était drastiquement réduite lorsque j'enlevais le select imbriqué :

Code :

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

1
2
3
4
5
6
7
8
9
(
          select
              string_agg(distinct("userId"), ',') as group_owners
            from
              jusers_groups_copy g
            where
              g.place_id = f.container_id
              and state like 'owner'
        ) as group_owners

De plus, les tables ne disposent d'aucun index.

Ainsi, j'aurai souhaité savoir si le pb venait de ce manque d'index ou s'il était possible d'optimiser la requête d'une quelconque façon ?
Je souhaiterai en découvrir d'avantage sur ce ralentissement, pourriez vous m'éclairer sur le fait que la requête met beaucoup de temps à s'exécuter ?

Merci d'avance

Koven

**escartefigue** · 05/09/2016, 19h50

Bonsoir,

Je ne suis pas expert PG, mais, une chose est sure, si vos tables contiennent plus que quelques enregistrements, des index sont indispensables si vous voulez des temps de réponse corrects et que vous faites de jointures (ce qui est quand même le but avec un SGBDR)

Donc commencez par créer des index sur vos colonnes identifiantes

Pensez ensuite à remplacer and state like 'owner' par and state like 'owner%' ou bien and state like '%owner%

**SQLpro** · 06/09/2016, 17h55

Votre requête est une horreur est le modèle merdique....

Créez ces deux index et étonnez vous des performances :

Code :

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

1
2
CREATE INDEX X001 ON fact_activity (container_type, "timestamp", user_id, activity_type, group_id);
CREATE INDEX X002 ON jusers_groups_copy ("state", place_id, userId);

Récrivez votre requête comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select f."timestamp"::date as date,
       user_id,
       activity_type,
       f.container_id as group_id,
       (select  string_agg(distinct(userId), ',') as group_owners
        from    jusers_groups_copy g
        where   g.place_id = f.container_id
          and   "state" = 'owner'
        ) as group_owners
from   fact_activity f
where  f.container_type = '700'
  and  f."timestamp"::date < to_date('2016-09-05', 'YYYY-MM-DD')
group  by "timestamp", user_id, activity_type, group_id
order  by "timestamp", user_id, activity_type, group_id;

Au passage il est parfiatemet stupide de créer des colonnes de tables qui portent les noms suivantes :
sate, timestamp, date
qui sont des mots réservés du SQL. Vous allez aux devant de multiples ennuis.

L'usage du LIKE sans joker est stupide.

Vérifiez si vous avez impérativement besoin du DISTINCT dans le string_agg. Si la base est bien modélisée vous ne devriez jamais avoir de doublons...

Assurez vous bien que la colonne container_type est de type VARCHAR ou CHAR. Si ce n'est pas le cas, ne passez pas un argument sous forme de chaine mais de nombre !

A +

**koven** · 07/09/2016, 17h11

Tout d'abord merci pour l'aide apportée.

Concernant le modèle et la requête de base je n'ai hélas pas grand chose à dire étant donné que je reprends un code existant.

J'ai appliqué vos suggestions de correctifs, cependant je me retrouve avec 2 résultats différents :

Requête initiale :

date	user_id	activity_type	group_id	group_owners
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1032	2018

Après application du correctif :

date	user_id	activity_type	group_id	group_owners
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1032	2018

Auriez vous une explication à cela ?

Mon job est de m'occuper de réduire le temps de traitement de la requête, les bases de données n'étant pas ma spécialité, je ne peux connaître si telle ou telle requête est horrible..

Cependant ceci n'est qu'une partie de la requête, si vous avez trouvé ce début horrible, je vous déconseille d'ouvrir le spoiler suivant :

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
with main_tab as (
      select
         f.timestamp::date as date,
           user_id,
           activity_type,
           f.container_id as group_id,
           u.department as user_department,
           u.location as user_location,
           (
              select
                  string_agg(distinct("userId"), ',') as group_owners
                from
                  jusers_groups_copy g
                where
                  g.place_id = f.container_id
                  and state like 'owner'
            ) as group_owners
         from
           fact_activity f
           inner join ref_user u
           on f.user_id = u.id
           inner join ref_group r
           on r.container_id = f.container_id
         where
           f.container_type like '700'
           and f.timestamp::date < to_date($2, 'YYYY-MM-DD')
         group by
          date, user_id, activity_type, group_id, user_department, user_location
         order by
          date, user_id, activity_type, group_id, user_department, user_location
     ) , actif_tab as (
       select date, group_id, user_id,
      (CASE WHEN  contributing > 0 THEN 1 ELSE 0 END) as contributing,
      (CASE WHEN partipating > 0 THEN 1 ELSE 0 END) as partipating
      from (select
      date, group_id, user_id,
      SUM(CASE WHEN activity_type in ('AddAttachment', 'Bookmark', 'BookmarkUpdate', 'Create', 'CreateOutcome', 'Move', 'Rate', 'Update', 'UpdateOutcome') THEN 1 ELSE 0 END)
                     as contributing,
         SUM (CASE WHEN activity_type in ('Acclaim', 'Apply', 'Approve', 'Comment', 'CommentUpdate', 'Endorse', 'EndorsementApproval', 'Follow', 'Like', 'Mention', 'Resolved','RSVP', 'Send', 'Share', 'Tag', 'Vote') THEN 1 ELSE 0 END)
                     as partipating
        from
         main_tab
        group by
         date, group_id, user_id)t
    )
        select
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners,
            SUM(CASE WHEN a.contributing > 0 THEN 1 ELSE 0 END) as sum_contribution,
            SUM(CASE WHEN a.contributing = 0 and a.partipating > 0 THEN 1 ELSE 0 END) as sum_partipating,
            (
            select
              count(distinct(user_id))
            from
              main_tab m2
            where
              m2.date = m.date
            group by
              date
            ) as actif,
          (
            select
              count(distinct(user_id))
            from
              main_tab m3
            where
              m3.date <= m.date
            ) as register
        from
          actif_tab a, main_tab m
        where
          m.date > to_date($1, 'YYYY-MM-DD')
          and m.date = a.date and m.group_id = a.group_id and m.user_id = a.user_id
      group by
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners
      order by
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners

En quelques mots, le calcul du nombre d'actifs et du nombre d'enregistrements est affreusement long car il faut les compter 1 par 1 depuis la première ligne. Etant donné que c'est une donnée calculée je trouverai ça dommage de l'intégrer dans une vue.. Si vous avez des pistes je suis preneur..

**SQLpro** · 07/09/2016, 18h08

Envoyé par koven

J'ai appliqué vos suggestions de correctifs, cependant je me retrouve avec 2 résultats différents :

Requête initiale :

date	user_id	activity_type	group_id	group_owners
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1032	2018

Après application du correctif :

date	user_id	activity_type	group_id	group_owners
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1006	2012
2015-08-03	2022	View	1032	2018

Auriez vous une explication à cela ?

A priori une erreur du moteur PostGreSQL qui effectue mal son groupage...

Essayez avec :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select f."timestamp"::date as date,
       user_id,
       activity_type,
       f.container_id as group_id,
       (select  string_agg(distinct(userId), ',') as group_owners
        from    jusers_groups_copy g
        where   g.place_id = f.container_id
          and   "state" = 'owner'
        ) as group_owners
from   fact_activity f
where  f.container_type = '700'
  and  f."timestamp"::date < to_date('2016-09-05', 'YYYY-MM-DD')
group  by f."timestamp", user_id, activity_type, f.container_id
order  by "date", user_id, activity_type, group_id;

Sinon en rajoutant un distinct au niveau du select final

Et au niveau temps de réponse ?

A +

**koven** · 09/09/2016, 10h55

La requête prend 5,3secondes à être exécutée. Cependant j'ai aussi plus de résultats qui me sont retournés.

Par ailleurs il existe toujours des doublons de ce côté là, je pense qu'il y a de toute manière un pb côté modélisation.

**SQLpro** · 13/09/2016, 17h58

Après il faudrait voir si vous êtes pas trop juste en ressources hardware compte tenu de la volumétrie.
Quelle est la taille de la base ?
Quelle est la RAM disponible sur le serveur ?

A +

**skuatamad** · 13/09/2016, 18h58

Je ne suis pas convaincu par la pertinance de actif_tab.
Dans le select final ACTIF semble juste être un count(distinct user_id), je ne vois pas l'intérêt du select imbriqué.
Et il faudrait voir s'il n'est pas possible de remonter le filtre m.date > to_date($1, 'YYYY-MM-DD') dans main_tab.
Ca devrait avoir un impacte sur le calcul de REGISTER, mais ça améliorerait probablement les perfs, pour ça il faut vérifier le besoin.
Le tri dans main_tab est inutile.

Qu'est ce que ça donne comme ça :

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
46
47
48
49
50
51
52
53
54
55
56
57
58
with main_tab as (
      select
         f.timestamp::date as date,
           user_id,
           activity_type,
           f.container_id as group_id,
           u.department as user_department,
           u.location as user_location,
           (
              select
                  string_agg(distinct("userId"), ',') as group_owners
                from
                  jusers_groups_copy g
                where
                  g.place_id = f.container_id
                  and state = 'owner'
            ) as group_owners
         from
           fact_activity f
           inner join ref_user u
           on f.user_id = u.id
           inner join ref_group r
           on r.container_id = f.container_id
         where
           f.container_type = '700'
           and f.timestamp::date < to_date($2, 'YYYY-MM-DD')
         group by
          date, user_id, activity_type, group_id, user_department, user_location
     )
        select
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners,
            SUM(CASE WHEN activity_type in ('AddAttachment', 'Bookmark', 'BookmarkUpdate', 'Create', 'CreateOutcome', 'Move', 'Rate', 'Update', 'UpdateOutcome') 
                     THEN 1 
                     ELSE 0 
                 END ) as sum_contribution,
            SUM(CASE WHEN activity_type not in ('AddAttachment', 'Bookmark', 'BookmarkUpdate', 'Create', 'CreateOutcome', 'Move', 'Rate', 'Update', 'UpdateOutcome') 
                      and activity_type     in ('Acclaim', 'Apply', 'Approve', 'Comment', 'CommentUpdate', 'Endorse', 'EndorsementApproval', 'Follow', 'Like', 'Mention'
                                              , 'Resolved','RSVP', 'Send', 'Share', 'Tag', 'Vote')
                     THEN 1 
                     ELSE 0 
                 END ) as sum_partipating,
            count(distinct user_id) as actif,
          (
            select
              count(distinct user_id)
            from
              main_tab m3
            where
              m3.date <= m.date
            ) as register
        from
          main_tab m
        where
          m.date > to_date($1, 'YYYY-MM-DD')
      group by
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners
      order by
          m.date, m.group_id, m.user_department, m.user_location, m.group_owners

**koven** · 06/10/2016, 18h50

Bonjour,

Désolé du retard, j'étais affecté sur un autre projet entre temps et j'ai oublié ce post concernant l'optimisation.

Pour les infos de la base :
- La base ne dépasse pas les 20 Mo
- La ram dispo sur le serveur est de 8Go, Environ 5go de libre constamment

Je pense que cela vient uniquement d'un problème d'optimisation des tables vis à vis des index. En fait les tables sont remplies en brut avec 0 clés étrangères, 0 index, 0 clés primaires donc forcément ce n'est pas optimisé.

Concernant le traitement des actifs, ces derniers sont calculés en fonction de la somme des activités sur un jour donné. Ainsi il faut obtenir la somme de toutes les activités sur 1 jour et non pas pour un groupe donné.
Ex :

Date	Groupe	Actif
Date 1	Groupe 1	10
Date 1	Groupe 2	10
Date 1	Groupe 3	10

Je pense que cela peut être calculé en post traitement, ce qui prendrai moins de temps à l'exécution.

Enfin les registers, ce sont les personnes ayant effectué des des opérations spécifiques et ce depuis le début de la mise en place du système, je pense que ce qui pourrait être plus optimisé, ça serait de sauvegarder ces enregistrement dans une table à part. Ces données seront par exemple calculés selon une routine et la requête prendrait beaucoup moins de temps à être exécutée.

Une refonte de la base en prévue dans les semaines à venir, ainsi je vais sous peu vous demander un avis concernant le nouveau schéma de la base de données.

Merci encore,
K.

Optimisation de requête

Requêtes PostgreSQL

Discussions similaires

Partager

Partager