explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5aF5

Settings
# exclusive inclusive rows x rows loops node
1. 246.700 7,235.317 ↓ 87.4 191,532 1

GroupAggregate (cost=355,238.87..355,332.03 rows=2,192 width=214) (actual time=6,947.308..7,235.317 rows=191,532 loops=1)

  • Group Key: pf.numctr, pf.prest, (ltrim(pf.posex, '0'::text)), act.no_act, act.date_deb_tard, act.date_fin_reel, act.status_user, act.date_rdv, act.lib_poste_travail, act.com_status_user
2.          

CTE presta_fibre

3. 117.919 1,996.837 ↓ 1.4 92,603 1

HashAggregate (cost=203,594.45..204,595.65 rows=66,747 width=77) (actual time=1,955.364..1,996.837 rows=92,603 loops=1)

  • Group Key: poste.prest
4. 150.848 1,878.918 ↓ 1.7 113,557 1

Hash Join (cost=93,376.79..202,926.98 rows=66,747 width=26) (actual time=1,630.247..1,878.918 rows=113,557 loops=1)

  • Hash Cond: ((poste.numctr)::text = (cde.numctr)::text)
  • Join Filter: ((poste.resil1 IS NULL) OR ((poste.resil1)::text <> ALL ('{ZB,ZE,ZD}'::text[])) OR ((cde.typctr)::text = ANY ('{ZC01,ZC02}'::text[])))
  • Rows Removed by Join Filter: 293
5. 154.937 185.425 ↓ 1.0 113,850 1

Bitmap Heap Scan on clip_poste poste (cost=2,126.42..111,380.39 rows=112,844 width=29) (actual time=51.396..185.425 rows=113,850 loops=1)

  • Recheck Cond: ((prestartcod)::text = ANY ('{LETE01,LETF01,LETG01,LET10G,CEEO01,CEEO02,CEEO03,CEEO04,1ILF01}'::text[]))
  • Heap Blocks: exact=50,876
6. 30.488 30.488 ↓ 1.0 113,850 1

Bitmap Index Scan on clip_poste_prestartcod (cost=0.00..2,098.21 rows=112,844 width=0) (actual time=30.488..30.488 rows=113,850 loops=1)

  • Index Cond: ((prestartcod)::text = ANY ('{LETE01,LETF01,LETG01,LET10G,CEEO01,CEEO02,CEEO03,CEEO04,1ILF01}'::text[]))
7. 719.638 1,542.645 ↑ 1.0 2,343,350 1

Hash (cost=61,958.50..61,958.50 rows=2,343,350 width=16) (actual time=1,542.645..1,542.645 rows=2,343,350 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 142,613kB
8. 823.007 823.007 ↑ 1.0 2,343,350 1

Seq Scan on clip_cde cde (cost=0.00..61,958.50 rows=2,343,350 width=16) (actual time=0.051..823.007 rows=2,343,350 loops=1)

9. 1,499.515 6,988.617 ↓ 216.0 473,487 1

Sort (cost=150,643.21..150,648.69 rows=2,192 width=214) (actual time=6,947.288..6,988.617 rows=473,487 loops=1)

  • Sort Key: pf.numctr, pf.prest, (ltrim(pf.posex, '0'::text)), act.no_act, act.date_deb_tard, act.date_fin_reel, act.status_user, act.date_rdv, act.lib_poste_travail, act.com_status_user
  • Sort Method: quicksort Memory: 100,545kB
10. 344.259 5,489.102 ↓ 216.0 473,487 1

Nested Loop Left Join (cost=144,343.26..150,521.58 rows=2,192 width=214) (actual time=3,041.728..5,489.102 rows=473,487 loops=1)

11. 127.967 3,229.523 ↓ 87.4 191,532 1

Merge Join (cost=144,342.84..148,199.22 rows=2,192 width=219) (actual time=3,041.637..3,229.523 rows=191,532 loops=1)

  • Merge Cond: ((pf.numctrint = act.no_dde) AND ((ltrim(pf.posex, '0'::text)) = (act.no_poste)::text))
12. 59.878 2,111.041 ↓ 1.4 92,602 1

Sort (cost=6,683.52..6,850.38 rows=66,747 width=156) (actual time=2,098.344..2,111.041 rows=92,602 loops=1)

  • Sort Key: pf.numctrint, (ltrim(pf.posex, '0'::text))
  • Sort Method: quicksort Memory: 10,307kB
13. 2,051.163 2,051.163 ↓ 1.4 92,603 1

CTE Scan on presta_fibre pf (cost=0.00..1,334.94 rows=66,747 width=156) (actual time=1,955.379..2,051.163 rows=92,603 loops=1)

14. 306.649 990.515 ↓ 1.0 322,384 1

Sort (cost=137,659.32..138,451.07 rows=316,699 width=74) (actual time=942.481..990.515 rows=322,384 loops=1)

  • Sort Key: act.no_dde, act.no_poste
  • Sort Method: quicksort Memory: 58,692kB
15. 42.629 683.866 ↓ 1.0 322,384 1

Gather (cost=1,000.00..108,724.51 rows=316,699 width=74) (actual time=0.680..683.866 rows=322,384 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 641.237 641.237 ↑ 1.2 107,461 3 / 3

Parallel Seq Scan on opu_activite act (cost=0.00..76,054.61 rows=131,958 width=74) (actual time=0.056..641.237 rows=107,461 loops=3)

  • Filter: ((no_act)::text = ANY ('{AO11,A011,AO12,A012,AOPT,A0PT,AOGC,A0GC,AOS1,A0S1,A0PR,AOPR,AORV,A0RV,AOP1,A0P1,AOTX,A0TX,A024,AO24,A040,AO40,A046,AO46,A050,AO50}'::text[]))
  • Rows Removed by Filter: 432,955
17. 1,915.320 1,915.320 ↓ 2.0 2 191,532

Index Scan using opu_comact_no_gra_act on opu_comact cmt (cost=0.43..1.05 rows=1 width=17) (actual time=0.009..0.010 rows=2 loops=191,532)

  • Index Cond: (((act.no_graphe)::text = (no_graphe)::text) AND ((act.no_act)::text = (no_act)::text))