Optimisation d'une requête postgreSQL

Version imprimable

Bonjour,
j'ai une certain requête, assez longue (et ce n'est que la sous requête d'une autre :aie: ) mais que j'ai fait le plus compréhensible possible ....
Je ne vais pas m'attarder plus, la voici :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT * FROM(SELECT *, seances.id AS id_seance, activities.id AS id_activity, MAX(nb_real) OVER(PARTITION BY realisations.seance) AS nb_real_max,modules.cycle AS module_cycle, seances.repetition AS module_repetition, modules.id AS id_mod, ROW_NUMBER() OVER(PARTITION BY modules.id ORDER BY realisations.nb_real ASC, ordre ASC) AS rn FROM modules INNER JOIN activities ON activities.id_module = modules.id INNER JOIN seances ON seances.id_module=modules.id INNER JOIN j_seances_niveaux js ON js.id_seance=seances.id AND id_niveau = 11 INNER JOIN j_membres_modules jm ON jm.membre = 1 AND jm.module = modules.id LEFT JOIN realisations ON realisations.activity = activities.id AND member = 1 LEFT JOIN provocations ON provocations.provoque = activities.id WHERE (activities.type_freq=3 OR activities.type_freq=1) -- filtre de la fréquence AND (modules.cycle='1' OR (date_start<NOW() AND date_end < NOW())) -- filtre pour la date ... AND (jm.report IS NULL OR jm.report <= NOW()) -- en faisant attention aux reports éventuels ) AS tmp WHERE rn = 1 AND (tmp.module_cycle='1' OR nb_real_max IS NULL OR nb_real_max < tmp.module_repetition) -- filtre pour la répétition
bon, c'est très bien, mais j'ai néanmoins remarqué que si au lieu de retourner tout au lieu de juste un champs, ça ne prenais que 100 ms. Avec cette requête qui renvoie tous les résultats, ça prend 700 ms.

Alors, j'ai eu une idée : au lieu de tout retourner, pourquoi ne pas retourner seulement quelques champs, et faire ensuite des INNER JOIN pour récupérer les même données ? Et ça donne ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT * FROM(SELECT seances.id AS id_seance, activities.id AS id_activity, MAX(nb_real) OVER(PARTITION BY realisations.seance) AS nb_real_max,modules.cycle, seances.repetition, modules.id AS id_mod, ROW_NUMBER() OVER(PARTITION BY modules.id ORDER BY realisations.nb_real ASC, ordre ASC) AS rn FROM modules INNER JOIN activities ON activities.id_module = modules.id INNER JOIN seances ON seances.id_module=modules.id INNER JOIN j_seances_niveaux js ON js.id_seance=seances.id AND id_niveau = 11 INNER JOIN j_membres_modules jm ON jm.membre = 1 AND jm.module = modules.id LEFT JOIN realisations ON realisations.activity = activities.id AND member = 1 LEFT JOIN provocations ON provocations.provoque = activities.id WHERE (activities.type_freq=3 OR activities.type_freq=1) -- filtre de la fréquence AND (modules.cycle='1' OR (date_start<NOW() AND date_end < NOW())) -- filtre pour la date ... AND (jm.report IS NULL OR jm.report <= NOW()) -- en faisant attention aux reports éventuels ) AS tmp INNER JOIN modules ON modules.id = tmp.id_mod INNER JOIN activities ON activities.id = id_activity INNER JOIN seances ON seances.id = id_seance WHERE rn = 1 AND (tmp.cycle='1' OR nb_real_max IS NULL OR nb_real_max < tmp.repetition) -- filtre pour la répétition
temps d'exécution : ... 150 ms !!!
En fait, je ne m'y attendais pas :ccool:
au fait, à quoi c'est dû ? je crois deviner à moitié, mais sans plus .... Je suppose que au plus la masse de données à traiter dans les fonction analytique sont faibles, au plus c'est rapide, même si toutes les données ne joue pas dans ces fonctions analytique ... Je me trompe ?

Et sinon, verriez-vous d'autre amélioration à apporter ?

Quelque infos qui pourrais servir :

les tables les plus lourde sont :

seance (~ 20 000 lignes)
realisations (très lourde, de 5 000 à 1 000 000 de lignes, et environ 10 000 pour un même membre (cf champ realisations.member))

Merci d'avance.

PS

Peut-être avez vous remarqué que dans la deuxième requête, je ne récupère pas les résultats des tables provocations et realisations, mais même si je les récupère, ça fait en tout 160 ms, presque rien quoi.

