explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F6b9B

Settings
# exclusive inclusive rows x rows loops node
1. 2,557.773 185,011.529 ↓ 0.0 0 1

Insert on desserte_tmp (cost=8,256,493.01..21,005,539.41 rows=1,780,213 width=612) (actual time=185,011.529..185,011.529 rows=0 loops=1)

2. 2,260.269 182,453.756 ↓ 1.2 2,210,456 1

Hash Left Join (cost=8,256,493.01..21,005,539.41 rows=1,780,213 width=612) (actual time=163,538.877..182,453.756 rows=2,210,456 loops=1)

  • Hash Cond: (t_real.id_vehicule = v.id_vehicule)
3. 3,741.590 180,065.531 ↓ 1.2 2,210,456 1

Hash Join (cost=8,246,067.93..20,781,783.19 rows=1,780,213 width=429) (actual time=163,410.833..180,065.531 rows=2,210,456 loops=1)

  • Hash Cond: (pa.id_arret = a.id_arret)
4. 3,985.249 175,203.387 ↓ 1.2 2,210,456 1

Hash Join (cost=8,143,310.25..20,462,744.44 rows=1,780,213 width=429) (actual time=155,032.048..175,203.387 rows=2,210,456 loops=1)

  • Hash Cond: (pap.id_point_arret = pa.id_point_arret)
5. 5,887.751 168,542.007 ↓ 1.2 2,210,456 1

Hash Join (cost=7,906,834.93..19,996,788.06 rows=1,780,213 width=421) (actual time=148,124.185..168,542.007 rows=2,210,456 loops=1)

  • Hash Cond: (d_theo.id_point_arret_parcours = pap.id_point_arret_parcours)
6. 1,167.893 152,550.264 ↓ 1.2 2,210,456 1

Hash Join (cost=6,966,620.94..18,746,359.01 rows=1,780,213 width=421) (actual time=134,649.053..152,550.264 rows=2,210,456 loops=1)

  • Hash Cond: (p.id_sens = s.id_sens)
7. 2,345.396 151,379.729 ↓ 1.2 2,210,456 1

Hash Left Join (cost=6,966,462.61..18,741,524.41 rows=1,780,213 width=423) (actual time=134,646.395..151,379.729 rows=2,210,456 loops=1)

  • Hash Cond: (t_real.id_real_parcours = rp_real.id_real_parcours)
8. 3,212.977 144,707.433 ↓ 1.2 2,210,456 1

Hash Join (cost=6,629,756.95..18,183,980.69 rows=1,780,213 width=399) (actual time=130,319.427..144,707.433 rows=2,210,456 loops=1)

  • Hash Cond: (rp_theo.id_parcours = p.id_parcours)
9. 2,111.990 138,008.391 ↓ 1.2 2,210,456 1

Hash Left Join (cost=6,434,921.33..17,764,454.61 rows=1,780,213 width=372) (actual time=122,150.009..138,008.391 rows=2,210,456 loops=1)

  • Hash Cond: (d_real.id_travail_depart = t_real.id_travail)
10. 2,359.619 132,088.017 ↓ 1.2 2,210,456 1

Hash Join (cost=6,060,499.10..17,187,068.31 rows=1,780,213 width=357) (actual time=118,341.540..132,088.017 rows=2,210,456 loops=1)

  • Hash Cond: (va.id_service_agent = sa.id_service_agent)
11. 1,331.046 129,549.053 ↓ 1.2 2,210,456 1

Hash Join (cost=6,043,774.68..16,996,354.80 rows=1,780,213 width=359) (actual time=111,947.678..129,549.053 rows=2,210,456 loops=1)

  • Hash Cond: ((t_theo.date_travail = c.date_calendrier) AND (t_theo.id_ce_travail = c.id_ce_calendrier))
12. 3,295.717 128,214.864 ↓ 1.2 2,210,456 1

