explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nhtI

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

GroupAggregate (cost=355,248.64..355,341.80 rows=2,192 width=214) (actual time=7,158.419..7,454.306 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. 125.036 2,039.823 ↓ 1.4 92,603 1

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

  • Group Key: poste.prest
4. 151.291 1,914.787 ↓ 1.7 113,557 1

Hash Join (cost=93,376.79..202,926.98 rows=66,747 width=26) (actual time=1,664.966..1,914.787 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. 156.132 182.623 ↓ 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=47.452..182.623 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. 26.491 26.491 ↓ 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=26.491..26.491 rows=113,850 loops=1)

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

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

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 142,613kB
8. 844.050 844.050 ↑ 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.046..844.050 rows=2,343,350 loops=1)

9. 1,536.698 7,203.709 ↓ 216.0 473,487 1

Sort (cost=150,652.99..150,658.47 rows=2,192 width=214) (actual time=7,158.402..7,203.709 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. 378.832 5,667.011 ↓ 216.0 473,487 1

Nested Loop Left Join (cost=144,352.84..150,531.35 rows=2,192 width=214) (actual time=3,177.411..5,667.011 rows=473,487 loops=1)

11. 133.420 3,372.859 ↓ 87.4 191,532 1

Merge Join (cost=144,352.42..148,209.13 rows=2,192 width=219) (actual time=3,177.313..3,372.859 rows=191,532 loops=1)

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

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

  • Sort Key: pf.numctrint, (ltrim(pf.posex, '0'::text))
  • Sort Method: quicksort Memory: 10,307kB
13. 2,100.470 2,100.470 ↓ 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,991.305..2,100.470 rows=92,603 loops=1)

14. 343.486 1,071.667 ↓ 1.0 322,384 1

Sort (cost=137,668.90..138,460.73 rows=316,732 width=74) (actual time=1,022.801..1,071.667 rows=322,384 loops=1)

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

Gather (cost=1,000.00..108,730.84 rows=316,732 width=74) (actual time=0.750..728.181 rows=322,384 loops=1)

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

Parallel Seq Scan on opu_activite act (cost=0.00..76,057.64 rows=131,972 width=74) (actual time=0.066..675.819 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))