Récrivez sous forme de CTE, évitez de mélanger filtre et jointure, systématisez l'utilisation des alias de table, évitez les apostrophe pour des chiffres si la colonne est en nombre (cycle='1' ???), utilisez COALESCE plutôt que IS NULL (jm.report IS NULL OR jm.report <= NOW() => COALESCE(jm.report, NOW() <= NOW()), cherchez les bons index...

Code:

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
WITH T0 AS
(SELECT S.id AS id_seance, A.id AS id_activity, 
        MAX(nb_real) OVER(PARTITION BY R.seance) AS nb_real_max,
        M.cycle, S.repetition,  M.id AS id_mod, 
        ROW_NUMBER() OVER(PARTITION BY M.id ORDER BY R.nb_real ASC, ordre ASC) AS rn 
 FROM   modules AS M
        INNER JOIN activities AS A
              ON A.id_module = M.id
        INNER JOIN seances AS S
              ON S.id_module=M.id
        INNER JOIN j_seances_niveaux AS js 
              ON js.id_seance=S.id 
        INNER JOIN j_membres_modules jm 
              ON jm.module = M.id
        LEFT JOIN realisations AS R
              ON R.activity = A.id AND member = 1
        LEFT JOIN provocations AS P
              ON P.provoque = A.id
WHERE js.id_niveau = 11 
AND   jm.membre = 1 
AND   (A.type_freq=3 OR A.type_freq=1) -- filtre de la fréquence
  AND (M.cycle='1' OR (date_start<NOW() AND date_end < NOW())) -- filtre pour la date ...
  AND (jm.report IS NULL OR jm.report <= NOW()) -- en faisant attention aux reports éventuels
) AS tmp
SELECT * 
FROM   T0
       INNER JOIN modules AS M 
              ON M.id = T0.id_mod
       INNER JOIN activities AS A 
              ON A.id = id_activity
       INNER JOIN seances AS S
              ON S.id = id_seance 
WHERE rn = 1
AND (T0.cycle='1' OR nb_real_max IS NULL OR nb_real_max < T0.repetition) -- filtre pour la répétition

Il me semble en outre que votre condition
date_start<NOW() AND date_end < NOW())
est dedondante, car j'ose espérer que votre date_end est supérieurs à la date_start ! (en principe vous devriez avoir mis une contrainte CHECK pour ce type de cas de figure...).

Sans la descrition des tables et des index, on ne peut pas vous aider...

Par exemple de quelle table viennent les colonnes nb_real, date_statr, date_end ? Quels sont les types de données de TOUTES vos colonnes ??
Tout cela n'apparait pas dans une boule de cristal !!!

A +

ok, tout d'abord, merci pour tout vos conseil, c'est la première fois qu'on me le dit, et la première fois que j'entends parler de l'écriture CTE.

Je viens juste de débuter dans PostgreSQL, je ne connais donc pas ces spécialités ... par exemple je ne sait jamais si je dois activer ou non le paramètre OIDS.

Voici donc la constitution de mes tables :
vu que j'en ai en tout 35, j'ai utiliser un outils de conversion.
Je suppose que tout n'est pas parfait, mais ne connaissant pas beaucoup, je ne peu pas m'avancer ... Enfin, j'ai déjà refait toutes les clés et les index à la main :aie:

Code:

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
-- Table: activities
 
-- DROP TABLE activities;
 