Hash Join (cost=6,043,612.86..16,986,839.04 rows=1,780,213 width=319) (actual time=111,944.510..128,214.864 rows=2,210,456 loops=1)

  • Hash Cond: (t_theo.id_real_parcours = rp_theo.id_real_parcours)
13. 2,180.708 120,485.907 ↓ 1.2 2,210,456 1

Hash Join (cost=5,706,907.20..16,471,017.32 rows=1,780,213 width=298) (actual time=71,697.997..120,485.907 rows=2,210,456 loops=1)

  • Hash Cond: (t_theo.id_voiture_continue = vc.id_voiture_continue)
14. 2,114.347 113,989.176 ↓ 1.2 2,210,456 1

Hash Join (cost=5,672,939.39..16,271,788.58 rows=1,780,213 width=296) (actual time=63,697.511..113,989.176 rows=2,210,456 loops=1)

  • Hash Cond: (t_theo.id_vacation_agent = va.id_vacation_agent)
15. 3,504.440 106,668.925 ↓ 1.2 2,210,456 1

Hash Join (cost=5,634,439.07..16,086,879.17 rows=1,780,213 width=296) (actual time=39,450.769..106,668.925 rows=2,210,456 loops=1)

  • Hash Cond: (d_theo.id_travail_depart = t_theo.id_travail)
16. 17,577.595 99,148.395 ↓ 1.2 2,210,456 1

Hash Right Join (cost=5,249,551.83..15,523,332.87 rows=1,780,213 width=275) (actual time=11,318.941..99,148.395 rows=2,210,456 loops=1)

  • Hash Cond: (d_real.id_desserte_theo = d_theo.id_desserte)
17. 78,800.637 78,800.637 ↓ 1.0 89,137,114 1

Seq Scan on desserte d_real (cost=0.00..4,106,626.12 rows=89,137,112 width=243) (actual time=0.262..78,800.637 rows=89,137,114 loops=1)

18. 681.785 2,770.163 ↓ 1.2 2,204,299 1

