explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zYIP

Settings
# exclusive inclusive rows x rows loops node
1. 320.064 133,723.930 ↓ 1.3 2,210,456 1

Gather (cost=5,163,926.70..9,776,624.39 rows=1,740,528 width=71) (actual time=131,525.543..133,723.930 rows=2,210,456 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 296.183 133,403.866 ↓ 1.0 736,819 3 / 3

Hash Left Join (cost=5,162,926.70..9,601,571.59 rows=725,220 width=71) (actual time=131,523.962..133,403.866 rows=736,819 loops=3)

  • Hash Cond: (p.id_sens = s.id_sens)
3. 156.570 133,107.568 ↓ 1.0 736,819 3 / 3

Hash Left Join (cost=5,162,881.24..9,599,621.12 rows=725,220 width=73) (actual time=131,523.841..133,107.568 rows=736,819 loops=3)

  • Hash Cond: (p.id_sous_ligne = sl.id_sous_ligne)
4. 310.456 132,950.959 ↓ 1.0 736,819 3 / 3

Parallel Hash Left Join (cost=5,162,879.30..9,597,715.47 rows=725,220 width=88) (actual time=131,523.794..132,950.959 rows=736,819 loops=3)

  • Hash Cond: (p.id_ligne = l.id_ligne)
5. 276.839 132,638.932 ↓ 1.0 736,819 3 / 3

Parallel Hash Left Join (cost=5,158,880.81..9,591,813.26 rows=725,220 width=91) (actual time=131,522.026..132,638.932 rows=736,819 loops=3)

  • Hash Cond: (d_real.id_travail_depart = t_real.id_travail)
6. 493.940 132,171.233 ↓ 1.0 736,819 3 / 3

Parallel Hash Left Join (cost=4,768,067.38..9,198,192.88 rows=725,220 width=83) (actual time=131,330.799..132,171.233 rows=736,819 loops=3)

  • Hash Cond: (d_theo.id_travail_depart = t_theo.id_travail)
7. 587.111 131,406.890 ↓ 1.0 736,819 3 / 3

Parallel Hash Left Join (cost=4,334,477.02..8,761,795.58 rows=725,220 width=32) (actual time=131,060.086..131,406.890 rows=736,819 loops=3)

  • Hash Cond: (d_theo.id_desserte = d_real.id_desserte_theo)
8. 65,611.451 65,611.451 ↓ 1.0 734,766 3 / 3

Parallel Seq Scan on desserte d_theo (cost=0.00..4,409,474.82 rows=725,220 width=22) (actual time=1,025.377..65,611.451 rows=734,766 loops=3)

  • 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: 28,977,605
9. 138.254 65,208.328 ↓ 1.0 1,000,863 3 / 3

Parallel Hash (cost=4,316,621.60..4,316,621.60 rows=972,514 width=24) (actual time=65,208.327..65,208.328 rows=1,000,863 loops=3)

  • Buckets: 262,144 Batches: 32 Memory Usage: 3,552kB
10. 65,070.074 65,070.074 ↓ 1.0 1,000,863 3 / 3

Parallel Seq Scan on desserte d_real (cost=0.00..4,316,621.60 rows=972,514 width=24) (actual time=7,141.958..65,070.074 rows=1,000,863 loops=3)

  • 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)))
  • Rows Removed by Filter: 28,711,509
11. 28.318 270.403 ↑ 1.0 64,232 3 / 3

Parallel Hash (cost=432,783.51..432,783.51 rows=64,548 width=67) (actual time=270.403..270.403 rows=64,232 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 17,568kB
12. 28.788 242.085 ↑ 1.0 64,232 3 / 3

Parallel Hash Left Join (cost=205,330.05..432,783.51 rows=64,548 width=67) (actual time=112.755..242.085 rows=64,232 loops=3)

  • Hash Cond: (rp_theo.id_parcours = p.id_parcours)
13. 32.855 183.765 ↑ 1.0 64,232 3 / 3

Parallel Hash Left Join (cost=163,087.98..390,371.99 rows=64,548 width=30) (actual time=83.073..183.765 rows=64,232 loops=3)

  • Hash Cond: (t_theo.id_real_parcours = rp_theo.id_real_parcours)
14. 26.519 81.082 ↑ 1.0 64,232 3 / 3

Hash Left Join (cost=4,349.15..231,463.73 rows=64,548 width=24) (actual time=12.973..81.082 rows=64,232 loops=3)

  • Hash Cond: ((t_theo.date_travail = c.date_calendrier) AND (t_theo.id_ce_travail = c.id_ce_calendrier))
15. 50.779 54.467 ↑ 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=23) (actual time=12.810..54.467 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=4,645
16. 3.688 3.688 ↑ 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.063..11.063 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. 0.016 0.096 ↓ 1.4 56 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 0.080 0.080 ↓ 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.059..0.080 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
19. 16.792 69.828 ↓ 1.0 57,158 3 / 3

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 10,112kB
20. 49.192 53.036 ↓ 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.537..53.036 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,669
21. 3.844 3.844 ↓ 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=11.531..11.531 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)))
22. 9.576 29.532 ↓ 1.0 30,928 3 / 3

