Optimisation requête SQL sur un booléen - Postgres

**olivierandré** · 20/06/2020, 15h42

Version Postgres 9.6

Bonjour à toutes et à tous,

Je viens poster car je me pose une question sur ma requête Sql.

Voici la requête, elle est une peu longue, mais ma question porte sur la ligne n°451.

Quand je mentionne la clause "AND p.anonyme = FALSE", ma requête renvoie le résultat en 1 seconde.
Mais si je mentionne la clause "AND p.anonyme = TRUE", alors la requête mouline pendant 1 minute.
La table p contient 7 000 ligne où p.anonyme = FALSE et 7 lignes où p.anonyme = TRUE.

La question que je me pose est pourquoi le SGBD prend autant de temps à ramener le résultat pour 7 lignes alors que le résultat est instantané avec 7 000 lignes ?

Cdlt,

Olivier

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
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
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
 
--Nouvelle requête
WITH RET AS (SELECT id AS A, code  AS B FROM debt_type) --Liste les codes et id des types de dettes
 
SELECT T.c2 AS Sec, 					--Secteur name
T.c6 AS Code,		 					-- Planter code
T.c7 AS Name, 		 					-- Planter last name
COALESCE(SUM(T.c8),0) AS Weight, 		-- Poids
COALESCE(SUM(T.c9),0) AS Brut, 			-- Montant brut
COALESCE(SUM(T.c13),0) AS B_EXC,		-- Prime PR_24
COALESCE(SUM(T.c15),0) AS PRECOL, 		-- Prime PR_6
COALESCE(SUM(T.c17),0) AS INT,			-- Prime PR_5
COALESCE(SUM(T.c19),0) AS FID, 			-- Prime PR_3
COALESCE(SUM(T.c21),0) AS SOUT,			-- Prime PR_4
COALESCE(SUM(T.c23),0) AS TEH, 			-- Prime PR_7
COALESCE(SUM(T.c25),0) AS TON,			-- Prime PR_1
COALESCE(SUM(T.c27),0) AS RELI, 		-- Prime PR_64
COALESCE(SUM(T.c29),0) AS S_FOUR,		-- Prime PR_11
COALESCE(SUM(T.c31),0) AS R_TRAN,		-- Prime PR_2
COALESCE(SUM(T.c33),0) AS AUT_P,		-- Prime PR_AUTRES
 
(
COALESCE(SUM(T.c9),0)+COALESCE(SUM(T.c13),0)+COALESCE(SUM(T.c15),0)+COALESCE(SUM(T.c17),0)+COALESCE(SUM(T.c19),0)+COALESCE(SUM(T.c21),0)+COALESCE(SUM(T.c23),0)+COALESCE(SUM(T.c25),0)+COALESCE(SUM(T.c27),0)+COALESCE(SUM(T.c29),0)+COALESCE(SUM(T.c31),0)+COALESCE(SUM(T.c33),0)
) AS BRUT_P, -- Montant Brut planteur (Brut + primes)
COALESCE(SUM(T.c11),0) AS TAX, 		-- Taxes
 
