explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5aaw

Settings
# exclusive inclusive rows x rows loops node
1. 115.832 131,358.571 ↓ 57,597.5 115,195 1

Unique (cost=257,174.15..257,174.32 rows=2 width=1,260) (actual time=131,237.016..131,358.571 rows=115,195 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Buffers: shared hit=14758484 read=197552
  • I/O Timings: read=51495.405
2. 976.020 131,242.739 ↓ 57,597.5 115,195 1

Sort (cost=257,174.15..257,174.15 rows=2 width=1,260) (actual time=131,237.013..131,242.739 rows=115,195 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Sort Key: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Sort Method: quicksort Memory: 53749kB
  • Buffers: shared hit=14758484 read=197552
  • I/O Timings: read=51495.405
3. 8.427 130,266.719 ↓ 57,597.5 115,195 1

Append (cost=161,556.35..257,174.14 rows=2 width=1,260) (actual time=129,675.060..130,266.719 rows=115,195 loops=1)

  • Buffers: shared hit=14758469 read=197552
  • I/O Timings: read=51495.405
4. 115.628 129,797.146 ↓ 115,177.0 115,177 1

Unique (cost=161,556.35..161,556.44 rows=1 width=463) (actual time=129,675.059..129,797.146 rows=115,177 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Buffers: shared hit=14646733 read=197552
  • I/O Timings: read=51495.405
5. 156.087 129,681.518 ↓ 115,177.0 115,177 1

Sort (cost=161,556.35..161,556.36 rows=1 width=463) (actual time=129,675.056..129,681.518 rows=115,177 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Sort Key: bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier.dossier_id)), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Sort Method: quicksort Memory: 53739kB
  • Buffers: shared hit=14646733 read=197552
  • I/O Timings: read=51495.405
6. 284.222 129,525.431 ↓ 115,177.0 115,177 1

GroupAggregate (cost=161,556.24..161,556.34 rows=1 width=463) (actual time=129,218.546..129,525.431 rows=115,177 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_prod.pro_id, bi_prod.pro_desc_courte, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx, '0'::numeric)), sum(il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext), sum(COALESCE(il_dwh_pa_gen_valorisation.vas_inventaire, '0'::numeric)), count(DISTINCT il_dwh_dossier.dossier_id), il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Group Key: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_fam_prod.fam_id, bi_prod.pro_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Buffers: shared hit=14646733 read=197552
  • I/O Timings: read=51495.405
7. 762.035 129,241.209 ↓ 115,177.0 115,177 1

Sort (cost=161,556.24..161,556.24 rows=1 width=310) (actual time=129,218.478..129,241.209 rows=115,177 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_fam_prod.fam_id, bi_prod.pro_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, bi_soc.soc_desc_courte, bi_prod.pro_desc_courte, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire
  • Sort Key: bi_vcouv.tcl_desc_longue, bi_fam_prod.fam_id, bi_prod.pro_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva
  • Sort Method: quicksort Memory: 37495kB
  • Buffers: shared hit=14646733 read=197552
  • I/O Timings: read=51495.405
8. 119.104 128,479.174 ↓ 115,177.0 115,177 1

Nested Loop (cost=70,879.56..161,556.23 rows=1 width=310) (actual time=199.744..128,479.174 rows=115,177 loops=1)

  • Output: bi_soc.soc_id, bi_vcouv.tcl_desc_longue, bi_fam_prod.fam_id, bi_prod.pro_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_pa_gen.dte_effet_idx, il_dwh_dossier.statut, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.avt_ap_ter, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, bi_soc.soc_desc_courte, bi_prod.pro_desc_courte, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire
  • Buffers: shared hit=14646733 read=197552
  • I/O Timings: read=51495.405
9. 183.608 128,129.716 ↓ 115,177.0 115,177 1

Nested Loop (cost=70,879.28..161,553.72 rows=1 width=314) (actual time=199.694..128,129.716 rows=115,177 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, bi_prod.pro_id, bi_prod.pro_desc_courte, bi_fam_prod.fam_id, bi_soc.soc_id, bi_soc.soc_desc_courte, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=14301202 read=197552
  • I/O Timings: read=51495.405
10. 141.566 127,830.931 ↓ 115,177.0 115,177 1

Nested Loop (cost=70,879.28..161,552.62 rows=1 width=236) (actual time=199.682..127,830.931 rows=115,177 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, bi_prod.pro_id, bi_prod.pro_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.soc_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Inner Unique: true
  • Buffers: shared hit=14186025 read=197552
  • I/O Timings: read=51495.405
11. 186.922 127,537.286 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,879.14..161,551.32 rows=1 width=232) (actual time=199.661..127,537.286 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, bi_prod.pro_id, bi_prod.pro_desc_courte, bi_prod.fam_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Inner Unique: true
  • Buffers: shared hit=13881867 read=197552
  • I/O Timings: read=51495.405
12. 266.098 127,198.285 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,878.99..161,549.56 rows=1 width=207) (actual time=199.646..127,198.285 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, il_dwh_contrat.pro_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Join Filter: (il_dwh_pa_gen.dl_soc_id = il_dwh_contrat.dl_soc_id)
  • Buffers: shared hit=13577709 read=197552
  • I/O Timings: read=51495.405
13. 138.954 55,911.294 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,870.87..161,530.59 rows=1 width=227) (actual time=198.688..55,911.294 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=5720429 read=197552
  • I/O Timings: read=51495.405
14. 146.055 55,620.261 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,870.87..161,529.53 rows=1 width=231) (actual time=198.669..55,620.261 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=5568350 read=197552
  • I/O Timings: read=51495.405
15. 173.361 55,170.048 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,870.87..161,528.31 rows=1 width=231) (actual time=198.658..55,170.048 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=5416271 read=197552
  • I/O Timings: read=51495.405
16. 298.371 53,627.976 ↓ 152,079.0 152,079 1

Nested Loop (cost=70,870.43..161,524.84 rows=1 width=209) (actual time=198.595..53,627.976 rows=152,079 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.vas_inventaire, il_dwh_pa_gen_valorisation.dl_soc_id, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Buffers: shared hit=4656381 read=197061
  • I/O Timings: read=51309.835
17. 247.479 31,855.128 ↓ 265,117.0 265,117 1

Nested Loop Left Join (cost=70,869.86..161,492.42 rows=1 width=199) (actual time=195.824..31,855.128 rows=265,117 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.reduc_idx, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Buffers: shared hit=3147597 read=139355
  • I/O Timings: read=31754.910
18. 532.655 1,384.311 ↓ 265,117.0 265,117 1

Nested Loop (cost=70,869.29..161,487.51 rows=1 width=190) (actual time=195.769..1,384.311 rows=265,117 loops=1)

  • Output: bi_vcouv.tcl_desc_longue, bi_vcouv.cou_id, bi_vcouv.col_nom_couv_fr, bi_vcouv.cou_desc_courte, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Inner Unique: true
  • Buffers: shared hit=1650088 read=36533
  • I/O Timings: read=3558.542
19. 0.000 321.422 ↓ 265,117.0 265,117 1

Gather (cost=70,869.01..161,485.31 rows=1 width=113) (actual time=195.734..321.422 rows=265,117 loops=1)

  • Output: il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=854739 read=36531
  • I/O Timings: read=3557.873
20. 18.363 1,220.302 ↓ 53,023.0 53,023 5 / 5

Nested Loop (cost=69,869.01..160,485.21 rows=1 width=113) (actual time=179.687..1,220.302 rows=53,023 loops=5)

  • Output: il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Join Filter: (((il_dwh_pa_gen.type_couv)::text <> 'RCON'::text) OR (((il_dwh_partie_dossier.type_partie)::text <> 'PEDE'::text) AND ((il_dwh_pa_gen.avt_ap_ter)::text = 'APTE'::text) AND ((il_dwh_pa_gen.type_couv)::text = 'RCON'::text)))
  • Rows Removed by Join Filter: 112
  • Buffers: shared hit=854739 read=36531
  • I/O Timings: read=3557.873
  • Worker 0: actual time=174.938..783.610 rows=46445 loops=1
  • Buffers: shared hit=112153 read=5055
  • I/O Timings: read=429.914
  • Worker 1: actual time=179.958..4748.919 rows=217760 loops=1
  • Buffers: shared hit=739422 read=18777
  • I/O Timings: read=2770.784
  • Worker 2: actual time=176.802..183.808 rows=412 loops=1
  • Buffers: shared hit=1265 read=3818
  • I/O Timings: read=114.095
  • Worker 3: actual time=175.855..193.943 rows=482 loops=1
  • Buffers: shared hit=1623 read=4416
  • I/O Timings: read=122.638
21. 11.475 334.681 ↓ 1,971.0 9,855 5 / 5

Nested Loop (cost=69,868.44..158,817.89 rows=5 width=80) (actual time=179.492..334.681 rows=9,855 loops=5)

  • Output: il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.dl_datextract, il_dwh_plan_partie.dl_soc_id, il_dwh_plan_partie.dl_flg_bcl, il_dwh_plan_partie.dl_flg_inv, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Buffers: shared hit=304717 read=21344
  • I/O Timings: read=667.888
  • Worker 0: actual time=174.851..313.471 rows=5683 loops=1
  • Buffers: shared hit=30666 read=4326
  • I/O Timings: read=177.799
  • Worker 1: actual time=179.885..809.730 rows=43485 loops=1
  • Buffers: shared hit=272436 read=4340
  • I/O Timings: read=143.963
  • Worker 2: actual time=176.741..178.220 rows=47 loops=1
  • Buffers: shared hit=578 read=3814
  • I/O Timings: read=111.933
  • Worker 3: actual time=175.165..180.941 rows=59 loops=1
  • Buffers: shared hit=792 read=4399
  • I/O Timings: read=113.752
22. 20.608 214.284 ↓ 32.6 9,902 5 / 5

Parallel Hash Join (cost=69,868.00..155,982.05 rows=304 width=68) (actual time=179.430..214.284 rows=9,902 loops=5)

  • Output: il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.dl_datextract, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dl_flg_bcl, il_dwh_partie_dossier.dl_flg_inv, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.contrat_id
  • Hash Cond: ((il_dwh_partie_dossier.dl_soc_id = il_dwh_dossier.dl_soc_id) AND (il_dwh_partie_dossier.dossier_gen_id = il_dwh_dossier.dossier_gen_id))
  • Buffers: shared hit=6644 read=21129
  • I/O Timings: read=586.707
  • Worker 0: actual time=174.783..200.977 rows=5462 loops=1
  • Buffers: shared hit=1204 read=4170
  • I/O Timings: read=118.918
  • Worker 1: actual time=179.821..322.310 rows=43946 loops=1
  • Buffers: shared hit=4388 read=4281
  • I/O Timings: read=121.663
  • Worker 2: actual time=176.667..177.413 rows=42 loops=1
  • Buffers: shared hit=349 read=3814
  • I/O Timings: read=111.933
  • Worker 3: actual time=175.109..179.750 rows=58 loops=1
  • Buffers: shared hit=469 read=4399
  • I/O Timings: read=113.752
23. 22.454 23.525 ↑ 2.1 9,902 5 / 5

Parallel Bitmap Heap Scan on bidwh.il_dwh_partie_dossier (cost=1,509.37..87,463.18 rows=21,197 width=39) (actual time=8.760..23.525 rows=9,902 loops=5)

  • Output: il_dwh_partie_dossier.dl_datextract, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dl_flg_bcl, il_dwh_partie_dossier.dl_flg_inv, il_dwh_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.partie_liee_partie_dossier_id, il_dwh_partie_dossier.dossier_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_partie_dossier.no_gen_dossier, il_dwh_partie_dossier.numero_dossier, il_dwh_partie_dossier.mut_dossier_id, il_dwh_partie_dossier.mut_dossier_id_prec, il_dwh_partie_dossier.contrat_id, il_dwh_partie_dossier.numero_contrat, il_dwh_partie_dossier.partie_dossier_orig_id, il_dwh_partie_dossier.etat, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.description_type_partie, il_dwh_partie_dossier.type_deces, il_dwh_partie_dossier.description_type_deces, il_dwh_partie_dossier.dte_deb_partie, il_dwh_partie_dossier.dte_effet_idx, il_dwh_partie_dossier.avoir_50_ans, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.description_type_invalidite, il_dwh_partie_dossier.dte_deb_inca_gain, il_dwh_partie_dossier.age_deb_inca_gain, il_dwh_partie_dossier.dte_fin_inca_gain, il_dwh_partie_dossier.dte_reexamen, il_dwh_partie_dossier.tx_inca_gain_reel, il_dwh_partie_dossier.tx_inca_gain_reel_mvt, il_dwh_partie_dossier.tx_inca_gain_calc, il_dwh_partie_dossier.tx_inca_gain_calc_mvt, il_dwh_partie_dossier.lib_passee, il_dwh_partie_dossier.inva_passee, il_dwh_partie_dossier.rechute, il_dwh_partie_dossier.dte_rechute, il_dwh_partie_dossier.faute_grave, il_dwh_partie_dossier.tx_reduc_faute_grave, il_dwh_partie_dossier.reticence, il_dwh_partie_dossier.accident, il_dwh_partie_dossier.cause_medicale_sinistre, il_dwh_partie_dossier.cause_risque_special_sinistre, il_dwh_partie_dossier.sal_actif_reparti, il_dwh_partie_dossier.delai_attente_lib_consomme, il_dwh_partie_dossier.delai_attente_ri_consomme, il_dwh_partie_dossier.dte_age_terme, il_dwh_partie_dossier.dte_deb_mise_en_pension, il_dwh_partie_dossier.tx_retraite, il_dwh_partie_dossier.suri_avec_pe, il_dwh_partie_dossier.suri_avec_idx, il_dwh_partie_dossier.dte_revision_ai, il_dwh_partie_dossier.dte_revision_caisse_pension, il_dwh_partie_dossier.tx_sal_suri, il_dwh_partie_dossier.tot_rev_recu, il_dwh_partie_dossier.reprise_ancienne_ip, il_dwh_partie_dossier.deb_paie_effectif, il_dwh_partie_dossier.sal_ann_presume_perdu, il_dwh_partie_dossier.dn_mut_id, il_dwh_partie_dossier.dn_dte_mut, il_dwh_partie_dossier.dn_nom_mut, il_dwh_partie_dossier.pro_id, il_dwh_partie_dossier.dte_echeance_partie, il_dwh_partie_dossier.calc_av_terme, il_dwh_partie_dossier.mnt_cpt_droits_transitoires, il_dwh_partie_dossier.int_cpt_droits_transitoires, il_dwh_partie_dossier.cd_mnt_cpt_droits_transitoires_fin_ann, il_dwh_partie_dossier.cd_mnt_cpt_droits_transitoires_dat_ext, il_dwh_partie_dossier.cd_int_cpt_droits_transitoires_fin_ann, il_dwh_partie_dossier.cd_int_cpt_droits_transitoires_dat_ext, il_dwh_partie_dossier.somme_risquee_reass_deces, il_dwh_partie_dossier.somme_risquee_reass_inva, il_dwh_partie_dossier.annonce_reass_deces, il_dwh_partie_dossier.annonce_reass_inva, il_dwh_partie_dossier.annonce_reass_deces_facultatif, il_dwh_partie_dossier.annonce_reass_inva_facultatif, il_dwh_partie_dossier.annonce_reass_deces_sinistre, il_dwh_partie_dossier.annonce_reass_inva_sinistre, il_dwh_partie_dossier.reass_deces, il_dwh_partie_dossier.reass_inva, il_dwh_partie_dossier.inva_proportion_reassuree, il_dwh_partie_dossier.fo_reass_id, il_dwh_partie_dossier.annonce_deces, il_dwh_partie_dossier.annonce_invalidite, il_dwh_partie_dossier.usr_log_i, il_dwh_partie_dossier.dte_log_i, il_dwh_partie_dossier.usr_log_u, il_dwh_partie_dossier.dte_log_u
  • Recheck Cond: ((il_dwh_partie_dossier.dl_datextract = '2019-12-31'::date) AND (il_dwh_partie_dossier.dl_flg_bcl = 1) AND (il_dwh_partie_dossier.dl_flg_inv = 1))
  • Filter: ((il_dwh_partie_dossier.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 2228
  • Heap Blocks: exact=4
  • Buffers: shared hit=5535 read=36
  • I/O Timings: read=4.598
  • Worker 0: actual time=5.852..21.181 rows=5462 loops=1
  • Buffers: shared hit=971
  • Worker 1: actual time=11.366..64.811 rows=43946 loops=1
  • Buffers: shared hit=4174 read=36
  • I/O Timings: read=4.598
  • Worker 2: actual time=8.949..9.559 rows=42 loops=1
  • Buffers: shared hit=145
  • Worker 3: actual time=6.220..10.654 rows=58 loops=1
  • Buffers: shared hit=241
24. 1.071 1.071 ↑ 1.5 60,651 1 / 5

Bitmap Index Scan on olm_il_dwh_partie_dossier_date_dl_flg_bcl_dl_flg_inv (cost=0.00..1,488.17 rows=88,747 width=0) (actual time=5.353..5.353 rows=60,651 loops=1)

  • Index Cond: ((il_dwh_partie_dossier.dl_datextract = '2019-12-31'::date) AND (il_dwh_partie_dossier.dl_flg_bcl = 1) AND (il_dwh_partie_dossier.dl_flg_inv = 1))
  • Buffers: shared hit=236
  • Worker 3: actual time=5.353..5.353 rows=60651 loops=1
  • Buffers: shared hit=236
25. 6.660 170.151 ↑ 2.8 11,849 5 / 5

Parallel Hash (cost=67,855.83..67,855.83 rows=33,520 width=37) (actual time=170.150..170.151 rows=11,849 loops=5)

  • Output: il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.dossier_gen_id, il_dwh_dossier.contrat_id
  • Buckets: 131072 Batches: 1 Memory Usage: 5280kB
  • Buffers: shared hit=933 read=21093
  • I/O Timings: read=582.110
  • Worker 0: actual time=167.946..167.946 rows=11745 loops=1
  • Buffers: shared hit=189 read=4170
  • I/O Timings: read=118.918
  • Worker 1: actual time=168.230..168.231 rows=11583 loops=1
  • Buffers: shared hit=170 read=4245
  • I/O Timings: read=117.065
  • Worker 2: actual time=167.489..167.490 rows=10179 loops=1
  • Buffers: shared hit=160 read=3814
  • I/O Timings: read=111.933
  • Worker 3: actual time=168.676..168.676 rows=12006 loops=1
  • Buffers: shared hit=184 read=4399
  • I/O Timings: read=113.752
26. 163.491 163.491 ↑ 2.8 11,849 5 / 5

Parallel Index Scan using olm_il_dwh_dossier_date_dl_flg_bcl_dl_flg_inv on bidwh.il_dwh_dossier (cost=0.43..67,855.83 rows=33,520 width=37) (actual time=0.446..163.491 rows=11,849 loops=5)

  • Output: il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.statut, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.dl_flg_inv, il_dwh_dossier.dossier_gen_id, il_dwh_dossier.contrat_id
  • Index Cond: ((il_dwh_dossier.dl_datextract = '2019-12-31'::date) AND (il_dwh_dossier.dl_flg_bcl = 1) AND (il_dwh_dossier.dl_flg_inv = 1))
  • Buffers: shared hit=933 read=21093
  • I/O Timings: read=582.110
  • Worker 0: actual time=0.074..161.040 rows=11745 loops=1
  • Buffers: shared hit=189 read=4170
  • I/O Timings: read=118.918
  • Worker 1: actual time=0.074..161.371 rows=11583 loops=1
  • Buffers: shared hit=170 read=4245
  • I/O Timings: read=117.065
  • Worker 2: actual time=0.116..161.812 rows=10179 loops=1
  • Buffers: shared hit=160 read=3814
  • I/O Timings: read=111.933
  • Worker 3: actual time=0.070..161.602 rows=12006 loops=1
  • Buffers: shared hit=184 read=4399
  • I/O Timings: read=113.752
27. 108.922 108.922 ↑ 1.0 1 49,510 / 5

Index Scan using olm_il_dwh_plan_partie_partie_dossier_id on bidwh.il_dwh_plan_partie (cost=0.44..9.32 rows=1 width=28) (actual time=0.010..0.011 rows=1 loops=49,510)

  • Output: il_dwh_plan_partie.dl_datextract, il_dwh_plan_partie.dl_soc_id, il_dwh_plan_partie.dl_flg_bcl, il_dwh_plan_partie.dl_flg_inv, il_dwh_plan_partie.dossier_id, il_dwh_plan_partie.dossier_gen_id, il_dwh_plan_partie.no_gen_dossier, il_dwh_plan_partie.numero_dossier, il_dwh_plan_partie.mut_dossier_id, il_dwh_plan_partie.contrat_id, il_dwh_plan_partie.numero_contrat, il_dwh_plan_partie.plan_partie_id, il_dwh_plan_partie.partie_dossier_id, il_dwh_plan_partie.plan_employeur_affilie_id, il_dwh_plan_partie.plan_partie_orig_id, il_dwh_plan_partie.plan_base_id, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.nom_comm, il_dwh_plan_partie.type_plan, il_dwh_plan_partie.description_type_plan, il_dwh_plan_partie.type_prev_lpp, il_dwh_plan_partie.description_type_prevoyance_lp, il_dwh_plan_partie.etat, il_dwh_plan_partie.deduc_1, il_dwh_plan_partie.deduc_2, il_dwh_plan_partie.mnt_capital_retraite, il_dwh_plan_partie.prct_capital_retraite, il_dwh_plan_partie.mnt_rte_retraite, il_dwh_plan_partie.prct_rte_retraite, il_dwh_plan_partie.corrections_manuelles, il_dwh_plan_partie.dte_effet, il_dwh_plan_partie.recalcul_prest_av_terme, il_dwh_plan_partie.figer_recalcul_prest_av_terme, il_dwh_plan_partie.propres_coti, il_dwh_plan_partie.int_propres_coti, il_dwh_plan_partie.propres_coti_prov, il_dwh_plan_partie.propres_coti_majorees, il_dwh_plan_partie.rachats, il_dwh_plan_partie.int_rachats, il_dwh_plan_partie.avoir_transfere, il_dwh_plan_partie.freq_paie_coti, il_dwh_plan_partie.description_frequence_paiement, il_dwh_plan_partie.surp_paie_frac_inclus, il_dwh_plan_partie.valorisable, il_dwh_plan_partie.dte_entree_theorique, il_dwh_plan_partie.dte_entree_av_va, il_dwh_plan_partie.dte_prevue_deb_inva, il_dwh_plan_partie.deduc_gar, il_dwh_plan_partie.deduc_gar_precedent, il_dwh_plan_partie.sal_cotisant_garanti, il_dwh_plan_partie.sal_cotisant_garanti_precedent, il_dwh_plan_partie.propres_coti_ord, il_dwh_plan_partie.propres_coti_maintien, il_dwh_plan_partie.part_suppltemp_rachats, il_dwh_plan_partie.rachats_sans_int, il_dwh_plan_partie.prest_sortie_gar_base, il_dwh_plan_partie.prop_coti_ord_prestsortgar, il_dwh_plan_partie.prop_coti_maint_prestsortgar, il_dwh_plan_partie.prop_coti_maj_prestsortgar, il_dwh_plan_partie.rachats_prestsortgar, il_dwh_plan_partie.vers_anticipes_prestsortgar, il_dwh_plan_partie.prct_capital_lpp_retraite, il_dwh_plan_partie.prest_payees, il_dwh_plan_partie.freq_paie_coti_figee, il_dwh_plan_partie.rachats_prevus, il_dwh_plan_partie.propo_rachats_id, il_dwh_plan_partie.rachat_unique_max, il_dwh_plan_partie.capital_retraite_max, il_dwh_plan_partie.rte_retraite_max, il_dwh_plan_partie.rachat_mens_max, il_dwh_plan_partie.rachat_unique_saisi, il_dwh_plan_partie.capital_retraite_rachat_unique, il_dwh_plan_partie.rte_retraite_rachat_unique, il_dwh_plan_partie.rachat_mens_saisi, il_dwh_plan_partie.capital_retraite_rachat_mens, il_dwh_plan_partie.rte_retraite_rachat_mens, il_dwh_plan_partie.capital_retraite_souhaite, il_dwh_plan_partie.rte_retraite_shtee, il_dwh_plan_partie.rachat_unique_retraite_shtee, il_dwh_plan_partie.rachat_mens_retraite_shtee, il_dwh_plan_partie.dn_mut_id, il_dwh_plan_partie.dn_dte_mut, il_dwh_plan_partie.dn_nom_mut, il_dwh_plan_partie.pro_id, il_dwh_plan_partie.part_suppltemp_rachat_prevu, il_dwh_plan_partie.rachat_prevu_prestsortgar, il_dwh_plan_partie.corrections_manuelles_primes, il_dwh_plan_partie.corrections_manuelles_prest, il_dwh_plan_partie.remb_va_prevus, il_dwh_plan_partie.part_suppltemp_remb_va_prevus, il_dwh_plan_partie.remb_va_prevus_prestsortgar, il_dwh_plan_partie.mnt_max_deb_ajournement, il_dwh_plan_partie.remb_va_a_recevoir, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.max_rachetable_31_12, il_dwh_plan_partie.sal_min_1, il_dwh_plan_partie.sal_min_2, il_dwh_plan_partie.sal_max_1, il_dwh_plan_partie.sal_max_2, il_dwh_plan_partie.duree_gar_sal_retraite, il_dwh_plan_partie.duree_gar_sal_risque, il_dwh_plan_partie.duree_gar_sal_sortie, il_dwh_plan_partie.capital_retraite, il_dwh_plan_partie.correction_ps_rappel, il_dwh_plan_partie.plan_calcul_avant_terme, il_dwh_plan_partie.prct_cpt_pref_retraite, il_dwh_plan_partie.prct_cpt_exc_lp, il_dwh_plan_partie.pref_retraite, il_dwh_plan_partie.int_pref_retraite, il_dwh_plan_partie.excedents_lp, il_dwh_plan_partie.int_excedents_lp, il_dwh_plan_partie.usr_log_i, il_dwh_plan_partie.dte_log_i, il_dwh_plan_partie.usr_log_u, il_dwh_plan_partie.dte_log_u, il_dwh_plan_partie.cd_propres_coti_majorees_dat_ext, il_dwh_plan_partie.cd_propres_coti_majorees_fin_ann, il_dwh_plan_partie.cd_int_rachats_dat_ext, il_dwh_plan_partie.cd_int_rachats_fin_ann, il_dwh_plan_partie.cd_int_pref_retraite_dat_ext, il_dwh_plan_partie.cd_int_pref_retraite_fin_ann, il_dwh_plan_partie.cd_int_excedents_lp_dat_ext, il_dwh_plan_partie.cd_int_excedents_lp_fin_ann
  • Index Cond: (il_dwh_plan_partie.partie_dossier_id = il_dwh_partie_dossier.partie_dossier_id)
  • Filter: (((il_dwh_plan_partie.type_plan)::text <> 'MLPP'::text) AND (il_dwh_plan_partie.dl_datextract = '2019-12-31'::date) AND (il_dwh_plan_partie.dl_flg_inv = 1) AND (il_dwh_plan_partie.dl_flg_bcl = 1) AND (il_dwh_partie_dossier.dl_soc_id = il_dwh_plan_partie.dl_soc_id))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=298073 read=215
  • I/O Timings: read=81.181
  • Worker 0: actual time=0.019..0.020 rows=1 loops=5462
  • Buffers: shared hit=29462 read=156
  • I/O Timings: read=58.881
  • Worker 1: actual time=0.009..0.010 rows=1 loops=43946
  • Buffers: shared hit=268048 read=59
  • I/O Timings: read=22.300
  • Worker 2: actual time=0.018..0.018 rows=1 loops=42
  • Buffers: shared hit=229
  • Worker 3: actual time=0.019..0.019 rows=1 loops=58
  • Buffers: shared hit=323
28. 867.258 867.258 ↓ 5.0 5 49,276 / 5

Index Scan using olm_il_dwh_pa_gen_plan_partie_id on bidwh.il_dwh_pa_gen (cost=0.57..333.45 rows=1 width=49) (actual time=0.040..0.088 rows=5 loops=49,276)

  • Output: il_dwh_pa_gen.dl_datextract, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen.dl_flg_bcl, il_dwh_pa_gen.dl_flg_inv, il_dwh_pa_gen.pa_gen_id, il_dwh_pa_gen.dossier_id, il_dwh_pa_gen.dossier_gen_id, il_dwh_pa_gen.no_gen_dossier, il_dwh_pa_gen.numero_dossier, il_dwh_pa_gen.mut_dossier_id, il_dwh_pa_gen.mut_dossier_id_prec, il_dwh_pa_gen.contrat_id, il_dwh_pa_gen.numero_contrat, il_dwh_pa_gen.assure_part_dossier_id, il_dwh_pa_gen.vco_id, il_dwh_pa_gen.cou_id, il_dwh_pa_gen.desc_crt_pa, il_dwh_pa_gen.plan_partie_id, il_dwh_pa_gen.pa_gen_orig_id, il_dwh_pa_gen.pa_gen_base_prest_id, il_dwh_pa_gen.pa_gen_base_coti_id, il_dwh_pa_gen.pa_gen_base_plan_base_prest_id, il_dwh_pa_gen.pa_gen_base_plan_base_coti_id, il_dwh_pa_gen.dn_nom_plan, il_dwh_pa_gen.dn_plan_base_id, il_dwh_pa_gen.dn_plan_partie_orig_id, il_dwh_pa_gen.partie_dossier_id, il_dwh_pa_gen.type_plan, il_dwh_pa_gen.description_type_plan, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.description_type_couv, il_dwh_pa_gen.etat, il_dwh_pa_gen.description_etat_pa_gen, il_dwh_pa_gen.dte_effet, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.dte_effet_pe, il_dwh_pa_gen.dte_effet_idx, il_dwh_pa_gen.dte_base_calc, il_dwh_pa_gen.dte_fin_differe, il_dwh_pa_gen.dte_deb_inca_gain, il_dwh_pa_gen.age_deb_inca_gain, il_dwh_pa_gen.delai_attente, il_dwh_pa_gen.capital_rte, il_dwh_pa_gen.description_capital_rente, il_dwh_pa_gen.doublement_rte_enfant, il_dwh_pa_gen.freq_paie_rte, il_dwh_pa_gen.description_frequence_paiement, il_dwh_pa_gen.sal, il_dwh_pa_gen.classe_risque, il_dwh_pa_gen.tx_prest_base, il_dwh_pa_gen.dte_derniere_idx_liee_avs, il_dwh_pa_gen.numero_ub_refact_base, il_dwh_pa_gen.numero_ub_refact_idx, il_dwh_pa_gen.no_var, il_dwh_pa_gen.nom_var, il_dwh_pa_gen.dte_deb, il_dwh_pa_gen.dte_fin, il_dwh_pa_gen.tx_int_technique, il_dwh_pa_gen.alpha1, il_dwh_pa_gen.alpha2, il_dwh_pa_gen.alpha3, il_dwh_pa_gen.beta, il_dwh_pa_gen.gamma1, il_dwh_pa_gen.gamma2, il_dwh_pa_gen.sigma, il_dwh_pa_gen.constante1, il_dwh_pa_gen.constante2, il_dwh_pa_gen.constante_pri_pure, il_dwh_pa_gen.penalite_rachat, il_dwh_pa_gen.maj_prest, il_dwh_pa_gen.utilisation_tbl_maj, il_dwh_pa_gen.duree_reduc_paie_pri, il_dwh_pa_gen.duree_gar_differe, il_dwh_pa_gen.age_terme_idx, il_dwh_pa_gen.dn_mut_id, il_dwh_pa_gen.dn_dte_mut, il_dwh_pa_gen.dn_nom_mut, il_dwh_pa_gen.pro_id, il_dwh_pa_gen.nom_ou_raison_sociale, il_dwh_pa_gen.prenom, il_dwh_pa_gen.dte_naissance, il_dwh_pa_gen.sexe, il_dwh_pa_gen.usr_log_i, il_dwh_pa_gen.dte_log_i, il_dwh_pa_gen.usr_log_u, il_dwh_pa_gen.dte_log_u, il_dwh_pa_gen.avt_ap_ter, il_dwh_pa_gen.type_fina_avance_avs, il_dwh_pa_gen.prct_avance_avs, il_dwh_pa_gen.type_numerando, il_dwh_pa_gen.vco_precedente_id, il_dwh_pa_gen.categorie_empl_secteur_act_id, il_dwh_pa_gen.secteur_act_origine_id
  • Index Cond: (il_dwh_pa_gen.plan_partie_id = il_dwh_plan_partie.plan_partie_id)
  • Filter: ((il_dwh_pa_gen.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen.dl_flg_inv = 1) AND (il_dwh_pa_gen.dl_flg_bcl = 1) AND ((il_dwh_pa_gen.etat)::text = 'VAL'::text) AND (il_dwh_plan_partie.dl_soc_id = il_dwh_pa_gen.dl_soc_id) AND (((il_dwh_pa_gen.type_couv)::text <> 'RCON'::text) OR (((il_dwh_pa_gen.avt_ap_ter)::text = 'APTE'::text) AND ((il_dwh_pa_gen.type_couv)::text = 'RCON'::text))))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=550022 read=15187
  • I/O Timings: read=2889.985
  • Worker 0: actual time=0.054..0.080 rows=8 loops=5683
  • Buffers: shared hit=81487 read=729
  • I/O Timings: read=252.114
  • Worker 1: actual time=0.038..0.089 rows=5 loops=43485
  • Buffers: shared hit=466986 read=14437
  • I/O Timings: read=2626.821
  • Worker 2: actual time=0.074..0.116 rows=9 loops=47
  • Buffers: shared hit=687 read=4
  • I/O Timings: read=2.163
  • Worker 3: actual time=0.110..0.217 rows=8 loops=59
  • Buffers: shared hit=831 read=17
  • I/O Timings: read=8.886
29. 530.234 530.234 ↑ 1.0 1 265,117

Index Scan using bi_vcouv_pkey on biinfra.bi_vcouv (cost=0.28..2.20 rows=1 width=81) (actual time=0.002..0.002 rows=1 loops=265,117)

  • Output: bi_vcouv.vco_id, bi_vcouv.vco_no_vari, bi_vcouv.tde1x_nom_table, bi_vcouv.tde1y_nom_table, bi_vcouv.tde2x_nom_table, bi_vcouv.tde2y_nom_table, bi_vcouv.tin1x_nom_table, bi_vcouv.tin1y_nom_table, bi_vcouv.tin_p1x_nom_table, bi_vcouv.tin_p1y_nom_table, bi_vcouv.tma1x_nom_table, bi_vcouv.tma1y_nom_table, bi_vcouv.tca_d1x_nom_table, bi_vcouv.tca_d1y_nom_table, bi_vcouv.tca_i1x_nom_table, bi_vcouv.tca_i1y_nom_table, bi_vcouv.tcc_id, bi_vcouv.vco_dte_deb, bi_vcouv.vco_dte_fin, bi_vcouv.tx_interet_tec, bi_vcouv.alpha1, bi_vcouv.alpha2, bi_vcouv.alpha3, bi_vcouv.beta, bi_vcouv.gamma1, bi_vcouv.gamma2, bi_vcouv.sigma, bi_vcouv.cste1, bi_vcouv.cste2, bi_vcouv.cste_pri_pure, bi_vcouv.penalite_rachat, bi_vcouv.maj_prest, bi_vcouv.use_table_maj, bi_vcouv.duree_red_paie_pri, bi_vcouv.fo_vap, bi_vcouv.fo_vas, bi_vcouv.fo_alpha, bi_vcouv.fo_age_a1_n_aff, bi_vcouv.fo_age_a2_n_aff, bi_vcouv.fo_age_couple_n_aff, bi_vcouv.fo_age_a1_tran, bi_vcouv.fo_age_a2_tran, bi_vcouv.fo_age_couple_tran, bi_vcouv.fo_age_a1_val, bi_vcouv.fo_age_a2_val, bi_vcouv.fo_age_couple_val, bi_vcouv.fo_rachat, bi_vcouv.fo_pri, bi_vcouv.fo_duree_restit, bi_vcouv.fo_interpol, bi_vcouv.fo_deces, bi_vcouv.fo_invalidite, bi_vcouv.fo_echeance, bi_vcouv.fo_prest_subord, bi_vcouv.fo_reserve, bi_vcouv.fo_som_risq_deces_int, bi_vcouv.fo_som_risq_invalid_int, bi_vcouv.fo_som_risq_deces_reass, bi_vcouv.fo_som_risq_invalidite_reass, bi_vcouv.fo_pe1, bi_vcouv.fo_pe2, bi_vcouv.fo_ajournement, bi_vcouv.fo_fin_paiement, bi_vcouv.fo_pri_pure, bi_vcouv.fo_bonus_calcul, bi_vcouv.fo_report_prime, bi_vcouv.fo_report_prest, bi_vcouv.fo_zillmer, bi_vcouv.fo_alpha_zillmer, bi_vcouv.fo_prestation_minimale, bi_vcouv.fo_prestation_pe, bi_vcouv.duree_garantie_differe, bi_vcouv.age_terme_indexation, bi_vcouv.type_calcul_indexation, bi_vcouv.fo_age_terme_indexation, bi_vcouv.fo_somme_risquee, bi_vcouv.fo_va, bi_vcouv.fo_rente_en_capital, bi_vcouv.fo_prest_compretravan, bi_vcouv.fo_capital_compretravan, bi_vcouv.prest_a_facturer, bi_vcouv.tarif_classe_risque, bi_vcouv.type_var_cou, bi_vcouv.vco_standard_id, bi_vcouv.fo_val_residuelle, bi_vcouv.fo_rachat_fiscal, bi_vcouv.fo_capital_fin_differe, bi_vcouv.fo_valeur_fonds_placement, bi_vcouv.alpha4, bi_vcouv.cou_id, bi_vcouv.soc_id, bi_vcouv.cou_desc_courte, bi_vcouv.cou_type_calc_pri, bi_vcouv.cou_type_calc_prest, bi_vcouv.cou_type_pror_fina_rte, bi_vcouv.cou_type_pror_init_rte, bi_vcouv.cou_type_pri, bi_vcouv.cou_type_int, bi_vcouv.cou_type_echeance_prest, bi_vcouv.cou_arrondi_pri, bi_vcouv.cou_arrondi_prest, bi_vcouv.cou_arrondi_res, bi_vcouv.cou_soumis_drt_timbre, bi_vcouv.cou_type_prestation, bi_vcouv.cou_fo_remplissage_date, bi_vcouv.cou_fo_remplissage_date_age, bi_vcouv.cou_type_mal_acc, bi_vcouv.cou_ged_definition1, bi_vcouv.cou_ged_definition2, bi_vcouv.cou_type_prest_2p, bi_vcouv.cou_avant_apres_terme, bi_vcouv.cou_inclus_suri, bi_vcouv.cou_type_rte_conjoint, bi_vcouv.cou_type_arrondi_prest, bi_vcouv.cou_nombre_tetes, bi_vcouv.cou_ordre_tri, bi_vcouv.col_nom_couv_fr, bi_vcouv.col_nom_pour_gest_fr, bi_vcouv.col_desc_longue_fr, bi_vcouv.col_nom_commercial_fr, bi_vcouv.act_nom_couv, bi_vcouv.act_nom_type_couv, bi_vcouv.act_nom_cat_couv, bi_vcouv.act_nom_cta, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.tco_type_sal, bi_vcouv.tco_type_risque, bi_vcouv.tco_ctrl_min_lpp, bi_vcouv.tco_regroupement_envoi_prestations, bi_vcouv.tco_rente_en_cours, bi_vcouv.tco_complement_avs, bi_vcouv.tco_prest_compl, bi_vcouv.tco_tri_couv_avant_terme, bi_vcouv.tco_tri_couv_apres_terme, bi_vcouv.tcl_desc_longue, bi_vcouv.cta_id, bi_vcouv.cta_nom_fr, bi_vcouv.pec_id, bi_vcouv.pec_nom, bi_vcouv.pcc_id, bi_vcouv.pcc_dte_deb, bi_vcouv.pcc_dte_fin, bi_vcouv.pcc_nom, bi_vcouv.pcc_delai_car1, bi_vcouv.pcc_delai_car2, bi_vcouv.ccc_cca_dte_deb, bi_vcouv.ccc_cca_dte_fin, bi_vcouv.cca_id, bi_vcouv.cca_ref_cca, bi_vcouv.cca_description, bi_vcouv.cca_doc_url, bi_vcouv.ccl_nom_commercial_cca_fr, bi_vcouv.ccl_doc_name_cca_fr
  • Index Cond: (bi_vcouv.vco_id = il_dwh_pa_gen.vco_id)
  • Buffers: shared hit=795349 read=2
  • I/O Timings: read=0.669
30. 30,223.338 30,223.338 ↑ 1.0 1 265,117

Index Scan using olm_il_dwh_pa_gen_prest_pa_gen_id on bidwh.il_dwh_pa_gen_prest (cost=0.57..4.89 rows=1 width=29) (actual time=0.114..0.114 rows=1 loops=265,117)

  • Output: il_dwh_pa_gen_prest.dl_datextract, il_dwh_pa_gen_prest.dl_soc_id, il_dwh_pa_gen_prest.dl_flg_bcl, il_dwh_pa_gen_prest.dl_flg_inv, il_dwh_pa_gen_prest.pa_gen_prest_id, il_dwh_pa_gen_prest.pa_gen_id, il_dwh_pa_gen_prest.pa_gen_orig_id, il_dwh_pa_gen_prest.dossier_id, il_dwh_pa_gen_prest.no_gen_dossier, il_dwh_pa_gen_prest.dossier_gen_id, il_dwh_pa_gen_prest.contrat_id, il_dwh_pa_gen_prest.corrections_manuelles, il_dwh_pa_gen_prest.prest_reglementaire, il_dwh_pa_gen_prest.prest_reglementaire_mvt, il_dwh_pa_gen_prest.prest_base, il_dwh_pa_gen_prest.prest_base_mvt, il_dwh_pa_gen_prest.supplement_lpp, il_dwh_pa_gen_prest.supplement_lpp_mvt, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.prest_tot_mvt, il_dwh_pa_gen_prest.avoir_31_12, il_dwh_pa_gen_prest.avoir_31_12_mvt, il_dwh_pa_gen_prest.prest_projetee_avec_int, il_dwh_pa_gen_prest.prest_projetee_sans_int, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_mvt, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.participation_excedents_mvt, il_dwh_pa_gen_prest.reduc_base, il_dwh_pa_gen_prest.reduc_base_mvt, il_dwh_pa_gen_prest.reduc_supplement_lpp, il_dwh_pa_gen_prest.reduc_supplement_lpp_mvt, il_dwh_pa_gen_prest.reduc_idx, il_dwh_pa_gen_prest.reduc_idx_mvt, il_dwh_pa_gen_prest.reduc_participation_excedents, il_dwh_pa_gen_prest.reduc_participation_excedents_mvt, il_dwh_pa_gen_prest.supplement_propres_coti, il_dwh_pa_gen_prest.supplement_propres_coti_mvt, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_prest.prest_due_mvt, il_dwh_pa_gen_prest.prest_reversee_par_reass, il_dwh_pa_gen_prest.prest_reversee_par_reass_mvt, il_dwh_pa_gen_prest.allocation_menage, il_dwh_pa_gen_prest.allocation_menage_mvt, il_dwh_pa_gen_prest.supplement_droits_acquis, il_dwh_pa_gen_prest.supplement_droits_acquis_mvt, il_dwh_pa_gen_prest.compens_retraite_avancee, il_dwh_pa_gen_prest.compens_retraite_avancee_mvt, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_capi_mvt, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.idx_employeur_fine_perio_mvt, il_dwh_pa_gen_prest.supplement_prest_sortie_gar, il_dwh_pa_gen_prest.supplement_prest_sortie_gar_mvt, il_dwh_pa_gen_prest.reduc_allocation_menage, il_dwh_pa_gen_prest.reduc_allocation_menage_mvt, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio_mvt, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_capi, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_capi_mvt, il_dwh_pa_gen_prest.reduc_supplement_droits_acquis, il_dwh_pa_gen_prest.reduc_supplement_droits_acquis_mvt, il_dwh_pa_gen_prest.dn_mut_id, il_dwh_pa_gen_prest.dn_dte_mut, il_dwh_pa_gen_prest.dn_nom_mut, il_dwh_pa_gen_prest.dn_plan_partie_id, il_dwh_pa_gen_prest.dn_partie_dossier_id, il_dwh_pa_gen_prest.dn_plan_base_id, il_dwh_pa_gen_prest.dn_plan_partie_orig_id, il_dwh_pa_gen_prest.dn_vco_id, il_dwh_pa_gen_prest.dn_nom_plan, il_dwh_pa_gen_prest.dn_type_plan, il_dwh_pa_gen_prest.description_type_plan, il_dwh_pa_gen_prest.mut_dossier_id, il_dwh_pa_gen_prest.mut_dossier_id_prec, il_dwh_pa_gen_prest.usr_log_i, il_dwh_pa_gen_prest.dte_log_i, il_dwh_pa_gen_prest.usr_log_u, il_dwh_pa_gen_prest.dte_log_u, il_dwh_pa_gen_prest.complement_fine_employeur, il_dwh_pa_gen_prest.reduc_compl_fine_employeur, il_dwh_pa_gen_prest.reduc_compens_retraite_avancee, il_dwh_pa_gen_prest.prest_gar, il_dwh_pa_gen_prest.supplement_garanti, il_dwh_pa_gen_prest.reduc_supplement_garanti, il_dwh_pa_gen_prest.compl_exc_libre_passage
  • Index Cond: (il_dwh_pa_gen_prest.pa_gen_id = il_dwh_pa_gen.pa_gen_id)
  • Filter: ((il_dwh_pa_gen_prest.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen_prest.dl_flg_bcl = 1) AND (il_dwh_pa_gen_prest.dl_flg_inv = 1) AND (il_dwh_pa_gen_prest.dl_datextract = il_dwh_pa_gen.dl_datextract) AND (il_dwh_pa_gen_prest.dl_flg_bcl = il_dwh_pa_gen.dl_flg_bcl) AND (il_dwh_pa_gen_prest.dl_flg_inv = il_dwh_pa_gen.dl_flg_inv) AND (il_dwh_pa_gen_prest.dl_soc_id = il_dwh_pa_gen.dl_soc_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1497509 read=102822
  • I/O Timings: read=28196.368
31. 21,474.477 21,474.477 ↑ 1.0 1 265,117

Index Scan using olm_il_dwh_pa_gen_valorisation_pa_gen_id on bidwh.il_dwh_pa_gen_valorisation (cost=0.57..32.41 rows=1 width=26) (actual time=0.081..0.081 rows=1 loops=265,117)

  • Output: il_dwh_pa_gen_valorisation.dl_datextract, il_dwh_pa_gen_valorisation.dl_soc_id, il_dwh_pa_gen_valorisation.dl_flg_bcl, il_dwh_pa_gen_valorisation.dl_flg_inv, il_dwh_pa_gen_valorisation.pa_gen_valorisation_id, il_dwh_pa_gen_valorisation.pa_gen_id, il_dwh_pa_gen_valorisation.pa_gen_orig_id, il_dwh_pa_gen_valorisation.dossier_id, il_dwh_pa_gen_valorisation.no_gen_dossier, il_dwh_pa_gen_valorisation.dossier_gen_id, il_dwh_pa_gen_valorisation.contrat_id, il_dwh_pa_gen_valorisation.type_av_ap, il_dwh_pa_gen_valorisation.description_type_avant_apres, il_dwh_pa_gen_valorisation.type_plan, il_dwh_pa_gen_valorisation.description_type_plan, il_dwh_pa_gen_valorisation.rev_commerciale, il_dwh_pa_gen_valorisation.rev_commerciale_mvt, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_fin_ann, il_dwh_pa_gen_valorisation.rev_inventaire, il_dwh_pa_gen_valorisation.rev_inventaire_mvt, il_dwh_pa_gen_valorisation.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_inventaire_fin_ann, il_dwh_pa_gen_valorisation.rev_pure, il_dwh_pa_gen_valorisation.rev_pure_mvt, il_dwh_pa_gen_valorisation.cd_rev_pure_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_pure_fin_ann, il_dwh_pa_gen_valorisation.val_deces, il_dwh_pa_gen_valorisation.val_deces_mvt, il_dwh_pa_gen_valorisation.val_inva, il_dwh_pa_gen_valorisation.val_inva_mvt, il_dwh_pa_gen_valorisation.val_vie, il_dwh_pa_gen_valorisation.val_vie_mvt, il_dwh_pa_gen_valorisation.somme_risquee_deces, il_dwh_pa_gen_valorisation.somme_risquee_deces_mvt, il_dwh_pa_gen_valorisation.cd_somme_risquee_deces_dat_ext, il_dwh_pa_gen_valorisation.cd_somme_risquee_deces_fin_ann, il_dwh_pa_gen_valorisation.somme_risquee_inva, il_dwh_pa_gen_valorisation.somme_risquee_inva_mvt, il_dwh_pa_gen_valorisation.cd_somme_risquee_inva_dat_ext, il_dwh_pa_gen_valorisation.cd_somme_risquee_inva_fin_ann, il_dwh_pa_gen_valorisation.somme_risquee_reass_deces, il_dwh_pa_gen_valorisation.somme_risquee_reass_deces_mvt, il_dwh_pa_gen_valorisation.cd_somme_risquee_reass_deces_dat_ext, il_dwh_pa_gen_valorisation.cd_somme_risquee_reass_deces_fin_ann, il_dwh_pa_gen_valorisation.somme_risquee_reass_inva, il_dwh_pa_gen_valorisation.somme_risquee_reass_inva_mvt, il_dwh_pa_gen_valorisation.cd_somme_risquee_reass_inva_dat_ext, il_dwh_pa_gen_valorisation.cd_somme_risquee_reass_inva_fin_ann, il_dwh_pa_gen_valorisation.vas_commerciale, il_dwh_pa_gen_valorisation.vas_commerciale_mvt, il_dwh_pa_gen_valorisation.cd_vas_commerciale_dat_ext, il_dwh_pa_gen_valorisation.cd_vas_commerciale_fin_ann, il_dwh_pa_gen_valorisation.vas_inventaire, il_dwh_pa_gen_valorisation.vas_inventaire_mvt, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_vas_inventaire_fin_ann, il_dwh_pa_gen_valorisation.vas_pure, il_dwh_pa_gen_valorisation.vas_pure_mvt, il_dwh_pa_gen_valorisation.cd_vas_pure_dat_ext, il_dwh_pa_gen_valorisation.cd_vas_pure_fin_ann, il_dwh_pa_gen_valorisation.val_rte_capital, il_dwh_pa_gen_valorisation.val_rte_capital_mvt, il_dwh_pa_gen_valorisation.val_compens_retraite_avancee, il_dwh_pa_gen_valorisation.val_compens_retraite_avancee_mvt, il_dwh_pa_gen_valorisation.dn_mut_id, il_dwh_pa_gen_valorisation.dn_dte_mut, il_dwh_pa_gen_valorisation.dn_nom_mut, il_dwh_pa_gen_valorisation.mut_dossier_id, il_dwh_pa_gen_valorisation.mut_dossier_id_prec, il_dwh_pa_gen_valorisation.usr_log_i, il_dwh_pa_gen_valorisation.dte_log_i, il_dwh_pa_gen_valorisation.usr_log_u, il_dwh_pa_gen_valorisation.dte_log_u
  • Index Cond: (il_dwh_pa_gen_valorisation.pa_gen_id = il_dwh_pa_gen.pa_gen_id)
  • Filter: ((il_dwh_pa_gen_valorisation.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen_valorisation.dl_flg_inv = 1) AND (il_dwh_pa_gen_valorisation.dl_flg_bcl = 1) AND ((il_dwh_pa_gen_valorisation.type_av_ap)::text = 'APRE'::text) AND (il_dwh_pa_gen.dl_soc_id = il_dwh_pa_gen_valorisation.dl_soc_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1508784 read=57706
  • I/O Timings: read=19554.925
32. 1,368.711 1,368.711 ↑ 1.0 1 152,079

Index Scan using olm_il_dwh_part_dossier_part_dossier_id on bidwh.il_dwh_part_dossier a1_il_dwh_part_dossier_assu (cost=0.44..3.46 rows=1 width=54) (actual time=0.009..0.009 rows=1 loops=152,079)

  • Output: a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.dl_datextract, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.dl_flg_bcl, a1_il_dwh_part_dossier_assu.dl_flg_inv, replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)
  • Index Cond: (a1_il_dwh_part_dossier_assu.part_dossier_id = il_dwh_pa_gen.assure_part_dossier_id)
  • Filter: ((a1_il_dwh_part_dossier_assu.dl_datextract = '2019-12-31'::date) AND (a1_il_dwh_part_dossier_assu.dl_flg_inv = 1) AND (a1_il_dwh_part_dossier_assu.dl_flg_bcl = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=759890 read=491
  • I/O Timings: read=185.569
33. 304.158 304.158 ↑ 1.0 1 152,079

Seq Scan on bidwhpar.bi_par_flg_bcl (cost=0.00..1.21 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=152,079)

  • Output: bi_par_flg_bcl.dl_flg_bcl, bi_par_flg_bcl.dl_flg_bcl_desc
  • Filter: (bi_par_flg_bcl.dl_flg_bcl = 1)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=152079
34. 152.079 152.079 ↑ 1.0 1 152,079

Seq Scan on bidwhpar.bi_par_flg_inv (cost=0.00..1.05 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=152,079)

  • Output: bi_par_flg_inv.dl_flg_inv, bi_par_flg_inv.dl_flg_inv_desc
  • Filter: (bi_par_flg_inv.dl_flg_inv = 1)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=152079
35. 608.316 71,020.893 ↑ 1.0 1 152,079

Bitmap Heap Scan on bidwh.il_dwh_contrat (cost=8.12..18.96 rows=1 width=24) (actual time=0.466..0.467 rows=1 loops=152,079)

  • Output: il_dwh_contrat.dl_datextract, il_dwh_contrat.dl_soc_id, il_dwh_contrat.dl_flg_bcl, il_dwh_contrat.dl_flg_inv, il_dwh_contrat.mut_contrat_id, il_dwh_contrat.mut_contrat_id_prec, il_dwh_contrat.per_gest_id, il_dwh_contrat.mut_id, il_dwh_contrat.contrat_id, il_dwh_contrat.donnees_mut_contrat_id, il_dwh_contrat.no_gen_contrat, il_dwh_contrat.contrat_gen_id, il_dwh_contrat.dte_mut, il_dwh_contrat.description, il_dwh_contrat.etat_mut_contrat, il_dwh_contrat.description_etat_mut_contrat, il_dwh_contrat.statut_mut_contrat, il_dwh_contrat.description_statut_mut_contrat, il_dwh_contrat.no_mut, il_dwh_contrat.no_mut_precedente, il_dwh_contrat.no_mut_extournee, il_dwh_contrat.repercussion_auto_dossiers, il_dwh_contrat.visa_cree_par, il_dwh_contrat.dte_creation, il_dwh_contrat.visa_modifie_par, il_dwh_contrat.dte_modification, il_dwh_contrat.visa_ctrl_par, il_dwh_contrat.dte_ctrl, il_dwh_contrat.employeur_part_id, il_dwh_contrat.mut_dossier_autorisee_id, il_dwh_contrat.ancienne_ip_part_id, il_dwh_contrat.pro_id, il_dwh_contrat.numero_contrat, il_dwh_contrat.dte_effet, il_dwh_contrat.dte_effet_pe, il_dwh_contrat.blocage_dossiers, il_dwh_contrat.blocage_fact, il_dwh_contrat.raison_blocage_fact, il_dwh_contrat.visa_blocage_fact, il_dwh_contrat.dte_operation_blocage_fact, il_dwh_contrat.dte_derniere_fact, il_dwh_contrat.responsable_gest, il_dwh_contrat.responsable_gest_medicale, il_dwh_contrat.ancien_numero_contrat, il_dwh_contrat.reass, il_dwh_contrat.description_reassurance, il_dwh_contrat.dte_entree, il_dwh_contrat.quotite_apport_initial, il_dwh_contrat.comp_cmt_pari, il_dwh_contrat.calc_dte_affiliation, il_dwh_contrat.objet_facturable_allocemp_cree, il_dwh_contrat.contrat_pilote_fonds_gar_id, il_dwh_contrat.nouvelle_ip_part_id, il_dwh_contrat.secteur_act_predominant_id, il_dwh_contrat.code_secteur_activite, il_dwh_contrat.libelle_secteur_activite, il_dwh_contrat.type_rattr, il_dwh_contrat.description_type_rattrapage, il_dwh_contrat.gest_reservee, il_dwh_contrat.code_voir_dossier, il_dwh_contrat.confidentiel, il_dwh_contrat.effectif_pour_rab, il_dwh_contrat.nombre_affilies_reel, il_dwh_contrat.appliquer_tx_validite, il_dwh_contrat.appliquer_tx_act, il_dwh_contrat.bonif_escomptee, il_dwh_contrat.fonds_gar, il_dwh_contrat.description_fonds_garantie, il_dwh_contrat.regrpmnt_contrat_fonds_gar, il_dwh_contrat.description_regroupement_contr, il_dwh_contrat.statut_cmt_pari, il_dwh_contrat.description_statut_comite_pari, il_dwh_contrat.dte_prochaine_maj_effectif, il_dwh_contrat.numero_avenant, il_dwh_contrat.dte_entree_vigueur_avenant, il_dwh_contrat.type_avenant, il_dwh_contrat.description_type_avenant, il_dwh_contrat.suivi_contentieux, il_dwh_contrat.dte_ctrl_cmt_pari, il_dwh_contrat.nombre_repr_employeur_requis, il_dwh_contrat.nombre_repr_empl_requis, il_dwh_contrat.grand_contrat, il_dwh_contrat.tx_coti_laa, il_dwh_contrat.type_mut_id, il_dwh_contrat.app_gest_id, il_dwh_contrat.nom_mut, il_dwh_contrat.niveau, il_dwh_contrat.impact_doss, il_dwh_contrat.mut_dossier_id, il_dwh_contrat.genere_par_contrat, il_dwh_contrat.ged_mut_1, il_dwh_contrat.ged_mut_2, il_dwh_contrat.description_per_ges, il_dwh_contrat.dte_deb_per_ges, il_dwh_contrat.dte_fin_per_ges, il_dwh_contrat.statut_periode_gestion, il_dwh_contrat.description_statut_per_ges, il_dwh_contrat.dte_deb_app, il_dwh_contrat.dte_fin_app, il_dwh_contrat.default_nbr_bvr_prod, il_dwh_contrat.ged_definition2_prod, il_dwh_contrat.ged_definition1_prod, il_dwh_contrat.oc_principal_id_employeur, il_dwh_contrat.oc_secondaire_id_employeur, il_dwh_contrat.type_part_employeur, il_dwh_contrat.description_type_partenaire_emp, il_dwh_contrat.nom_ou_raison_sociale_employeur, il_dwh_contrat.nom_complementaire_employeur, il_dwh_contrat.nom_alliance_employeur, il_dwh_contrat.prenom_employeur, il_dwh_contrat.dte_naissance_employeur, il_dwh_contrat.dte_deces_employeur, il_dwh_contrat.sexe_employeur, il_dwh_contrat.description_sexe_employeur, il_dwh_contrat.etat_civil_employeur, il_dwh_contrat.description_etat_civil_employeur, il_dwh_contrat.no_avs_employeur, il_dwh_contrat.langue_correspondance_employeur, il_dwh_contrat.telephone_employeur, il_dwh_contrat.telephone_mobile_employeur, il_dwh_contrat.fax_employeur, il_dwh_contrat.e_mail_employeur, il_dwh_contrat.contrat_pilote_regrp_gest_id, il_dwh_contrat.pilote_regrpmnt_gest, il_dwh_contrat.age_terme_vise, il_dwh_contrat.contrat_resilie_cours_annee, il_dwh_contrat.correspondance_employeur_seul, il_dwh_contrat.envoi_liste_annuelle_salaires, il_dwh_contrat.envoi_extrait_rapport_annuel, il_dwh_contrat.envoi_situation_prev, il_dwh_contrat.envoi_courrier_va_logement, il_dwh_contrat.usr_log_i, il_dwh_contrat.dte_log_i, il_dwh_contrat.usr_log_u, il_dwh_contrat.dte_log_u, il_dwh_contrat.dte_derniere_fact_prest, il_dwh_contrat.idx_particuliere, il_dwh_contrat.fact_rattr, il_dwh_contrat.reass_complete, il_dwh_contrat.dte_echeance, il_dwh_contrat.blocage_extranet, il_dwh_contrat.envoi_bordereau_fact, il_dwh_contrat.dte_escompte, il_dwh_contrat.choix_plan_complementaire, il_dwh_contrat.numero_contact
  • Recheck Cond: ((il_dwh_contrat.contrat_id = il_dwh_dossier.contrat_id) AND (il_dwh_contrat.dl_datextract = '2019-12-31'::date) AND (il_dwh_contrat.dl_soc_id = il_dwh_dossier.dl_soc_id) AND (il_dwh_contrat.dl_flg_bcl = 1) AND (il_dwh_contrat.dl_flg_inv = 1))
  • Filter: (il_dwh_contrat.no_gen_contrat = (SubPlan 1))
  • Heap Blocks: exact=152079
  • Buffers: shared hit=7857280
36. 608.316 31,632.432 ↓ 0.0 0 152,079

BitmapAnd (cost=8.12..8.12 rows=1 width=0) (actual time=0.208..0.208 rows=0 loops=152,079)

  • Buffers: shared hit=3146761
37. 1,368.711 1,368.711 ↓ 1.5 117 152,079

Bitmap Index Scan on olm_il_dwh_contrat_contrat_id (cost=0.00..1.98 rows=78 width=0) (actual time=0.009..0.009 rows=117 loops=152,079)

  • Index Cond: (il_dwh_contrat.contrat_id = il_dwh_dossier.contrat_id)
  • Buffers: shared hit=497690
38. 29,655.405 29,655.405 ↓ 10.7 2,526 152,079

Bitmap Index Scan on il_dwh_contrat_pkey (cost=0.00..5.89 rows=235 width=0) (actual time=0.195..0.195 rows=2,526 loops=152,079)

  • Index Cond: ((il_dwh_contrat.dl_datextract = '2019-12-31'::date) AND (il_dwh_contrat.dl_soc_id = il_dwh_dossier.dl_soc_id) AND (il_dwh_contrat.dl_flg_bcl = 1) AND (il_dwh_contrat.dl_flg_inv = 1))
  • Buffers: shared hit=2649071
39.          

SubPlan (for Bitmap Heap Scan)

40. 152.079 38,780.145 ↑ 1.0 1 152,079

Aggregate (cost=9.70..9.71 rows=1 width=4) (actual time=0.255..0.255 rows=1 loops=152,079)

  • Output: max(a.no_gen_contrat)
  • Buffers: shared hit=4558440
41. 456.237 38,628.066 ↓ 2.0 2 152,079

Bitmap Heap Scan on bidwh.il_dwh_contrat a (cost=8.57..9.70 rows=1 width=4) (actual time=0.253..0.254 rows=2 loops=152,079)

  • Output: a.dl_datextract, a.dl_soc_id, a.dl_flg_bcl, a.dl_flg_inv, a.mut_contrat_id, a.mut_contrat_id_prec, a.per_gest_id, a.mut_id, a.contrat_id, a.donnees_mut_contrat_id, a.no_gen_contrat, a.contrat_gen_id, a.dte_mut, a.description, a.etat_mut_contrat, a.description_etat_mut_contrat, a.statut_mut_contrat, a.description_statut_mut_contrat, a.no_mut, a.no_mut_precedente, a.no_mut_extournee, a.repercussion_auto_dossiers, a.visa_cree_par, a.dte_creation, a.visa_modifie_par, a.dte_modification, a.visa_ctrl_par, a.dte_ctrl, a.employeur_part_id, a.mut_dossier_autorisee_id, a.ancienne_ip_part_id, a.pro_id, a.numero_contrat, a.dte_effet, a.dte_effet_pe, a.blocage_dossiers, a.blocage_fact, a.raison_blocage_fact, a.visa_blocage_fact, a.dte_operation_blocage_fact, a.dte_derniere_fact, a.responsable_gest, a.responsable_gest_medicale, a.ancien_numero_contrat, a.reass, a.description_reassurance, a.dte_entree, a.quotite_apport_initial, a.comp_cmt_pari, a.calc_dte_affiliation, a.objet_facturable_allocemp_cree, a.contrat_pilote_fonds_gar_id, a.nouvelle_ip_part_id, a.secteur_act_predominant_id, a.code_secteur_activite, a.libelle_secteur_activite, a.type_rattr, a.description_type_rattrapage, a.gest_reservee, a.code_voir_dossier, a.confidentiel, a.effectif_pour_rab, a.nombre_affilies_reel, a.appliquer_tx_validite, a.appliquer_tx_act, a.bonif_escomptee, a.fonds_gar, a.description_fonds_garantie, a.regrpmnt_contrat_fonds_gar, a.description_regroupement_contr, a.statut_cmt_pari, a.description_statut_comite_pari, a.dte_prochaine_maj_effectif, a.numero_avenant, a.dte_entree_vigueur_avenant, a.type_avenant, a.description_type_avenant, a.suivi_contentieux, a.dte_ctrl_cmt_pari, a.nombre_repr_employeur_requis, a.nombre_repr_empl_requis, a.grand_contrat, a.tx_coti_laa, a.type_mut_id, a.app_gest_id, a.nom_mut, a.niveau, a.impact_doss, a.mut_dossier_id, a.genere_par_contrat, a.ged_mut_1, a.ged_mut_2, a.description_per_ges, a.dte_deb_per_ges, a.dte_fin_per_ges, a.statut_periode_gestion, a.description_statut_per_ges, a.dte_deb_app, a.dte_fin_app, a.default_nbr_bvr_prod, a.ged_definition2_prod, a.ged_definition1_prod, a.oc_principal_id_employeur, a.oc_secondaire_id_employeur, a.type_part_employeur, a.description_type_partenaire_emp, a.nom_ou_raison_sociale_employeur, a.nom_complementaire_employeur, a.nom_alliance_employeur, a.prenom_employeur, a.dte_naissance_employeur, a.dte_deces_employeur, a.sexe_employeur, a.description_sexe_employeur, a.etat_civil_employeur, a.description_etat_civil_employeur, a.no_avs_employeur, a.langue_correspondance_employeur, a.telephone_employeur, a.telephone_mobile_employeur, a.fax_employeur, a.e_mail_employeur, a.contrat_pilote_regrp_gest_id, a.pilote_regrpmnt_gest, a.age_terme_vise, a.contrat_resilie_cours_annee, a.correspondance_employeur_seul, a.envoi_liste_annuelle_salaires, a.envoi_extrait_rapport_annuel, a.envoi_situation_prev, a.envoi_courrier_va_logement, a.usr_log_i, a.dte_log_i, a.usr_log_u, a.dte_log_u, a.dte_derniere_fact_prest, a.idx_particuliere, a.fact_rattr, a.reass_complete, a.dte_echeance, a.blocage_extranet, a.envoi_bordereau_fact, a.dte_escompte, a.choix_plan_complementaire, a.numero_contact
  • Recheck Cond: ((il_dwh_contrat.contrat_id = a.contrat_id) AND (a.dl_datextract = il_dwh_contrat.dl_datextract) AND (il_dwh_contrat.dl_soc_id = a.dl_soc_id) AND (a.dl_flg_bcl = il_dwh_contrat.dl_flg_bcl))
  • Filter: (((a.etat_mut_contrat)::text = 'VALI'::text) AND (CASE WHEN (il_dwh_contrat.dl_datextract <= '2014-01-01'::date) THEN a.dl_flg_inv ELSE 1 END = CASE WHEN (il_dwh_contrat.dl_datextract <= '2014-01-01'::date) THEN il_dwh_contrat.dl_flg_inv ELSE 1 END))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=232162
  • Buffers: shared hit=4558440
42. 456.237 38,171.829 ↓ 0.0 0 152,079

BitmapAnd (cost=8.57..8.57 rows=1 width=0) (actual time=0.251..0.251 rows=0 loops=152,079)

  • Buffers: shared hit=4326278
43. 1,216.632 1,216.632 ↓ 1.5 117 152,079

Bitmap Index Scan on olm_il_dwh_contrat_contrat_id (cost=0.00..2.11 rows=78 width=0) (actual time=0.008..0.008 rows=117 loops=152,079)

  • Index Cond: (il_dwh_contrat.contrat_id = a.contrat_id)
  • Buffers: shared hit=497690
44. 36,498.960 36,498.960 ↓ 12.7 3,639 152,079

Bitmap Index Scan on il_dwh_contrat_pkey (cost=0.00..6.21 rows=287 width=0) (actual time=0.240..0.240 rows=3,639 loops=152,079)

  • Index Cond: ((a.dl_datextract = il_dwh_contrat.dl_datextract) AND (il_dwh_contrat.dl_soc_id = a.dl_soc_id) AND (a.dl_flg_bcl = il_dwh_contrat.dl_flg_bcl))
  • Buffers: shared hit=3828588
45. 152.079 152.079 ↑ 1.0 1 152,079

Index Scan using bi_prod_pkey on biinfra.bi_prod (cost=0.14..1.73 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=152,079)

  • Output: bi_prod.pro_id, bi_prod.fam_id, bi_prod.soc_id, bi_prod.pro_dte_deb_app, bi_prod.pro_dte_fin_app, bi_prod.pro_desc_courte, bi_prod.pro_default_nbr_bvr, bi_prod.pro_ged_definition1, bi_prod.pro_ged_definition2, bi_prod.pro_dernier_num_police_utilise, bi_prod.pro_type_prod, bi_prod.pro_ged_type_caisse, bi_prod.prl_nom_prod_fr, bi_prod.prl_desc_longue_fr, bi_prod.prl_desc_portail_fr, bi_prod.prl_ged_definition_fiscale_fr, bi_prod.act_nom_prod, bi_prod.act_regr_prod, bi_prod.pvi_type_prev, bi_prod.pvi_type_dte_annual, bi_prod.pvi_type_libe_pri, bi_prod.pvi_type_versement_add, bi_prod.pvi_seuil_red, bi_prod.pvi_seuil_paiement, bi_prod.pvi_droit_pe_redu, bi_prod.pvi_plan_repart, bi_prod.pvi_fo_agregation, bi_prod.pvi_arrondi_pu, bi_prod.pvi_arrondi_pp, bi_prod.pvi_periodicite_prelevement, bi_prod.pvi_periodicite_rebalancement, bi_prod.pvi_arrondi_fonds_parts, bi_prod.pvi_arrondi_fonds_montants, bi_prod.pvi_pe_couv_id, bi_prod.pvi_type_interpolation, bi_prod.pvi_montant_limite_versement, bi_prod.pvi_montant_limite_versement_add, bi_prod.pvi_fam_prod_informatique, bi_prod.pvi_rachetable, bi_prod.pvi_liber_assure1_obli, bi_prod.pvi_surprime_exploitant, bi_prod.pvi_categorie_prod, bi_prod.pvi_proj_plusieurs_tx, bi_prod.pvi_type_pro, bi_prod.pvi_parametrage_capital_reserve, bi_prod.pvi_anti_selection_rachat, bi_prod.pvi_pe_prest_reduction, bi_prod.pvi_parametrage_capital_ddecr, bi_prod.pvi_option_capital_rente_deces, bi_prod.pvi_type_pe_apres_differe, bi_prod.pvi_frais_gestion_min_max, bi_prod.pvl_benef_deces_fr, bi_prod.pvl_benef_vie_fr, bi_prod.pvl_preneur_success_fr, bi_prod.pvl_benef_deces_2t_fr, bi_prod.pvl_benef_vie_2t_fr
  • Index Cond: (bi_prod.pro_id = il_dwh_contrat.pro_id)
  • Buffers: shared hit=304158
46. 152.079 152.079 ↑ 1.0 1 152,079

Index Scan using bi_fam_prod_pkey on biinfra.bi_fam_prod (cost=0.14..0.72 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=152,079)

  • Output: bi_fam_prod.fam_id, bi_fam_prod.soc_id, bi_fam_prod.fam_desc_courte, bi_fam_prod.fam_type_famille, bi_fam_prod.fam_app_gest_id, bi_fam_prod.fam_dernier_num_police_utilise, bi_fam_prod.fal_nom_fam_fr, bi_fam_prod.fal_desc_longue_fr, bi_fam_prod.apg_nom_app, bi_fam_prod.apg_desc_courte, bi_fam_prod.apg_type_app
  • Index Cond: (bi_fam_prod.fam_id = bi_prod.fam_id)
  • Filter: (bi_fam_prod.soc_id = 3)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=304158
47. 115.177 115.177 ↑ 1.0 1 115,177

Seq Scan on biinfra.bi_soc (cost=0.00..1.09 rows=1 width=82) (actual time=0.001..0.001 rows=1 loops=115,177)

  • Output: bi_soc.soc_id, bi_soc.soc_desc_courte, bi_soc.sol_nom_soc_fr, bi_soc.sol_nom_soc_ged_fr, bi_soc.sol_sign_soc_fr, bi_soc.sol_desc_voies_recours_fr
  • Filter: (bi_soc.soc_id = 3)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=115177
48. 230.354 230.354 ↑ 1.0 1 115,177

Index Only Scan using bi_dates_pkey on biinfra.bi_dates (cost=0.28..2.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=115,177)

  • Output: bi_dates.dte_jour
  • Index Cond: (bi_dates.dte_jour = '2019-12-31'::date)
  • Heap Fetches: 115177
  • Buffers: shared hit=345531
49. 0.020 461.146 ↓ 18.0 18 1

Unique (cost=95,617.59..95,617.67 rows=1 width=463) (actual time=461.127..461.146 rows=18 loops=1)

  • Output: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest_1.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation_1.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier_1.dossier_id)), il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Buffers: shared hit=111736
50. 0.044 461.126 ↓ 18.0 18 1

Sort (cost=95,617.59..95,617.59 rows=1 width=463) (actual time=461.125..461.126 rows=18 loops=1)

  • Output: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest_1.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation_1.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier_1.dossier_id)), il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Sort Key: bi_vcouv_1.tcl_desc_longue, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, (sum(COALESCE(il_dwh_pa_gen_prest_1.prest_due, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest_1.reduc_idx, '0'::numeric))), (sum(il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext)), (sum(COALESCE(il_dwh_pa_gen_valorisation_1.vas_inventaire, '0'::numeric))), (count(DISTINCT il_dwh_dossier_1.dossier_id)), il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=111736
51. 1.444 461.082 ↓ 18.0 18 1

GroupAggregate (cost=95,617.46..95,617.58 rows=1 width=463) (actual time=459.804..461.082 rows=18 loops=1)

  • Output: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, sum(COALESCE(il_dwh_pa_gen_prest_1.prest_due, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.idx, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.reduc_idx, '0'::numeric)), sum(il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext), sum(COALESCE(il_dwh_pa_gen_valorisation_1.vas_inventaire, '0'::numeric)), count(DISTINCT il_dwh_dossier_1.dossier_id), il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Group Key: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Filter: (sum(il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext) <> '0'::numeric)
  • Rows Removed by Filter: 626
  • Buffers: shared hit=111736
52. 2.184 459.638 ↓ 644.0 644 1

Sort (cost=95,617.46..95,617.47 rows=1 width=310) (actual time=459.602..459.638 rows=644 loops=1)

  • Output: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, bi_soc_1.soc_desc_courte, bi_prod_1.pro_desc_courte, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire
  • Sort Key: bi_vcouv_1.tcl_desc_longue, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva
  • Sort Method: quicksort Memory: 231kB
  • Buffers: shared hit=111736
53. 0.386 457.454 ↓ 644.0 644 1

Nested Loop (cost=2,501.79..95,617.45 rows=1 width=310) (actual time=31.079..457.454 rows=644 loops=1)

  • Output: bi_soc_1.soc_id, bi_vcouv_1.tcl_desc_longue, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_dossier_1.statut, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, bi_soc_1.soc_desc_courte, bi_prod_1.pro_desc_courte, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire
  • Buffers: shared hit=111736
54. 0.787 455.780 ↓ 644.0 644 1

Nested Loop (cost=2,501.51..95,614.94 rows=1 width=314) (actual time=31.068..455.780 rows=644 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_fam_prod_1.fam_id, bi_soc_1.soc_id, bi_soc_1.soc_desc_courte, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=109804
55. 0.630 454.349 ↓ 644.0 644 1

Nested Loop (cost=2,501.51..95,613.85 rows=1 width=236) (actual time=31.060..454.349 rows=644 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_fam_prod_1.fam_id, bi_fam_prod_1.soc_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Inner Unique: true
  • Buffers: shared hit=109160
56. 0.841 452.893 ↓ 826.0 826 1

Nested Loop (cost=2,501.37..95,612.54 rows=1 width=232) (actual time=13.522..452.893 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_prod_1.fam_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Inner Unique: true
  • Buffers: shared hit=107508
57. 1.064 451.226 ↓ 826.0 826 1

Nested Loop (cost=2,501.22..95,610.79 rows=1 width=207) (actual time=13.513..451.226 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, il_dwh_contrat_1.pro_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Join Filter: (il_dwh_pa_gen_1.dl_soc_id = il_dwh_contrat_1.dl_soc_id)
  • Buffers: shared hit=105856
58. 0.592 56.986 ↓ 826.0 826 1

Nested Loop (cost=2,493.10..95,591.82 rows=1 width=227) (actual time=13.402..56.986 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.dl_soc_id, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=62149
59. 0.421 55.568 ↓ 826.0 826 1

Nested Loop (cost=2,493.10..95,590.76 rows=1 width=231) (actual time=13.396..55.568 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.dl_soc_id, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=61323
60. 0.360 53.495 ↓ 826.0 826 1

Nested Loop (cost=2,493.10..95,589.53 rows=1 width=231) (actual time=13.387..53.495 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.dl_soc_id, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id, (replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=60497
61. 1.193 45.701 ↓ 826.0 826 1

Nested Loop (cost=2,492.66..95,586.04 rows=1 width=209) (actual time=13.362..45.701 rows=826 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.dl_soc_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Buffers: shared hit=56375
62. 0.953 34.548 ↓ 1,660.0 1,660 1

Nested Loop Left Join (cost=2,492.09..95,553.42 rows=1 width=199) (actual time=13.306..34.548 rows=1,660 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Buffers: shared hit=47067
63. 2.161 21.975 ↓ 1,660.0 1,660 1

Nested Loop (cost=2,491.52..95,548.38 rows=1 width=190) (actual time=13.288..21.975 rows=1,660 loops=1)

  • Output: bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cou_id, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.cou_desc_courte, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Inner Unique: true
  • Buffers: shared hit=37063
64. 0.000 16.494 ↓ 1,660.0 1,660 1

Gather (cost=2,491.24..95,546.18 rows=1 width=113) (actual time=13.274..16.494 rows=1,660 loops=1)

  • Output: il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=32083
65. 0.224 40.111 ↓ 332.0 332 5 / 5

Nested Loop (cost=1,491.24..94,546.08 rows=1 width=113) (actual time=1.968..40.111 rows=332 loops=5)

  • Output: il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Buffers: shared hit=32083
  • Worker 0: actual time=0.373..48.844 rows=422 loops=1
  • Buffers: shared hit=8141
  • Worker 1: actual time=0.626..44.922 rows=296 loops=1
  • Buffers: shared hit=5918
  • Worker 2: actual time=0.356..48.383 rows=462 loops=1
  • Buffers: shared hit=8671
  • Worker 3: actual time=0.456..48.130 rows=454 loops=1
  • Buffers: shared hit=8412
66. 0.187 22.167 ↓ 177.0 177 5 / 5

Nested Loop (cost=1,490.67..94,214.33 rows=1 width=80) (actual time=1.839..22.167 rows=177 loops=5)

  • Output: il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.dl_datextract, il_dwh_plan_partie_1.dl_soc_id, il_dwh_plan_partie_1.dl_flg_bcl, il_dwh_plan_partie_1.dl_flg_inv, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Buffers: shared hit=15328
  • Worker 0: actual time=0.249..27.190 rows=226 loops=1
  • Buffers: shared hit=3858
  • Worker 1: actual time=0.405..24.263 rows=158 loops=1
  • Buffers: shared hit=2860
  • Worker 2: actual time=0.243..24.996 rows=246 loops=1
  • Buffers: shared hit=3990
  • Worker 3: actual time=0.329..25.033 rows=243 loops=1
  • Buffers: shared hit=3911
67. 0.263 17.228 ↓ 7.3 176 5 / 5

Nested Loop (cost=1,490.24..93,975.42 rows=24 width=68) (actual time=1.773..17.228 rows=176 loops=5)

  • Output: il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.dl_datextract, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dl_flg_bcl, il_dwh_partie_dossier_1.dl_flg_inv, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.statut, il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.contrat_id
  • Buffers: shared hit=9991
  • Worker 0: actual time=0.176..19.947 rows=225 loops=1
  • Buffers: shared hit=2484
  • Worker 1: actual time=0.327..19.391 rows=157 loops=1
  • Buffers: shared hit=1905
  • Worker 2: actual time=0.166..18.898 rows=243 loops=1
  • Buffers: shared hit=2509
  • Worker 3: actual time=0.254..18.793 rows=242 loops=1
  • Buffers: shared hit=2459
68. 13.259 14.325 ↑ 9.3 176 5 / 5

Parallel Bitmap Heap Scan on bidwh.il_dwh_partie_dossier il_dwh_partie_dossier_1 (cost=1,489.80..87,499.08 rows=1,633 width=39) (actual time=1.703..14.325 rows=176 loops=5)

  • Output: il_dwh_partie_dossier_1.dl_datextract, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dl_flg_bcl, il_dwh_partie_dossier_1.dl_flg_inv, il_dwh_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.partie_liee_partie_dossier_id, il_dwh_partie_dossier_1.dossier_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_partie_dossier_1.no_gen_dossier, il_dwh_partie_dossier_1.numero_dossier, il_dwh_partie_dossier_1.mut_dossier_id, il_dwh_partie_dossier_1.mut_dossier_id_prec, il_dwh_partie_dossier_1.contrat_id, il_dwh_partie_dossier_1.numero_contrat, il_dwh_partie_dossier_1.partie_dossier_orig_id, il_dwh_partie_dossier_1.etat, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.description_type_partie, il_dwh_partie_dossier_1.type_deces, il_dwh_partie_dossier_1.description_type_deces, il_dwh_partie_dossier_1.dte_deb_partie, il_dwh_partie_dossier_1.dte_effet_idx, il_dwh_partie_dossier_1.avoir_50_ans, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.description_type_invalidite, il_dwh_partie_dossier_1.dte_deb_inca_gain, il_dwh_partie_dossier_1.age_deb_inca_gain, il_dwh_partie_dossier_1.dte_fin_inca_gain, il_dwh_partie_dossier_1.dte_reexamen, il_dwh_partie_dossier_1.tx_inca_gain_reel, il_dwh_partie_dossier_1.tx_inca_gain_reel_mvt, il_dwh_partie_dossier_1.tx_inca_gain_calc, il_dwh_partie_dossier_1.tx_inca_gain_calc_mvt, il_dwh_partie_dossier_1.lib_passee, il_dwh_partie_dossier_1.inva_passee, il_dwh_partie_dossier_1.rechute, il_dwh_partie_dossier_1.dte_rechute, il_dwh_partie_dossier_1.faute_grave, il_dwh_partie_dossier_1.tx_reduc_faute_grave, il_dwh_partie_dossier_1.reticence, il_dwh_partie_dossier_1.accident, il_dwh_partie_dossier_1.cause_medicale_sinistre, il_dwh_partie_dossier_1.cause_risque_special_sinistre, il_dwh_partie_dossier_1.sal_actif_reparti, il_dwh_partie_dossier_1.delai_attente_lib_consomme, il_dwh_partie_dossier_1.delai_attente_ri_consomme, il_dwh_partie_dossier_1.dte_age_terme, il_dwh_partie_dossier_1.dte_deb_mise_en_pension, il_dwh_partie_dossier_1.tx_retraite, il_dwh_partie_dossier_1.suri_avec_pe, il_dwh_partie_dossier_1.suri_avec_idx, il_dwh_partie_dossier_1.dte_revision_ai, il_dwh_partie_dossier_1.dte_revision_caisse_pension, il_dwh_partie_dossier_1.tx_sal_suri, il_dwh_partie_dossier_1.tot_rev_recu, il_dwh_partie_dossier_1.reprise_ancienne_ip, il_dwh_partie_dossier_1.deb_paie_effectif, il_dwh_partie_dossier_1.sal_ann_presume_perdu, il_dwh_partie_dossier_1.dn_mut_id, il_dwh_partie_dossier_1.dn_dte_mut, il_dwh_partie_dossier_1.dn_nom_mut, il_dwh_partie_dossier_1.pro_id, il_dwh_partie_dossier_1.dte_echeance_partie, il_dwh_partie_dossier_1.calc_av_terme, il_dwh_partie_dossier_1.mnt_cpt_droits_transitoires, il_dwh_partie_dossier_1.int_cpt_droits_transitoires, il_dwh_partie_dossier_1.cd_mnt_cpt_droits_transitoires_fin_ann, il_dwh_partie_dossier_1.cd_mnt_cpt_droits_transitoires_dat_ext, il_dwh_partie_dossier_1.cd_int_cpt_droits_transitoires_fin_ann, il_dwh_partie_dossier_1.cd_int_cpt_droits_transitoires_dat_ext, il_dwh_partie_dossier_1.somme_risquee_reass_deces, il_dwh_partie_dossier_1.somme_risquee_reass_inva, il_dwh_partie_dossier_1.annonce_reass_deces, il_dwh_partie_dossier_1.annonce_reass_inva, il_dwh_partie_dossier_1.annonce_reass_deces_facultatif, il_dwh_partie_dossier_1.annonce_reass_inva_facultatif, il_dwh_partie_dossier_1.annonce_reass_deces_sinistre, il_dwh_partie_dossier_1.annonce_reass_inva_sinistre, il_dwh_partie_dossier_1.reass_deces, il_dwh_partie_dossier_1.reass_inva, il_dwh_partie_dossier_1.inva_proportion_reassuree, il_dwh_partie_dossier_1.fo_reass_id, il_dwh_partie_dossier_1.annonce_deces, il_dwh_partie_dossier_1.annonce_invalidite, il_dwh_partie_dossier_1.usr_log_i, il_dwh_partie_dossier_1.dte_log_i, il_dwh_partie_dossier_1.usr_log_u, il_dwh_partie_dossier_1.dte_log_u
  • Recheck Cond: ((il_dwh_partie_dossier_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_partie_dossier_1.dl_flg_bcl = 1) AND (il_dwh_partie_dossier_1.dl_flg_inv = 1))
  • Filter: (((il_dwh_partie_dossier_1.etat)::text = 'VALI'::text) AND ((il_dwh_partie_dossier_1.type_partie)::text = 'PEIN'::text))
  • Rows Removed by Filter: 11954
  • Heap Blocks: exact=334
  • Buffers: shared hit=5570
  • Worker 0: actual time=0.090..16.365 rows=225 loops=1
  • Buffers: shared hit=1355
  • Worker 1: actual time=0.240..16.317 rows=157 loops=1
  • Buffers: shared hit=1116
  • Worker 2: actual time=0.092..15.096 rows=243 loops=1
  • Buffers: shared hit=1286
  • Worker 3: actual time=0.183..14.895 rows=242 loops=1
  • Buffers: shared hit=1244
69. 1.066 1.066 ↑ 1.5 60,651 1 / 5

Bitmap Index Scan on olm_il_dwh_partie_dossier_date_dl_flg_bcl_dl_flg_inv (cost=0.00..1,488.17 rows=88,747 width=0) (actual time=5.331..5.332 rows=60,651 loops=1)

  • Index Cond: ((il_dwh_partie_dossier_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_partie_dossier_1.dl_flg_bcl = 1) AND (il_dwh_partie_dossier_1.dl_flg_inv = 1))
  • Buffers: shared hit=235
70. 2.640 2.640 ↑ 1.0 1 880 / 5

Index Scan using olm_il_dwh_dossier_dossier_gen_id on bidwh.il_dwh_dossier il_dwh_dossier_1 (cost=0.43..3.96 rows=1 width=37) (actual time=0.015..0.015 rows=1 loops=880)

  • Output: il_dwh_dossier_1.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.dl_flg_inv, il_dwh_dossier_1.dossier_id, il_dwh_dossier_1.no_gen_dossier, il_dwh_dossier_1.dossier_gen_id, il_dwh_dossier_1.mut_dossier_id, il_dwh_dossier_1.mut_dossier_id_prec, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.contrat_id, il_dwh_dossier_1.numero_contrat, il_dwh_dossier_1.donnees_mut_dossier_id, il_dwh_dossier_1.per_gest_id, il_dwh_dossier_1.mut_id, il_dwh_dossier_1.dte_mut, il_dwh_dossier_1.description, il_dwh_dossier_1.desc_treeview, il_dwh_dossier_1.etat, il_dwh_dossier_1.description_etat_mut_dossier, il_dwh_dossier_1.statut, il_dwh_dossier_1.description_statut_mut_dossier, il_dwh_dossier_1.statut_precedent, il_dwh_dossier_1.description_statut_precedent, il_dwh_dossier_1.no_mut, il_dwh_dossier_1.no_mut_precedente, il_dwh_dossier_1.no_mut_extournee, il_dwh_dossier_1.maj_cpt_epa, il_dwh_dossier_1.dte_prochaine_fact_escompte, il_dwh_dossier_1.visa_cree_par, il_dwh_dossier_1.dte_creation, il_dwh_dossier_1.visa_modifie_par, il_dwh_dossier_1.dte_modification, il_dwh_dossier_1.visa_ctrl_par, il_dwh_dossier_1.dte_ctrl, il_dwh_dossier_1.numero_regrpmnt_mut, il_dwh_dossier_1.dte_derniere_fact_coti, il_dwh_dossier_1.dte_derniere_fact_prest, il_dwh_dossier_1.pro_id, il_dwh_dossier_1.dossier_parent_id, il_dwh_dossier_1.dte_affiliation, il_dwh_dossier_1.mnt_epa_tot, il_dwh_dossier_1.cd_cpte_epa_tot_d_ext, il_dwh_dossier_1.cd_cpte_epa_tot_fann, il_dwh_dossier_1.mnt_epa_min_lpp, il_dwh_dossier_1.cd_cpte_epa_min_lpp_d_ext, il_dwh_dossier_1.cd_cpte_epa_min_lpp_fann, il_dwh_dossier_1.dte_blocage_fact, il_dwh_dossier_1.raison_blocage_fact, il_dwh_dossier_1.visa_blocage_fact, il_dwh_dossier_1.dte_operation_blocage_fact, il_dwh_dossier_1.envoi_quest_mise_pension, il_dwh_dossier_1.objet_facturable_cree, il_dwh_dossier_1.deposant_epa, il_dwh_dossier_1.confidentiel, il_dwh_dossier_1.code_voir_dossier, il_dwh_dossier_1.impots_source, il_dwh_dossier_1.avoir_50_ans, il_dwh_dossier_1.retour_avoir_min_lpp, il_dwh_dossier_1.int_retour_avoir_min_lpp, il_dwh_dossier_1.retour_avoir_evlp, il_dwh_dossier_1.int_retour_avoir_evlp, il_dwh_dossier_1.restitution_avoir_min_lpp, il_dwh_dossier_1.int_restitution_avoir_min_lpp, il_dwh_dossier_1.restitution_avoir_evlp, il_dwh_dossier_1.int_restitution_avoir_evlp, il_dwh_dossier_1.beneficiaires_deces, il_dwh_dossier_1.donnees_medicales_introduites, il_dwh_dossier_1.description_donnees_med_int, il_dwh_dossier_1.refus_medical, il_dwh_dossier_1.cause_refus_medical, il_dwh_dossier_1.non_reponse_etat_sante, il_dwh_dossier_1.suivi_medical, il_dwh_dossier_1.description_suivi_medical, il_dwh_dossier_1.dte_suivi_medical, il_dwh_dossier_1.surprimes, il_dwh_dossier_1.reserves, il_dwh_dossier_1.inva, il_dwh_dossier_1.tot_rev_paye, il_dwh_dossier_1.dte_calc_avoir, il_dwh_dossier_1.dte_precedent_calc_avoir, il_dwh_dossier_1.dte_derniere_idx, il_dwh_dossier_1.dte_prochaine_annualisation, il_dwh_dossier_1.dte_prochain_bouclement, il_dwh_dossier_1.dte_prochain_recalcul, il_dwh_dossier_1.prise_cpt_causes_reduc, il_dwh_dossier_1.somme_tx_validite_inva, il_dwh_dossier_1.vers_anticipe_effectue, il_dwh_dossier_1.dte_retraite, il_dwh_dossier_1.trtmt_partie_active_retraite, il_dwh_dossier_1.dte_deces_reel, il_dwh_dossier_1.dte_demission, il_dwh_dossier_1.freq_paie_rentes, il_dwh_dossier_1.description_frequence_paiement, il_dwh_dossier_1.capital_inva, il_dwh_dossier_1.cd_capital_inva_d_ext, il_dwh_dossier_1.cd_capital_inva_fann, il_dwh_dossier_1.tx_inva_externe, il_dwh_dossier_1.cause_fin_inva, il_dwh_dossier_1.description_cause_fin_invalidi, il_dwh_dossier_1.vers_anticipe_en_cours, il_dwh_dossier_1.dte_demande_vers_anticipe, il_dwh_dossier_1.dte_der_vers_anticipe, il_dwh_dossier_1.raison_vers_anticipe, il_dwh_dossier_1.description_raison_versement_a, il_dwh_dossier_1.remb_tot_der_vers_anticipe, il_dwh_dossier_1.remb_tot_ensbl_vers_anticipes, il_dwh_dossier_1.div_en_cours, il_dwh_dossier_1.inscription_registre_foncier, il_dwh_dossier_1.mise_en_gage, il_dwh_dossier_1.jamais_affilie, il_dwh_dossier_1.dte_premiere_affiliation, il_dwh_dossier_1.dte_demande_avance_avs, il_dwh_dossier_1.avance_avs, il_dwh_dossier_1.dte_fin_avance_avs, il_dwh_dossier_1.mnt_avance_avs, il_dwh_dossier_1.ajournement, il_dwh_dossier_1.dte_fin_ajournement, il_dwh_dossier_1.maintien_fina_ajournement, il_dwh_dossier_1.ajournement_annee_en_annee, il_dwh_dossier_1.dte_prochain_ajournement, il_dwh_dossier_1.age_premier_ajournement, il_dwh_dossier_1.calc_av_terme, il_dwh_dossier_1.conge, il_dwh_dossier_1.demande_affiliation_recue, il_dwh_dossier_1.suri, il_dwh_dossier_1.dte_der_ctrl_suri, il_dwh_dossier_1.regles_avert_outrepassees, il_dwh_dossier_1.adresse_paie_connue, il_dwh_dossier_1.somme_risquee_reass_deces, il_dwh_dossier_1.somme_risquee_reass_deces_mvt, il_dwh_dossier_1.cd_som_risq_reas_d_d_ext, il_dwh_dossier_1.cd_som_risq_reas_d_fann, il_dwh_dossier_1.somme_risquee_reass_inva, il_dwh_dossier_1.somme_risquee_reass_inva_mvt, il_dwh_dossier_1.cd_som_risq_reas_i_d_ext, il_dwh_dossier_1.cd_som_risq_reas_i_fann, il_dwh_dossier_1.annonce_reass_deces, il_dwh_dossier_1.annonce_reass_inva, il_dwh_dossier_1.duree_cip_illimitee, il_dwh_dossier_1.dte_entree_actuarielle, il_dwh_dossier_1.dte_entree_av_conversion, il_dwh_dossier_1.dte_deb_conge, il_dwh_dossier_1.dte_deb_conge_effectif, il_dwh_dossier_1.dte_retour_conge, il_dwh_dossier_1.dte_retour_conge_effectif, il_dwh_dossier_1.conge_administratif, il_dwh_dossier_1.duree_conges_non_rachetes, il_dwh_dossier_1.mnt_rachat_excd, il_dwh_dossier_1.dte_rachat_excd, il_dwh_dossier_1.dte_echeance_rachat_excd, il_dwh_dossier_1.dte_fin_fine_entree, il_dwh_dossier_1.sal_inferieur_min_lpp, il_dwh_dossier_1.calc_prest_sortie_gar, il_dwh_dossier_1.dte_demande_retraite_capital, il_dwh_dossier_1.dte_ren_retraite_capital, il_dwh_dossier_1.dte_ren_avance_avs, il_dwh_dossier_1.type_compens, il_dwh_dossier_1.dte_retraite_compensee, il_dwh_dossier_1.capital_compens_retraite, il_dwh_dossier_1.capital_compens_suppltemp, il_dwh_dossier_1.paie_autr_beneficiaires_deces, il_dwh_dossier_1.suppltemp_avs, il_dwh_dossier_1.majo_suppltemp_avs, il_dwh_dossier_1.dte_fin_suppltemp_avs, il_dwh_dossier_1.dte_fin_majo_suppltemp_avs, il_dwh_dossier_1.dte_calc_situation_ass, il_dwh_dossier_1.majo_tx_pension, il_dwh_dossier_1.no_gen_dossier_situation_ass, il_dwh_dossier_1.numero_dossier_etat, il_dwh_dossier_1.cause_changement_employeur, il_dwh_dossier_1.type_mut_id, il_dwh_dossier_1.app_gest_id, il_dwh_dossier_1.nom_mut, il_dwh_dossier_1.niveau, il_dwh_dossier_1.impact_dossiers, il_dwh_dossier_1.genere_par_contrat, il_dwh_dossier_1.ged_mut_1, il_dwh_dossier_1.ged_mut_2, il_dwh_dossier_1.description_per_ges, il_dwh_dossier_1.dte_deb_per_ges, il_dwh_dossier_1.dte_fin_per_ges, il_dwh_dossier_1.statut_periode_gestion, il_dwh_dossier_1.description_statut_per_ges, il_dwh_dossier_1.base_salariale_id, il_dwh_dossier_1.dte_ref_base_sal, il_dwh_dossier_1.dte_fin_derniere_moyenne_base_salariale, il_dwh_dossier_1.base_salariale, il_dwh_dossier_1.base_coti_empl, il_dwh_dossier_1.base_coti_employeur, il_dwh_dossier_1.base_coti_tiers, il_dwh_dossier_1.base_fine_entree, il_dwh_dossier_1.op_usr_log_i, il_dwh_dossier_1.op_dte_log_i, il_dwh_dossier_1.op_usr_log_u, il_dwh_dossier_1.op_dte_log_u, il_dwh_dossier_1.age_terme_vise, il_dwh_dossier_1.code_urev_urcip, il_dwh_dossier_1.pas_pu_se_prononcer, il_dwh_dossier_1.prest_sortie_en_especes, il_dwh_dossier_1.gest_reservee, il_dwh_dossier_1.usr_log_i, il_dwh_dossier_1.dte_log_i, il_dwh_dossier_1.usr_log_u, il_dwh_dossier_1.dte_log_u, il_dwh_dossier_1.reass_deces, il_dwh_dossier_1.reass_inva, il_dwh_dossier_1.inva_proportion_reassuree, il_dwh_dossier_1.annonce_reass_deces_facultatif, il_dwh_dossier_1.annonce_reass_inva_facultatif, il_dwh_dossier_1.annonce_reass_deces_sinistre, il_dwh_dossier_1.annonce_reass_inva_sinistre, il_dwh_dossier_1.dte_escompte, il_dwh_dossier_1.part_id, il_dwh_dossier_1.nom_ou_raison_sociale, il_dwh_dossier_1.prenom, il_dwh_dossier_1.sexe, il_dwh_dossier_1.dte_naissance, il_dwh_dossier_1.statut_avant, il_dwh_dossier_1.mvt_actif, il_dwh_dossier_1.mvt_pens, il_dwh_dossier_1.mvt_term, il_dwh_dossier_1.mvt_bloq, il_dwh_dossier_1.prct_avance_avs, il_dwh_dossier_1.remb_avance_avs_prime_unique, il_dwh_dossier_1.paie_remb_avance_avs_effectue, il_dwh_dossier_1.frais_gest_demission, il_dwh_dossier_1.difference_cumulee, il_dwh_dossier_1.dte_calc, il_dwh_dossier_1.dte_fin_cumul, il_dwh_dossier_1.ecart_bande_etat_id, il_dwh_dossier_1.orig_mut, il_dwh_dossier_1.suivi_demande_affiliation, il_dwh_dossier_1.dte_suivi_demande_affiliation, il_dwh_dossier_1.annonce_extranet_id, il_dwh_dossier_1.blocage_extranet, il_dwh_dossier_1.responsable_gest, il_dwh_dossier_1.responsable_gest_medicale, il_dwh_dossier_1.stipulation_beneficiaire, il_dwh_dossier_1.pg_mut_ext, il_dwh_dossier_1.mois_demission_paye, il_dwh_dossier_1.pleine_capacite_travail, il_dwh_dossier_1.beneficiaire_rte_ai, il_dwh_dossier_1.dte_deb_inva_reconnue_ai, il_dwh_dossier_1.envoi_doc_affiliation, il_dwh_dossier_1.adresse_paie, il_dwh_dossier_1.id_mut_blocage_fact, il_dwh_dossier_1.dte_calc_deroulement, il_dwh_dossier_1.dte_fina_plus_maintenu, il_dwh_dossier_1.no_offre, il_dwh_dossier_1.raison_blocage_mutations, il_dwh_dossier_1.blocage_mutations, il_dwh_dossier_1.dte_blocage_mutations, il_dwh_dossier_1.donnees_retraite_annoncees, il_dwh_dossier_1.mnt_rachat_deg_pos, il_dwh_dossier_1.suivi_retraite, il_dwh_dossier_1.dte_prochain_suivi_retraite, il_dwh_dossier_1.quest_retraite_employeur_recu
  • Index Cond: (il_dwh_dossier_1.dossier_gen_id = il_dwh_partie_dossier_1.dossier_gen_id)
  • Filter: ((il_dwh_dossier_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_dossier_1.dl_flg_inv = 1) AND (il_dwh_dossier_1.dl_flg_bcl = 1) AND (il_dwh_partie_dossier_1.dl_soc_id = il_dwh_dossier_1.dl_soc_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4421
  • Worker 0: actual time=0.014..0.015 rows=1 loops=225
  • Buffers: shared hit=1129
  • Worker 1: actual time=0.017..0.018 rows=1 loops=157
  • Buffers: shared hit=789
  • Worker 2: actual time=0.014..0.014 rows=1 loops=243
  • Buffers: shared hit=1223
  • Worker 3: actual time=0.014..0.015 rows=1 loops=242
  • Buffers: shared hit=1215
71. 4.752 4.752 ↑ 1.0 1 880 / 5

Index Scan using olm_il_dwh_plan_partie_partie_dossier_id on bidwh.il_dwh_plan_partie il_dwh_plan_partie_1 (cost=0.44..9.94 rows=1 width=28) (actual time=0.025..0.027 rows=1 loops=880)

  • Output: il_dwh_plan_partie_1.dl_datextract, il_dwh_plan_partie_1.dl_soc_id, il_dwh_plan_partie_1.dl_flg_bcl, il_dwh_plan_partie_1.dl_flg_inv, il_dwh_plan_partie_1.dossier_id, il_dwh_plan_partie_1.dossier_gen_id, il_dwh_plan_partie_1.no_gen_dossier, il_dwh_plan_partie_1.numero_dossier, il_dwh_plan_partie_1.mut_dossier_id, il_dwh_plan_partie_1.contrat_id, il_dwh_plan_partie_1.numero_contrat, il_dwh_plan_partie_1.plan_partie_id, il_dwh_plan_partie_1.partie_dossier_id, il_dwh_plan_partie_1.plan_employeur_affilie_id, il_dwh_plan_partie_1.plan_partie_orig_id, il_dwh_plan_partie_1.plan_base_id, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.nom_comm, il_dwh_plan_partie_1.type_plan, il_dwh_plan_partie_1.description_type_plan, il_dwh_plan_partie_1.type_prev_lpp, il_dwh_plan_partie_1.description_type_prevoyance_lp, il_dwh_plan_partie_1.etat, il_dwh_plan_partie_1.deduc_1, il_dwh_plan_partie_1.deduc_2, il_dwh_plan_partie_1.mnt_capital_retraite, il_dwh_plan_partie_1.prct_capital_retraite, il_dwh_plan_partie_1.mnt_rte_retraite, il_dwh_plan_partie_1.prct_rte_retraite, il_dwh_plan_partie_1.corrections_manuelles, il_dwh_plan_partie_1.dte_effet, il_dwh_plan_partie_1.recalcul_prest_av_terme, il_dwh_plan_partie_1.figer_recalcul_prest_av_terme, il_dwh_plan_partie_1.propres_coti, il_dwh_plan_partie_1.int_propres_coti, il_dwh_plan_partie_1.propres_coti_prov, il_dwh_plan_partie_1.propres_coti_majorees, il_dwh_plan_partie_1.rachats, il_dwh_plan_partie_1.int_rachats, il_dwh_plan_partie_1.avoir_transfere, il_dwh_plan_partie_1.freq_paie_coti, il_dwh_plan_partie_1.description_frequence_paiement, il_dwh_plan_partie_1.surp_paie_frac_inclus, il_dwh_plan_partie_1.valorisable, il_dwh_plan_partie_1.dte_entree_theorique, il_dwh_plan_partie_1.dte_entree_av_va, il_dwh_plan_partie_1.dte_prevue_deb_inva, il_dwh_plan_partie_1.deduc_gar, il_dwh_plan_partie_1.deduc_gar_precedent, il_dwh_plan_partie_1.sal_cotisant_garanti, il_dwh_plan_partie_1.sal_cotisant_garanti_precedent, il_dwh_plan_partie_1.propres_coti_ord, il_dwh_plan_partie_1.propres_coti_maintien, il_dwh_plan_partie_1.part_suppltemp_rachats, il_dwh_plan_partie_1.rachats_sans_int, il_dwh_plan_partie_1.prest_sortie_gar_base, il_dwh_plan_partie_1.prop_coti_ord_prestsortgar, il_dwh_plan_partie_1.prop_coti_maint_prestsortgar, il_dwh_plan_partie_1.prop_coti_maj_prestsortgar, il_dwh_plan_partie_1.rachats_prestsortgar, il_dwh_plan_partie_1.vers_anticipes_prestsortgar, il_dwh_plan_partie_1.prct_capital_lpp_retraite, il_dwh_plan_partie_1.prest_payees, il_dwh_plan_partie_1.freq_paie_coti_figee, il_dwh_plan_partie_1.rachats_prevus, il_dwh_plan_partie_1.propo_rachats_id, il_dwh_plan_partie_1.rachat_unique_max, il_dwh_plan_partie_1.capital_retraite_max, il_dwh_plan_partie_1.rte_retraite_max, il_dwh_plan_partie_1.rachat_mens_max, il_dwh_plan_partie_1.rachat_unique_saisi, il_dwh_plan_partie_1.capital_retraite_rachat_unique, il_dwh_plan_partie_1.rte_retraite_rachat_unique, il_dwh_plan_partie_1.rachat_mens_saisi, il_dwh_plan_partie_1.capital_retraite_rachat_mens, il_dwh_plan_partie_1.rte_retraite_rachat_mens, il_dwh_plan_partie_1.capital_retraite_souhaite, il_dwh_plan_partie_1.rte_retraite_shtee, il_dwh_plan_partie_1.rachat_unique_retraite_shtee, il_dwh_plan_partie_1.rachat_mens_retraite_shtee, il_dwh_plan_partie_1.dn_mut_id, il_dwh_plan_partie_1.dn_dte_mut, il_dwh_plan_partie_1.dn_nom_mut, il_dwh_plan_partie_1.pro_id, il_dwh_plan_partie_1.part_suppltemp_rachat_prevu, il_dwh_plan_partie_1.rachat_prevu_prestsortgar, il_dwh_plan_partie_1.corrections_manuelles_primes, il_dwh_plan_partie_1.corrections_manuelles_prest, il_dwh_plan_partie_1.remb_va_prevus, il_dwh_plan_partie_1.part_suppltemp_remb_va_prevus, il_dwh_plan_partie_1.remb_va_prevus_prestsortgar, il_dwh_plan_partie_1.mnt_max_deb_ajournement, il_dwh_plan_partie_1.remb_va_a_recevoir, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.max_rachetable_31_12, il_dwh_plan_partie_1.sal_min_1, il_dwh_plan_partie_1.sal_min_2, il_dwh_plan_partie_1.sal_max_1, il_dwh_plan_partie_1.sal_max_2, il_dwh_plan_partie_1.duree_gar_sal_retraite, il_dwh_plan_partie_1.duree_gar_sal_risque, il_dwh_plan_partie_1.duree_gar_sal_sortie, il_dwh_plan_partie_1.capital_retraite, il_dwh_plan_partie_1.correction_ps_rappel, il_dwh_plan_partie_1.plan_calcul_avant_terme, il_dwh_plan_partie_1.prct_cpt_pref_retraite, il_dwh_plan_partie_1.prct_cpt_exc_lp, il_dwh_plan_partie_1.pref_retraite, il_dwh_plan_partie_1.int_pref_retraite, il_dwh_plan_partie_1.excedents_lp, il_dwh_plan_partie_1.int_excedents_lp, il_dwh_plan_partie_1.usr_log_i, il_dwh_plan_partie_1.dte_log_i, il_dwh_plan_partie_1.usr_log_u, il_dwh_plan_partie_1.dte_log_u, il_dwh_plan_partie_1.cd_propres_coti_majorees_dat_ext, il_dwh_plan_partie_1.cd_propres_coti_majorees_fin_ann, il_dwh_plan_partie_1.cd_int_rachats_dat_ext, il_dwh_plan_partie_1.cd_int_rachats_fin_ann, il_dwh_plan_partie_1.cd_int_pref_retraite_dat_ext, il_dwh_plan_partie_1.cd_int_pref_retraite_fin_ann, il_dwh_plan_partie_1.cd_int_excedents_lp_dat_ext, il_dwh_plan_partie_1.cd_int_excedents_lp_fin_ann
  • Index Cond: (il_dwh_plan_partie_1.partie_dossier_id = il_dwh_partie_dossier_1.partie_dossier_id)
  • Filter: (((il_dwh_plan_partie_1.type_plan)::text <> 'MLPP'::text) AND (il_dwh_plan_partie_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_plan_partie_1.dl_flg_inv = 1) AND (il_dwh_plan_partie_1.dl_flg_bcl = 1) AND (il_dwh_partie_dossier_1.dl_soc_id = il_dwh_plan_partie_1.dl_soc_id))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=5337
  • Worker 0: actual time=0.029..0.032 rows=1 loops=225
  • Buffers: shared hit=1374
  • Worker 1: actual time=0.028..0.030 rows=1 loops=157
  • Buffers: shared hit=955
  • Worker 2: actual time=0.023..0.025 rows=1 loops=243
  • Buffers: shared hit=1481
  • Worker 3: actual time=0.023..0.025 rows=1 loops=242
  • Buffers: shared hit=1452
72. 17.720 17.720 ↓ 2.0 2 886 / 5

Index Scan using olm_il_dwh_pa_gen_plan_partie_id on bidwh.il_dwh_pa_gen il_dwh_pa_gen_1 (cost=0.57..331.74 rows=1 width=49) (actual time=0.069..0.100 rows=2 loops=886)

  • Output: il_dwh_pa_gen_1.dl_datextract, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_1.dl_flg_bcl, il_dwh_pa_gen_1.dl_flg_inv, il_dwh_pa_gen_1.pa_gen_id, il_dwh_pa_gen_1.dossier_id, il_dwh_pa_gen_1.dossier_gen_id, il_dwh_pa_gen_1.no_gen_dossier, il_dwh_pa_gen_1.numero_dossier, il_dwh_pa_gen_1.mut_dossier_id, il_dwh_pa_gen_1.mut_dossier_id_prec, il_dwh_pa_gen_1.contrat_id, il_dwh_pa_gen_1.numero_contrat, il_dwh_pa_gen_1.assure_part_dossier_id, il_dwh_pa_gen_1.vco_id, il_dwh_pa_gen_1.cou_id, il_dwh_pa_gen_1.desc_crt_pa, il_dwh_pa_gen_1.plan_partie_id, il_dwh_pa_gen_1.pa_gen_orig_id, il_dwh_pa_gen_1.pa_gen_base_prest_id, il_dwh_pa_gen_1.pa_gen_base_coti_id, il_dwh_pa_gen_1.pa_gen_base_plan_base_prest_id, il_dwh_pa_gen_1.pa_gen_base_plan_base_coti_id, il_dwh_pa_gen_1.dn_nom_plan, il_dwh_pa_gen_1.dn_plan_base_id, il_dwh_pa_gen_1.dn_plan_partie_orig_id, il_dwh_pa_gen_1.partie_dossier_id, il_dwh_pa_gen_1.type_plan, il_dwh_pa_gen_1.description_type_plan, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.description_type_couv, il_dwh_pa_gen_1.etat, il_dwh_pa_gen_1.description_etat_pa_gen, il_dwh_pa_gen_1.dte_effet, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.dte_effet_pe, il_dwh_pa_gen_1.dte_effet_idx, il_dwh_pa_gen_1.dte_base_calc, il_dwh_pa_gen_1.dte_fin_differe, il_dwh_pa_gen_1.dte_deb_inca_gain, il_dwh_pa_gen_1.age_deb_inca_gain, il_dwh_pa_gen_1.delai_attente, il_dwh_pa_gen_1.capital_rte, il_dwh_pa_gen_1.description_capital_rente, il_dwh_pa_gen_1.doublement_rte_enfant, il_dwh_pa_gen_1.freq_paie_rte, il_dwh_pa_gen_1.description_frequence_paiement, il_dwh_pa_gen_1.sal, il_dwh_pa_gen_1.classe_risque, il_dwh_pa_gen_1.tx_prest_base, il_dwh_pa_gen_1.dte_derniere_idx_liee_avs, il_dwh_pa_gen_1.numero_ub_refact_base, il_dwh_pa_gen_1.numero_ub_refact_idx, il_dwh_pa_gen_1.no_var, il_dwh_pa_gen_1.nom_var, il_dwh_pa_gen_1.dte_deb, il_dwh_pa_gen_1.dte_fin, il_dwh_pa_gen_1.tx_int_technique, il_dwh_pa_gen_1.alpha1, il_dwh_pa_gen_1.alpha2, il_dwh_pa_gen_1.alpha3, il_dwh_pa_gen_1.beta, il_dwh_pa_gen_1.gamma1, il_dwh_pa_gen_1.gamma2, il_dwh_pa_gen_1.sigma, il_dwh_pa_gen_1.constante1, il_dwh_pa_gen_1.constante2, il_dwh_pa_gen_1.constante_pri_pure, il_dwh_pa_gen_1.penalite_rachat, il_dwh_pa_gen_1.maj_prest, il_dwh_pa_gen_1.utilisation_tbl_maj, il_dwh_pa_gen_1.duree_reduc_paie_pri, il_dwh_pa_gen_1.duree_gar_differe, il_dwh_pa_gen_1.age_terme_idx, il_dwh_pa_gen_1.dn_mut_id, il_dwh_pa_gen_1.dn_dte_mut, il_dwh_pa_gen_1.dn_nom_mut, il_dwh_pa_gen_1.pro_id, il_dwh_pa_gen_1.nom_ou_raison_sociale, il_dwh_pa_gen_1.prenom, il_dwh_pa_gen_1.dte_naissance, il_dwh_pa_gen_1.sexe, il_dwh_pa_gen_1.usr_log_i, il_dwh_pa_gen_1.dte_log_i, il_dwh_pa_gen_1.usr_log_u, il_dwh_pa_gen_1.dte_log_u, il_dwh_pa_gen_1.avt_ap_ter, il_dwh_pa_gen_1.type_fina_avance_avs, il_dwh_pa_gen_1.prct_avance_avs, il_dwh_pa_gen_1.type_numerando, il_dwh_pa_gen_1.vco_precedente_id, il_dwh_pa_gen_1.categorie_empl_secteur_act_id, il_dwh_pa_gen_1.secteur_act_origine_id
  • Index Cond: (il_dwh_pa_gen_1.plan_partie_id = il_dwh_plan_partie_1.plan_partie_id)
  • Filter: ((il_dwh_pa_gen_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen_1.dl_flg_inv = 1) AND (il_dwh_pa_gen_1.dl_flg_bcl = 1) AND ((il_dwh_pa_gen_1.etat)::text = 'VAL'::text) AND ((il_dwh_pa_gen_1.type_couv)::text = 'RCON'::text) AND (il_dwh_plan_partie_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=16755
  • Worker 0: actual time=0.066..0.094 rows=2 loops=226
  • Buffers: shared hit=4283
  • Worker 1: actual time=0.088..0.129 rows=2 loops=158
  • Buffers: shared hit=3058
  • Worker 2: actual time=0.066..0.094 rows=2 loops=246
  • Buffers: shared hit=4681
  • Worker 3: actual time=0.064..0.094 rows=2 loops=243
  • Buffers: shared hit=4501
73. 3.320 3.320 ↑ 1.0 1 1,660

Index Scan using bi_vcouv_pkey on biinfra.bi_vcouv bi_vcouv_1 (cost=0.28..2.20 rows=1 width=81) (actual time=0.002..0.002 rows=1 loops=1,660)

  • Output: bi_vcouv_1.vco_id, bi_vcouv_1.vco_no_vari, bi_vcouv_1.tde1x_nom_table, bi_vcouv_1.tde1y_nom_table, bi_vcouv_1.tde2x_nom_table, bi_vcouv_1.tde2y_nom_table, bi_vcouv_1.tin1x_nom_table, bi_vcouv_1.tin1y_nom_table, bi_vcouv_1.tin_p1x_nom_table, bi_vcouv_1.tin_p1y_nom_table, bi_vcouv_1.tma1x_nom_table, bi_vcouv_1.tma1y_nom_table, bi_vcouv_1.tca_d1x_nom_table, bi_vcouv_1.tca_d1y_nom_table, bi_vcouv_1.tca_i1x_nom_table, bi_vcouv_1.tca_i1y_nom_table, bi_vcouv_1.tcc_id, bi_vcouv_1.vco_dte_deb, bi_vcouv_1.vco_dte_fin, bi_vcouv_1.tx_interet_tec, bi_vcouv_1.alpha1, bi_vcouv_1.alpha2, bi_vcouv_1.alpha3, bi_vcouv_1.beta, bi_vcouv_1.gamma1, bi_vcouv_1.gamma2, bi_vcouv_1.sigma, bi_vcouv_1.cste1, bi_vcouv_1.cste2, bi_vcouv_1.cste_pri_pure, bi_vcouv_1.penalite_rachat, bi_vcouv_1.maj_prest, bi_vcouv_1.use_table_maj, bi_vcouv_1.duree_red_paie_pri, bi_vcouv_1.fo_vap, bi_vcouv_1.fo_vas, bi_vcouv_1.fo_alpha, bi_vcouv_1.fo_age_a1_n_aff, bi_vcouv_1.fo_age_a2_n_aff, bi_vcouv_1.fo_age_couple_n_aff, bi_vcouv_1.fo_age_a1_tran, bi_vcouv_1.fo_age_a2_tran, bi_vcouv_1.fo_age_couple_tran, bi_vcouv_1.fo_age_a1_val, bi_vcouv_1.fo_age_a2_val, bi_vcouv_1.fo_age_couple_val, bi_vcouv_1.fo_rachat, bi_vcouv_1.fo_pri, bi_vcouv_1.fo_duree_restit, bi_vcouv_1.fo_interpol, bi_vcouv_1.fo_deces, bi_vcouv_1.fo_invalidite, bi_vcouv_1.fo_echeance, bi_vcouv_1.fo_prest_subord, bi_vcouv_1.fo_reserve, bi_vcouv_1.fo_som_risq_deces_int, bi_vcouv_1.fo_som_risq_invalid_int, bi_vcouv_1.fo_som_risq_deces_reass, bi_vcouv_1.fo_som_risq_invalidite_reass, bi_vcouv_1.fo_pe1, bi_vcouv_1.fo_pe2, bi_vcouv_1.fo_ajournement, bi_vcouv_1.fo_fin_paiement, bi_vcouv_1.fo_pri_pure, bi_vcouv_1.fo_bonus_calcul, bi_vcouv_1.fo_report_prime, bi_vcouv_1.fo_report_prest, bi_vcouv_1.fo_zillmer, bi_vcouv_1.fo_alpha_zillmer, bi_vcouv_1.fo_prestation_minimale, bi_vcouv_1.fo_prestation_pe, bi_vcouv_1.duree_garantie_differe, bi_vcouv_1.age_terme_indexation, bi_vcouv_1.type_calcul_indexation, bi_vcouv_1.fo_age_terme_indexation, bi_vcouv_1.fo_somme_risquee, bi_vcouv_1.fo_va, bi_vcouv_1.fo_rente_en_capital, bi_vcouv_1.fo_prest_compretravan, bi_vcouv_1.fo_capital_compretravan, bi_vcouv_1.prest_a_facturer, bi_vcouv_1.tarif_classe_risque, bi_vcouv_1.type_var_cou, bi_vcouv_1.vco_standard_id, bi_vcouv_1.fo_val_residuelle, bi_vcouv_1.fo_rachat_fiscal, bi_vcouv_1.fo_capital_fin_differe, bi_vcouv_1.fo_valeur_fonds_placement, bi_vcouv_1.alpha4, bi_vcouv_1.cou_id, bi_vcouv_1.soc_id, bi_vcouv_1.cou_desc_courte, bi_vcouv_1.cou_type_calc_pri, bi_vcouv_1.cou_type_calc_prest, bi_vcouv_1.cou_type_pror_fina_rte, bi_vcouv_1.cou_type_pror_init_rte, bi_vcouv_1.cou_type_pri, bi_vcouv_1.cou_type_int, bi_vcouv_1.cou_type_echeance_prest, bi_vcouv_1.cou_arrondi_pri, bi_vcouv_1.cou_arrondi_prest, bi_vcouv_1.cou_arrondi_res, bi_vcouv_1.cou_soumis_drt_timbre, bi_vcouv_1.cou_type_prestation, bi_vcouv_1.cou_fo_remplissage_date, bi_vcouv_1.cou_fo_remplissage_date_age, bi_vcouv_1.cou_type_mal_acc, bi_vcouv_1.cou_ged_definition1, bi_vcouv_1.cou_ged_definition2, bi_vcouv_1.cou_type_prest_2p, bi_vcouv_1.cou_avant_apres_terme, bi_vcouv_1.cou_inclus_suri, bi_vcouv_1.cou_type_rte_conjoint, bi_vcouv_1.cou_type_arrondi_prest, bi_vcouv_1.cou_nombre_tetes, bi_vcouv_1.cou_ordre_tri, bi_vcouv_1.col_nom_couv_fr, bi_vcouv_1.col_nom_pour_gest_fr, bi_vcouv_1.col_desc_longue_fr, bi_vcouv_1.col_nom_commercial_fr, bi_vcouv_1.act_nom_couv, bi_vcouv_1.act_nom_type_couv, bi_vcouv_1.act_nom_cat_couv, bi_vcouv_1.act_nom_cta, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.tco_type_sal, bi_vcouv_1.tco_type_risque, bi_vcouv_1.tco_ctrl_min_lpp, bi_vcouv_1.tco_regroupement_envoi_prestations, bi_vcouv_1.tco_rente_en_cours, bi_vcouv_1.tco_complement_avs, bi_vcouv_1.tco_prest_compl, bi_vcouv_1.tco_tri_couv_avant_terme, bi_vcouv_1.tco_tri_couv_apres_terme, bi_vcouv_1.tcl_desc_longue, bi_vcouv_1.cta_id, bi_vcouv_1.cta_nom_fr, bi_vcouv_1.pec_id, bi_vcouv_1.pec_nom, bi_vcouv_1.pcc_id, bi_vcouv_1.pcc_dte_deb, bi_vcouv_1.pcc_dte_fin, bi_vcouv_1.pcc_nom, bi_vcouv_1.pcc_delai_car1, bi_vcouv_1.pcc_delai_car2, bi_vcouv_1.ccc_cca_dte_deb, bi_vcouv_1.ccc_cca_dte_fin, bi_vcouv_1.cca_id, bi_vcouv_1.cca_ref_cca, bi_vcouv_1.cca_description, bi_vcouv_1.cca_doc_url, bi_vcouv_1.ccl_nom_commercial_cca_fr, bi_vcouv_1.ccl_doc_name_cca_fr
  • Index Cond: (bi_vcouv_1.vco_id = il_dwh_pa_gen_1.vco_id)
  • Buffers: shared hit=4980
74. 11.620 11.620 ↑ 1.0 1 1,660

Index Scan using olm_il_dwh_pa_gen_prest_pa_gen_id on bidwh.il_dwh_pa_gen_prest il_dwh_pa_gen_prest_1 (cost=0.57..5.03 rows=1 width=29) (actual time=0.007..0.007 rows=1 loops=1,660)

  • Output: il_dwh_pa_gen_prest_1.dl_datextract, il_dwh_pa_gen_prest_1.dl_soc_id, il_dwh_pa_gen_prest_1.dl_flg_bcl, il_dwh_pa_gen_prest_1.dl_flg_inv, il_dwh_pa_gen_prest_1.pa_gen_prest_id, il_dwh_pa_gen_prest_1.pa_gen_id, il_dwh_pa_gen_prest_1.pa_gen_orig_id, il_dwh_pa_gen_prest_1.dossier_id, il_dwh_pa_gen_prest_1.no_gen_dossier, il_dwh_pa_gen_prest_1.dossier_gen_id, il_dwh_pa_gen_prest_1.contrat_id, il_dwh_pa_gen_prest_1.corrections_manuelles, il_dwh_pa_gen_prest_1.prest_reglementaire, il_dwh_pa_gen_prest_1.prest_reglementaire_mvt, il_dwh_pa_gen_prest_1.prest_base, il_dwh_pa_gen_prest_1.prest_base_mvt, il_dwh_pa_gen_prest_1.supplement_lpp, il_dwh_pa_gen_prest_1.supplement_lpp_mvt, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.prest_tot_mvt, il_dwh_pa_gen_prest_1.avoir_31_12, il_dwh_pa_gen_prest_1.avoir_31_12_mvt, il_dwh_pa_gen_prest_1.prest_projetee_avec_int, il_dwh_pa_gen_prest_1.prest_projetee_sans_int, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_mvt, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.participation_excedents_mvt, il_dwh_pa_gen_prest_1.reduc_base, il_dwh_pa_gen_prest_1.reduc_base_mvt, il_dwh_pa_gen_prest_1.reduc_supplement_lpp, il_dwh_pa_gen_prest_1.reduc_supplement_lpp_mvt, il_dwh_pa_gen_prest_1.reduc_idx, il_dwh_pa_gen_prest_1.reduc_idx_mvt, il_dwh_pa_gen_prest_1.reduc_participation_excedents, il_dwh_pa_gen_prest_1.reduc_participation_excedents_mvt, il_dwh_pa_gen_prest_1.supplement_propres_coti, il_dwh_pa_gen_prest_1.supplement_propres_coti_mvt, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_prest_1.prest_due_mvt, il_dwh_pa_gen_prest_1.prest_reversee_par_reass, il_dwh_pa_gen_prest_1.prest_reversee_par_reass_mvt, il_dwh_pa_gen_prest_1.allocation_menage, il_dwh_pa_gen_prest_1.allocation_menage_mvt, il_dwh_pa_gen_prest_1.supplement_droits_acquis, il_dwh_pa_gen_prest_1.supplement_droits_acquis_mvt, il_dwh_pa_gen_prest_1.compens_retraite_avancee, il_dwh_pa_gen_prest_1.compens_retraite_avancee_mvt, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi_mvt, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio_mvt, il_dwh_pa_gen_prest_1.supplement_prest_sortie_gar, il_dwh_pa_gen_prest_1.supplement_prest_sortie_gar_mvt, il_dwh_pa_gen_prest_1.reduc_allocation_menage, il_dwh_pa_gen_prest_1.reduc_allocation_menage_mvt, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio_mvt, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_capi_mvt, il_dwh_pa_gen_prest_1.reduc_supplement_droits_acquis, il_dwh_pa_gen_prest_1.reduc_supplement_droits_acquis_mvt, il_dwh_pa_gen_prest_1.dn_mut_id, il_dwh_pa_gen_prest_1.dn_dte_mut, il_dwh_pa_gen_prest_1.dn_nom_mut, il_dwh_pa_gen_prest_1.dn_plan_partie_id, il_dwh_pa_gen_prest_1.dn_partie_dossier_id, il_dwh_pa_gen_prest_1.dn_plan_base_id, il_dwh_pa_gen_prest_1.dn_plan_partie_orig_id, il_dwh_pa_gen_prest_1.dn_vco_id, il_dwh_pa_gen_prest_1.dn_nom_plan, il_dwh_pa_gen_prest_1.dn_type_plan, il_dwh_pa_gen_prest_1.description_type_plan, il_dwh_pa_gen_prest_1.mut_dossier_id, il_dwh_pa_gen_prest_1.mut_dossier_id_prec, il_dwh_pa_gen_prest_1.usr_log_i, il_dwh_pa_gen_prest_1.dte_log_i, il_dwh_pa_gen_prest_1.usr_log_u, il_dwh_pa_gen_prest_1.dte_log_u, il_dwh_pa_gen_prest_1.complement_fine_employeur, il_dwh_pa_gen_prest_1.reduc_compl_fine_employeur, il_dwh_pa_gen_prest_1.reduc_compens_retraite_avancee, il_dwh_pa_gen_prest_1.prest_gar, il_dwh_pa_gen_prest_1.supplement_garanti, il_dwh_pa_gen_prest_1.reduc_supplement_garanti, il_dwh_pa_gen_prest_1.compl_exc_libre_passage
  • Index Cond: (il_dwh_pa_gen_prest_1.pa_gen_id = il_dwh_pa_gen_1.pa_gen_id)
  • Filter: ((il_dwh_pa_gen_prest_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen_prest_1.dl_flg_bcl = 1) AND (il_dwh_pa_gen_prest_1.dl_flg_inv = 1) AND (il_dwh_pa_gen_prest_1.dl_datextract = il_dwh_pa_gen_1.dl_datextract) AND (il_dwh_pa_gen_prest_1.dl_flg_bcl = il_dwh_pa_gen_1.dl_flg_bcl) AND (il_dwh_pa_gen_prest_1.dl_flg_inv = il_dwh_pa_gen_1.dl_flg_inv) AND (il_dwh_pa_gen_prest_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10004
75. 9.960 9.960 ↓ 0.0 0 1,660

Index Scan using olm_il_dwh_pa_gen_valorisation_pa_gen_id on bidwh.il_dwh_pa_gen_valorisation il_dwh_pa_gen_valorisation_1 (cost=0.57..32.61 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1,660)

  • Output: il_dwh_pa_gen_valorisation_1.dl_datextract, il_dwh_pa_gen_valorisation_1.dl_soc_id, il_dwh_pa_gen_valorisation_1.dl_flg_bcl, il_dwh_pa_gen_valorisation_1.dl_flg_inv, il_dwh_pa_gen_valorisation_1.pa_gen_valorisation_id, il_dwh_pa_gen_valorisation_1.pa_gen_id, il_dwh_pa_gen_valorisation_1.pa_gen_orig_id, il_dwh_pa_gen_valorisation_1.dossier_id, il_dwh_pa_gen_valorisation_1.no_gen_dossier, il_dwh_pa_gen_valorisation_1.dossier_gen_id, il_dwh_pa_gen_valorisation_1.contrat_id, il_dwh_pa_gen_valorisation_1.type_av_ap, il_dwh_pa_gen_valorisation_1.description_type_avant_apres, il_dwh_pa_gen_valorisation_1.type_plan, il_dwh_pa_gen_valorisation_1.description_type_plan, il_dwh_pa_gen_valorisation_1.rev_commerciale, il_dwh_pa_gen_valorisation_1.rev_commerciale_mvt, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_fin_ann, il_dwh_pa_gen_valorisation_1.rev_inventaire, il_dwh_pa_gen_valorisation_1.rev_inventaire_mvt, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_inventaire_fin_ann, il_dwh_pa_gen_valorisation_1.rev_pure, il_dwh_pa_gen_valorisation_1.rev_pure_mvt, il_dwh_pa_gen_valorisation_1.cd_rev_pure_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_pure_fin_ann, il_dwh_pa_gen_valorisation_1.val_deces, il_dwh_pa_gen_valorisation_1.val_deces_mvt, il_dwh_pa_gen_valorisation_1.val_inva, il_dwh_pa_gen_valorisation_1.val_inva_mvt, il_dwh_pa_gen_valorisation_1.val_vie, il_dwh_pa_gen_valorisation_1.val_vie_mvt, il_dwh_pa_gen_valorisation_1.somme_risquee_deces, il_dwh_pa_gen_valorisation_1.somme_risquee_deces_mvt, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_deces_dat_ext, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_deces_fin_ann, il_dwh_pa_gen_valorisation_1.somme_risquee_inva, il_dwh_pa_gen_valorisation_1.somme_risquee_inva_mvt, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_inva_dat_ext, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_inva_fin_ann, il_dwh_pa_gen_valorisation_1.somme_risquee_reass_deces, il_dwh_pa_gen_valorisation_1.somme_risquee_reass_deces_mvt, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_reass_deces_dat_ext, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_reass_deces_fin_ann, il_dwh_pa_gen_valorisation_1.somme_risquee_reass_inva, il_dwh_pa_gen_valorisation_1.somme_risquee_reass_inva_mvt, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_reass_inva_dat_ext, il_dwh_pa_gen_valorisation_1.cd_somme_risquee_reass_inva_fin_ann, il_dwh_pa_gen_valorisation_1.vas_commerciale, il_dwh_pa_gen_valorisation_1.vas_commerciale_mvt, il_dwh_pa_gen_valorisation_1.cd_vas_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.cd_vas_commerciale_fin_ann, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.vas_inventaire_mvt, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_fin_ann, il_dwh_pa_gen_valorisation_1.vas_pure, il_dwh_pa_gen_valorisation_1.vas_pure_mvt, il_dwh_pa_gen_valorisation_1.cd_vas_pure_dat_ext, il_dwh_pa_gen_valorisation_1.cd_vas_pure_fin_ann, il_dwh_pa_gen_valorisation_1.val_rte_capital, il_dwh_pa_gen_valorisation_1.val_rte_capital_mvt, il_dwh_pa_gen_valorisation_1.val_compens_retraite_avancee, il_dwh_pa_gen_valorisation_1.val_compens_retraite_avancee_mvt, il_dwh_pa_gen_valorisation_1.dn_mut_id, il_dwh_pa_gen_valorisation_1.dn_dte_mut, il_dwh_pa_gen_valorisation_1.dn_nom_mut, il_dwh_pa_gen_valorisation_1.mut_dossier_id, il_dwh_pa_gen_valorisation_1.mut_dossier_id_prec, il_dwh_pa_gen_valorisation_1.usr_log_i, il_dwh_pa_gen_valorisation_1.dte_log_i, il_dwh_pa_gen_valorisation_1.usr_log_u, il_dwh_pa_gen_valorisation_1.dte_log_u
  • Index Cond: (il_dwh_pa_gen_valorisation_1.pa_gen_id = il_dwh_pa_gen_1.pa_gen_id)
  • Filter: ((il_dwh_pa_gen_valorisation_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_pa_gen_valorisation_1.dl_flg_inv = 1) AND (il_dwh_pa_gen_valorisation_1.dl_flg_bcl = 1) AND ((il_dwh_pa_gen_valorisation_1.type_av_ap)::text = 'APRE'::text) AND (il_dwh_pa_gen_1.dl_soc_id = il_dwh_pa_gen_valorisation_1.dl_soc_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=9308
76. 7.434 7.434 ↑ 1.0 1 826

Index Scan using olm_il_dwh_part_dossier_part_dossier_id on bidwh.il_dwh_part_dossier a1_il_dwh_part_dossier_assu_1 (cost=0.44..3.48 rows=1 width=54) (actual time=0.009..0.009 rows=1 loops=826)

  • Output: a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.dl_datextract, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.dl_flg_bcl, a1_il_dwh_part_dossier_assu_1.dl_flg_inv, replace(btrim(replace(((((((btrim((a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_alliance, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_il_dwh_part_dossier_assu_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)
  • Index Cond: (a1_il_dwh_part_dossier_assu_1.part_dossier_id = il_dwh_pa_gen_1.assure_part_dossier_id)
  • Filter: ((a1_il_dwh_part_dossier_assu_1.dl_datextract = '2019-12-31'::date) AND (a1_il_dwh_part_dossier_assu_1.dl_flg_inv = 1) AND (a1_il_dwh_part_dossier_assu_1.dl_flg_bcl = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4122
77. 1.652 1.652 ↑ 1.0 1 826

Seq Scan on bidwhpar.bi_par_flg_bcl bi_par_flg_bcl_1 (cost=0.00..1.21 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=826)

  • Output: bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_bcl_1.dl_flg_bcl_desc
  • Filter: (bi_par_flg_bcl_1.dl_flg_bcl = 1)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=826
78. 0.826 0.826 ↑ 1.0 1 826

Seq Scan on bidwhpar.bi_par_flg_inv bi_par_flg_inv_1 (cost=0.00..1.05 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=826)

  • Output: bi_par_flg_inv_1.dl_flg_inv, bi_par_flg_inv_1.dl_flg_inv_desc
  • Filter: (bi_par_flg_inv_1.dl_flg_inv = 1)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=826
79. 3.304 393.176 ↑ 1.0 1 826

Bitmap Heap Scan on bidwh.il_dwh_contrat il_dwh_contrat_1 (cost=8.12..18.96 rows=1 width=24) (actual time=0.475..0.476 rows=1 loops=826)

  • Output: il_dwh_contrat_1.dl_datextract, il_dwh_contrat_1.dl_soc_id, il_dwh_contrat_1.dl_flg_bcl, il_dwh_contrat_1.dl_flg_inv, il_dwh_contrat_1.mut_contrat_id, il_dwh_contrat_1.mut_contrat_id_prec, il_dwh_contrat_1.per_gest_id, il_dwh_contrat_1.mut_id, il_dwh_contrat_1.contrat_id, il_dwh_contrat_1.donnees_mut_contrat_id, il_dwh_contrat_1.no_gen_contrat, il_dwh_contrat_1.contrat_gen_id, il_dwh_contrat_1.dte_mut, il_dwh_contrat_1.description, il_dwh_contrat_1.etat_mut_contrat, il_dwh_contrat_1.description_etat_mut_contrat, il_dwh_contrat_1.statut_mut_contrat, il_dwh_contrat_1.description_statut_mut_contrat, il_dwh_contrat_1.no_mut, il_dwh_contrat_1.no_mut_precedente, il_dwh_contrat_1.no_mut_extournee, il_dwh_contrat_1.repercussion_auto_dossiers, il_dwh_contrat_1.visa_cree_par, il_dwh_contrat_1.dte_creation, il_dwh_contrat_1.visa_modifie_par, il_dwh_contrat_1.dte_modification, il_dwh_contrat_1.visa_ctrl_par, il_dwh_contrat_1.dte_ctrl, il_dwh_contrat_1.employeur_part_id, il_dwh_contrat_1.mut_dossier_autorisee_id, il_dwh_contrat_1.ancienne_ip_part_id, il_dwh_contrat_1.pro_id, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dte_effet, il_dwh_contrat_1.dte_effet_pe, il_dwh_contrat_1.blocage_dossiers, il_dwh_contrat_1.blocage_fact, il_dwh_contrat_1.raison_blocage_fact, il_dwh_contrat_1.visa_blocage_fact, il_dwh_contrat_1.dte_operation_blocage_fact, il_dwh_contrat_1.dte_derniere_fact, il_dwh_contrat_1.responsable_gest, il_dwh_contrat_1.responsable_gest_medicale, il_dwh_contrat_1.ancien_numero_contrat, il_dwh_contrat_1.reass, il_dwh_contrat_1.description_reassurance, il_dwh_contrat_1.dte_entree, il_dwh_contrat_1.quotite_apport_initial, il_dwh_contrat_1.comp_cmt_pari, il_dwh_contrat_1.calc_dte_affiliation, il_dwh_contrat_1.objet_facturable_allocemp_cree, il_dwh_contrat_1.contrat_pilote_fonds_gar_id, il_dwh_contrat_1.nouvelle_ip_part_id, il_dwh_contrat_1.secteur_act_predominant_id, il_dwh_contrat_1.code_secteur_activite, il_dwh_contrat_1.libelle_secteur_activite, il_dwh_contrat_1.type_rattr, il_dwh_contrat_1.description_type_rattrapage, il_dwh_contrat_1.gest_reservee, il_dwh_contrat_1.code_voir_dossier, il_dwh_contrat_1.confidentiel, il_dwh_contrat_1.effectif_pour_rab, il_dwh_contrat_1.nombre_affilies_reel, il_dwh_contrat_1.appliquer_tx_validite, il_dwh_contrat_1.appliquer_tx_act, il_dwh_contrat_1.bonif_escomptee, il_dwh_contrat_1.fonds_gar, il_dwh_contrat_1.description_fonds_garantie, il_dwh_contrat_1.regrpmnt_contrat_fonds_gar, il_dwh_contrat_1.description_regroupement_contr, il_dwh_contrat_1.statut_cmt_pari, il_dwh_contrat_1.description_statut_comite_pari, il_dwh_contrat_1.dte_prochaine_maj_effectif, il_dwh_contrat_1.numero_avenant, il_dwh_contrat_1.dte_entree_vigueur_avenant, il_dwh_contrat_1.type_avenant, il_dwh_contrat_1.description_type_avenant, il_dwh_contrat_1.suivi_contentieux, il_dwh_contrat_1.dte_ctrl_cmt_pari, il_dwh_contrat_1.nombre_repr_employeur_requis, il_dwh_contrat_1.nombre_repr_empl_requis, il_dwh_contrat_1.grand_contrat, il_dwh_contrat_1.tx_coti_laa, il_dwh_contrat_1.type_mut_id, il_dwh_contrat_1.app_gest_id, il_dwh_contrat_1.nom_mut, il_dwh_contrat_1.niveau, il_dwh_contrat_1.impact_doss, il_dwh_contrat_1.mut_dossier_id, il_dwh_contrat_1.genere_par_contrat, il_dwh_contrat_1.ged_mut_1, il_dwh_contrat_1.ged_mut_2, il_dwh_contrat_1.description_per_ges, il_dwh_contrat_1.dte_deb_per_ges, il_dwh_contrat_1.dte_fin_per_ges, il_dwh_contrat_1.statut_periode_gestion, il_dwh_contrat_1.description_statut_per_ges, il_dwh_contrat_1.dte_deb_app, il_dwh_contrat_1.dte_fin_app, il_dwh_contrat_1.default_nbr_bvr_prod, il_dwh_contrat_1.ged_definition2_prod, il_dwh_contrat_1.ged_definition1_prod, il_dwh_contrat_1.oc_principal_id_employeur, il_dwh_contrat_1.oc_secondaire_id_employeur, il_dwh_contrat_1.type_part_employeur, il_dwh_contrat_1.description_type_partenaire_emp, il_dwh_contrat_1.nom_ou_raison_sociale_employeur, il_dwh_contrat_1.nom_complementaire_employeur, il_dwh_contrat_1.nom_alliance_employeur, il_dwh_contrat_1.prenom_employeur, il_dwh_contrat_1.dte_naissance_employeur, il_dwh_contrat_1.dte_deces_employeur, il_dwh_contrat_1.sexe_employeur, il_dwh_contrat_1.description_sexe_employeur, il_dwh_contrat_1.etat_civil_employeur, il_dwh_contrat_1.description_etat_civil_employeur, il_dwh_contrat_1.no_avs_employeur, il_dwh_contrat_1.langue_correspondance_employeur, il_dwh_contrat_1.telephone_employeur, il_dwh_contrat_1.telephone_mobile_employeur, il_dwh_contrat_1.fax_employeur, il_dwh_contrat_1.e_mail_employeur, il_dwh_contrat_1.contrat_pilote_regrp_gest_id, il_dwh_contrat_1.pilote_regrpmnt_gest, il_dwh_contrat_1.age_terme_vise, il_dwh_contrat_1.contrat_resilie_cours_annee, il_dwh_contrat_1.correspondance_employeur_seul, il_dwh_contrat_1.envoi_liste_annuelle_salaires, il_dwh_contrat_1.envoi_extrait_rapport_annuel, il_dwh_contrat_1.envoi_situation_prev, il_dwh_contrat_1.envoi_courrier_va_logement, il_dwh_contrat_1.usr_log_i, il_dwh_contrat_1.dte_log_i, il_dwh_contrat_1.usr_log_u, il_dwh_contrat_1.dte_log_u, il_dwh_contrat_1.dte_derniere_fact_prest, il_dwh_contrat_1.idx_particuliere, il_dwh_contrat_1.fact_rattr, il_dwh_contrat_1.reass_complete, il_dwh_contrat_1.dte_echeance, il_dwh_contrat_1.blocage_extranet, il_dwh_contrat_1.envoi_bordereau_fact, il_dwh_contrat_1.dte_escompte, il_dwh_contrat_1.choix_plan_complementaire, il_dwh_contrat_1.numero_contact
  • Recheck Cond: ((il_dwh_contrat_1.contrat_id = il_dwh_dossier_1.contrat_id) AND (il_dwh_contrat_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_contrat_1.dl_soc_id = il_dwh_dossier_1.dl_soc_id) AND (il_dwh_contrat_1.dl_flg_bcl = 1) AND (il_dwh_contrat_1.dl_flg_inv = 1))
  • Filter: (il_dwh_contrat_1.no_gen_contrat = (SubPlan 2))
  • Heap Blocks: exact=826
  • Buffers: shared hit=43707
80. 4.130 175.938 ↓ 0.0 0 826

BitmapAnd (cost=8.12..8.12 rows=1 width=0) (actual time=0.213..0.213 rows=0 loops=826)

  • Buffers: shared hit=17533
81. 9.912 9.912 ↓ 1.9 150 826

Bitmap Index Scan on olm_il_dwh_contrat_contrat_id (cost=0.00..1.98 rows=78 width=0) (actual time=0.012..0.012 rows=150 loops=826)

  • Index Cond: (il_dwh_contrat_1.contrat_id = il_dwh_dossier_1.contrat_id)
  • Buffers: shared hit=2815
82. 161.896 161.896 ↓ 11.1 2,601 826

Bitmap Index Scan on il_dwh_contrat_pkey (cost=0.00..5.89 rows=235 width=0) (actual time=0.196..0.196 rows=2,601 loops=826)

  • Index Cond: ((il_dwh_contrat_1.dl_datextract = '2019-12-31'::date) AND (il_dwh_contrat_1.dl_soc_id = il_dwh_dossier_1.dl_soc_id) AND (il_dwh_contrat_1.dl_flg_bcl = 1) AND (il_dwh_contrat_1.dl_flg_inv = 1))
  • Buffers: shared hit=14718
83.          

SubPlan (for Bitmap Heap Scan)

84. 0.826 213.934 ↑ 1.0 1 826

Aggregate (cost=9.70..9.71 rows=1 width=4) (actual time=0.259..0.259 rows=1 loops=826)

  • Output: max(a_1.no_gen_contrat)
  • Buffers: shared hit=25348
85. 2.478 213.108 ↓ 2.0 2 826

Bitmap Heap Scan on bidwh.il_dwh_contrat a_1 (cost=8.57..9.70 rows=1 width=4) (actual time=0.257..0.258 rows=2 loops=826)

  • Output: a_1.dl_datextract, a_1.dl_soc_id, a_1.dl_flg_bcl, a_1.dl_flg_inv, a_1.mut_contrat_id, a_1.mut_contrat_id_prec, a_1.per_gest_id, a_1.mut_id, a_1.contrat_id, a_1.donnees_mut_contrat_id, a_1.no_gen_contrat, a_1.contrat_gen_id, a_1.dte_mut, a_1.description, a_1.etat_mut_contrat, a_1.description_etat_mut_contrat, a_1.statut_mut_contrat, a_1.description_statut_mut_contrat, a_1.no_mut, a_1.no_mut_precedente, a_1.no_mut_extournee, a_1.repercussion_auto_dossiers, a_1.visa_cree_par, a_1.dte_creation, a_1.visa_modifie_par, a_1.dte_modification, a_1.visa_ctrl_par, a_1.dte_ctrl, a_1.employeur_part_id, a_1.mut_dossier_autorisee_id, a_1.ancienne_ip_part_id, a_1.pro_id, a_1.numero_contrat, a_1.dte_effet, a_1.dte_effet_pe, a_1.blocage_dossiers, a_1.blocage_fact, a_1.raison_blocage_fact, a_1.visa_blocage_fact, a_1.dte_operation_blocage_fact, a_1.dte_derniere_fact, a_1.responsable_gest, a_1.responsable_gest_medicale, a_1.ancien_numero_contrat, a_1.reass, a_1.description_reassurance, a_1.dte_entree, a_1.quotite_apport_initial, a_1.comp_cmt_pari, a_1.calc_dte_affiliation, a_1.objet_facturable_allocemp_cree, a_1.contrat_pilote_fonds_gar_id, a_1.nouvelle_ip_part_id, a_1.secteur_act_predominant_id, a_1.code_secteur_activite, a_1.libelle_secteur_activite, a_1.type_rattr, a_1.description_type_rattrapage, a_1.gest_reservee, a_1.code_voir_dossier, a_1.confidentiel, a_1.effectif_pour_rab, a_1.nombre_affilies_reel, a_1.appliquer_tx_validite, a_1.appliquer_tx_act, a_1.bonif_escomptee, a_1.fonds_gar, a_1.description_fonds_garantie, a_1.regrpmnt_contrat_fonds_gar, a_1.description_regroupement_contr, a_1.statut_cmt_pari, a_1.description_statut_comite_pari, a_1.dte_prochaine_maj_effectif, a_1.numero_avenant, a_1.dte_entree_vigueur_avenant, a_1.type_avenant, a_1.description_type_avenant, a_1.suivi_contentieux, a_1.dte_ctrl_cmt_pari, a_1.nombre_repr_employeur_requis, a_1.nombre_repr_empl_requis, a_1.grand_contrat, a_1.tx_coti_laa, a_1.type_mut_id, a_1.app_gest_id, a_1.nom_mut, a_1.niveau, a_1.impact_doss, a_1.mut_dossier_id, a_1.genere_par_contrat, a_1.ged_mut_1, a_1.ged_mut_2, a_1.description_per_ges, a_1.dte_deb_per_ges, a_1.dte_fin_per_ges, a_1.statut_periode_gestion, a_1.description_statut_per_ges, a_1.dte_deb_app, a_1.dte_fin_app, a_1.default_nbr_bvr_prod, a_1.ged_definition2_prod, a_1.ged_definition1_prod, a_1.oc_principal_id_employeur, a_1.oc_secondaire_id_employeur, a_1.type_part_employeur, a_1.description_type_partenaire_emp, a_1.nom_ou_raison_sociale_employeur, a_1.nom_complementaire_employeur, a_1.nom_alliance_employeur, a_1.prenom_employeur, a_1.dte_naissance_employeur, a_1.dte_deces_employeur, a_1.sexe_employeur, a_1.description_sexe_employeur, a_1.etat_civil_employeur, a_1.description_etat_civil_employeur, a_1.no_avs_employeur, a_1.langue_correspondance_employeur, a_1.telephone_employeur, a_1.telephone_mobile_employeur, a_1.fax_employeur, a_1.e_mail_employeur, a_1.contrat_pilote_regrp_gest_id, a_1.pilote_regrpmnt_gest, a_1.age_terme_vise, a_1.contrat_resilie_cours_annee, a_1.correspondance_employeur_seul, a_1.envoi_liste_annuelle_salaires, a_1.envoi_extrait_rapport_annuel, a_1.envoi_situation_prev, a_1.envoi_courrier_va_logement, a_1.usr_log_i, a_1.dte_log_i, a_1.usr_log_u, a_1.dte_log_u, a_1.dte_derniere_fact_prest, a_1.idx_particuliere, a_1.fact_rattr, a_1.reass_complete, a_1.dte_echeance, a_1.blocage_extranet, a_1.envoi_bordereau_fact, a_1.dte_escompte, a_1.choix_plan_complementaire, a_1.numero_contact
  • Recheck Cond: ((il_dwh_contrat_1.contrat_id = a_1.contrat_id) AND (a_1.dl_datextract = il_dwh_contrat_1.dl_datextract) AND (il_dwh_contrat_1.dl_soc_id = a_1.dl_soc_id) AND (a_1.dl_flg_bcl = il_dwh_contrat_1.dl_flg_bcl))
  • Filter: (((a_1.etat_mut_contrat)::text = 'VALI'::text) AND (CASE WHEN (il_dwh_contrat_1.dl_datextract <= '2014-01-01'::date) THEN a_1.dl_flg_inv ELSE 1 END = CASE WHEN (il_dwh_contrat_1.dl_datextract <= '2014-01-01'::date) THEN il_dwh_contrat_1.dl_flg_inv ELSE 1 END))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=1215
  • Buffers: shared hit=25348
86. 4.130 210.630 ↓ 0.0 0 826

BitmapAnd (cost=8.57..8.57 rows=1 width=0) (actual time=0.255..0.255 rows=0 loops=826)

  • Buffers: shared hit=24133
87. 7.434 7.434 ↓ 1.9 150 826

Bitmap Index Scan on olm_il_dwh_contrat_contrat_id (cost=0.00..2.11 rows=78 width=0) (actual time=0.009..0.009 rows=150 loops=826)

  • Index Cond: (il_dwh_contrat_1.contrat_id = a_1.contrat_id)
  • Buffers: shared hit=2815
88. 199.066 199.066 ↓ 13.1 3,747 826

Bitmap Index Scan on il_dwh_contrat_pkey (cost=0.00..6.21 rows=287 width=0) (actual time=0.241..0.241 rows=3,747 loops=826)

  • Index Cond: ((a_1.dl_datextract = il_dwh_contrat_1.dl_datextract) AND (il_dwh_contrat_1.dl_soc_id = a_1.dl_soc_id) AND (a_1.dl_flg_bcl = il_dwh_contrat_1.dl_flg_bcl))
  • Buffers: shared hit=21318
89. 0.826 0.826 ↑ 1.0 1 826

Index Scan using bi_prod_pkey on biinfra.bi_prod bi_prod_1 (cost=0.14..1.73 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=826)

  • Output: bi_prod_1.pro_id, bi_prod_1.fam_id, bi_prod_1.soc_id, bi_prod_1.pro_dte_deb_app, bi_prod_1.pro_dte_fin_app, bi_prod_1.pro_desc_courte, bi_prod_1.pro_default_nbr_bvr, bi_prod_1.pro_ged_definition1, bi_prod_1.pro_ged_definition2, bi_prod_1.pro_dernier_num_police_utilise, bi_prod_1.pro_type_prod, bi_prod_1.pro_ged_type_caisse, bi_prod_1.prl_nom_prod_fr, bi_prod_1.prl_desc_longue_fr, bi_prod_1.prl_desc_portail_fr, bi_prod_1.prl_ged_definition_fiscale_fr, bi_prod_1.act_nom_prod, bi_prod_1.act_regr_prod, bi_prod_1.pvi_type_prev, bi_prod_1.pvi_type_dte_annual, bi_prod_1.pvi_type_libe_pri, bi_prod_1.pvi_type_versement_add, bi_prod_1.pvi_seuil_red, bi_prod_1.pvi_seuil_paiement, bi_prod_1.pvi_droit_pe_redu, bi_prod_1.pvi_plan_repart, bi_prod_1.pvi_fo_agregation, bi_prod_1.pvi_arrondi_pu, bi_prod_1.pvi_arrondi_pp, bi_prod_1.pvi_periodicite_prelevement, bi_prod_1.pvi_periodicite_rebalancement, bi_prod_1.pvi_arrondi_fonds_parts, bi_prod_1.pvi_arrondi_fonds_montants, bi_prod_1.pvi_pe_couv_id, bi_prod_1.pvi_type_interpolation, bi_prod_1.pvi_montant_limite_versement, bi_prod_1.pvi_montant_limite_versement_add, bi_prod_1.pvi_fam_prod_informatique, bi_prod_1.pvi_rachetable, bi_prod_1.pvi_liber_assure1_obli, bi_prod_1.pvi_surprime_exploitant, bi_prod_1.pvi_categorie_prod, bi_prod_1.pvi_proj_plusieurs_tx, bi_prod_1.pvi_type_pro, bi_prod_1.pvi_parametrage_capital_reserve, bi_prod_1.pvi_anti_selection_rachat, bi_prod_1.pvi_pe_prest_reduction, bi_prod_1.pvi_parametrage_capital_ddecr, bi_prod_1.pvi_option_capital_rente_deces, bi_prod_1.pvi_type_pe_apres_differe, bi_prod_1.pvi_frais_gestion_min_max, bi_prod_1.pvl_benef_deces_fr, bi_prod_1.pvl_benef_vie_fr, bi_prod_1.pvl_preneur_success_fr, bi_prod_1.pvl_benef_deces_2t_fr, bi_prod_1.pvl_benef_vie_2t_fr
  • Index Cond: (bi_prod_1.pro_id = il_dwh_contrat_1.pro_id)
  • Buffers: shared hit=1652
90. 0.826 0.826 ↑ 1.0 1 826

Index Scan using bi_fam_prod_pkey on biinfra.bi_fam_prod bi_fam_prod_1 (cost=0.14..0.72 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=826)

  • Output: bi_fam_prod_1.fam_id, bi_fam_prod_1.soc_id, bi_fam_prod_1.fam_desc_courte, bi_fam_prod_1.fam_type_famille, bi_fam_prod_1.fam_app_gest_id, bi_fam_prod_1.fam_dernier_num_police_utilise, bi_fam_prod_1.fal_nom_fam_fr, bi_fam_prod_1.fal_desc_longue_fr, bi_fam_prod_1.apg_nom_app, bi_fam_prod_1.apg_desc_courte, bi_fam_prod_1.apg_type_app
  • Index Cond: (bi_fam_prod_1.fam_id = bi_prod_1.fam_id)
  • Filter: (bi_fam_prod_1.soc_id = 3)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1652
91. 0.644 0.644 ↑ 1.0 1 644

Seq Scan on biinfra.bi_soc bi_soc_1 (cost=0.00..1.09 rows=1 width=82) (actual time=0.001..0.001 rows=1 loops=644)

  • Output: bi_soc_1.soc_id, bi_soc_1.soc_desc_courte, bi_soc_1.sol_nom_soc_fr, bi_soc_1.sol_nom_soc_ged_fr, bi_soc_1.sol_sign_soc_fr, bi_soc_1.sol_desc_voies_recours_fr
  • Filter: (bi_soc_1.soc_id = 3)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=644
92. 1.288 1.288 ↑ 1.0 1 644

Index Only Scan using bi_dates_pkey on biinfra.bi_dates bi_dates_1 (cost=0.28..2.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=644)

  • Output: bi_dates_1.dte_jour
  • Index Cond: (bi_dates_1.dte_jour = '2019-12-31'::date)
  • Heap Fetches: 644
  • Buffers: shared hit=1932
Planning time : 139.405 ms
Execution time : 131,381.850 ms