explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NMnX

Settings
# exclusive inclusive rows x rows loops node
1. 138.796 16,011.715 ↓ 30,694.0 92,082 1

Unique (cost=443,131.6..443,131.84 rows=3 width=339) (actual time=15,816.723..16,011.715 rows=92,082 loops=1)

  • Buffers: shared hit=2908307
2.          

CTE presta_fibre

3. 236.238 1,178.742 ↓ 1.4 92,083 1

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

  • Buffers: shared hit=404351
4. 100.059 942.504 ↓ 1.9 104,393 1

Gather (cost=146,328.19..151,993.15 rows=53,952 width=41) (actual time=823.615..942.504 rows=104,393 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=404351
5. 73.989 842.445 ↓ 1.3 34,798 3 / 3

Aggregate (cost=145,328.19..145,597.95 rows=26,976 width=41) (actual time=815.691..842.445 rows=34,798 loops=3)

  • Buffers: shared hit=404351
6. 88.077 768.456 ↓ 1.4 37,627 3 / 3

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

  • Buffers: shared hit=404351
7. 146.780 152.243 ↑ 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=21.83..152.243 rows=37,724 loops=3)

  • Heap Blocks: exact=19112
  • Buffers: shared hit=50862
8. 5.463 5.463 ↓ 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=16.387..16.388 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. 528.136 528.136 ↑ 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.014..0.014 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: 28
  • Buffers: shared hit=353489
10. 855.036 15,872.919 ↓ 30,694.3 92,083 1

Sort (cost=289,762.68..289,762.69 rows=3 width=339) (actual time=15,816.717..15,872.919 rows=92,083 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: 27761kB
  • Buffers: shared hit=2908307
11. 297.301 15,017.883 ↓ 30,694.3 92,083 1

Nested Loop (cost=131,799.05..289,762.66 rows=3 width=339) (actual time=5,672.135..15,017.883 rows=92,083 loops=1)

  • Buffers: shared hit=2908307
12. 224.260 13,891.835 ↓ 92,083.0 92,083 1

Nested Loop (cost=131,798.62..289,746.38 rows=1 width=256) (actual time=5,672.106..13,891.835 rows=92,083 loops=1)

  • Buffers: shared hit=2528252
13. 232.027 12,194.247 ↓ 92,083.0 92,083 1

Nested Loop (cost=131,798.19..289,745.9 rows=1 width=274) (actual time=5,672.072..12,194.247 rows=92,083 loops=1)

  • Buffers: shared hit=2159563
14. 164.776 10,857.236 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,797.76..289,744.41 rows=1 width=357) (actual time=5,672.038..10,857.236 rows=92,082 loops=1)

  • Buffers: shared hit=1850149
15. 226.788 10,416.214 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,797.49..289,744.11 rows=1 width=337) (actual time=5,672.025..10,416.214 rows=92,082 loops=1)

  • Buffers: shared hit=1693286
16. 190.536 9,913.180 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,797.22..289,743.81 rows=1 width=320) (actual time=5,672.01..9,913.18 rows=92,082 loops=1)

  • Buffers: shared hit=1536423
17. 225.040 9,354.316 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,796.95..289,743.52 rows=1 width=300) (actual time=5,671.993..9,354.316 rows=92,082 loops=1)

  • Buffers: shared hit=1379572
18. 213.523 8,853.030 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,796.67..289,743.23 rows=1 width=280) (actual time=5,671.975..8,853.03 rows=92,082 loops=1)

  • Buffers: shared hit=1222721
19. 227.146 8,087.015 ↓ 92,082.0 92,082 1

Nested Loop (cost=131,796.24..289,740.5 rows=1 width=267) (actual time=5,671.956..8,087.015 rows=92,082 loops=1)

  • Buffers: shared hit=867820
20. 119.696 6,294.475 ↓ 5.3 92,082 1

