explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r8vt

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

GroupAggregate (cost=355,238.87..355,332.03 rows=2,192 width=214) (actual time=7,077.751..7,375.178 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. 119.509 2,048.102 ↓ 1.4 92,603 1

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

  • Group Key: poste.prest
4. 152.465 1,928.593 ↓ 1.7 113,557 1

Hash Join (cost=93,376.79..202,926.98 rows=66,747 width=26) (actual time=1,679.601..1,928.593 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.418 186.370 ↓ 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=52.901..186.370 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. 31.952 31.952 ↓ 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=31.952..31.952 rows=113,850 loops=1)

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

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

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

9. 1,541.101 7,123.464 ↓ 216.0 473,487 1

Sort (cost=150,643.21..150,648.69 rows=2,192 width=214) (actual time=7,077.731..7,123.464 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. 332.120 5,582.363 ↓ 216.0 473,487 1

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

11. 143.988 3,334.923 ↓ 87.4 191,532 1

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

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

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

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

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

14. 332.855 1,025.333 ↓ 1.0 322,384 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
16. 653.393 653.393 ↑ 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.051..653.393 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))