explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JxZ4

Settings
# exclusive inclusive rows x rows loops node
1. 115.693 20,888.064 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=47,054.57..224,635.71 rows=1 width=71) (actual time=53.172..20,888.064 rows=556,472 loops=1)

2. 311.201 19,102.955 ↓ 556,472.0 556,472 1

Nested Loop Left Join (cost=47,054.29..224,628.52 rows=1 width=73) (actual time=53.159..19,102.955 rows=556,472 loops=1)

  • Join Filter: (p.id_sous_ligne = sl.id_sous_ligne)
3. 1,381.762 9,888.202 ↓ 556,472.0 556,472 1

Nested Loop (cost=47,054.29..224,626.57 rows=1 width=88) (actual time=53.134..9,888.202 rows=556,472 loops=1)

4. 1,186.529 1,875.072 ↓ 61,401.6 2,210,456 1

Hash Left Join (cost=47,053.87..224,360.73 rows=36 width=91) (actual time=46.822..1,875.072 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))
5. 0.000 688.493 ↓ 61,401.6 2,210,456 1

Gather (cost=47,027.61..224,334.28 rows=36 width=90) (actual time=46.759..688.493 rows=2,210,456 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 633.194 7,724.015 ↓ 49,121.3 736,819 3 / 3

Nested Loop Left Join (cost=46,027.61..223,330.68 rows=15 width=90) (actual time=39.809..7,724.015 rows=736,819 loops=3)

7. 625.174 6,354.002 ↓ 49,121.3 736,819 3 / 3

Nested Loop Left Join (cost=46,027.18..223,219.40 rows=15 width=90) (actual time=39.800..6,354.002 rows=736,819 loops=3)

8. 531.050 4,992.009 ↓ 49,121.3 736,819 3 / 3

Nested Loop Left Join (cost=46,026.75..223,108.66 rows=15 width=82) (actual time=39.793..4,992.009 rows=736,819 loops=3)

9. 176.680 2,256.660 ↓ 48,984.4 734,766 3 / 3

Nested Loop (cost=46,026.18..222,994.16 rows=15 width=66) (actual time=39.763..2,256.660 rows=734,766 loops=3)

10. 57.962 469.980 ↓ 1,250.0 57,500 3 / 3

Nested Loop (cost=46,025.61..210,832.17 rows=46 width=66) (actual time=39.729..469.980 rows=57,500 loops=3)

11. 40.502 126.228 ↓ 41.0 57,158 3 / 3

Parallel Hash Join (cost=46,025.18..200,439.09 rows=1,393 width=51) (actual time=39.663..126.228 rows=57,158 loops=3)

  • Hash Cond: (rp_theo.id_parcours = p.id_parcours)
12. 55.881 59.378 ↓ 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=12.895..59.378 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=66
13. 3.497 3.497 ↓ 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=10.492..10.492 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)))
14. 9.478 26.348 ↓ 1.0 30,928 3 / 3

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,912kB
15. 15.316 16.870 ↓ 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=1.628..16.870 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=501
16. 1.554 1.554 ↑ 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=4.662..4.663 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)))
17. 285.790 285.790 ↑ 1.0 1 171,474 / 3

Index Scan using travail_id_rparc_index on travail t_theo (cost=0.43..7.45 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=171,474)

  • Index Cond: (id_real_parcours = rp_theo.id_real_parcours)
  • Filter: ((id_travail IS NOT NULL) AND (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)))
18. 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.36 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
19. 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)))
20. 736.819 736.819 ↓ 0.0 0 2,210,456 / 3

Index Scan using pk_travail on travail t_real (cost=0.43..7.38 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=2,210,456)

  • 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)))
21. 736.819 736.819 ↓ 0.0 0 2,210,456 / 3

Index Scan using pk_real_parcours on real_parcours rp_real (cost=0.43..7.42 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=2,210,456)

  • 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)))
22. 0.013 0.050 ↓ 1.4 56 1

Hash (cost=25.67..25.67 rows=39 width=8) (actual time=0.050..0.050 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
23. 0.037 0.037 ↓ 1.4 56 1

Index Only Scan using pk_calendrier on calendrier c (cost=0.28..25.67 rows=39 width=8) (actual time=0.022..0.037 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)))
  • 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: 10
24. 6,631.368 6,631.368 ↓ 0.0 0 2,210,456

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
25. 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
26. 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)))
Planning time : 9.542 ms
Execution time : 20,990.519 ms