explain.depesz.com

PostgreSQL's explain analyze made readable

Result: REl3

Settings
# exclusive inclusive rows x rows loops node
1. 540.047 22,312.740 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=166,900.13..889,063.38 rows=1 width=79) (actual time=111.982..22,312.740 rows=556,472 loops=1)

2. 112.745 21,216.221 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=166,899.71..889,056.01 rows=1 width=82) (actual time=111.975..21,216.221 rows=556,472 loops=1)

3. 496.261 19,434.060 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=166,899.43..889,048.81 rows=1 width=84) (actual time=111.955..19,434.060 rows=556,472 loops=1)

  • Join Filter: (p.id_sous_ligne = sl.id_sous_ligne)
4. 131.595 10,034.247 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=166,899.43..889,046.87 rows=1 width=99) (actual time=111.921..10,034.247 rows=556,472 loops=1)

5. 140.671 8,789.708 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=166,899.00..889,039.45 rows=1 width=99) (actual time=111.915..8,789.708 rows=556,472 loops=1)

6. 0.000 7,536.093 ↓ 556,472.0 556,472 1

Gather (cost=166,898.56..889,032.07 rows=1 width=84) (actual time=111.907..7,536.093 rows=556,472 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 611.034 13,911.888 ↓ 185,491.0 185,491 3 / 3

Nested Loop (cost=165,898.56..888,031.97 rows=1 width=84) (actual time=105.998..13,911.888 rows=185,491 loops=3)

8. 768.258 11,090.398 ↓ 49,121.3 736,819 3 / 3

Nested Loop (cost=165,898.14..887,921.13 rows=15 width=87) (actual time=104.984..11,090.398 rows=736,819 loops=3)

9. 332.188 8,111.684 ↓ 1,244.6 736,819 3 / 3

Parallel Hash Left Join (cost=165,897.71..883,545.41 rows=592 width=50) (actual time=104.945..8,111.684 rows=736,819 loops=3)

  • Hash Cond: (vc.id_service_voiture = sv.id_service_voiture)
10. 545.306 7,776.777 ↓ 1,244.6 736,819 3 / 3

Nested Loop Left Join (cost=163,089.54..880,735.68 rows=592 width=53) (actual time=102.052..7,776.777 rows=736,819 loops=3)

11. 535.443 5,021.015 ↓ 1,244.6 736,819 3 / 3

Nested Loop Left Join (cost=163,089.12..876,374.50 rows=592 width=53) (actual time=102.014..5,021.015 rows=736,819 loops=3)

12. 346.106 2,281.273 ↓ 1,241.2 734,766 3 / 3

Hash Left Join (cost=163,088.55..871,854.81 rows=592 width=37) (actual time=101.966..2,281.273 rows=734,766 loops=3)

  • Hash Cond: ((t_theo.date_travail = c.date_calendrier) AND (t_theo.id_ce_travail = c.id_ce_calendrier))
13. 123.741 1,935.079 ↓ 1,241.2 734,766 3 / 3

Nested Loop (cost=163,062.29..871,825.45 rows=592 width=36) (actual time=101.804..1,935.079 rows=734,766 loops=3)

14. 46.650 201.338 ↓ 31.6 57,500 3 / 3

Parallel Hash Join (cost=163,061.72..390,006.58 rows=1,822 width=36) (actual time=101.697..201.338 rows=57,500 loops=3)

  • Hash Cond: (t_theo.id_real_parcours = rp_theo.id_real_parcours)
15. 62.715 66.428 ↑ 1.0 64,232 3 / 3

Parallel Bitmap Heap Scan on travail t_theo (cost=4,322.90..231,098.31 rows=64,548 width=37) (actual time=12.997..66.428 rows=64,232 loops=3)

  • 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=1,440
16. 3.713 3.713 ↑ 1.0 192,696 1 / 3

Bitmap Index Scan on travail_date_ce_index (cost=0.00..4,284.17 rows=193,173 width=0) (actual time=11.139..11.139 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)))
17. 21.031 88.260 ↓ 1.0 57,158 3 / 3

