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

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:

---

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

---

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

Code:

---

, 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:

---

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

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 +

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"

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:

---

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

---