--CALCUL ACOMPTE
CASE 
     WHEN COALESCE(SUM(T.c35),0)=0 THEN 0 --SI ACHAT DIRECT NULL, ON CALCUL RIEN, C'EST 0
     ELSE ( -- ON calcule montant des AD + primes - impôts - retenues
(
COALESCE(SUM(T.c35),0) + COALESCE(SUM(T.c13),0) + COALESCE(SUM(T.c15),0) + COALESCE(SUM(T.c17),0) + COALESCE(SUM(T.c19),0) + COALESCE(SUM(T.c21),0) + COALESCE(SUM(T.c23),0) +  COALESCE(SUM(T.c25),0) + COALESCE(SUM(T.c27),0) + COALESCE(SUM(T.c29),0) + COALESCE(SUM(T.c31),0) + COALESCE(SUM(T.c33),0) 
) -
(
COALESCE(SUM(T.c11),0) + 
(
SELECT COALESCE(SUM(pbcrdt4.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt4 ON pcr.id = pbcrdt4.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_4')
LEFT JOIN pay_back pbdt4 ON pbcrdt4.pay_back_id = pbdt4.id
WHERE pbdt4.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt4.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt2.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt2 ON pcr.id = pbcrdt2.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_2')
LEFT JOIN pay_back pbdt2 ON pbcrdt2.pay_back_id = pbdt2.id
WHERE pbdt2.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt2.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt6.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt6 ON pcr.id = pbcrdt6.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_6')
LEFT JOIN pay_back pbdt6 ON pbcrdt6.pay_back_id = pbdt6.id
WHERE pbdt6.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt6.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) 
+
(
SELECT COALESCE(SUM(pbcrdt7.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt7 ON pcr.id = pbcrdt7.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_7')
LEFT JOIN pay_back pbdt7 ON pbcrdt7.pay_back_id = pbdt7.id
WHERE pbdt7.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt7.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt5.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt5 ON pcr.id = pbcrdt5.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_5')
LEFT JOIN pay_back pbdt5 ON pbcrdt5.pay_back_id = pbdt5.id
WHERE pbdt5.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt5.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt99.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt99 ON pcr.id = pbcrdt99.payment_calendar_row_id AND debt.debt_type_id IN (SELECT A FROM RET WHERE RET.B NOT IN ('DT_2','DT_4','DT_5','DT_6','DT_7'))
LEFT JOIN pay_back pbdt99 ON pbcrdt99.pay_back_id = pbdt99.id
WHERE pbdt99.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt99.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
))
) 
END
AS ACC,			-- Accompte (Achat direct - tax (reste à ajouter les primes et soustraire les retenues))
 
 
 
--RETENUES DEBUT
(
SELECT COALESCE(SUM(pbcrdt4.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt4 ON pcr.id = pbcrdt4.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_4')
LEFT JOIN pay_back pbdt4 ON pbcrdt4.pay_back_id = pbdt4.id
 
WHERE pbdt4.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt4.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS FUM,
 
(
SELECT COALESCE(SUM(pbcrdt2.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt2 ON pcr.id = pbcrdt2.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_2')
LEFT JOIN pay_back pbdt2 ON pbcrdt2.pay_back_id = pbdt2.id
 
WHERE pbdt2.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt2.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS A_PROD
 
,
(
SELECT COALESCE(SUM(pbcrdt6.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt6 ON pcr.id = pbcrdt6.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_6')
LEFT JOIN pay_back pbdt6 ON pbcrdt6.pay_back_id = pbdt6.id
 
WHERE pbdt6.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt6.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS PHYTO
,
 
(
SELECT COALESCE(SUM(pbcrdt7.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt7 ON pcr.id = pbcrdt7.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_7')
LEFT JOIN pay_back pbdt7 ON pbcrdt7.pay_back_id = pbdt7.id
 
WHERE pbdt7.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt7.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS PLAN
,
 
(
SELECT COALESCE(SUM(pbcrdt5.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt5 ON pcr.id = pbcrdt5.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_5')
LEFT JOIN pay_back pbdt5 ON pbcrdt5.pay_back_id = pbdt5.id
 
WHERE pbdt5.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt5.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS MAT
,
 
(
SELECT COALESCE(SUM(pbcrdt99.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt99 ON pcr.id = pbcrdt99.payment_calendar_row_id AND debt.debt_type_id IN (SELECT A FROM RET WHERE RET.B NOT IN ('DT_2','DT_4','DT_5','DT_6','DT_7'))
LEFT JOIN pay_back pbdt99 ON pbcrdt99.pay_back_id = pbdt99.id
 
WHERE pbdt99.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt99.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) AS RET_AUTRES,
 
 
--DEBUT TOTAL RETENUES
--CALCUL ACOMPTE
(
 
(
CASE 
     WHEN COALESCE(SUM(T.c35),0)=0 THEN 0 --SI ACHAT DIRECT NULL, ON CALCUL RIEN, C'EST 0
     ELSE ( -- ON calcule montant des AD + primes - impôts - retenues
(
COALESCE(SUM(T.c35),0) + COALESCE(SUM(T.c13),0) + COALESCE(SUM(T.c15),0) + COALESCE(SUM(T.c17),0) + COALESCE(SUM(T.c19),0) + COALESCE(SUM(T.c21),0) + COALESCE(SUM(T.c23),0) +  COALESCE(SUM(T.c25),0) + COALESCE(SUM(T.c27),0) + COALESCE(SUM(T.c29),0) + COALESCE(SUM(T.c31),0) + COALESCE(SUM(T.c33),0) 
) -
(
COALESCE(SUM(T.c11),0) + 
(
SELECT COALESCE(SUM(pbcrdt4.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt4 ON pcr.id = pbcrdt4.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_4')
LEFT JOIN pay_back pbdt4 ON pbcrdt4.pay_back_id = pbdt4.id
WHERE pbdt4.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt4.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt2.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt2 ON pcr.id = pbcrdt2.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_2')
LEFT JOIN pay_back pbdt2 ON pbcrdt2.pay_back_id = pbdt2.id
WHERE pbdt2.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt2.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt6.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt6 ON pcr.id = pbcrdt6.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_6')
LEFT JOIN pay_back pbdt6 ON pbcrdt6.pay_back_id = pbdt6.id
WHERE pbdt6.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt6.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) 
+
(
SELECT COALESCE(SUM(pbcrdt7.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt7 ON pcr.id = pbcrdt7.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_7')
LEFT JOIN pay_back pbdt7 ON pbcrdt7.pay_back_id = pbdt7.id
WHERE pbdt7.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt7.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt5.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt5 ON pcr.id = pbcrdt5.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_5')
LEFT JOIN pay_back pbdt5 ON pbcrdt5.pay_back_id = pbdt5.id
WHERE pbdt5.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt5.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt99.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt99 ON pcr.id = pbcrdt99.payment_calendar_row_id AND debt.debt_type_id IN (SELECT A FROM RET WHERE RET.B NOT IN ('DT_2','DT_4','DT_5','DT_6','DT_7'))
LEFT JOIN pay_back pbdt99 ON pbcrdt99.pay_back_id = pbdt99.id
WHERE pbdt99.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt99.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
))
) 
END
)
+			-- Accompte
COALESCE(SUM(T.c11),0) --on ajoute les impots
+
 
 
--RETENUES DEBUT
(
SELECT COALESCE(SUM(pbcrdt4.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt4 ON pcr.id = pbcrdt4.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_4')
LEFT JOIN pay_back pbdt4 ON pbcrdt4.pay_back_id = pbdt4.id
 
WHERE pbdt4.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt4.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt2.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt2 ON pcr.id = pbcrdt2.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_2')
LEFT JOIN pay_back pbdt2 ON pbcrdt2.pay_back_id = pbdt2.id
 
WHERE pbdt2.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt2.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
) 
+
(
SELECT COALESCE(SUM(pbcrdt6.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt6 ON pcr.id = pbcrdt6.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_6')
LEFT JOIN pay_back pbdt6 ON pbcrdt6.pay_back_id = pbdt6.id
 
WHERE pbdt6.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt6.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt7.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt7 ON pcr.id = pbcrdt7.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_7')
LEFT JOIN pay_back pbdt7 ON pbcrdt7.pay_back_id = pbdt7.id
 
WHERE pbdt7.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt7.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt5.amount),0) FROM planter plan
 
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt5 ON pcr.id = pbcrdt5.payment_calendar_row_id AND debt.debt_type_id = (SELECT A FROM RET WHERE RET.B = 'DT_5')
LEFT JOIN pay_back pbdt5 ON pbcrdt5.pay_back_id = pbdt5.id
 
WHERE pbdt5.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt5.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
+
(
SELECT COALESCE(SUM(pbcrdt99.amount),0) FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt99 ON pcr.id = pbcrdt99.payment_calendar_row_id AND debt.debt_type_id IN (SELECT A FROM RET WHERE RET.B NOT IN ('DT_2','DT_4','DT_5','DT_6','DT_7'))
LEFT JOIN pay_back pbdt99 ON pbcrdt99.pay_back_id = pbdt99.id
WHERE pbdt99.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt99.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6
)
)
--FIN TOTAL RETENUES
 
 
FROM (
WITH PR AS (SELECT id AS A, code  AS B FROM prime)  --On liste les codes et id des primes pour plus tard
 
SELECT 
 
 
s.id AS c0,										--sector : id
s.code AS c1,									--sector : code
s.name AS c2,									--sector : name
pi.id AS c3, 									--purchase_invoice : id
pi.date_invoice AS c4,							--purchase_invoice : date
p.id AS c5,										--planter : id
p.code AS c6,									--planter : code
p.last_name AS c7,								--planter : lastname
pi.totalweight AS c8,							--purchase_invoice : poids
pi.totalbrut AS c9,								--purchase_invoice : montant brut,
pit.id AS c10,									--purchase_invoice_tax : id
pit.value_purchase_invoice_tax AS c11,			--purchase_invoice_tax : montant tax
pip24.id AS c12,								--purchase_invoice_prime : id 24
pip24.value_purchase_invoice_prime AS c13,		--purchase_invoice_prime : montant 24
pip6.id AS c14,									--purchase_invoice_prime : id 6
pip6.value_purchase_invoice_prime AS c15,		--purchase_invoice_prime : montant 6
pip5.id AS c16,									--purchase_invoice_prime : id 5
pip5.value_purchase_invoice_prime AS c17,		--purchase_invoice_prime : montant 5
pip3.id AS c18,									--purchase_invoice_prime : id 3
pip3.value_purchase_invoice_prime AS c19,		--purchase_invoice_prime : montant 3
pip4.id AS c20,									--purchase_invoice_prime : id 4
pip4.value_purchase_invoice_prime AS c21,		--purchase_invoice_prime : montant 4
pip7.id AS c22,									--purchase_invoice_prime : id 7
pip7.value_purchase_invoice_prime AS c23,		--purchase_invoice_prime : montant 7
pip1.id AS c24,									--purchase_invoice_prime : id 7
pip1.value_purchase_invoice_prime AS c25,		--purchase_invoice_prime : montant 7
pip64.id AS c26,								--purchase_invoice_prime : id 64
pip64.value_purchase_invoice_prime AS c27,		--purchase_invoice_prime : montant 64
pip11.id AS c28,								--purchase_invoice_prime : id 11
pip11.value_purchase_invoice_prime AS c29,		--purchase_invoice_prime : montant 11
pip2.id AS c30,									--purchase_invoice_prime : id 2
pip2.value_purchase_invoice_prime AS c31,		--purchase_invoice_prime : montant 2
pip99.id AS c32,								--purchase_invoice_prime : id 99
pip99.value_purchase_invoice_prime AS c33,		--purchase_invoice_prime : montant 99
piacc.id AS c34,								--ACCOMPTE : id
piacc.totalbrut AS c35  						--ACCOMPTE MAIS DOIT SOUSTRAIRE LES RETENUES PLUS TARD
 
FROM purchase_invoice pi
INNER JOIN purchase_invoice_tax pit ON pi.id = pit.purchase_invoice_id
INNER JOIN planter p ON pi.planter_id = p.id
INNER JOIN purchase_invoice_delivery pid ON pi.id = pid.purchase_invoice_id
INNER JOIN delivery d ON pid.delivery_id = d.id
INNER JOIN origin o ON d.origin_id = o.id
INNER JOIN ancadrementgroup ag ON o.ancadrementgroup_id = ag.id
INNER JOIN sector s ON ag.sector_id = s.id
INNER JOIN eai e ON s.eai_id = e.id
 
--JOINTURES POUR RECUP DES PRIME PAR TYPE
LEFT JOIN purchase_invoice_prime pip24 ON pi.id = pip24.purchase_invoice_id AND pip24.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_24')
LEFT JOIN purchase_invoice_prime pip6 ON pi.id = pip6.purchase_invoice_id AND pip6.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_6')
LEFT JOIN purchase_invoice_prime pip5 ON pi.id = pip5.purchase_invoice_id AND pip5.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_5')
LEFT JOIN purchase_invoice_prime pip3 ON pi.id = pip3.purchase_invoice_id AND pip3.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_3')
LEFT JOIN purchase_invoice_prime pip4 ON pi.id = pip4.purchase_invoice_id AND pip4.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_4')
LEFT JOIN purchase_invoice_prime pip7 ON pi.id = pip7.purchase_invoice_id AND pip7.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_7')
LEFT JOIN purchase_invoice_prime pip1 ON pi.id = pip1.purchase_invoice_id AND pip1.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_1')
LEFT JOIN purchase_invoice_prime pip64 ON pi.id = pip64.purchase_invoice_id AND pip64.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_64')
LEFT JOIN purchase_invoice_prime pip11 ON pi.id = pip11.purchase_invoice_id AND pip11.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_11')
LEFT JOIN purchase_invoice_prime pip2 ON pi.id = pip2.purchase_invoice_id AND pip2.prime_id = (SELECT A FROM PR WHERE PR.B = 'PR_2')
LEFT JOIN purchase_invoice_prime pip99 ON pi.id = pip99.purchase_invoice_id AND pip99.prime_id IN (SELECT A FROM PR WHERE PR.B NOT IN ('PR_1','PR_2','PR_3','PR_4','PR_5','PR_6','PR_7','PR_11','PR_24','PR_64')) 
--FIN PRIMES	
LEFT JOIN purchase_invoice piacc ON pi.id = piacc.id AND piacc.type = 1	--JOINTURE POUR ACCOMPTES
 
 
WHERE pi.date_invoice >= '2020-04-01 00:00:00' 	--paramètres date début
AND pi.date_invoice <= '2020-04-30 23:59:59'	--paramètres date fin	
AND e.id = 8 									--paramètres EAIID
AND p.anonyme = FALSE 						    --paramètres Type planter (TRUE OR FALSE)
 
GROUP BY s.id, pi.id, p.id, pit.id, pip24.id, pip6.id, pip5.id, pip3.id, pip4.id, pip7.id, pip1.id, pip64.id, pip11.id, pip2.id, pip99.id, piacc.id					--GROUPAGE
ORDER BY p.code ASC
) AS T
 
GROUP BY T.c1, T.c2, T.c6, T.c7
ORDER BY T.c1

**olivierandré** · 20/06/2020, 15h56

A la ligne 4, j'ai ajouté un SELECT :

Code :

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

, PLA AS (SELECT id AS A, anonyme  AS B FROM planter)

Et à la ligne 424, j'ai ajouté une clause à la jointure sur la table "p" et je suis passé à 380 ms.

Code :

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

1
2
 
INNER JOIN planter p ON pi.planter_id = p.id AND p.id IN (SELECT PLA.A FROM PLA WHERE PLA.B = TRUE)

Mais je ne sais pas pourquoi le SGBD prenait autant de temps avec la clause en fin de requête.

Cdlt,

Olivier

**SQLpro** · 22/06/2020, 17h13

Il faut regarder le plan d'exécution de chacune des requêtes pour comprendre par ou il passe et là ou il perd du temps.

A +

**escartefigue** · 24/06/2020, 10h29

Bonjour

Cause possible : la table est mal organisée et il y a un index sur le booléen, l'index cluster est donc peu efficient, en ce cas, le tablescan est rapide pour pour récupérer un grand nombre de lignes, alors qu'un accès par un index est mauvais pour récupérer les quelques cas de "true"

**aieeeuuuuu** · 26/06/2020, 09h41

Bonjour

Par ailleurs, vous auriez potentiellement de meilleures performances (et une meilleure maintenabilité, mais ça, ça reste une question de gout...) en évitant toutes les sous requete quasi identiques (à l'exception du filtre sur debt_type_id )

Vous pouvez pour cela joindre toutes les tables des sous requetes à la requete principale, et faire un CASE dans le SUM pour effectuer le calcul voulu

un truc sur ce principe (à compléter et sans doute corriger, c'est juste pour illustrer...):

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
 
SELECT 
    SUM(CASE RET.B 
        WHEN  'DT_4' THEN pbcrdt4.amount
        WHEN 'DT_2' THEN pbcrdt2.amount
....
FROM planter plan
LEFT JOIN debt debt ON plan.id = debt.planter_id
LEFT JOIN payment_calendar pc ON debt.id = pc.debt_id
LEFT JOIN payment_calendar_row pcr ON pc.id = pcr.payment_calendar_id
LEFT JOIN pay_back_calendar_row pbcrdt4 ON pcr.id = pbcrdt4.payment_calendar_row_id 
LEFT JOIN RET ON debt.debt_type_id = RET.A 
LEFT JOIN pay_back pbdt4 ON pbcrdt4.pay_back_id = pbdt4.id
WHERE pbdt4.pay_back_date >= '2020-04-01 00:00:00'  --parametres date debut
AND pbdt4.pay_back_date   <= '2020-04-30 23:59:59'	--parametres date fin
AND plan.code = T.c6

Optimisation requête SQL sur un booléen - Postgres

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager