explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EPA9

Settings
# exclusive inclusive rows x rows loops node
1. 1,530.009 1,254,283.567 ↓ 0.0 0 1

Insert on desserte_tmp (cost=168,123.16..1,621,224.19 rows=1 width=623) (actual time=1,254,283.567..1,254,283.567 rows=0 loops=1)

2. 664.702 1,252,753.558 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,123.16..1,621,224.19 rows=1 width=623) (actual time=212.352..1,252,753.558 rows=556,472 loops=1)

3. 377.986 1,252,088.856 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,122.73..1,621,209.74 rows=1 width=469) (actual time=212.347..1,252,088.856 rows=556,472 loops=1)

4. 523.178 1,248,928.510 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,122.30..1,621,202.27 rows=1 width=469) (actual time=212.061..1,248,928.510 rows=556,472 loops=1)

5. 652.478 1,245,622.972 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,121.86..1,621,194.80 rows=1 width=461) (actual time=212.047..1,245,622.972 rows=556,472 loops=1)

6. 420.191 1,242,744.606 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,121.43..1,621,187.32 rows=1 width=461) (actual time=212.035..1,242,744.606 rows=556,472 loops=1)

7. 700.572 1,241,211.471 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,121.00..1,621,179.88 rows=1 width=452) (actual time=212.032..1,241,211.471 rows=556,472 loops=1)

8. 699.453 1,238,841.483 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,120.72..1,621,172.68 rows=1 width=454) (actual time=212.021..1,238,841.483 rows=556,472 loops=1)

  • Join Filter: (p.id_sous_ligne = sl.id_sous_ligne)
9. 621.369 1,229,238.478 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,120.72..1,621,170.74 rows=1 width=469) (actual time=212.000..1,229,238.478 rows=556,472 loops=1)

10. 591.092 1,227,504.165 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,120.29..1,621,163.25 rows=1 width=451) (actual time=211.996..1,227,504.165 rows=556,472 loops=1)

11. 679.924 1,225,800.129 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=168,119.86..1,621,155.79 rows=1 width=436) (actual time=211.992..1,225,800.129 rows=556,472 loops=1)

12. 862,069.364 1,222,894.317 ↓ 556,472.0 556,472 1

Nested Loop (cost=168,119.43..1,621,148.33 rows=1 width=438) (actual time=211.828..1,222,894.317 rows=556,472 loops=1)

  • Join Filter: (p.id_parcours = rp_theo.id_parcours)
  • Rows Removed by Join Filter: 6,701,546,120
13. 1,029.719 51,361.113 ↓ 1,555.6 2,210,456 1

Nested Loop Left Join (cost=167,942.24..1,612,019.15 rows=1,421 width=396) (actual time=188.041..51,361.113 rows=2,210,456 loops=1)

14. 2,606.582 41,489.570 ↓ 1,555.6 2,210,456 1

Nested Loop Left Join (cost=167,941.81..1,601,425.93 rows=1,421 width=396) (actual time=187.744..41,489.570 rows=2,210,456 loops=1)

15. 1,028.714 32,251.620 ↓ 1,555.6 2,210,456 1

Nested Loop Left Join (cost=167,941.39..1,590,828.81 rows=1,421 width=399) (actual time=187.570..32,251.620 rows=2,210,456 loops=1)

16. 2,517.500 22,381.082 ↓ 1,555.6 2,210,456 1

Nested Loop Left Join (cost=167,940.97..1,580,246.11 rows=1,421 width=394) (actual time=187.385..22,381.082 rows=2,210,456 loops=1)

17. 1,670.092 11,046.386 ↓ 1,551.2 2,204,299 1

Hash Left Join (cost=167,940.40..1,569,308.12 rows=1,421 width=122) (actual time=186.917..11,046.386 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))
18. 1,258.522 9,376.227 ↓ 1,551.2 2,204,299 1

Nested Loop (cost=167,859.53..1,569,219.78 rows=1,421 width=82) (actual time=186.840..9,376.227 rows=2,204,299 loops=1)

19. 291.760 700.205 ↓ 39.4 172,500 1

Hash Join (cost=167,858.96..401,744.55 rows=4,373 width=58) (actual time=185.379..700.205 rows=172,500 loops=1)

  • Hash Cond: (t_theo.id_real_parcours = rp_theo.id_real_parcours)
20. 224.551 234.563 ↓ 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=11.427..234.563 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
21. 10.012 10.012 ↑ 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.012..10.012 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)))
22. 44.373 173.882 ↓ 1.3 171,474 1

Hash (cost=160,989.05..160,989.05 rows=131,681 width=29) (actual time=173.881..173.882 rows=171,474 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 6,207kB
23. 118.191 129.509 ↓ 1.3 171,474 1

Bitmap Heap Scan on real_parcours rp_theo (cost=3,783.11..160,989.05 rows=131,681 width=29) (actual time=13.562..129.509 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
24. 11.318 11.318 ↓ 1.0 171,474 1

Bitmap Index Scan on real_parcours_date_ce_index (cost=0.00..3,750.19 rows=167,775 width=0) (actual time=11.318..11.318 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)))
25. 7,417.500 7,417.500 ↓ 4.3 13 172,500

Index Scan using desserte_trav_dep_rang_index on desserte d_theo (cost=0.57..266.94 rows=3 width=40) (actual time=0.018..0.043 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
26. 0.015 0.067 ↓ 1.4 56 1

Hash (cost=80.28..80.28 rows=39 width=47) (actual time=0.067..0.067 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
27. 0.041 0.052 ↓ 1.4 56 1

Bitmap Heap Scan on calendrier c (cost=4.86..80.28 rows=39 width=47) (actual time=0.017..0.052 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
28. 0.011 0.011 ↑ 1.0 56 1

Bitmap Index Scan on pk_calendrier (cost=0.00..4.86 rows=57 width=0) (actual time=0.011..0.011 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)))
29. 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=280) (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)))
30. 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)))
31. 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)))
32. 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.45 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)))
33. 309,443.538 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)

34. 0.704 20.302 ↓ 72.2 3,032 1

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

35. 3.254 3.638 ↓ 1.4 210 1

Bitmap Heap Scan on ligne l (cost=176.77..4,215.12 rows=155 width=17) (actual time=0.399..3.638 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
36. 0.384 0.384 ↑ 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.384..0.384 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)))
37. 15.960 15.960 ↓ 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.076 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)))
38. 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)))
39. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_travail on travail t_real (cost=0.43..7.46 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)))
40. 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.49 rows=1 width=26) (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)))
41. 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
42. 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)))
43. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_vehicule on vehicule v (cost=0.42..7.44 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)))
44. 2,225.888 2,225.888 ↑ 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.004..0.004 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)))
45. 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)))
46. 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)))
47. 0.000 0.000 ↓ 0.0 0 556,472

Index Scan using pk_point_arret on point_arret pavar (cost=0.43..7.44 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=556,472)

  • Index Cond: (d_real.id_point_arret_variante = id_point_arret)
Planning time : 12.244 ms
Execution time : 1,254,283.946 ms