Parallel Hash (cost=158,052.99..158,052.99 rows=54,867 width=14) (actual time=88.260..88.260 rows=57,158 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 10,112kB
18. 63.198 67.229 ↓ 1.0 57,158 3 / 3

Parallel Bitmap Heap Scan on real_parcours rp_theo (cost=3,783.11..158,052.99 rows=54,867 width=14) (actual time=14.524..67.229 rows=57,158 loops=3)

  • 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=5,410
19. 4.031 4.031 ↓ 1.0 171,474 1 / 3

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

Index Scan using desserte_trav_dep_rang_index on desserte d_theo (cost=0.57..264.42 rows=3 width=22) (actual time=0.008..0.028 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
21. 0.021 0.088 ↓ 1.4 56 3 / 3

Hash (cost=25.67..25.67 rows=39 width=8) (actual time=0.088..0.088 rows=56 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
22. 0.067 0.067 ↓ 1.4 56 3 / 3

Index Only Scan using pk_calendrier on calendrier c (cost=0.28..25.67 rows=39 width=8) (actual time=0.047..0.067 rows=56 loops=3)

  • 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)))
  • 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 Fetches: 30
23. 2,204.299 2,204.299 ↓ 0.0 0 2,204,299 / 3

Index Scan using dessrt_id_desserte_theo_index on desserte d_real (cost=0.57..7.62 rows=1 width=24) (actual time=0.003..0.003 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)))
24. 2,210.456 2,210.456 ↑ 1.0 1 2,210,456 / 3

Index Scan using pk_voiture_continue on voiture_continue vc (cost=0.42..7.37 rows=1 width=14) (actual time=0.003..0.003 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)))
25. 0.892 2.719 ↑ 1.4 4,209 3 / 3

Parallel Hash (cost=2,735.22..2,735.22 rows=5,836 width=11) (actual time=2.719..2.719 rows=4,209 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 768kB
26. 1.618 1.827 ↑ 1.4 4,209 3 / 3

Parallel Bitmap Heap Scan on service_voiture sv (cost=289.62..2,735.22 rows=5,836 width=11) (actual time=0.237..1.827 rows=4,209 loops=3)

  • Recheck Cond: ((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)))
  • Filter: ((id_service_voiture IS NOT NULL) 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)))
  • Heap Blocks: exact=114
27. 0.209 0.209 ↑ 1.0 12,627 1 / 3

Bitmap Index Scan on service_voiture_date_ce_index (cost=0.00..287.14 rows=12,671 width=0) (actual time=0.627..0.627 rows=12,627 loops=1)

  • Index Cond: ((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)))
28. 2,210.456 2,210.456 ↑ 1.0 1 2,210,456 / 3

Index Scan using pk_parcours on parcours p (cost=0.43..7.39 rows=1 width=49) (actual time=0.003..0.003 rows=1 loops=2,210,456)

  • Index Cond: ((id_parcours = rp_theo.id_parcours) AND (id_parcours IS NOT NULL))
  • Filter: ((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)))
29. 2,210.456 2,210.456 ↓ 0.0 0 2,210,456 / 3

Index Scan using pk_ligne on ligne l (cost=0.42..7.38 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=2,210,456)

  • Index Cond: ((id_ligne = p.id_ligne) AND (id_ligne IS NOT NULL))
  • Filter: ((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)) 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: 1
30. 1,112.944 1,112.944 ↓ 0.0 0 556,472

Index Scan using pk_travail on travail t_real (cost=0.43..7.38 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)))
31. 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.42 rows=1 width=8) (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)))
32. 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
33. 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)))
34. 556.472 556.472 ↓ 0.0 0 556,472

Index Scan using pk_vehicule on vehicule v (cost=0.42..7.37 rows=1 width=11) (actual time=0.001..0.001 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)))
Planning time : 9.313 ms
Execution time : 22,403.545 ms