Hash Join (cost=131,795.81..183,045.82 rows=17,325 width=166) (actual time=5,671.909..6,294.475 rows=92,082 loops=1)

  • Filter: (((lcr.type)::text = 'CAUSERETARD'::text) OR (lcr.type IS NULL))
  • Buffers: shared hit=479918
21. 236.813 6,174.341 ↓ 1.4 92,083 1

Hash Join (cost=131,783.83..181,958.89 rows=64,742 width=147) (actual time=5,671.447..6,174.341 rows=92,083 loops=1)

  • Buffers: shared hit=479914
22. 1,323.479 1,323.479 ↓ 1.4 92,083 1

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

  • Buffers: shared hit=404351
23. 2,360.486 4,614.049 ↓ 1.0 2,249,891 1

Hash (cost=98,051.33..98,051.33 rows=2,248,833 width=39) (actual time=4,614.049..4,614.049 rows=2,249,891 loops=1)

  • Buffers: shared hit=75563
24. 2,253.563 2,253.563 ↓ 1.0 2,249,891 1

Seq Scan on opu_postedde dde (cost=0..98,051.33 rows=2,248,833 width=39) (actual time=0.023..2,253.563 rows=2,249,891 loops=1)

  • Buffers: shared hit=75563
25. 0.233 0.438 ↑ 1.0 355 1

Hash (cost=7.55..7.55 rows=355 width=36) (actual time=0.438..0.438 rows=355 loops=1)

  • Buffers: shared hit=4
26. 0.205 0.205 ↑ 1.0 355 1

Seq Scan on opu_lib lcr (cost=0..7.55 rows=355 width=36) (actual time=0.011..0.205 rows=355 loops=1)

  • Buffers: shared hit=4
27. 1,565.394 1,565.394 ↑ 1.0 1 92,082

Index Scan using si_clip_poste_prest_idx on clip_poste poste (cost=0.43..6.15 rows=1 width=101) (actual time=0.016..0.017 rows=1 loops=92,082)

  • Index Cond: ((poste.prest)::text = (pf.prest)::text)
  • Filter: (pf.numctr = (poste.numctr)::text)
  • Buffers: shared hit=387902
28. 552.492 552.492 ↑ 1.0 1 92,082

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

  • Index Cond: (odde.no_dde = pf.numctrint)
  • Buffers: shared hit=354901
29. 276.246 276.246 ↓ 0.0 0 92,082

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.003..0.003 rows=0 loops=92,082)

  • Index Cond: ((lmr.type = 'CDMOTIF'::text) AND (lmr.code = (dde.motif_refus)::text))
  • Buffers: shared hit=156851
30. 368.328 368.328 ↓ 0.0 0 92,082

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.004..0.004 rows=0 loops=92,082)

  • Index Cond: ((lcc.type = 'CAUSERETARDCOM'::text) AND (lcc.code = (dde.cause_retard_com)::text))
  • Buffers: shared hit=156851
31. 276.246 276.246 ↑ 1.0 1 92,082

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.003..0.003 rows=1 loops=92,082)

  • Index Cond: ((ltd.type = 'CDTYPEDDE'::text) AND (ltd.code = (odde.type_dde)::text))
  • Buffers: shared hit=156863
32. 276.246 276.246 ↑ 1.0 1 92,082

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,082)

  • Index Cond: ((lsp.type = 'STATGLOBPOSTE'::text) AND (lsp.code = (odde.status)::text))
  • Buffers: shared hit=156863
33. 1,104.984 1,104.984 ↓ 0.0 0 92,082

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

  • Index Cond: ((pf.prest)::text = (g.prestation)::text)
  • Filter: (g.no_dde = pf.numctrint)
  • Buffers: shared hit=309414
34. 1,473.328 1,473.328 ↑ 1.0 1 92,083

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.016..0.016 rows=1 loops=92,083)

  • Index Cond: ((cde.numctr)::text = (poste.numctr)::text)
  • Buffers: shared hit=368689
35. 828.747 828.747 ↑ 3.0 1 92,083

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

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