explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sMjS

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

GroupAggregate (cost=4,613,988.85..4,614,031.14 rows=5 width=1,031) (actual rows= loops=)

  • Group Key: soc.soc_id, plan_employeur_affilie.plan_employeur_affilie_id, contrat.contrat_id, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying)
2. 0.000 0.000 ↓ 0.0

Sort (cost=4,613,988.85..4,613,988.87 rows=5 width=762) (actual rows= loops=)

  • Sort Key: soc.soc_id, plan_employeur_affilie.plan_employeur_affilie_id, contrat.contrat_id, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character var
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,242.17..4,613,988.80 rows=5 width=762) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,241.60..4,613,907.03 rows=5 width=206) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,241.16..4,613,750.13 rows=6 width=202) (actual rows= loops=)

  • Join Filter: ((couv.type_nat_type_couv)::text = (type_couv.type_nat_type_couv)::text)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=9,241.16..4,613,742.57 rows=6 width=187) (actual rows= loops=)

  • Hash Cond: (vcouv.cou_id = couv.cou_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,208.81..4,613,710.13 rows=6 width=182) (actual rows= loops=)

  • Join Filter: (pa_gen.vco_id = vcouv.vco_id)
8. 0.000 0.000 ↓ 0.0

Index Scan using a_vco2 on vcouv (cost=0.28..9,330.70 rows=3,229 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=9,208.53..4,604,088.83 rows=6 width=182) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,208.53..4,604,088.80 rows=6 width=182) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,207.96..4,602,647.46 rows=1 width=194) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,207.39..4,601,775.57 rows=1 width=184) (actual rows= loops=)

  • Join Filter: (plan_partie.plan_partie_id = coti_plan_part_comm.plan_partie_id)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,138.10..4,601,702.25 rows=1 width=188) (actual rows= loops=)

  • Join Filter: ((plan_partie.plan_partie_id = part_comm_plan_partie.plan_partie_id) AND (part_comm_dossier.part_comm_dossier_id = part_comm_plan_partie.part_comm_dossier
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,129.33..4,601,689.45 rows=1 width=180) (actual rows= loops=)

  • Join Filter: (part_comm_dossier.part_comm_dossier_id = tx_inva.part_comm_dossier_id)
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,128.89..4,601,681.58 rows=1 width=168) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,128.33..4,591,941.77 rows=1 width=154) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,127.90..4,591,933.93 rows=1 width=134) (actual rows= loops=)

  • Join Filter: (partie_dossier.dossier_gen_id = dossier_gen.dossier_gen_id)
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,066.42..4,546,471.51 rows=578 width=70) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,065.98..4,535,807.75 rows=578 width=50) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,065.55..4,502,199.17 rows=585 width=42) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,065.12..4,497,541.57 rows=584 width=42) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=9,064.68..2,204,226.70 rows=7,535 width=38) (actual rows= loops=)

  • Hash Cond: (contrat.contrat_id = dossier.contrat_id)
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.84..2,177,246.81 rows=167 width=34) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.42..2,175,936.08 rows=167 width=20) (actual rows= loops=)

  • -> Index Scan using c_g_in_pt_c_fk on part_contrat a1_part_contrat_employeur (cost=0.42..7.90 rows=
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.00..2,174,615.28 rows=167 width=20) (actual rows= loops=)

  • Index Cond: (contrat_gen_id = contrat_gen.contrat_gen_id)
  • Filter: ((role)::text = 'EMPL'::text)
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.71..2,173,316.62 rows=167 width=20) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.42..5,324.32 rows=2,317 width=12) (actual rows= loops=)

  • Join Filter: (prod.pro_id = contrat.pro_id)
28. 0.000 0.000 ↓ 0.0

Index Scan using p_contrat on contrat (cost=0.28..289.86 rows=5,406 width=12) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Materialize (cost=1.13..7.04 rows=62 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.13..6.73 rows=62 width=8) (actual rows= loops=)

  • Hash Cond: (prod.dn_soc_id = soc.soc_id)
31. 0.000 0.000 ↓ 0.0

Seq Scan on prod (cost=0.00..4.44 rows=144 width=6) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=3 width=4) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on soc (cost=0.00..1.10 rows=3 width=4) (actual rows= loops=)

  • Filter: (soc_id = ANY ('{3,8,7}'::integer[]))
34. 0.000 0.000 ↓ 0.0