Parallel Hash (cost=41,871.14..41,871.14 rows=29,675 width=49) (actual time=29.532..29.532 rows=30,928 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,944kB
23. 18.166 19.956 ↓ 1.0 30,928 3 / 3

Parallel Bitmap Heap Scan on parcours p (cost=2,156.86..41,871.14 rows=29,675 width=49) (actual time=5.565..19.956 rows=30,928 loops=3)

  • Recheck Cond: ((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)))
  • Filter: ((id_parcours IS NOT NULL) AND ((id_ce_parcours = '-1'::numeric) OR (id_ce_parcours = '2'::numeric) OR (id_ce_parcours = '0'::numeric) OR (id_ce_parcours = '1'::numeric)))
  • Heap Blocks: exact=475
24. 1.790 1.790 ↑ 1.0 92,784 1 / 3

Bitmap Index Scan on parcours_date_ce_index (cost=0.00..2,139.05 rows=93,862 width=0) (actual time=5.371..5.371 rows=92,784 loops=1)

  • Index Cond: ((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)))
25. 23.209 190.860 ↑ 1.0 64,232 3 / 3

Parallel Hash (cost=390,006.58..390,006.58 rows=64,548 width=16) (actual time=190.860..190.860 rows=64,232 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 12,480kB
26. 37.110 167.651 ↑ 1.0 64,232 3 / 3

Parallel Hash Left Join (cost=163,061.72..390,006.58 rows=64,548 width=16) (actual time=85.318..167.651 rows=64,232 loops=3)

  • Hash Cond: (t_real.id_real_parcours = rp_real.id_real_parcours)
27. 56.197 60.182 ↑ 1.0 64,232 3 / 3

Parallel Bitmap Heap Scan on travail t_real (cost=4,322.90..231,098.31 rows=64,548 width=16) (actual time=14.626..60.182 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_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=5,280
28. 3.985 3.985 ↑ 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.956..11.956 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)))
29. 16.848 70.359 ↓ 1.0 57,158 3 / 3

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 10,112kB
30. 49.593 53.511 ↓ 1.0 57,158 3 / 3

Parallel Bitmap Heap Scan on real_parcours rp_real (cost=3,783.11..158,052.99 rows=54,867 width=8) (actual time=14.159..53.511 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_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=6,551
31. 3.918 3.918 ↓ 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=11.753..11.753 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)))
32. 0.483 1.571 ↓ 1.1 2,543 3 / 3

Parallel Hash (cost=3,968.54..3,968.54 rows=2,396 width=11) (actual time=1.571..1.571 rows=2,543 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 448kB
33. 0.967 1.088 ↓ 3.2 7,630 1 / 3

Parallel Bitmap Heap Scan on ligne l (cost=178.17..3,968.54 rows=2,396 width=11) (actual time=0.383..3.263 rows=7,630 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)))
  • Heap Blocks: exact=106
34. 0.121 0.121 ↑ 1.0 7,630 1 / 3

Bitmap Index Scan on ligne_date_ce_index (cost=0.00..176.73 rows=7,630 width=0) (actual time=0.363..0.363 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)))
35. 0.000 0.039 ↓ 0.0 0 3 / 3

Hash (cost=1.93..1.93 rows=1 width=12) (actual time=0.039..0.039 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
36. 0.039 0.039 ↓ 0.0 0 3 / 3

Seq Scan on sous_ligne sl (cost=0.00..1.93 rows=1 width=12) (actual time=0.039..0.039 rows=0 loops=3)

  • 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
37. 0.028 0.115 ↓ 1.4 126 3 / 3

Hash (cost=44.35..44.35 rows=89 width=12) (actual time=0.115..0.115 rows=126 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
38. 0.066 0.087 ↓ 1.4 126 3 / 3

Bitmap Heap Scan on sens s (cost=5.57..44.35 rows=89 width=12) (actual time=0.029..0.087 rows=126 loops=3)

  • Recheck Cond: ((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)))
  • Filter: ((id_sens IS NOT NULL) AND ((id_ce_sens = '-1'::numeric) OR (id_ce_sens = '2'::numeric) OR (id_ce_sens = '0'::numeric) OR (id_ce_sens = '1'::numeric)))
  • Heap Blocks: exact=2
39. 0.021 0.021 ↑ 1.0 126 3 / 3

Bitmap Index Scan on sens_date_ce_index (cost=0.00..5.55 rows=126 width=0) (actual time=0.021..0.021 rows=126 loops=3)

  • Index Cond: ((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)))
Planning time : 6.130 ms
Execution time : 133,821.136 ms