explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EEDv

Settings
# exclusive inclusive rows x rows loops node
1. 1,612.887 1,272,205.602 ↓ 0.0 0 1

Insert on desserte_tmp (cost=218,303.77..1,706,391.04 rows=1 width=612) (actual time=1,272,205.602..1,272,205.602 rows=0 loops=1)

2. 560.936 1,270,592.715 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,303.77..1,706,391.04 rows=1 width=612) (actual time=214.381..1,270,592.715 rows=556,472 loops=1)

3. 575.399 1,267,249.419 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,303.34..1,706,383.56 rows=1 width=438) (actual time=214.090..1,267,249.419 rows=556,472 loops=1)

4. 502.171 1,263,891.660 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,302.90..1,706,376.09 rows=1 width=430) (actual time=213.787..1,263,891.660 rows=556,472 loops=1)

5. 449.315 1,260,607.129 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,302.47..1,706,368.60 rows=1 width=430) (actual time=213.493..1,260,607.129 rows=556,472 loops=1)

6. 702.889 1,259,044.870 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,302.04..1,706,361.15 rows=1 width=421) (actual time=213.489..1,259,044.870 rows=556,472 loops=1)

7. 796.025 1,256,672.565 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,301.76..1,706,353.95 rows=1 width=423) (actual time=213.478..1,256,672.565 rows=556,472 loops=1)

  • Join Filter: (p.id_sous_ligne = sl.id_sous_ligne)
8. 736.720 1,246,972.988 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,301.76..1,706,352.01 rows=1 width=438) (actual time=213.452..1,246,972.988 rows=556,472 loops=1)

9. 785.900 1,245,123.324 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,301.33..1,706,344.50 rows=1 width=414) (actual time=213.448..1,245,123.324 rows=556,472 loops=1)

10. 709.874 1,243,224.480 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=218,300.90..1,706,337.03 rows=1 width=399) (actual time=213.442..1,243,224.480 rows=556,472 loops=1)

11. 880,011.479 1,240,288.718 ↓ 556,472.0 556,472 1

Nested Loop (cost=218,300.47..1,706,329.57 rows=1 width=401) (actual time=213.284..1,240,288.718 rows=556,472 loops=1)

  • Join Filter: (p.id_parcours = rp_theo.id_parcours)
  • Rows Removed by Join Filter: 6,701,546,120
12. 1,107.686 50,813.399 ↓ 1,480.5 2,210,456 1

Nested Loop Left Join (cost=218,123.28..1,697,155.03 rows=1,493 width=359) (actual time=188.742..50,813.399 rows=2,210,456 loops=1)

13. 2,640.824 40,863.889 ↓ 1,480.5 2,210,456 1

Nested Loop Left Join (cost=218,122.85..1,686,016.85 rows=1,493 width=359) (actual time=188.213..40,863.889 rows=2,210,456 loops=1)

14. 939.875 31,591.697 ↓ 1,480.5 2,210,456 1

Nested Loop Left Join (cost=218,122.43..1,674,874.28 rows=1,493 width=362) (actual time=188.034..31,591.697 rows=2,210,456 loops=1)

15. 2,339.905 21,809.998 ↓ 1,480.5 2,210,456 1

Nested Loop Left Join (cost=218,122.01..1,663,745.12 rows=1,493 width=357) (actual time=187.843..21,809.998 rows=2,210,456 loops=1)

16. 1,662.350 10,652.897 ↓ 1,476.4 2,204,299 1

Hash Left Join (cost=218,121.44..1,652,247.33 rows=1,493 width=122) (actual time=187.502..10,652.897 rows=2,204,299 loops=1)

  • Hash Cond: ((t_theo.date_travail = c.date_calendrier) AND (t_theo.id_ce_travail = c.id_ce_calendrier))
17. 1,221.041 8,990.471 ↓ 1,476.4 2,204,299 1

Nested Loop (cost=218,026.23..1,652,144.27 rows=1,493 width=82) (actual time=187.414..8,990.471 rows=2,204,299 loops=1)

18. 288.856 696.930 ↓ 38.4 172,500 1

Hash Join (cost=218,025.66..451,935.26 rows=4,493 width=58) (actual time=186.492..696.930 rows=172,500 loops=1)

  • Hash Cond: (t_theo.id_real_parcours = rp_theo.id_real_parcours)