Hash (cost=5,213,391.17..5,213,391.17 rows=1,780,213 width=40) (actual time=2,770.162..2,770.163 rows=2,204,299 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 6,067kB
19. 2,088.378 2,088.378 ↓ 1.2 2,204,299 1

Index Scan using desserte_date_ce_index on desserte d_theo (cost=0.57..5,213,391.17 rows=1,780,213 width=40) (actual time=0.357..2,088.378 rows=2,204,299 loops=1)

  • Index Cond: ((date_desserte >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_desserte <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
  • Filter: ((id_origine_realisation = '1'::numeric) AND ((id_ce_desserte = '-1'::numeric) OR (id_ce_desserte = '2'::numeric) OR (id_ce_desserte = '0'::numeric) OR (id_ce_desserte = '1'::numeric)))
  • Rows Removed by Filter: 798,289
20. 1,563.533 4,016.090 ↑ 1.0 5,357,966 1

Hash (cost=276,052.66..276,052.66 rows=5,357,966 width=37) (actual time=4,016.090..4,016.090 rows=5,357,966 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 6,848kB
21. 2,452.557 2,452.557 ↑ 1.0 5,357,966 1

Seq Scan on travail t_theo (cost=0.00..276,052.66 rows=5,357,966 width=37) (actual time=0.013..2,452.557 rows=5,357,966 loops=1)

22. 151.087 5,205.904 ↑ 1.0 543,881 1

Hash (cost=29,045.81..29,045.81 rows=543,881 width=14) (actual time=5,205.904..5,205.904 rows=543,881 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 8,153kB
23. 5,054.817 5,054.817 ↑ 1.0 543,881 1

Seq Scan on vacation_agent va (cost=0.00..29,045.81 rows=543,881 width=14) (actual time=14.003..5,054.817 rows=543,881 loops=1)

24. 91.362 4,316.023 ↑ 1.0 348,764 1

Hash (cost=27,905.26..27,905.26 rows=348,764 width=16) (actual time=4,316.023..4,316.023 rows=348,764 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 6,049kB
25. 277.886 4,224.661 ↑ 1.0 348,764 1

Hash Join (cost=11,772.10..27,905.26 rows=348,764 width=16) (actual time=1,066.980..4,224.661 rows=348,764 loops=1)

  • Hash Cond: (vc.id_service_voiture = sv.id_service_voiture)
26. 2,880.136 2,880.136 ↑ 1.0 348,764 1

Seq Scan on voiture_continue vc (cost=0.00..9,426.64 rows=348,764 width=19) (actual time=0.186..2,880.136 rows=348,764 loops=1)

27. 76.189 1,066.639 ↑ 1.0 348,760 1

Hash (cost=5,709.60..5,709.60 rows=348,760 width=11) (actual time=1,066.639..1,066.639 rows=348,760 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 5,659kB
28. 990.450 990.450 ↑ 1.0 348,760 1

Seq Scan on service_voiture sv (cost=0.00..5,709.60 rows=348,760 width=11) (actual time=0.284..990.450 rows=348,760 loops=1)

29. 1,188.854 4,433.240 ↑ 1.0 4,664,296 1

Hash (cost=246,516.96..246,516.96 rows=4,664,296 width=29) (actual time=4,433.240..4,433.240 rows=4,664,296 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 5,392kB
30. 3,244.386 3,244.386 ↑ 1.0 4,664,296 1

Seq Scan on real_parcours rp_theo (cost=0.00..246,516.96 rows=4,664,296 width=29) (actual time=0.193..3,244.386 rows=4,664,296 loops=1)

31. 0.710 3.143 ↓ 1.0 2,597 1

Hash (cost=122.93..122.93 rows=2,593 width=47) (actual time=3.143..3.143 rows=2,597 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 224kB
32. 2.433 2.433 ↓ 1.0 2,597 1

Seq Scan on calendrier c (cost=0.00..122.93 rows=2,593 width=47) (actual time=0.012..2.433 rows=2,597 loops=1)

33. 103.494 179.345 ↑ 1.0 495,485 1

Hash (cost=8,110.85..8,110.85 rows=495,485 width=12) (actual time=179.345..179.345 rows=495,485 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 7,433kB
34. 75.851 75.851 ↑ 1.0 495,485 1

Seq Scan on service_agent sa (cost=0.00..8,110.85 rows=495,485 width=12) (actual time=0.366..75.851 rows=495,485 loops=1)

35. 1,348.465 3,808.384 ↑ 1.0 5,357,966 1

Hash (cost=276,052.66..276,052.66 rows=5,357,966 width=23) (actual time=3,808.384..3,808.384 rows=5,357,966 loops=1)

  • Buckets: 262,144 Batches: 64 Memory Usage: 6,185kB
36. 2,459.919 2,459.919 ↑ 1.0 5,357,966 1

Seq Scan on travail t_real (cost=0.00..276,052.66 rows=5,357,966 width=23) (actual time=0.028..2,459.919 rows=5,357,966 loops=1)

37. 697.068 3,486.065 ↑ 1.1 2,498,780 1

Hash (cost=137,865.60..137,865.60 rows=2,804,642 width=39) (actual time=3,486.065..3,486.065 rows=2,498,780 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 6,201kB
38. 1,509.678 2,788.997 ↑ 1.1 2,498,780 1

Hash Join (cost=11,182.71..137,865.60 rows=2,804,642 width=39) (actual time=125.702..2,788.997 rows=2,498,780 loops=1)

  • Hash Cond: (p.id_ligne = l.id_ligne)
39. 746.650 1,153.773 ↑ 1.0 2,802,229 1

Hash Left Join (cost=1.70..73,951.30 rows=2,804,642 width=36) (actual time=0.026..1,153.773 rows=2,802,229 loops=1)

  • Hash Cond: (p.id_sous_ligne = sl.id_sous_ligne)
40. 407.107 407.107 ↑ 1.0 2,802,229 1

Seq Scan on parcours p (cost=0.00..66,587.42 rows=2,804,642 width=51) (actual time=0.005..407.107 rows=2,802,229 loops=1)

41. 0.007 0.016 ↑ 1.0 31 1

Hash (cost=1.31..1.31 rows=31 width=12) (actual time=0.016..0.016 rows=31 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
42. 0.009 0.009 ↑ 1.0 31 1

Seq Scan on sous_ligne sl (cost=0.00..1.31 rows=31 width=12) (actual time=0.005..0.009 rows=31 loops=1)

43. 60.774 125.546 ↑ 1.0 263,675 1

Hash (cost=6,334.56..6,334.56 rows=263,956 width=17) (actual time=125.546..125.546 rows=263,675 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 5,269kB
44. 64.772 64.772 ↑ 1.0 263,675 1

Seq Scan on ligne l (cost=0.00..6,334.56 rows=263,956 width=17) (actual time=0.221..64.772 rows=263,675 loops=1)

45. 1,356.112 4,326.900 ↑ 1.0 4,664,296 1

Hash (cost=246,516.96..246,516.96 rows=4,664,296 width=32) (actual time=4,326.900..4,326.900 rows=4,664,296 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 5,188kB
46. 2,970.788 2,970.788 ↑ 1.0 4,664,296 1

Seq Scan on real_parcours rp_real (cost=0.00..246,516.96 rows=4,664,296 width=32) (actual time=0.315..2,970.788 rows=4,664,296 loops=1)

47. 1.041 2.642 ↑ 1.0 5,478 1

Hash (cost=89.81..89.81 rows=5,481 width=12) (actual time=2.642..2.642 rows=5,478 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 300kB
48. 1.601 1.601 ↑ 1.0 5,478 1

Seq Scan on sens s (cost=0.00..89.81 rows=5,481 width=12) (actual time=0.209..1.601 rows=5,478 loops=1)

49. 5,042.698 10,103.992 ↑ 1.0 22,677,119 1

Hash (cost=545,661.22..545,661.22 rows=22,697,822 width=12) (actual time=10,103.992..10,103.992 rows=22,677,119 loops=1)

  • Buckets: 262,144 Batches: 256 Memory Usage: 6,013kB
50. 5,061.294 5,061.294 ↑ 1.0 22,677,119 1

Seq Scan on point_arret_parcours pap (cost=0.00..545,661.22 rows=22,697,822 width=12) (actual time=0.263..5,061.294 rows=22,677,119 loops=1)

51. 1,318.874 2,676.131 ↑ 1.0 5,133,234 1

Hash (cost=142,175.81..142,175.81 rows=5,136,281 width=20) (actual time=2,676.131..2,676.131 rows=5,133,234 loops=1)

  • Buckets: 262,144 Batches: 64 Memory Usage: 6,425kB
52. 1,357.257 1,357.257 ↑ 1.0 5,133,234 1

Seq Scan on point_arret pa (cost=0.00..142,175.81 rows=5,136,281 width=20) (actual time=0.246..1,357.257 rows=5,133,234 loops=1)

53. 582.273 1,120.554 ↑ 1.0 2,746,702 1

Hash (cost=54,983.41..54,983.41 rows=2,748,341 width=12) (actual time=1,120.554..1,120.554 rows=2,746,702 loops=1)

  • Buckets: 262,144 Batches: 32 Memory Usage: 5,914kB
54. 538.281 538.281 ↑ 1.0 2,746,702 1

Seq Scan on arret a (cost=0.00..54,983.41 rows=2,748,341 width=12) (actual time=0.251..538.281 rows=2,746,702 loops=1)

55. 64.087 127.956 ↑ 1.0 267,498 1

Hash (cost=5,510.48..5,510.48 rows=267,648 width=23) (actual time=127.956..127.956 rows=267,498 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 5,765kB
56. 63.869 63.869 ↑ 1.0 267,498 1

Seq Scan on vehicule v (cost=0.00..5,510.48 rows=267,648 width=23) (actual time=0.254..63.869 rows=267,498 loops=1)

Planning time : 11.695 ms
Execution time : 185,012.060 ms