Index Scan using c_in_mu_c_fk on mut_contrat (cost=0.29..935.68 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (contrat_id = contrat.contrat_id)
  • Filter: (no_gen_contrat = (SubPlan 4))
35.          

SubPlan (forIndex Scan)

36. 0.000 0.000 ↓ 0.0

Aggregate (cost=58.75..58.76 rows=1 width=4) (actual rows= loops=)

  • -> Index Scan using c_in_mu_c_fk on mut_contrat a_2 (cost=0.29..58.71 row
  • Index Cond: (mut_contrat.contrat_id = contrat_id)
  • Filter: ((dte_mut <= '2020-01-01'::date) AND ((etat)::text = 'VALI'::
37. 0.000 0.000 ↓ 0.0

Index Scan using mu_c_in_c_g_fk on contrat_gen (cost=0.29..7.77 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (mut_contrat_id = mut_contrat.mut_contrat_id)
38. 0.000 0.000 ↓ 0.0

Index Scan using p_part on part a1_part_employeur (cost=0.42..7.84 rows=1 width=22) (actual rows= loops=)

  • Index Cond: (part_id = a1_part_contrat_employeur.part_id)
39. 0.000 0.000 ↓ 0.0

Hash (cost=6,013.04..6,013.04 rows=243,904 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on dossier (cost=0.00..6,013.04 rows=243,904 width=12) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Scan using idx_mut_dossier_dossier_id_mut_id on mut_dossier (cost=0.43..304.34 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (dossier_id = dossier.dossier_id)
  • Filter: (((statut)::text = ANY ('{MIXT,ACTI,ATTE}'::text[])) AND (no_gen_dossier = (SubPlan 3)))
42.          

SubPlan (forIndex Scan)

43. 0.000 0.000 ↓ 0.0

Result (cost=5.42..5.43 rows=1 width=4) (actual rows= loops=)

44.          

Initplan (forResult)

45. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..5.42 rows=1 width=4) (actual rows= loops=)

  • -> Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on mut_dossier a_1 (cost
  • Index Cond: ((mut_dossier.dossier_id = dossier_id) AND (no_gen_dossier IS NOT NULL))
  • Filter: ((dte_mut <= '2020-01-01'::date) AND ((etat)::text = 'VALI'::text))
46. 0.000 0.000 ↓ 0.0

Index Scan using mu_d_in_d_g_fk on dossier_gen (cost=0.43..7.97 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (mut_dossier_id = mut_dossier.mut_dossier_id)
47. 0.000 0.000 ↓ 0.0

Index Scan using d_g_in_pt_d_fk on part_dossier a1_part_dossier_affi (cost=0.43..57.40 rows=5 width=8) (actual rows= loops=)

  • Index Cond: (dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: ((role)::text = 'AFFI'::text)
48. 0.000 0.000 ↓ 0.0

Index Scan using d_g_in_ptc_d_fk on part_comm_dossier (cost=0.43..18.42 rows=3 width=20) (actual rows= loops=)

  • Index Cond: (dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: ((type_part_comm)::text = 'EMPL'::text)
49. 0.000 0.000 ↓ 0.0

Materialize (cost=61.49..40,738.62 rows=545 width=80) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=61.49..40,735.90 rows=545 width=80) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=61.05..35,593.85 rows=654 width=68) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Index Only Scan using p_plan_empl_affil on plan_employeur_affilie (cost=0.29..12.92 rows=3 width=4) (actual rows= loops=)

  • Index Cond: (plan_employeur_affilie_id = ANY ('{554631,554632,554633}'::integer[]))
53. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on plan_partie (cost=60.77..11,843.76 rows=1,655 width=68) (actual rows= loops=)

  • Recheck Cond: (plan_employeur_affilie_id = plan_employeur_affilie.plan_employeur_affilie_id)
  • Filter: (((type_plan)::text <> 'MLPP'::text) AND ((etat)::text = 'VALI'::text))
54. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx4fk_f_p_ea_in_p_pi (cost=0.00..60.35 rows=3,189 width=0) (actual rows= loops=)

  • Index Cond: (plan_employeur_affilie_id = plan_employeur_affilie.plan_employeur_affilie_id)
55. 0.000 0.000 ↓ 0.0

Index Scan using p_partie_dossier on partie_dossier (cost=0.43..7.85 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (partie_dossier_id = plan_partie.partie_dossier_id)
  • Filter: ((type_partie)::text = 'ACTI'::text)
56. 0.000 0.000 ↓ 0.0

Index Scan using p_part on part a1_part_affi (cost=0.42..7.84 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (part_id = a1_part_dossier_affi.part_dossier_part_id)
57. 0.000 0.000 ↓ 0.0

Index Scan using p_pi_in_cet_fk on cpt_epa_tot (cost=0.57..9,739.80 rows=1 width=14) (actual rows= loops=)

  • Index Cond: (plan_partie_id = plan_partie.plan_partie_id)
  • Filter: (((type_av_ap)::text = 'APRE'::text) AND (((SubPlan 5))::text = 'Total'::text))
58.          

SubPlan (forIndex Scan)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..9.98 rows=1 width=15) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on nature_cpt_epa a_3 (cost=0.00..1.66 rows=1 width=4) (actual rows= loops=)

  • Filter: (cpt_epa_tot.nat_cpt_epa_id = nature_cpt_epa_id)
61. 0.000 0.000 ↓ 0.0

Index Scan using idx_code_texte_code_cle_code_type on code_texte b (cost=0.28..8.30 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (((code_cle)::text = (a_3.nature)::text) AND ((code_type)::text = 'TypeNatureCompteEpargne'::text))
  • Filter: ((codet_langue)::text = 'FR'::text)
62. 0.000 0.000 ↓ 0.0

Index Scan using p_pi_in_tx_inva_fk on tx_inva (cost=0.43..7.85 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (plan_partie_id = plan_partie.plan_partie_id)
63. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on part_comm_plan_partie (cost=8.77..12.78 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((plan_partie_id = tx_inva.plan_partie_id) AND (part_comm_dossier_id = tx_inva.part_comm_dossier_id))
64. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=8.77..8.77 rows=1 width=0) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx4fk_f_p_pi_in_ptc_p (cost=0.00..4.20 rows=8 width=0) (actual rows= loops=)

  • Index Cond: (plan_partie_id = tx_inva.plan_partie_id)
66. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx4fk_f_ptc_in_ptc_p (cost=0.00..4.32 rows=25 width=0) (actual rows= loops=)

  • Index Cond: (part_comm_dossier_id = tx_inva.part_comm_dossier_id)
67. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on coti_plan_part_comm (cost=69.30..73.32 rows=1 width=12) (actual rows= loops=)

  • Recheck Cond: ((part_comm_dossier_id = part_comm_plan_partie.part_comm_dossier_id) AND (plan_partie_id = part_comm_plan_partie.plan_partie_id))
  • Filter: (((type_part)::text = 'TOTA'::text) AND ((type_mnt)::text = 'TOTA'::text))
68. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=69.30..69.30 rows=1 width=0) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ptc_in_cot_p_fk (cost=0.00..30.42 rows=1,508 width=0) (actual rows= loops=)

  • Index Cond: (part_comm_dossier_id = part_comm_plan_partie.part_comm_dossier_id)
70. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on p_pi_in_cot_p_fk (cost=0.00..38.63 rows=2,108 width=0) (actual rows= loops=)

  • Index Cond: (plan_partie_id = part_comm_plan_partie.plan_partie_id)
71. 0.000 0.000 ↓ 0.0

Index Scan using p_pi_in_sal_fk on sal_plan_partie (cost=0.57..871.57 rows=31 width=10) (actual rows= loops=)

  • Index Cond: (plan_partie_id = plan_partie.plan_partie_id)
  • Filter: ((type_sal)::text = 'PRRE'::text)
72. 0.000 0.000 ↓ 0.0

Index Scan using p_pi_in_pag_fk on pa_gen (cost=0.57..1,438.47 rows=287 width=16) (actual rows= loops=)

  • Index Cond: (plan_partie_id = plan_partie.plan_partie_id)
  • Filter: (((etat)::text = 'VAL'::text) AND ((type_couv)::text = ANY ('{EPGN,DCON,RCON,REIN,RERT,RIG,RORF,RTRE}'::text[])))
73. 0.000 0.000 ↓ 0.0

Hash (cost=23.27..23.27 rows=727 width=13) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on couv (cost=0.00..23.27 rows=727 width=13) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.96 rows=64 width=23) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on type_couv (cost=0.00..1.64 rows=64 width=23) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Index Scan using pi_in_prestso_fk on prest_sortie (cost=0.43..26.12 rows=3 width=16) (actual rows= loops=)

  • Index Cond: (partie_dossier_id = partie_dossier.partie_dossier_id)
  • Filter: ((type_av_ap)::text = 'APRE'::text)
78. 0.000 0.000 ↓ 0.0

Index Scan using idx4fk_f_pag_in_pagprest on pa_gen_prest (cost=0.57..8.00 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (pa_gen_id = pa_gen.pa_gen_id)
79.          

SubPlan (forNested Loop)

80. 0.000 0.000 ↓ 0.0

Index Scan using idx_code_texte_code_cle_code_type on code_texte a (cost=0.28..8.30 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (((plan_partie.type_plan)::text = (code_cle)::text) AND ((code_type)::text = 'TypePlan'::text))
  • Filter: ((codet_langue)::text = 'FR'::text)