CREATE TABLE activities
(
  id serial NOT NULL,
  ref integer NOT NULL,
  id_module integer NOT NULL,
  nom text NOT NULL,
  difficult integer,
  date_register timestamp without time zone,
  der_modif timestamp without time zone,
  lundi boolean,
  mardi boolean,
  mercredi boolean,
  jeudi boolean,
  vendredi boolean,
  samedi boolean,
  dimanche boolean,
  note_max integer,
  periode_notation integer,
  eleve_duree real,
  ass_duree real,
  prof_duree real,
  redondance integer,
  is_visuel boolean,
  is_sonore boolean,
  is_notation boolean,
  type_freq integer NOT NULL,
  retro real,
  demarrage timestamp without time zone,
  id_ass1 integer DEFAULT 0,
  assn1 boolean DEFAULT false,
  id_ass2 integer DEFAULT 0,
  assn2 boolean DEFAULT false,
  id_ass3 integer DEFAULT 0,
  assn3 boolean DEFAULT false,
  rem1 text,
  rem2 text,
  rem3 text,
  rem4 text,
  rem5 text,
  rem6 text,
  type_group integer NOT NULL,
  eleve_ref integer DEFAULT 0,
  coeff_duree real DEFAULT 0,
  ref_carnet integer,
  coeff_carnet real,
  freq_note integer,
  son_time real,
  indications text,
  groups text,
  id_etablissement text NOT NULL,
  facultatif boolean DEFAULT false,
  CONSTRAINT activities_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
 
 
-- Table: j_membres_modules
 
-- DROP TABLE j_membres_modules;
 
CREATE TABLE j_membres_modules
(
  id serial NOT NULL,
  membre integer NOT NULL,
  module integer NOT NULL,
  date_start date,
  date_end date,
  ass text,
  date_register timestamp without time zone NOT NULL,
  report timestamp without time zone,
  virtual_start timestamp without time zone,
  CONSTRAINT j_membres_modules_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
 
 
-- Index: j_membres_modules_index_j_membres_seances
 
-- DROP INDEX j_membres_modules_index_j_membres_seances;
 
CREATE UNIQUE INDEX j_membres_modules_index_j_membres_seances
  ON j_membres_modules
  USING btree
  (membre, module);
 
-- Table: j_seances_niveaux
 
-- DROP TABLE j_seances_niveaux;
 
CREATE TABLE j_seances_niveaux
(
  id_seance integer NOT NULL,
  id_niveau integer NOT NULL,
  conform boolean DEFAULT false,
  CONSTRAINT j_seances_niveaux_index PRIMARY KEY (id_seance, id_niveau)
)
WITH (
  OIDS=FALSE
);
 
 
-- Table: provocations
 
-- DROP TABLE provocations;
 
CREATE TABLE provocations
(
  id serial NOT NULL,
  provocante integer NOT NULL,
  provoque integer NOT NULL,
  delai text,
  facultatif boolean,
  CONSTRAINT provocations_primary_key PRIMARY KEY (id),
  CONSTRAINT provocations_unique UNIQUE (provocante, provoque)
)
WITH (
  OIDS=FALSE
);
 
-- Table: realisations
 
-- DROP TABLE realisations;
 
CREATE TABLE realisations
(
  id serial NOT NULL,
  activity integer NOT NULL,
  seance integer NOT NULL,
  nb_real integer NOT NULL,
  "cycle" integer,
  member integer NOT NULL,
  date_real timestamp without time zone NOT NULL,
  duree_prof real,
  duree_ass real,
  duree_eleve real,
  type_real integer NOT NULL,
  note real,
  ass integer,
  prof integer
)
WITH (
  OIDS=FALSE
);
 
 
-- Table: seances
 
-- DROP TABLE seances;
 
CREATE TABLE seances
(
  id serial NOT NULL,
  nom text NOT NULL,
  id_module integer NOT NULL,
  date_register timestamp without time zone,
  der_modif timestamp without time zone,
  ordre integer,
  obs text,
  "version" integer,
  rem1 text,
  rem2 text,
  rem3 text,
  rem4 text,
  rem5 text,
  rem6 text,
  "exec" text,
  video text,
  lien text,
  repetition integer,
  id_etablissement integer,
  CONSTRAINT seances_id PRIMARY KEY (id),
  CONSTRAINT index_seances_clean UNIQUE (id_module, version, nom, id_etablissement)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE seances OWNER TO activator;
 
-- Index: index_seances_ordre
 
-- DROP INDEX index_seances_ordre;
 
CREATE UNIQUE INDEX index_seances_ordre
  ON seances
  USING btree
  (id_module, nom, version, id_etablissement);
 
-- Table: modules
 
-- DROP TABLE modules;
 
CREATE TABLE modules
(
  id serial NOT NULL,
  nom text NOT NULL,
  descript text,
  periode_debut timestamp without time zone,
  periode_fin timestamp without time zone,
  ans integer DEFAULT 0,
  "cycle" boolean DEFAULT false,
  id_etablissement integer,
  master1 integer DEFAULT 0,
  master2 integer DEFAULT 0,
  date_register timestamp without time zone,
  der_modif timestamp without time zone,
  CONSTRAINT "modules.id" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Au fait, es-ce que des index UNIQUE on tendance à ralentir ou a accélérer ?
par exemple je pourrais mettre un index unique sur la table realisations sur les champs seance-activity-member-nb_real . Si je le met, ça risque d'aller plus vite ? a savoir que pour certaine table, la vitesse de calcul est privilégiée, tandis que sur d'autre la vitesse d'insertion est privilégiées ...

Merci encore.

EDIT

A oui au fait, le active = '1', c'est parce que active est de type boolean ...