19. 223.060 233.702 ↓ 1.2 192,696 1

Bitmap Heap Scan on travail t_theo (cost=4,322.90..234,478.84 rows=154,914 width=37) (actual time=12.046..233.702 rows=192,696 loops=1)

  • Recheck Cond: ((date_travail >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_travail <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
  • Filter: ((id_travail IS NOT NULL) AND ((id_ce_travail = '-1'::numeric) OR (id_ce_travail = '2'::numeric) OR (id_ce_travail = '0'::numeric) OR (id_ce_travail = '1'::numeric)))
  • Heap Blocks: exact=12,912
20. 10.642 10.642 ↑ 1.0 192,696 1

Bitmap Index Scan on travail_date_ce_index (cost=0.00..4,284.17 rows=193,173 width=0) (actual time=10.642..10.642 rows=192,696 loops=1)

  • Index Cond: ((date_travail >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_travail <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
21. 45.117 174.372 ↓ 1.3 171,474 1

Hash (cost=211,086.73..211,086.73 rows=135,283 width=29) (actual time=174.372..174.372 rows=171,474 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 6,207kB
22. 118.666 129.255 ↓ 1.3 171,474 1

Bitmap Heap Scan on real_parcours rp_theo (cost=4,641.72..211,086.73 rows=135,283 width=29) (actual time=12.566..129.255 rows=171,474 loops=1)

  • Recheck Cond: ((date_real_parcours >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_real_parcours <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
  • Filter: ((id_real_parcours IS NOT NULL) AND ((id_ce_real_parcours = '-1'::numeric) OR (id_ce_real_parcours = '2'::numeric) OR (id_ce_real_parcours = '0'::numeric) OR (id_ce_real_parcours = '1'::numeric)))
  • Heap Blocks: exact=15,454
23. 10.589 10.589 ↑ 1.0 171,474 1

Bitmap Index Scan on real_parcours_date_ce_index (cost=0.00..4,607.90 rows=172,346 width=0) (actual time=10.589..10.589 rows=171,474 loops=1)

  • Index Cond: ((date_real_parcours >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_real_parcours <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
24. 7,072.500 7,072.500 ↓ 4.3 13 172,500

Index Scan using desserte_trav_dep_rang_index on desserte d_theo (cost=0.57..267.10 rows=3 width=40) (actual time=0.016..0.041 rows=13 loops=172,500)

  • Index Cond: (id_travail_depart = t_theo.id_travail)
  • Filter: ((id_origine_realisation = '1'::numeric) AND (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)) 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: 5
25. 0.018 0.076 ↓ 1.4 56 1

Hash (cost=94.62..94.62 rows=39 width=47) (actual time=0.076..0.076 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
26. 0.046 0.058 ↓ 1.4 56 1

Bitmap Heap Scan on calendrier c (cost=4.86..94.62 rows=39 width=47) (actual time=0.019..0.058 rows=56 loops=1)

  • Recheck Cond: ((date_calendrier >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_calendrier <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
  • Filter: ((id_ce_calendrier = '-1'::numeric) OR (id_ce_calendrier = '2'::numeric) OR (id_ce_calendrier = '0'::numeric) OR (id_ce_calendrier = '1'::numeric))
  • Heap Blocks: exact=11
27. 0.012 0.012 ↑ 1.0 56 1

Bitmap Index Scan on pk_calendrier (cost=0.00..4.86 rows=57 width=0) (actual time=0.012..0.012 rows=56 loops=1)

  • Index Cond: ((date_calendrier >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_calendrier <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
28. 8,817.196 8,817.196 ↓ 0.0 0 2,204,299

Index Scan using dessrt_id_desserte_theo_index on desserte d_real (cost=0.57..7.69 rows=1 width=243) (actual time=0.004..0.004 rows=0 loops=2,204,299)

  • Index Cond: (id_desserte_theo = d_theo.id_desserte)
  • Filter: ((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)) AND ((id_ce_desserte = '-1'::numeric) OR (id_ce_desserte = '2'::numeric) OR (id_ce_desserte = '0'::numeric) OR (id_ce_desserte = '1'::numeric)))
29. 8,841.824 8,841.824 ↑ 1.0 1 2,210,456

Index Scan using pk_voiture_continue on voiture_continue vc (cost=0.42..7.45 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=2,210,456)

  • Index Cond: ((t_theo.id_voiture_continue = id_voiture_continue) AND (id_voiture_continue IS NOT NULL))
  • Filter: ((date_voiture_continue >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_voiture_continue <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_voiture_continue = '-1'::numeric) OR (id_ce_voiture_continue = '2'::numeric) OR (id_ce_voiture_continue = '0'::numeric) OR (id_ce_voiture_continue = '1'::numeric)))
30. 6,631.368 6,631.368 ↑ 1.0 1 2,210,456

Index Scan using pk_service_voiture on service_voiture sv (cost=0.42..7.46 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=2,210,456)

  • Index Cond: ((vc.id_service_voiture = id_service_voiture) AND (id_service_voiture IS NOT NULL))
  • Filter: ((date_service_voiture >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_service_voiture <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_service_voiture = '-1'::numeric) OR (id_ce_service_voiture = '2'::numeric) OR (id_ce_service_voiture = '0'::numeric) OR (id_ce_service_voiture = '1'::numeric)))
31. 8,841.824 8,841.824 ↑ 1.0 1 2,210,456

Index Scan using pk_vacation_agent on vacation_agent va (cost=0.42..7.46 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=2,210,456)

  • Index Cond: ((t_theo.id_vacation_agent = id_vacation_agent) AND (id_vacation_agent IS NOT NULL))
  • Filter: ((date_vacation_agent >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_vacation_agent <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_vacation_agent = '-1'::numeric) OR (id_ce_vacation_agent = '2'::numeric) OR (id_ce_vacation_agent = '0'::numeric) OR (id_ce_vacation_agent = '1'::numeric)))
32. 309,442.975 309,463.840 ↓ 72.2 3,032 2,210,456

Materialize (cost=177.20..8,234.06 rows=42 width=54) (actual time=0.000..0.140 rows=3,032 loops=2,210,456)

33. 0.750 20.865 ↓ 72.2 3,032 1

Nested Loop (cost=177.20..8,233.85 rows=42 width=54) (actual time=0.751..20.865 rows=3,032 loops=1)

34. 3.374 3.735 ↓ 1.4 210 1

Bitmap Heap Scan on ligne l (cost=176.77..4,215.12 rows=155 width=17) (actual time=0.376..3.735 rows=210 loops=1)

  • Recheck Cond: ((date_ligne >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_ligne <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
  • Filter: ((id_ligne IS NOT NULL) AND ((id_ce_ligne = '-1'::numeric) OR (id_ce_ligne = '2'::numeric) OR (id_ce_ligne = '0'::numeric) OR (id_ce_ligne = '1'::numeric)) AND (((mnemo_ligne)::text = '1'::text) OR ((mnemo_ligne)::text = '2'::text) OR ((mnemo_ligne)::text = '3'::text) OR ((mnemo_ligne)::text = '5'::text) OR ((mnemo_ligne)::text = '6'::text) OR ((mnemo_ligne)::text = '7'::text)))
  • Rows Removed by Filter: 7,420
  • Heap Blocks: exact=106
35. 0.361 0.361 ↑ 1.0 7,630 1

Bitmap Index Scan on ligne_date_ce_index (cost=0.00..176.73 rows=7,630 width=0) (actual time=0.361..0.361 rows=7,630 loops=1)

  • Index Cond: ((date_ligne >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_ligne <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)))
36. 16.380 16.380 ↓ 14.0 14 210

Index Scan using parcours_id_ligne_sens_index on parcours p (cost=0.43..25.92 rows=1 width=51) (actual time=0.050..0.078 rows=14 loops=210)

  • Index Cond: (id_ligne = l.id_ligne)
  • Filter: ((id_parcours IS NOT NULL) AND (date_parcours >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_parcours <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_parcours = '-1'::numeric) OR (id_ce_parcours = '2'::numeric) OR (id_ce_parcours = '0'::numeric) OR (id_ce_parcours = '1'::numeric)))
37. 2,225.888 2,225.888 ↑ 1.0 1 556,472

Index Scan using pk_service_agent on service_agent sa (cost=0.42..7.46 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=556,472)

  • Index Cond: ((va.id_service_agent = id_service_agent) AND (id_service_agent IS NOT NULL))
  • Filter: ((date_service_agent >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_service_agent <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_service_agent = '-1'::numeric) OR (id_ce_service_agent = '2'::numeric) OR (id_ce_service_agent = '0'::numeric) OR (id_ce_service_agent = '1'::numeric)))
38. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_travail on travail t_real (cost=0.43..7.47 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=556,472)

  • Index Cond: (d_real.id_travail_depart = id_travail)
  • Filter: ((date_travail >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_travail <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_travail = '-1'::numeric) OR (id_ce_travail = '2'::numeric) OR (id_ce_travail = '0'::numeric) OR (id_ce_travail = '1'::numeric)))
39. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_real_parcours on real_parcours rp_real (cost=0.43..7.51 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=556,472)

  • Index Cond: (id_real_parcours = t_real.id_real_parcours)
  • Filter: ((date_real_parcours >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_real_parcours <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_real_parcours = '-1'::numeric) OR (id_ce_real_parcours = '2'::numeric) OR (id_ce_real_parcours = '0'::numeric) OR (id_ce_real_parcours = '1'::numeric)))
40. 8,903.552 8,903.552 ↓ 0.0 0 556,472

Seq Scan on sous_ligne sl (cost=0.00..1.93 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=556,472)

  • Filter: ((date_sous_ligne >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_sous_ligne <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_sous_ligne = '-1'::numeric) OR (id_ce_sous_ligne = '2'::numeric) OR (id_ce_sous_ligne = '0'::numeric) OR (id_ce_sous_ligne = '1'::numeric)))
  • Rows Removed by Filter: 31
41. 1,669.416 1,669.416 ↑ 1.0 1 556,472

Index Scan using pk_sens on sens s (cost=0.28..7.05 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=556,472)

  • Index Cond: ((p.id_sens = id_sens) AND (id_sens IS NOT NULL))
  • Filter: ((date_sens >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_sens <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_sens = '-1'::numeric) OR (id_ce_sens = '2'::numeric) OR (id_ce_sens = '0'::numeric) OR (id_ce_sens = '1'::numeric)))
42. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_vehicule on vehicule v (cost=0.42..7.45 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=556,472)

  • Index Cond: (t_real.id_vehicule = id_vehicule)
  • Filter: ((date_vehicule >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_vehicule <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_vehicule = '-1'::numeric) OR (id_ce_vehicule = '2'::numeric) OR (id_ce_vehicule = '0'::numeric) OR (id_ce_vehicule = '1'::numeric)))
43. 2,782.360 2,782.360 ↑ 1.0 1 556,472

Index Scan using pk_point_arret_parcours on point_arret_parcours pap (cost=0.44..7.48 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=556,472)

  • Index Cond: ((d_theo.id_point_arret_parcours = id_point_arret_parcours) AND (id_point_arret_parcours IS NOT NULL))
  • Filter: ((date_point_arret_parcours >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_point_arret_parcours <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_point_arret_parcours = '-1'::numeric) OR (id_ce_point_arret_parcours = '2'::numeric) OR (id_ce_point_arret_parcours = '0'::numeric) OR (id_ce_point_arret_parcours = '1'::numeric)))
44. 2,782.360 2,782.360 ↑ 1.0 1 556,472

Index Scan using pk_point_arret on point_arret pa (cost=0.43..7.47 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=556,472)

  • Index Cond: ((pap.id_point_arret = id_point_arret) AND (id_point_arret IS NOT NULL))
  • Filter: ((date_point_arret >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_point_arret <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_point_arret = '-1'::numeric) OR (id_ce_point_arret = '2'::numeric) OR (id_ce_point_arret = '0'::numeric) OR (id_ce_point_arret = '1'::numeric)))
45. 2,782.360 2,782.360 ↑ 1.0 1 556,472

Index Scan using pk_arret on arret a (cost=0.43..7.47 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=556,472)

  • Index Cond: ((pa.id_arret = id_arret) AND (id_arret IS NOT NULL))
  • Filter: ((date_arret >= to_date('09/09/2019'::text, 'dd/mm/yyyy'::text)) AND (date_arret <= to_date('22/09/2019'::text, 'dd/mm/yyyy'::text)) AND ((id_ce_arret = '-1'::numeric) OR (id_ce_arret = '2'::numeric) OR (id_ce_arret = '0'::numeric) OR (id_ce_arret = '1'::numeric)))
Planning time : 13.520 ms
Execution time : 1,272,205.965 ms