explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 07IQ : vieille requête

Settings
# exclusive inclusive rows x rows loops node
1. 24.600 1,179,949.092 ↓ 1.3 3,272 1

GroupAggregate (cost=18,653,926.89..29,208,704.97 rows=2,495 width=123) (actual time=1,165,505.381..1,179,949.092 rows=3,272 loops=1)

  • Group Key: fp.cliresp_no, fp.client_no, fp.serie_no, fp.oblgrp_no, fp.projet_no, fp.sous_proj_no
2.          

CTE liste_date

3. 0.013 0.013 ↑ 1.0 1 1

Result (cost=0.00..0.06 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

4.          

CTE capital_en_retard_par_client

5. 0.034 713.531 ↑ 14,111.2 8 1

GroupAggregate (cost=100,257.29..105,055.12 rows=112,890 width=66) (actual time=713.513..713.531 rows=8 loops=1)

  • Group Key: f.cliresp_no, f.client_no, f.serie_no, g_1.oblgrp_no, fd.projet_no, f.sous_proj_no, g_1.serier_no, g_1.oblgrpr_no
6.          

Initplan (for GroupAggregate)

7. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on liste_date (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on liste_date liste_date_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.046 713.493 ↑ 8,683.8 13 1

Sort (cost=100,257.25..100,539.48 rows=112,890 width=38) (actual time=713.492..713.493 rows=13 loops=1)

  • Sort Key: f.cliresp_no, f.client_no, f.serie_no, g_1.oblgrp_no, fd.projet_no, f.sous_proj_no, g_1.serier_no, g_1.oblgrpr_no
  • Sort Method: quicksort Memory: 26kB
10. 0.022 713.447 ↑ 8,683.8 13 1

Hash Join (cost=14,722.79..87,696.21 rows=112,890 width=38) (actual time=701.565..713.447 rows=13 loops=1)

  • Hash Cond: ((f.serie_no = g_1.serie_no) AND (fd.oblgrp_no = g_1.oblgrp_no))
11. 112.996 710.567 ↑ 29,971.8 13 1

Hash Join (cost=14,678.66..52,430.30 rows=389,633 width=30) (actual time=698.692..710.567 rows=13 loops=1)

  • Hash Cond: (fd.facture_no = f.facture_no)
12. 452.121 452.121 ↑ 1.0 1,208,313 1

Seq Scan on tfactdet fd (cost=0.00..29,324.13 rows=1,208,313 width=17) (actual time=0.018..452.121 rows=1,208,313 loops=1)

13. 0.012 145.450 ↑ 5,384.9 10 1

Hash (cost=14,005.55..14,005.55 rows=53,849 width=31) (actual time=145.450..145.450 rows=10 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 513kB
14. 18.224 145.438 ↑ 5,384.9 10 1

Hash Join (cost=5,619.66..14,005.55 rows=53,849 width=31) (actual time=114.759..145.438 rows=10 loops=1)

  • Hash Cond: (e.facture_no = f.facture_no)
15. 14.970 14.970 ↑ 114.2 1,414 1

Index Scan using tencais_depot_dt on tencais e (cost=0.42..7,242.02 rows=161,547 width=6) (actual time=1.450..14.970 rows=1,414 loops=1)

  • Index Cond: (depot_dt > $1)
16. 44.118 112.244 ↓ 3.0 165,518 1

Hash (cost=4,923.43..4,923.43 rows=55,665 width=25) (actual time=112.244..112.244 rows=165,518 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 4 (originally 1) Memory Usage: 3528kB
17. 68.126 68.126 ↓ 3.0 165,518 1

Seq Scan on tfacture f (cost=0.00..4,923.43 rows=55,665 width=25) (actual time=0.016..68.126 rows=165,518 loops=1)

  • Filter: (facteche_dt < $2)
  • Rows Removed by Filter: 1476
18. 0.173 2.858 ↑ 1.0 925 1

Hash (cost=30.25..30.25 rows=925 width=14) (actual time=2.857..2.858 rows=925 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
19. 2.685 2.685 ↑ 1.0 925 1

Seq Scan on toblgrp g_1 (cost=0.00..30.25 rows=925 width=14) (actual time=1.001..2.685 rows=925 loops=1)

20. 13.027 1,164,673.700 ↓ 1.3 3,272 1

Sort (cost=18,548,871.72..18,548,877.96 rows=2,495 width=35) (actual time=1,164,670.598..1,164,673.700 rows=3,272 loops=1)

  • Sort Key: fp.cliresp_no, fp.client_no, fp.serie_no, fp.oblgrp_no, fp.projet_no, fp.sous_proj_no
  • Sort Method: quicksort Memory: 352kB
21. 64.034 1,164,660.673 ↓ 1.3 3,272 1

Nested Loop (cost=7.09..18,548,730.94 rows=2,495 width=35) (actual time=4,739.635..1,164,660.673 rows=3,272 loops=1)

  • Join Filter: ((o.serie_no = fp.serie_no) AND (((g.serier_no IS NULL) AND (SubPlan 10)) OR (NOT (SubPlan 11))))
  • Rows Removed by Join Filter: 4061
22. 1.410 5.238 ↑ 1.0 925 1

Hash Join (cost=6.80..49.77 rows=925 width=15) (actual time=0.063..5.238 rows=925 loops=1)

  • Hash Cond: (g.serie_no = o.serie_no)
23. 3.778 3.778 ↑ 1.0 925 1

Seq Scan on toblgrp g (cost=0.00..30.25 rows=925 width=11) (actual time=0.005..3.778 rows=925 loops=1)

24. 0.024 0.050 ↑ 1.0 169 1

Hash (cost=4.69..4.69 rows=169 width=4) (actual time=0.050..0.050 rows=169 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
25. 0.026 0.026 ↑ 1.0 169 1

Seq Scan on toblig o (cost=0.00..4.69 rows=169 width=4) (actual time=0.003..0.026 rows=169 loops=1)

26. 137.825 137.825 ↓ 8.0 8 925

Index Scan using tcrfp_serie_no_idx on tcrfp fp (cost=0.29..6.34 rows=1 width=35) (actual time=0.108..0.149 rows=8 loops=925)

  • Index Cond: (serie_no = g.serie_no)
  • Filter: ((client_no <> '99999'::text) AND (g.oblgrp_no = oblgrp_no))
  • Rows Removed by Filter: 332
27.          

SubPlan (for Nested Loop)

28. 51.110 427,925.200 ↑ 7.4 14 2,690

Unique (cost=10,024.49..10,025.31 rows=104 width=4) (actual time=159.057..159.080 rows=14 loops=2,690)

29. 669.810 427,874.090 ↓ 1.2 199 2,690

Sort (cost=10,024.49..10,024.90 rows=164 width=4) (actual time=159.053..159.061 rows=199 loops=2,690)

  • Sort Key: ff_2.serie_no
  • Sort Method: quicksort Memory: 44kB
30. 100,705.530 427,204.280 ↓ 2.9 475 2,690

Hash Join (cost=553.93..10,018.46 rows=164 width=4) (actual time=8.309..158.812 rows=475 loops=2,690)

  • Hash Cond: (dd.facture_no = ff_2.facture_no)
31. 118,583.270 324,486.630 ↓ 3.0 484,177 2,690

Nested Loop (cost=0.42..8,857.51 rows=161,547 width=6) (actual time=0.015..120.627 rows=484,177 loops=2,690)

32. 8.070 8.070 ↑ 1.0 1 2,690

CTE Scan on liste_date ld_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=2,690)

33. 205,895.290 205,895.290 ↓ 3.0 484,177 2,690

Index Scan using tencais_depot_dt on tencais dd (cost=0.42..7,242.02 rows=161,547 width=10) (actual time=0.012..76.541 rows=484,177 loops=2,690)

  • Index Cond: (depot_dt <= ld_2.dernier_jour_annee)
34. 239.410 2,012.120 ↓ 2.9 493 2,690

Hash (cost=551.38..551.38 rows=170 width=10) (actual time=0.748..0.748 rows=493 loops=2,690)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
35. 1,570.960 1,772.710 ↓ 2.9 493 2,690

Bitmap Heap Scan on tfacture ff_2 (cost=5.74..551.38 rows=170 width=10) (actual time=0.123..0.659 rows=493 loops=2,690)

  • Recheck Cond: (client_no = fp.client_no)
  • Heap Blocks: exact=1136936
36. 201.750 201.750 ↓ 2.9 493 2,690

Bitmap Index Scan on tfacture_client_no_idx (cost=0.00..5.70 rows=170 width=0) (actual time=0.075..0.075 rows=493 loops=2,690)

  • Index Cond: (client_no = fp.client_no)
37. 78.931 736,528.376 ↑ 6.7 12 4,643

Unique (cost=10,020.87..10,021.42 rows=80 width=4) (actual time=158.611..158.632 rows=12 loops=4,643)

38. 1,012.174 736,449.445 ↓ 1.6 169 4,643

Sort (cost=10,020.87..10,021.14 rows=109 width=4) (actual time=158.607..158.615 rows=169 loops=4,643)

  • Sort Key: ff_3.serie_no
  • Sort Method: quicksort Memory: 33kB
39. 173,091.040 735,437.271 ↓ 3.2 351 4,643

Hash Join (cost=553.20..10,017.18 rows=109 width=4) (actual time=14.685..158.397 rows=351 loops=4,643)

  • Hash Cond: (dd_1.facture_no = ff_3.facture_no)
40. 204,556.651 559,063.630 ↓ 3.0 484,177 4,643

Nested Loop (cost=0.42..8,857.51 rows=161,547 width=6) (actual time=0.015..120.410 rows=484,177 loops=4,643)

41. 9.286 9.286 ↑ 1.0 1 4,643

CTE Scan on liste_date ld_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=4,643)

42. 354,497.693 354,497.693 ↓ 3.0 484,177 4,643

Index Scan using tencais_depot_dt on tencais dd_1 (cost=0.42..7,242.02 rows=161,547 width=10) (actual time=0.012..76.351 rows=484,177 loops=4,643)

  • Index Cond: (depot_dt <= ld_3.dernier_jour_annee)
43. 325.010 3,282.601 ↓ 3.3 371 4,643

Hash (cost=551.36..551.36 rows=113 width=10) (actual time=0.707..0.707 rows=371 loops=4,643)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
44. 2,572.222 2,957.591 ↓ 3.3 371 4,643

Bitmap Heap Scan on tfacture ff_3 (cost=5.72..551.36 rows=113 width=10) (actual time=0.143..0.637 rows=371 loops=4,643)

  • Recheck Cond: (client_no = fp.client_no)
  • Filter: (serie_no IS NOT NULL)
  • Rows Removed by Filter: 107
  • Heap Blocks: exact=1908259
45. 385.369 385.369 ↓ 2.8 478 4,643

Bitmap Index Scan on tfacture_client_no_idx (cost=0.00..5.70 rows=170 width=0) (actual time=0.083..0.083 rows=478 loops=4,643)

  • Index Cond: (client_no = fp.client_no)
46.          

SubPlan (for GroupAggregate)

47. 45.808 7,914.968 ↑ 1.0 1 3,272

Aggregate (cost=139.58..139.59 rows=1 width=32) (actual time=2.419..2.419 rows=1 loops=3,272)

48. 168.142 7,869.160 ↓ 24.0 24 3,272

Nested Loop (cost=67.38..139.57 rows=1 width=4) (actual time=1.577..2.405 rows=24 loops=3,272)

  • Join Filter: (SubPlan 5)
  • Rows Removed by Join Filter: 1
49. 349.664 6,884.288 ↓ 25.0 25 3,272

Nested Loop (cost=67.38..83.38 rows=1 width=10) (actual time=1.553..2.104 rows=25 loops=3,272)

50. 2,022.096 4,404.112 ↓ 163.0 163 3,272

Bitmap Heap Scan on tfactdet fd_1 (cost=66.96..74.93 rows=1 width=10) (actual time=0.891..1.346 rows=163 loops=3,272)

  • Recheck Cond: ((client_no = fp.client_no) AND (projet_no = fp.projet_no))
  • Filter: ((oblgrp_no = fp.oblgrp_no) AND (sous_proj_no = fp.sous_proj_no))
  • Rows Removed by Filter: 1070
  • Heap Blocks: exact=1459285
51. 71.984 2,382.016 ↓ 0.0 0 3,272

BitmapAnd (cost=66.96..66.96 rows=2 width=0) (actual time=0.728..0.728 rows=0 loops=3,272)

52. 386.096 386.096 ↑ 1.3 1,305 3,272

Bitmap Index Scan on tfactdet_client_no (cost=0.00..33.19 rows=1,702 width=0) (actual time=0.118..0.118 rows=1,305 loops=3,272)

  • Index Cond: (client_no = fp.client_no)
53. 1,923.936 1,923.936 ↓ 4.3 7,480 3,272

Bitmap Index Scan on tfactdet_projet_no (cost=0.00..33.52 rows=1,745 width=0) (actual time=0.588..0.588 rows=7,480 loops=3,272)

  • Index Cond: (projet_no = fp.projet_no)
54. 2,130.512 2,130.512 ↓ 0.0 0 532,628

Index Scan using tfacture_facture_no_idx on tfacture ff (cost=0.42..8.44 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=532,628)

  • Index Cond: (facture_no = fd_1.facture_no)
  • Filter: (serie_no = fp.serie_no)
  • Rows Removed by Filter: 1
55. 0.000 0.000 ↑ 1.0 1 81,673

CTE Scan on liste_date ld (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=81,673)

56.          

SubPlan (for Nested Loop)

57. 81.673 816.730 ↑ 9.0 1 81,673

Bitmap Heap Scan on tencais e1 (cost=4.63..107.66 rows=9 width=6) (actual time=0.010..0.010 rows=1 loops=81,673)

  • Recheck Cond: (facture_no = fd_1.facture_no)
  • Filter: (depot_dt <= ld.premier_jour_annee)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=80732
58. 735.057 735.057 ↑ 27.0 1 81,673

Bitmap Index Scan on tencais_facture_no_idx (cost=0.00..4.62 rows=27 width=0) (actual time=0.009..0.009 rows=1 loops=81,673)

  • Index Cond: (facture_no = fd_1.facture_no)
59. 719.840 719.840 ↓ 0.0 0 3,272

CTE Scan on capital_en_retard_par_client cr (cost=0.00..3,951.15 rows=1 width=32) (actual time=0.220..0.220 rows=0 loops=3,272)

  • Filter: ((cliresp_no = fp.cliresp_no) AND (client_no = fp.client_no) AND (serier_no = fp.serie_no) AND (oblgrpr_no = fp.oblgrp_no) AND (projet_no = fp.projet_no) AND (sous_proj_no = fp.sous_proj_no))
  • Rows Removed by Filter: 8
60. 39.264 6,615.984 ↑ 1.0 1 3,272

Aggregate (cost=139.58..139.59 rows=1 width=32) (actual time=2.022..2.022 rows=1 loops=3,272)

61. 121.699 6,576.720 ↓ 25.0 25 3,272

Nested Loop (cost=67.38..139.57 rows=1 width=4) (actual time=1.378..2.010 rows=25 loops=3,272)

  • Join Filter: (SubPlan 8)
  • Rows Removed by Join Filter: 0
62. 87.904 6,046.656 ↓ 25.0 25 3,272

Nested Loop (cost=67.38..83.38 rows=1 width=10) (actual time=1.364..1.848 rows=25 loops=3,272)

63. 1,776.696 3,828.240 ↓ 163.0 163 3,272

Bitmap Heap Scan on tfactdet fd_2 (cost=66.96..74.93 rows=1 width=10) (actual time=0.765..1.170 rows=163 loops=3,272)

  • Recheck Cond: ((client_no = fp.client_no) AND (projet_no = fp.projet_no))
  • Filter: ((oblgrp_no = fp.oblgrp_no) AND (sous_proj_no = fp.sous_proj_no))
  • Rows Removed by Filter: 1070
  • Heap Blocks: exact=1459285
64. 71.984 2,051.544 ↓ 0.0 0 3,272

BitmapAnd (cost=66.96..66.96 rows=2 width=0) (actual time=0.627..0.627 rows=0 loops=3,272)

65. 350.104 350.104 ↑ 1.3 1,305 3,272

Bitmap Index Scan on tfactdet_client_no (cost=0.00..33.19 rows=1,702 width=0) (actual time=0.107..0.107 rows=1,305 loops=3,272)

  • Index Cond: (client_no = fp.client_no)
66. 1,629.456 1,629.456 ↓ 4.3 7,480 3,272

Bitmap Index Scan on tfactdet_projet_no (cost=0.00..33.52 rows=1,745 width=0) (actual time=0.498..0.498 rows=7,480 loops=3,272)

  • Index Cond: (projet_no = fp.projet_no)
67. 2,130.512 2,130.512 ↓ 0.0 0 532,628

Index Scan using tfacture_facture_no_idx on tfacture ff_1 (cost=0.42..8.44 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=532,628)

  • Index Cond: (facture_no = fd_2.facture_no)
  • Filter: (serie_no = fp.serie_no)
  • Rows Removed by Filter: 1
68. 0.000 0.000 ↑ 1.0 1 81,673

CTE Scan on liste_date ld_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=81,673)

69.          

SubPlan (for Nested Loop)

70. 81.673 408.365 ↑ 9.0 1 81,673

Bitmap Heap Scan on tencais e1_1 (cost=4.63..107.66 rows=9 width=6) (actual time=0.005..0.005 rows=1 loops=81,673)

  • Recheck Cond: (facture_no = fd_2.facture_no)
  • Filter: (depot_dt <= ld_1.dernier_jour_annee)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=80732
71. 326.692 326.692 ↑ 27.0 1 81,673

Bitmap Index Scan on tencais_facture_no_idx (cost=0.00..4.62 rows=27 width=0) (actual time=0.004..0.004 rows=1 loops=81,673)

  • Index Cond: (facture_no = fd_2.facture_no)
Planning time : 6.052 ms
Execution time : 1,179,951.500 ms