explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AzbR

Settings
# exclusive inclusive rows x rows loops node
1. 146.185 26,243.908 ↓ 30,694.3 92,083 1

Unique (cost=553,407.83..553,408.08 rows=3 width=339) (actual time=26,037.401..26,243.908 rows=92,083 loops=1)

  • Buffers: shared hit=3553855
2.          

CTE presta_fibre

3. 325.504 1,415.104 ↓ 1.4 92,083 1

Aggregate (cost=152,397.79..153,368.92 rows=64,742 width=45) (actual time=1,202.464..1,415.104 rows=92,083 loops=1)

  • Buffers: shared hit=404398
4. 143.599 1,089.600 ↓ 1.9 104,440 1

Gather (cost=146,328.19..151,993.15 rows=53,952 width=41) (actual time=921.356..1,089.6 rows=104,440 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=404398
5. 87.129 946.001 ↓ 1.3 34,813 3 / 3

Aggregate (cost=145,328.19..145,597.95 rows=26,976 width=41) (actual time=913.062..946.001 rows=34,813 loops=3)

  • Buffers: shared hit=404398
6. 80.108 858.872 ↓ 1.4 37,627 3 / 3

Nested Loop (cost=2,058.07..145,193.31 rows=26,976 width=19) (actual time=37.071..858.872 rows=37,627 loops=3)

  • Buffers: shared hit=404398
7. 167.352 175.180 ↑ 1.2 37,724 3 / 3

Bitmap Heap Scan on clip_poste poste_1 (cost=2,057.64..109,187.59 rows=45,256 width=22) (actual time=36.937..175.18 rows=37,724 loops=3)

  • Heap Blocks: exact=19253
  • Buffers: shared hit=50862
8. 7.828 7.828 ↓ 1.0 113,172 1 / 3

Bitmap Index Scan on clip_poste_prestartcod (cost=0..2,030.48 rows=108,615 width=0) (actual time=23.483..23.483 rows=113,172 loops=1)

  • Index Cond: ((poste_1.prestartcod)::text = ANY ('{LETE01,LETF01,LETG01,LET10G,CEEO01,CEEO02,CEEO03,CEEO04,1ILF01}'::text[]))
  • Buffers: shared hit=335
9. 603.584 603.584 ↑ 1.0 1 113,172 / 3

Index Only Scan using idx_si_clip_cde_numctr_typctr on clip_cde cde_1 (cost=0.43..0.79 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=113,172)

  • Index Cond: (cde_1.numctr = (poste_1.numctr)::text)
  • Filter: ((poste_1.resil1 IS NULL) OR ((poste_1.resil1)::text <> ALL ('{ZB,ZE,ZD}'::text[])) OR ((cde_1.typctr)::text = ANY ('{ZC01,ZC02}'::text[])))
  • Heap Fetches: 18
  • Buffers: shared hit=353536
10. 982.687 26,097.723 ↓ 30,694.7 92,084 1

Sort (cost=400,038.92..400,038.92 rows=3 width=339) (actual time=26,037.396..26,097.723 rows=92,084 loops=1)

  • Sort Key: poste.numctr, poste.prest, cde.typcde, cde.typctr, cde.datdepot, poste.posex, poste.prestartcod, (ltrim((poste.posex)::text, '0'::text)), poste.sitcorendd, poste.sitcoreossp, poste.datlivprev, odde.date_depot, odde.type_op, ltd.libelle, lsp.libelle, dde.status, dde.date_contract, dde.date_liv_reelle, poste.resil1, poste.resil2, poste.datliveff, poste.datdebfac, poste.dateffresil, poste.datenvoiar, dde.nom_rol, partd.lib_part, partd.nom2, (CASE WHEN (((poste.resil1 IS NULL) OR ((poste.resil1)::text <> ALL ('{ZB,ZE,ZD}'::text[]))) AND ((poste.datliveff IS NOT NULL) OR (dde.date_liv_reelle IS NOT NULL)) AND (poste.datdebfac IS NOT NULL) AND (poste.dateffresil IS NULL)) THEN 'MAD facturé'::text WHEN (((poste.resil1 IS NULL) OR ((poste.resil1)::text <> ALL ('{ZB,ZE,ZD}'::text[]))) AND ((poste.datliveff IS NOT NULL) OR (dde.date_liv_reelle IS NOT NULL)) AND (poste.datdebfac IS NULL) AND (poste.dateffresil IS NULL)) THEN 'MAD non facturé'::text WHEN (lmr.libelle IS NOT NULL) THEN 'Refus OPUS'::text WHEN ((cde.typctr)::text = 'ZR01'::text) THEN 'Résilié'::text WHEN (((poste.resil1 IS NULL) OR ((poste.resil1)::text <> ALL ('{ZB,ZE,ZD}'::text[]))) AND (poste.datliveff IS NULL) AND (dde.date_liv_reelle IS NULL) AND (poste.dateffresil IS NULL)) THEN 'En cours de production'::text WHEN (((poste.datliveff IS NOT NULL) OR (dde.date_liv_reelle IS NOT NULL)) AND (poste.dateffresil IS NOT NULL)) THEN 'Résilié'::text WHEN ((poste.resil1)::text = ANY ('{ZB,ZE,ZD}'::text[])) THEN 'Annulé'::text WHEN (((poste.resil1)::text = ANY ('{ZA,ZC}'::text[])) AND (poste.datliveff IS NULL) AND (dde.date_liv_reelle IS NULL) AND (poste.dateffresil IS NOT NULL)) THEN 'Annulé'::text ELSE 'Anomalie'::text END), lmr.libelle, lcr.libelle, lcc.libelle, g.status
  • Sort Method: quicksort Memory: 27762kB
  • Buffers: shared hit=3553855
11. 394.366 25,115.036 ↓ 30,694.7 92,084 1

Nested Loop (cost=3.67..400,038.89 rows=3 width=339) (actual time=1,202.863..25,115.036 rows=92,084 loops=1)

  • Buffers: shared hit=3553855
12. 305.875 23,707.746 ↓ 92,084.0 92,084 1

Nested Loop (cost=3.24..400,022.62 rows=1 width=256) (actual time=1,202.839..23,707.746 rows=92,084 loops=1)

  • Buffers: shared hit=3173796
13. 282.493 21,744.359 ↓ 92,084.0 92,084 1

Nested Loop (cost=2.81..400,022.13 rows=1 width=274) (actual time=1,202.799..21,744.359 rows=92,084 loops=1)

  • Buffers: shared hit=2805103
14. 236.466 20,172.704 ↓ 92,083.0 92,083 1

Nested Loop (cost=2.38..400,020.65 rows=1 width=357) (actual time=1,202.767..20,172.704 rows=92,083 loops=1)

  • Buffers: shared hit=2495686
15. 250.509 19,659.989 ↓ 92,083.0 92,083 1

Nested Loop (cost=2.11..400,020.34 rows=1 width=337) (actual time=1,202.759..19,659.989 rows=92,083 loops=1)

  • Buffers: shared hit=2338821
16. 300.050 19,041.148 ↓ 92,083.0 92,083 1

Nested Loop (cost=1.84..400,020.05 rows=1 width=320) (actual time=1,202.75..19,041.148 rows=92,083 loops=1)

  • Buffers: shared hit=2181956
17. 4,856.161 18,188.600 ↓ 92,083.0 92,083 1

Nested Loop (cost=1.56..400,019.76 rows=1 width=300) (actual time=1,202.743..18,188.6 rows=92,083 loops=1)

  • Buffers: shared hit=2025103
18. 225.749 6,242.048 ↓ 92,083.0 92,083 1

Nested Loop (cost=1.56..400,010.13 rows=1 width=281) (actual time=1,202.581..6,242.048 rows=92,083 loops=1)

  • Buffers: shared hit=1659223
19. 307.661 5,647.967 ↓ 92,083.0 92,083 1

Nested Loop (cost=1.29..400,009.84 rows=1 width=261) (actual time=1,202.571..5,647.967 rows=92,083 loops=1)

  • Buffers: shared hit=1502370
20. 259.025 4,603.642 ↓ 92,083.0 92,083 1

Nested Loop (cost=0.86..400,006.2 rows=1 width=238) (actual time=1,202.552..4,603.642 rows=92,083 loops=1)

  • Buffers: shared hit=1147209
21. 350.322 3,700.036 ↓ 92,083.0 92,083 1

Nested Loop (cost=0.43..400,003.47 rows=1 width=225) (actual time=1,202.529..3,700.036 rows=92,083 loops=1)

  • Buffers: shared hit=792304
22. 1,600.137 1,600.137 ↓ 1.4 92,083 1

CTE Scan on presta_fibre pf (cost=0..1,294.84 rows=64,742 width=124) (actual time=1,202.471..1,600.137 rows=92,083 loops=1)

  • Buffers: shared hit=404398
23. 1,749.577 1,749.577 ↑ 1.0 1 92,083

Index Scan using si_clip_poste_prest_idx on clip_poste poste (cost=0.43..6.15 rows=1 width=101) (actual time=0.018..0.019 rows=1 loops=92,083)

  • Index Cond: ((poste.prest)::text = (pf.prest)::text)
  • Filter: (pf.numctr = (poste.numctr)::text)
  • Buffers: shared hit=387906
24. 644.581 644.581 ↑ 1.0 1 92,083

Index Scan using opu_demande_pkey on opu_demande odde (cost=0.43..2.72 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=92,083)

  • Index Cond: (odde.no_dde = pf.numctrint)
  • Buffers: shared hit=354905
25. 736.664 736.664 ↑ 1.0 1 92,083

Index Scan using opu_postedde_no_dde on opu_postedde dde (cost=0.43..3.63 rows=1 width=39) (actual time=0.007..0.008 rows=1 loops=92,083)

  • Index Cond: (dde.no_dde = pf.numctrint)
  • Filter: ((pf.prest)::text = (dde.prestation)::text)
  • Buffers: shared hit=355161
26. 368.332 368.332 ↓ 0.0 0 92,083

Index Only Scan using si_opu_lib_type_code_libelle_idx on opu_lib lmr (cost=0.27..0.29 rows=1 width=26) (actual time=0.004..0.004 rows=0 loops=92,083)

  • Index Cond: ((lmr.type = 'CDMOTIF'::text) AND (lmr.code = (dde.motif_refus)::text))
  • Buffers: shared hit=156853
27. 7,090.391 7,090.391 ↑ 1.0 94 92,083

Seq Scan on opu_lib lcr (cost=0..8.44 rows=95 width=26) (actual time=0.018..0.077 rows=94 loops=92,083)

  • Filter: ((lcr.type)::text = 'CAUSERETARD'::text)
  • Buffers: shared hit=365880
28. 552.498 552.498 ↓ 0.0 0 92,083

Index Only Scan using si_opu_lib_type_code_libelle_idx on opu_lib lcc (cost=0.27..0.29 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=92,083)

  • Index Cond: ((lcc.type = 'CAUSERETARDCOM'::text) AND (lcc.code = (dde.cause_retard_com)::text))
  • Buffers: shared hit=156853
29. 368.332 368.332 ↑ 1.0 1 92,083

Index Only Scan using si_opu_lib_type_code_libelle_idx on opu_lib ltd (cost=0.27..0.29 rows=1 width=26) (actual time=0.004..0.004 rows=1 loops=92,083)

  • Index Cond: ((ltd.type = 'CDTYPEDDE'::text) AND (ltd.code = (odde.type_dde)::text))
  • Buffers: shared hit=156865
30. 276.249 276.249 ↑ 1.0 1 92,083

Index Only Scan using si_opu_lib_type_code_libelle_idx on opu_lib lsp (cost=0.27..0.29 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=92,083)

  • Index Cond: ((lsp.type = 'STATGLOBPOSTE'::text) AND (lsp.code = (odde.status)::text))
  • Buffers: shared hit=156865
31. 1,289.162 1,289.162 ↓ 0.0 0 92,083

Index Scan using opu_graphe_prestation on opu_graphe g (cost=0.42..1.48 rows=1 width=22) (actual time=0.014..0.014 rows=0 loops=92,083)

  • Index Cond: ((pf.prest)::text = (g.prestation)::text)
  • Filter: (g.no_dde = pf.numctrint)
  • Buffers: shared hit=309417
32. 1,657.512 1,657.512 ↑ 1.0 1 92,084

Index Scan using idx_si_clip_cde_numctr_typctr on clip_cde cde (cost=0.43..0.49 rows=1 width=25) (actual time=0.018..0.018 rows=1 loops=92,084)

  • Index Cond: ((cde.numctr)::text = (poste.numctr)::text)
  • Buffers: shared hit=368693
33. 1,012.924 1,012.924 ↑ 3.0 1 92,084

Index Scan using opu_partdde_no_dde on opu_partdde partd (cost=0.43..16.17 rows=3 width=27) (actual time=0.01..0.011 rows=1 loops=92,084)

  • Index Cond: (pf.numctrint = partd.no_dde)
  • Filter: ((partd.part)::text ~~ '7'::text)
  • Buffers: shared hit=380059