explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GDz8

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=560,655,637.57..560,655,637.60 rows=1 width=45) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=560,655,637.57..560,655,637.58 rows=1 width=45) (actual rows= loops=)

  • Sort Key: p.numero_contact, d.dossier_id, d.numero_dossier, dg.confidentiel, md.mut_dossier_id, pd.part_dossier_id, pad.partie_dossier_id, pp.plan_partie_id, pg.vco_id, md.cas_id
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=176,647.08..560,655,637.56 rows=1 width=45) (actual rows= loops=)

  • Join Filter: ((md.no_gen_dossier = pg.no_gen_dossier) AND (dg.dossier_id = pg.dossier_id))
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..556,769,651.67 rows=1 width=41) (actual rows= loops=)

  • Join Filter: ((dg.no_gen_dossier = md.no_gen_dossier) AND (dg.dossier_id = md.dossier_id))
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..556,616,950.00 rows=1 width=21) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on dossier_gen dg (cost=0.00..556,602,827.12 rows=100 width=9) (actual rows= loops=)

  • Filter: (((SubPlan 3) = 0) AND ((SubPlan 2) = 0))
7.          

SubPlan (forSeq Scan)

8. 0.000 0.000 ↓ 0.0

Aggregate (cost=64.91..64.92 rows=1 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.72..64.91 rows=1 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using d_g_in_pt_d_fk on part_dossier pd2_1 (cost=0.43..56.60 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (dossier_gen_id = dg.dossier_gen_id)
  • Filter: ((role)::text = 'PACS'::text)
11. 0.000 0.000 ↓ 0.0

Index Scan using idx4fk_f_pt_d_in_pacse_d on pacse_dossier pcsd (cost=0.28..8.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (part_dossier_id = pd2_1.part_dossier_id)
  • Filter: rentes_non_indexables
12. 0.000 0.000 ↓ 0.0

Aggregate (cost=73.52..73.53 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..73.52 rows=1 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using d_g_in_pt_d_fk on part_dossier pd2 (cost=0.43..56.60 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (dossier_gen_id = dg.dossier_gen_id)
  • Filter: ((role)::text = 'CONJ'::text)
15. 0.000 0.000 ↓ 0.0

Index Scan using idx4fk_f_pt_d_in_cj_d on conjoint_dossier cj (cost=0.43..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (part_dossier_id = pd2.part_dossier_id)
  • Filter: rentes_non_indexables
16. 0.000 0.000 ↓ 0.0

Index Scan using p_dossier on dossier d (cost=0.42..141.22 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (dossier_id = dg.dossier_id)
  • Filter: ((pro_id = 86) AND (dg.no_gen_dossier = (SubPlan 1)))
17.          

SubPlan (forIndex Scan)

18. 0.000 0.000 ↓ 0.0

Aggregate (cost=133.09..133.10 rows=1 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Scan using d_in_mu_d_fk on mut_dossier (cost=0.43..133.01 rows=31 width=4) (actual rows= loops=)

  • Index Cond: (dossier_id = d.dossier_id)
  • Filter: ((etat)::text <> 'SIMU'::text)
20. 0.000 0.000 ↓ 0.0

Seq Scan on mut_dossier md (cost=0.00..146,120.87 rows=438,720 width=20) (actual rows= loops=)

  • Filter: (((statut)::text = ANY ('{MIXT,PENS}'::text[])) AND ((etat)::text = 'VALI'::text))
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=176,646.66..3,885,510.31 rows=31,706 width=28) (actual rows= loops=)

  • Hash Cond: (pd.part_dossier_part_id = p.part_id)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=156,743.96..3,862,920.73 rows=32,485 width=28) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=156,743.53..3,674,891.05 rows=32,485 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=156,743.09..3,302,976.19 rows=62,439 width=24) (actual rows= loops=)

  • Hash Cond: (pg.partie_dossier_id = pad.partie_dossier_id)
25. 0.000 0.000 ↓ 0.0

Seq Scan on pa_gen pg (cost=0.00..3,136,993.96 rows=368,732 width=24) (actual rows= loops=)

  • Filter: (((etat)::text = 'VAL'::text) AND ((type_couv)::text = ANY ('{RENR,RCCO,RCOR,RCRE,RCVV,RCVF}'::text[])))
26. 0.000 0.000 ↓ 0.0

Hash (cost=144,522.63..144,522.63 rows=744,837 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on partie_dossier pad (cost=0.00..144,522.63 rows=744,837 width=4) (actual rows= loops=)

  • Filter: ((type_partie)::text = ANY ('{PEDE,PEIN,PERE,TEMP}'::text[]))
28. 0.000 0.000 ↓ 0.0

Index Scan using p_plan_partie on plan_partie pp (cost=0.43..5.95 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (plan_partie_id = pg.plan_partie_id)
  • Filter: (((type_plan)::text <> 'MLPP'::text) AND ((etat)::text = 'VALI'::text))
29. 0.000 0.000 ↓ 0.0

Index Scan using p_part_dossier on part_dossier pd (cost=0.43..5.78 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (part_dossier_id = pg.assure_part_dossier_id)
30. 0.000 0.000 ↓ 0.0

Hash (cost=12,335.45..12,335.45 rows=461,220 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on part p (cost=0.00..12,335.45 rows=461,220 width=8) (actual rows= loops=)

  • Filter: (dte_deces IS NULL)