explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ypv5

Settings
# exclusive inclusive rows x rows loops node
1. 21.544 804,094.904 ↓ 20,030.0 40,060 1

Unique (cost=122,475.94..122,476.10 rows=2 width=2,582) (actual time=804,071.546..804,094.904 rows=40,060 loops=1)

  • Output: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), (sum(COALESCE(il_dwh_pa_gen_prest.prest_tot, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_capi, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.participation_excedents, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, (sum(il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext)), a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff
  • Buffers: shared hit=530694122 read=830905
2. 105.417 804,073.360 ↓ 20,030.0 40,060 1

Sort (cost=122,475.94..122,475.95 rows=2 width=2,582) (actual time=804,071.538..804,073.360 rows=40,060 loops=1)

  • Output: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), (sum(COALESCE(il_dwh_pa_gen_prest.prest_tot, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_capi, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.participation_excedents, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, (sum(il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext)), a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff
  • Sort Key: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), (sum(COALESCE(il_dwh_pa_gen_prest.prest_tot, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_capi, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.participation_excedents, '0'::numeric))), (sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric))), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, (sum(il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext)), a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff
  • Sort Method: quicksort Memory: 12715kB
  • Buffers: shared hit=530694122 read=830905
3. 3.501 803,967.943 ↓ 20,030.0 40,060 1

Append (cost=61,237.83..122,475.93 rows=2 width=2,582) (actual time=75,975.853..803,967.943 rows=40,060 loops=1)

  • Buffers: shared hit=530694110 read=830905
4. 65.134 76,044.061 ↓ 23,992.0 23,992 1

GroupAggregate (cost=61,237.83..61,237.95 rows=1 width=1,427) (actual time=75,975.851..76,044.061 rows=23,992 loops=1)

  • Output: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), sum(COALESCE(il_dwh_pa_gen_prest.prest_tot, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.idx, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_capi, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.idx_employeur_fine_perio, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.participation_excedents, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest.prest_due, '0'::numeric)), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, sum(il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext), a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff
  • Group Key: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff
  • Buffers: shared hit=31229701 read=357253
5. 114.945 75,978.927 ↓ 27,526.0 27,526 1

Sort (cost=61,237.83..61,237.84 rows=1 width=1,198) (actual time=75,975.465..75,978.927 rows=27,526 loops=1)

  • Output: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, ((bi_vcouv.tco_rente_en_cours)::integer), bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext
  • Sort Key: il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, a1_il_dwh_part_dossier_affi.type_eff
  • Sort Method: quicksort Memory: 8436kB
  • Buffers: shared hit=31229701 read=357253
6. 22.353 75,863.982 ↓ 27,526.0 27,526 1

Nested Loop (cost=12,919.79..61,237.82 rows=1 width=1,198) (actual time=2,475.752..75,863.982 rows=27,526 loops=1)

  • Output: bi_soc.soc_id, il_dwh_contrat.numero_contrat, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_partie_dossier.type_partie, ((SubPlan 1)), il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_inva, il_dwh_pa_gen.type_couv, ((SubPlan 2)), il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu.etat_civil, (bi_vcouv.tco_rente_en_cours)::integer, bi_dates.dte_jour, a1_il_dwh_part_dossier_affi.type_eff, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext
  • Buffers: shared hit=31229701 read=357253
7. 30.655 75,731.525 ↓ 27,526.0 27,526 1

Nested Loop (cost=12,919.51..61,235.31 rows=1 width=1,195) (actual time=2,475.381..75,731.525 rows=27,526 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, a1_il_dwh_part_dossier_affi.type_eff, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, bi_soc.soc_id, ((SubPlan 1)), ((SubPlan 2)), (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=31119597 read=357253
8. 57.918 75,645.818 ↓ 27,526.0 27,526 1

Nested Loop (cost=12,919.51..61,234.21 rows=1 width=1,195) (actual time=2,475.367..75,645.818 rows=27,526 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, a1_il_dwh_part_dossier_affi.type_eff, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, bi_prod.soc_id, ((SubPlan 1)), ((SubPlan 2)), (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=31092071 read=357253
9. 427.062 75,512.058 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,919.36..61,231.53 rows=1 width=1,195) (actual time=2,475.338..75,512.058 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, a1_il_dwh_part_dossier_affi.type_eff, il_dwh_contrat.numero_contrat, il_dwh_contrat.dl_datextract, il_dwh_contrat.pro_id, ((SubPlan 1)), ((SubPlan 2)), (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_dossier.contrat_id = il_dwh_contrat.contrat_id)
  • Rows Removed by Join Filter: 3790516
  • Buffers: shared hit=31016229 read=357253
10. 48.777 47,137.219 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,918.94..61,225.24 rows=1 width=1,219) (actual time=2,474.569..47,137.219 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, il_dwh_dossier.dl_datextract, il_dwh_dossier.dl_soc_id, il_dwh_dossier.dl_flg_bcl, il_dwh_dossier.contrat_id, a1_il_dwh_part_dossier_affi.type_eff, a1_il_dwh_part_dossier_affi.dl_soc_id, ((SubPlan 1)), ((SubPlan 2)), (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=12280033 read=357251
11. 70.489 47,012.600 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,918.94..61,224.18 rows=1 width=1,223) (actual time=2,474.560..47,012.600 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, a1_il_dwh_part_dossier_affi.type_eff, a1_il_dwh_part_dossier_affi.dl_soc_id, ((SubPlan 1)), ((SubPlan 2)), (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=12242112 read=357251
12. 61.809 46,790.427 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,918.94..61,222.83 rows=1 width=1,223) (actual time=2,474.546..46,790.427 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, a1_il_dwh_part_dossier_affi.type_eff, a1_il_dwh_part_dossier_affi.dl_soc_id, ((SubPlan 1)), ((SubPlan 2)), (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 = a1_il_dwh_part_dossier_affi.dl_soc_id)
  • Buffers: shared hit=12204191 read=357251
13. 70.919 45,401.383 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,918.50..61,205.15 rows=1 width=1,223) (actual time=2,474.508..45,401.383 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.dl_soc_id, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.dl_soc_id, a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1)), ((SubPlan 2)), (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=11983837 read=344712
14. 90.344 44,723.728 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,918.07..61,200.61 rows=1 width=1,178) (actual time=2,474.450..44,723.728 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, 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_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_pa_gen_valorisation.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation.dl_soc_id, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1)), ((SubPlan 2))
  • Buffers: shared hit=11800016 read=340381
15. 102.116 38,983.155 ↓ 37,921.0 37,921 1

Nested Loop Left Join (cost=12,917.50..61,197.87 rows=1 width=1,172) (actual time=2,472.670..38,983.155 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, 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.assure_part_dossier_id, il_dwh_pa_gen_prest.prest_tot, il_dwh_pa_gen_prest.idx, il_dwh_pa_gen_prest.idx_employeur_fine_capi, il_dwh_pa_gen_prest.idx_employeur_fine_perio, il_dwh_pa_gen_prest.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest.participation_excedents, il_dwh_pa_gen_prest.prest_due, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1)), ((SubPlan 2))
  • Buffers: shared hit=11640098 read=310239
16. 821.158 32,851.600 ↓ 37,921.0 37,921 1

Nested Loop (cost=12,916.93..61,195.14 rows=1 width=1,148) (actual time=2,471.160..32,851.600 rows=37,921 loops=1)

  • Output: bi_vcouv.tco_rente_en_cours, il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, 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.assure_part_dossier_id, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1)), ((SubPlan 2))
  • Inner Unique: true
  • Buffers: shared hit=11475664 read=284602
17. 405.726 29,036.860 ↓ 1,496,791.0 1,496,791 1

Nested Loop (cost=12,916.65..61,192.72 rows=1 width=1,151) (actual time=2,301.332..29,036.860 rows=1,496,791 loops=1)

  • Output: il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.vco_id, 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.assure_part_dossier_id, il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, il_dwh_plan_partie.dl_soc_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1)), ((SubPlan 2))
  • Buffers: shared hit=6985243 read=284600
18. 292.101 6,133.643 ↓ 206,399.0 206,399 1

Nested Loop (cost=12,916.08..60,850.84 rows=1 width=615) (actual time=2,299.282..6,133.643 rows=206,399 loops=1)

  • Output: il_dwh_plan_partie.nom_plan, il_dwh_plan_partie.transitoire, 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.plan_partie_id, il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_inva, il_dwh_partie_dossier.dl_soc_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1))
  • Buffers: shared hit=716765 read=38951
19. 129.376 3,638.828 ↓ 2,913.6 81,582 1

Hash Join (cost=12,915.64..60,559.51 rows=28 width=582) (actual time=2,299.039..3,638.828 rows=81,582 loops=1)

  • Output: il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_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_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.dossier_gen_id, il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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, ((SubPlan 1))
  • 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=248128 read=9478
20. 722.618 1,212.308 ↓ 6.3 81,615 1

Index Scan using olm_il_dwh_partie_dossier_date_dl_flg_bcl_dl_flg_inv on bidwh.il_dwh_partie_dossier (cost=0.43..47,482.30 rows=12,938 width=550) (actual time=1.797..1,212.308 rows=81,615 loops=1)

  • Output: il_dwh_partie_dossier.type_partie, il_dwh_partie_dossier.type_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_partie_dossier.partie_dossier_id, il_dwh_partie_dossier.dossier_gen_id, (SubPlan 1)
  • Index Cond: ((il_dwh_partie_dossier.dl_datextract = '2020-02-01'::date) AND (il_dwh_partie_dossier.dl_flg_bcl = 0) AND (il_dwh_partie_dossier.dl_flg_inv = 1))
  • Filter: ((il_dwh_partie_dossier.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 442
  • Buffers: shared hit=246323 read=2104
21.          

SubPlan (for Index Scan)

22. 489.690 489.690 ↑ 1.0 1 81,615

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a (cost=0.28..2.50 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=81,615)

  • Output: a.texte
  • Index Cond: (((il_dwh_partie_dossier.type_partie)::text = (a.code_cle)::text) AND ((a.code_type)::text = 'TypePartieDossier'::text))
  • Filter: ((a.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=244845
23. 36.369 2,297.144 ↓ 5.9 76,032 1

Hash (cost=12,722.43..12,722.43 rows=12,852 width=32) (actual time=2,297.143..2,297.144 rows=76,032 loops=1)

  • Output: il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5776kB
  • Buffers: shared hit=1805 read=7374
24. 2,260.775 2,260.775 ↓ 5.9 76,032 1

Index Scan using olm_il_dwh_dossier_date_dl_flg_bcl_dl_flg_inv on bidwh.il_dwh_dossier (cost=0.43..12,722.43 rows=12,852 width=32) (actual time=1.798..2,260.775 rows=76,032 loops=1)

  • Output: il_dwh_dossier.numero_dossier, il_dwh_dossier.dossier_id, 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 = '2020-02-01'::date) AND (il_dwh_dossier.dl_flg_bcl = 0) AND (il_dwh_dossier.dl_flg_inv = 1))
  • Filter: ((il_dwh_dossier.statut)::text <> 'TERM'::text)
  • Rows Removed by Filter: 414
  • Buffers: shared hit=1805 read=7374
25. 2,202.714 2,202.714 ↓ 3.0 3 81,582

Index Scan using olm_il_dwh_plan_partie_partie_dossier_id on bidwh.il_dwh_plan_partie (cost=0.44..10.39 rows=1 width=53) (actual time=0.010..0.027 rows=3 loops=81,582)

  • 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.dl_datextract = '2020-02-01'::date) AND (il_dwh_plan_partie.dl_flg_bcl = 0) AND (il_dwh_plan_partie.dl_flg_inv = 1) AND (il_dwh_partie_dossier.dl_soc_id = il_dwh_plan_partie.dl_soc_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=468637 read=29473
26. 16,510.327 22,497.491 ↓ 7.0 7 206,399

Index Scan using olm_il_dwh_pa_gen_plan_partie_id on bidwh.il_dwh_pa_gen (cost=0.57..341.87 rows=1 width=556) (actual time=0.022..0.109 rows=7 loops=206,399)

  • Output: il_dwh_pa_gen.type_couv, il_dwh_pa_gen.dte_echeance, il_dwh_pa_gen.vco_id, 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.assure_part_dossier_id, il_dwh_pa_gen.plan_partie_id, (SubPlan 2)
  • Index Cond: (il_dwh_pa_gen.plan_partie_id = il_dwh_plan_partie.plan_partie_id)
  • Filter: (((il_dwh_pa_gen.type_couv)::text <> 'REAM'::text) AND (il_dwh_pa_gen.dl_datextract = '2020-02-01'::date) AND (il_dwh_pa_gen.dl_flg_bcl = 0) AND (il_dwh_pa_gen.dl_flg_inv = 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))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=6268478 read=245649
27.          

SubPlan (for Index Scan)

28. 5,987.164 5,987.164 ↑ 1.0 1 1,496,791

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_1 (cost=0.28..2.50 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,496,791)

  • Output: a_1.texte
  • Index Cond: (((il_dwh_pa_gen.type_couv)::text = (a_1.code_cle)::text) AND ((a_1.code_type)::text = 'TypeNatureTypeCouverture'::text))
  • Filter: ((a_1.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=4496827 read=2
29. 2,993.582 2,993.582 ↓ 0.0 0 1,496,791

Index Scan using bi_vcouv_pkey on biinfra.bi_vcouv (cost=0.28..2.22 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=1,496,791)

  • 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)
  • Filter: ((bi_vcouv.tco_rente_en_cours)::integer = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4490421 read=2
30. 6,029.439 6,029.439 ↑ 1.0 1 37,921

Index Scan using olm_il_dwh_pa_gen_prest_pa_gen_id_dl_datextract_etc on bidwh.il_dwh_pa_gen_prest (cost=0.57..2.72 rows=1 width=44) (actual time=0.159..0.159 rows=1 loops=37,921)

  • 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) AND (il_dwh_pa_gen_prest.dl_datextract = il_dwh_pa_gen.dl_datextract) AND (il_dwh_pa_gen_prest.dl_datextract = '2020-02-01'::date) AND (il_dwh_pa_gen_prest.dl_flg_bcl = il_dwh_pa_gen.dl_flg_bcl) AND (il_dwh_pa_gen_prest.dl_flg_bcl = 0) AND (il_dwh_pa_gen_prest.dl_flg_inv = il_dwh_pa_gen.dl_flg_inv) AND (il_dwh_pa_gen_prest.dl_flg_inv = 1) AND (il_dwh_pa_gen_prest.dl_soc_id = il_dwh_pa_gen.dl_soc_id))
  • Buffers: shared hit=164434 read=25637
31. 5,650.229 5,650.229 ↑ 1.0 1 37,921

Index Scan using olm_il_dwh_pa_gen_valorisation_pa_gen_id_etc on bidwh.il_dwh_pa_gen_valorisation (cost=0.57..2.73 rows=1 width=26) (actual time=0.148..0.149 rows=1 loops=37,921)

  • 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.dl_datextract = '2020-02-01'::date) AND (il_dwh_pa_gen_valorisation.pa_gen_id = il_dwh_pa_gen.pa_gen_id) AND (il_dwh_pa_gen_valorisation.dl_flg_inv = 1) AND (il_dwh_pa_gen_valorisation.dl_flg_bcl = 0) AND ((il_dwh_pa_gen_valorisation.type_av_ap)::text = 'APRE'::text) AND (il_dwh_pa_gen_valorisation.dl_soc_id = il_dwh_pa_gen.dl_soc_id))
  • Buffers: shared hit=159918 read=30142
32. 606.736 606.736 ↑ 1.0 1 37,921

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..4.53 rows=1 width=73) (actual time=0.015..0.016 rows=1 loops=37,921)

  • Output: a1_il_dwh_part_dossier_assu.part_dossier_id, a1_il_dwh_part_dossier_assu.type_eff, a1_il_dwh_part_dossier_assu.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu.dte_naissance, a1_il_dwh_part_dossier_assu.sexe, a1_il_dwh_part_dossier_assu.etat_civil, a1_il_dwh_part_dossier_assu.dl_datextract, 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 = '2020-02-01'::date) AND (a1_il_dwh_part_dossier_assu.dl_flg_bcl = 0) AND (a1_il_dwh_part_dossier_assu.dl_flg_inv = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=183821 read=4331
33. 1,327.235 1,327.235 ↑ 1.0 1 37,921

Index Scan using olm_il_dwh_part_dossier_dossier_gen_id on bidwh.il_dwh_part_dossier a1_il_dwh_part_dossier_affi (cost=0.44..17.67 rows=1 width=24) (actual time=0.009..0.035 rows=1 loops=37,921)

  • Output: a1_il_dwh_part_dossier_affi.dl_datextract, a1_il_dwh_part_dossier_affi.dl_soc_id, a1_il_dwh_part_dossier_affi.dl_flg_bcl, a1_il_dwh_part_dossier_affi.dl_flg_inv, a1_il_dwh_part_dossier_affi.part_dossier_id, a1_il_dwh_part_dossier_affi.personne_contact_part_id, a1_il_dwh_part_dossier_affi.part_dossier_part_id, a1_il_dwh_part_dossier_affi.dossier_id, a1_il_dwh_part_dossier_affi.no_gen_dossier, a1_il_dwh_part_dossier_affi.dossier_gen_id, a1_il_dwh_part_dossier_affi.role, a1_il_dwh_part_dossier_affi.description_role, a1_il_dwh_part_dossier_affi.personne_contact, a1_il_dwh_part_dossier_affi.role_personne_contact, a1_il_dwh_part_dossier_affi.description_role_personne_cont, a1_il_dwh_part_dossier_affi.lien_parente_personne_contact, a1_il_dwh_part_dossier_affi.description_lien_parente, a1_il_dwh_part_dossier_affi.oc_principal_id, a1_il_dwh_part_dossier_affi.oc_secondaire_id, a1_il_dwh_part_dossier_affi.type_part, a1_il_dwh_part_dossier_affi.description_type_partenaire, a1_il_dwh_part_dossier_affi.nom_ou_raison_sociale, a1_il_dwh_part_dossier_affi.nom_complementaire, a1_il_dwh_part_dossier_affi.nom_alliance, a1_il_dwh_part_dossier_affi.prenom, a1_il_dwh_part_dossier_affi.dte_naissance, a1_il_dwh_part_dossier_affi.ann_naissance, a1_il_dwh_part_dossier_affi.dte_deces, a1_il_dwh_part_dossier_affi.sexe, a1_il_dwh_part_dossier_affi.description_sexe, a1_il_dwh_part_dossier_affi.etat_civil, a1_il_dwh_part_dossier_affi.description_etat_civil, a1_il_dwh_part_dossier_affi.no_avs, a1_il_dwh_part_dossier_affi.langue_correspondance, a1_il_dwh_part_dossier_affi.description_langue_corresponda, a1_il_dwh_part_dossier_affi.telephone, a1_il_dwh_part_dossier_affi.telephone_mobile, a1_il_dwh_part_dossier_affi.fax, a1_il_dwh_part_dossier_affi.e_mail, a1_il_dwh_part_dossier_affi.dte_deb_concubinage, a1_il_dwh_part_dossier_affi.indemnite_unique_conc, a1_il_dwh_part_dossier_affi.reduc_prest_deces_conc, a1_il_dwh_part_dossier_affi.reduc_allocation_deces_conc, a1_il_dwh_part_dossier_affi.dte_mar_conj, a1_il_dwh_part_dossier_affi.dte_div_conj, a1_il_dwh_part_dossier_affi.prest_libre_pass_lpp_mar_conj, a1_il_dwh_part_dossier_affi.prest_libre_pass_tot_mar_conj, a1_il_dwh_part_dossier_affi.choix_capital_conj, a1_il_dwh_part_dossier_affi.indemnite_unique_conj, a1_il_dwh_part_dossier_affi.reduc_prest_deces_conj, a1_il_dwh_part_dossier_affi.provenance_bande_etat_conj, a1_il_dwh_part_dossier_affi.reduc_allocation_deces_conj, a1_il_dwh_part_dossier_affi.rentes_non_indexables_conj, a1_il_dwh_part_dossier_affi.invalide_enfant, a1_il_dwh_part_dossier_affi.a_charge_enfant, a1_il_dwh_part_dossier_affi.type_charge_enfant, a1_il_dwh_part_dossier_affi.description_type_charge_enfant, a1_il_dwh_part_dossier_affi.dte_fin_charge_enfant, a1_il_dwh_part_dossier_affi.doublement_rte_enfant, a1_il_dwh_part_dossier_affi.index_bande_etat_enfant, a1_il_dwh_part_dossier_affi.dte_pacs, a1_il_dwh_part_dossier_affi.dte_dis_pacs, a1_il_dwh_part_dossier_affi.prest_libre_pass_lpp_pacs, a1_il_dwh_part_dossier_affi.prest_libre_pass_tot_pacs, a1_il_dwh_part_dossier_affi.choix_capital_pacs, a1_il_dwh_part_dossier_affi.indemnite_unique_pacs, a1_il_dwh_part_dossier_affi.reduc_prest_deces_pacs, a1_il_dwh_part_dossier_affi.rentes_non_indexables_pacs, a1_il_dwh_part_dossier_affi.reduc_allocation_deces_pacs, a1_il_dwh_part_dossier_affi.provenance_bande_etat_pacs, a1_il_dwh_part_dossier_affi.dn_mut_id, a1_il_dwh_part_dossier_affi.dn_dte_mut, a1_il_dwh_part_dossier_affi.dn_nom_mut, a1_il_dwh_part_dossier_affi.cree_par_batch, a1_il_dwh_part_dossier_affi.type_eff, a1_il_dwh_part_dossier_affi.type_eff_prec, a1_il_dwh_part_dossier_affi.effcal, a1_il_dwh_part_dossier_affi.numero_contact, a1_il_dwh_part_dossier_affi.usr_log_i, a1_il_dwh_part_dossier_affi.dte_log_i, a1_il_dwh_part_dossier_affi.usr_log_u, a1_il_dwh_part_dossier_affi.dte_log_u
  • Index Cond: (a1_il_dwh_part_dossier_affi.dossier_gen_id = il_dwh_partie_dossier.dossier_gen_id)
  • Filter: ((a1_il_dwh_part_dossier_affi.dl_datextract = '2020-02-01'::date) AND (a1_il_dwh_part_dossier_affi.dl_flg_bcl = 0) AND (a1_il_dwh_part_dossier_affi.dl_flg_inv = 1) AND ((a1_il_dwh_part_dossier_affi.role)::text = 'AFFI'::text) AND (il_dwh_partie_dossier.dl_soc_id = a1_il_dwh_part_dossier_affi.dl_soc_id))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=220354 read=12539
34. 151.684 151.684 ↑ 1.0 1 37,921

Seq Scan on bidwhpar.bi_par_flg_bcl (cost=0.00..1.34 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=37,921)

  • Output: bi_par_flg_bcl.dl_flg_bcl, bi_par_flg_bcl.dl_flg_bcl_desc
  • Filter: (bi_par_flg_bcl.dl_flg_bcl = 0)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=37921
35. 75.842 75.842 ↑ 1.0 1 37,921

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

  • 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=37921
36. 4,977.155 27,947.777 ↓ 101.0 101 37,921

Index Scan using il_dwh_contrat_pkey on bidwh.il_dwh_contrat (cost=0.42..6.28 rows=1 width=24) (actual time=0.015..0.737 rows=101 loops=37,921)

  • 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
  • Index Cond: ((il_dwh_contrat.dl_datextract = '2020-02-01'::date) AND (il_dwh_contrat.dl_soc_id = il_dwh_pa_gen.dl_soc_id) AND (il_dwh_contrat.dl_flg_bcl = 0) AND (il_dwh_contrat.dl_flg_inv = 1))
  • Filter: (il_dwh_contrat.no_gen_contrat = (SubPlan 3))
  • Buffers: shared hit=18736196 read=2
37.          

SubPlan (for Index Scan)

38. 0.000 22,970.622 ↑ 1.0 1 3,828,437

Aggregate (cost=3.83..3.84 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3,828,437)

  • Output: max(a_2.no_gen_contrat)
  • Buffers: shared hit=17638567 read=2
39. 22,970.622 22,970.622 ↑ 1.0 1 3,828,437

Index Scan using olm_il_dwh_contrat_ili0065 on bidwh.il_dwh_contrat a_2 (cost=0.42..3.83 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3,828,437)

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

Index Scan using bi_prod_pkey on biinfra.bi_prod (cost=0.14..2.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=37,921)

  • 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)
  • Filter: (bi_prod.soc_id = 6)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=75842
41. 55.052 55.052 ↑ 1.0 1 27,526

Seq Scan on biinfra.bi_soc (cost=0.00..1.09 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=27,526)

  • 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 = 6)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=27526
42. 110.104 110.104 ↑ 1.0 1 27,526

Index Only Scan using bi_dates_pkey on biinfra.bi_dates (cost=0.28..2.50 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=27,526)

  • Output: bi_dates.dte_jour
  • Index Cond: (bi_dates.dte_jour = '2020-02-01'::date)
  • Heap Fetches: 55052
  • Buffers: shared hit=110104
43. 1,805.801 727,920.381 ↓ 16,068.0 16,068 1

GroupAggregate (cost=61,237.83..61,237.95 rows=1 width=1,427) (actual time=726,038.217..727,920.381 rows=16,068 loops=1)

  • Output: bi_soc_1.soc_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_partie_dossier_1.type_partie, ((SubPlan 4)), il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_inva, il_dwh_pa_gen_1.type_couv, ((SubPlan 5)), sum(COALESCE(il_dwh_pa_gen_prest_1.prest_tot, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.idx, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.participation_excedents, '0'::numeric)), sum(COALESCE(il_dwh_pa_gen_prest_1.prest_due, '0'::numeric)), il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, sum(il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext), a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu_1.etat_civil, ((bi_vcouv_1.tco_rente_en_cours)::integer), bi_dates_1.dte_jour, a1_il_dwh_part_dossier_affi_1.type_eff
  • Group Key: bi_soc_1.soc_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_partie_dossier_1.type_partie, ((SubPlan 4)), il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_inva, il_dwh_pa_gen_1.type_couv, ((SubPlan 5)), il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu_1.etat_civil, ((bi_vcouv_1.tco_rente_en_cours)::integer), bi_dates_1.dte_jour, a1_il_dwh_part_dossier_affi_1.type_eff
  • Filter: (sum(COALESCE(il_dwh_pa_gen_valorisation_1.vas_inventaire, '0'::numeric)) <> '0'::numeric)
  • Rows Removed by Filter: 669438
  • Buffers: shared hit=499464409 read=473652
44. 3,055.875 726,114.580 ↓ 707,120.0 707,120 1

Sort (cost=61,237.83..61,237.84 rows=1 width=1,201) (actual time=726,038.170..726,114.580 rows=707,120 loops=1)

  • Output: bi_soc_1.soc_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_partie_dossier_1.type_partie, ((SubPlan 4)), il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_inva, il_dwh_pa_gen_1.type_couv, ((SubPlan 5)), il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu_1.etat_civil, ((bi_vcouv_1.tco_rente_en_cours)::integer), bi_dates_1.dte_jour, a1_il_dwh_part_dossier_affi_1.type_eff, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire
  • Sort Key: il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_partie_dossier_1.type_partie, ((SubPlan 4)), il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_inva, il_dwh_pa_gen_1.type_couv, ((SubPlan 5)), il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu_1.etat_civil, a1_il_dwh_part_dossier_affi_1.type_eff
  • Sort Method: quicksort Memory: 212919kB
  • Buffers: shared hit=499464409 read=473652
45. 599.385 723,058.705 ↓ 707,120.0 707,120 1

Nested Loop (cost=12,919.79..61,237.82 rows=1 width=1,201) (actual time=1,169.860..723,058.705 rows=707,120 loops=1)

  • Output: bi_soc_1.soc_id, il_dwh_contrat_1.numero_contrat, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, il_dwh_partie_dossier_1.type_partie, ((SubPlan 4)), il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_inva, il_dwh_pa_gen_1.type_couv, ((SubPlan 5)), il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, (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, a1_il_dwh_part_dossier_assu_1.etat_civil, (bi_vcouv_1.tco_rente_en_cours)::integer, bi_dates_1.dte_jour, a1_il_dwh_part_dossier_affi_1.type_eff, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire
  • Buffers: shared hit=499464409 read=473652
46. 821.606 721,045.080 ↓ 707,120.0 707,120 1

Nested Loop (cost=12,919.51..61,235.31 rows=1 width=1,198) (actual time=1,169.842..721,045.080 rows=707,120 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, a1_il_dwh_part_dossier_affi_1.type_eff, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, bi_soc_1.soc_id, ((SubPlan 4)), ((SubPlan 5)), (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=496635929 read=473652
47. 379.161 719,516.354 ↓ 707,120.0 707,120 1

Nested Loop (cost=12,919.51..61,234.21 rows=1 width=1,198) (actual time=1,169.834..719,516.354 rows=707,120 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, a1_il_dwh_part_dossier_affi_1.type_eff, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, bi_prod_1.soc_id, ((SubPlan 4)), ((SubPlan 5)), (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=495928809 read=473652
48. 9,092.612 717,217.483 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,919.36..61,231.53 rows=1 width=1,198) (actual time=1,169.821..717,217.483 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, a1_il_dwh_part_dossier_affi_1.type_eff, il_dwh_contrat_1.numero_contrat, il_dwh_contrat_1.dl_datextract, il_dwh_contrat_1.pro_id, ((SubPlan 4)), ((SubPlan 5)), (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_dossier_1.contrat_id = il_dwh_contrat_1.contrat_id)
  • Rows Removed by Join Filter: 92197190
  • Buffers: shared hit=494009099 read=473652
49. 1,222.817 86,138.831 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,918.94..61,225.24 rows=1 width=1,222) (actual time=1,169.754..86,138.831 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_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.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_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.dl_datextract, il_dwh_dossier_1.dl_soc_id, il_dwh_dossier_1.dl_flg_bcl, il_dwh_dossier_1.contrat_id, a1_il_dwh_part_dossier_affi_1.type_eff, a1_il_dwh_part_dossier_affi_1.dl_soc_id, ((SubPlan 4)), ((SubPlan 5)), (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=37981362 read=473349
50. 1,565.658 83,956.159 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,918.94..61,224.18 rows=1 width=1,226) (actual time=1,169.743..83,956.159 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_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.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_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.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, a1_il_dwh_part_dossier_affi_1.type_eff, a1_il_dwh_part_dossier_affi_1.dl_soc_id, ((SubPlan 4)), ((SubPlan 5)), (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=37021507 read=473349
51. 927.588 79,510.936 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,918.94..61,222.83 rows=1 width=1,226) (actual time=1,169.732..79,510.936 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_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.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_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.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, a1_il_dwh_part_dossier_affi_1.type_eff, a1_il_dwh_part_dossier_affi_1.dl_soc_id, ((SubPlan 4)), ((SubPlan 5)), (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 = a1_il_dwh_part_dossier_affi_1.dl_soc_id)
  • Buffers: shared hit=36061652 read=473349
52. 1,285.248 70,904.508 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,918.50..61,205.15 rows=1 width=1,226) (actual time=1,169.702..70,904.508 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.dl_soc_id, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_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.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4)), ((SubPlan 5)), (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=30126328 read=452168
53. 1,720.634 63,860.130 ↓ 959,855.0 959,855 1

Nested Loop (cost=12,918.07..61,200.61 rows=1 width=1,181) (actual time=1,169.649..63,860.130 rows=959,855 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, 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_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_pa_gen_valorisation_1.cd_vas_inventaire_dat_ext, il_dwh_pa_gen_valorisation_1.cd_rev_commerciale_dat_ext, il_dwh_pa_gen_valorisation_1.vas_inventaire, il_dwh_pa_gen_valorisation_1.dl_soc_id, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4)), ((SubPlan 5))
  • Buffers: shared hit=25434474 read=447704
54. 2,245.360 50,468.536 ↓ 1,458,870.0 1,458,870 1

Nested Loop Left Join (cost=12,917.50..61,197.87 rows=1 width=1,172) (actual time=1,167.996..50,468.536 rows=1,458,870 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, 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.assure_part_dossier_id, il_dwh_pa_gen_prest_1.prest_tot, il_dwh_pa_gen_prest_1.idx, il_dwh_pa_gen_prest_1.idx_employeur_fine_capi, il_dwh_pa_gen_prest_1.idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.reduc_idx_employeur_fine_perio, il_dwh_pa_gen_prest_1.participation_excedents, il_dwh_pa_gen_prest_1.prest_due, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4)), ((SubPlan 5))
  • Buffers: shared hit=18696791 read=375436
55. 1,393.071 36,552.216 ↓ 1,458,870.0 1,458,870 1

Nested Loop (cost=12,916.93..61,195.14 rows=1 width=1,148) (actual time=1,167.393..36,552.216 rows=1,458,870 loops=1)

  • Output: bi_vcouv_1.tco_rente_en_cours, il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, 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.assure_part_dossier_id, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4)), ((SubPlan 5))
  • Inner Unique: true
  • Buffers: shared hit=11441053 read=319217
56. 642.131 30,668.772 ↓ 1,496,791.0 1,496,791 1

Nested Loop (cost=12,916.65..61,192.72 rows=1 width=1,151) (actual time=1,167.368..30,668.772 rows=1,496,791 loops=1)

  • Output: il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.vco_id, 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.assure_part_dossier_id, il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, il_dwh_plan_partie_1.dl_soc_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4)), ((SubPlan 5))
  • Buffers: shared hit=6950663 read=319180
57. 455.328 7,116.352 ↓ 206,399.0 206,399 1

Nested Loop (cost=12,916.08..60,850.84 rows=1 width=615) (actual time=1,166.804..7,116.352 rows=206,399 loops=1)

  • Output: il_dwh_plan_partie_1.nom_plan, il_dwh_plan_partie_1.transitoire, 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.plan_partie_id, il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_inva, il_dwh_partie_dossier_1.dl_soc_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4))
  • Buffers: shared hit=699040 read=56676
58. 216.161 3,071.416 ↓ 2,913.6 81,582 1

Hash Join (cost=12,915.64..60,559.51 rows=28 width=582) (actual time=1,166.559..3,071.416 rows=81,582 loops=1)

  • Output: il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_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_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.dossier_gen_id, il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id, ((SubPlan 4))
  • Hash Cond: ((il_dwh_partie_dossier_1.dl_soc_id = il_dwh_dossier_1.dl_soc_id) AND (il_dwh_partie_dossier_1.dossier_gen_id = il_dwh_dossier_1.dossier_gen_id))
  • Buffers: shared hit=247329 read=10277
59. 1,036.530 1,689.450 ↓ 6.3 81,615 1

Index Scan using olm_il_dwh_partie_dossier_date_dl_flg_bcl_dl_flg_inv on bidwh.il_dwh_partie_dossier il_dwh_partie_dossier_1 (cost=0.43..47,482.30 rows=12,938 width=550) (actual time=0.655..1,689.450 rows=81,615 loops=1)

  • Output: il_dwh_partie_dossier_1.type_partie, il_dwh_partie_dossier_1.type_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_partie_dossier_1.partie_dossier_id, il_dwh_partie_dossier_1.dossier_gen_id, (SubPlan 4)
  • Index Cond: ((il_dwh_partie_dossier_1.dl_datextract = '2020-02-01'::date) AND (il_dwh_partie_dossier_1.dl_flg_bcl = 0) AND (il_dwh_partie_dossier_1.dl_flg_inv = 1))
  • Filter: ((il_dwh_partie_dossier_1.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 442
  • Buffers: shared hit=245867 read=2560
60.          

SubPlan (for Index Scan)

61. 652.920 652.920 ↑ 1.0 1 81,615

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_3 (cost=0.28..2.50 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=81,615)

  • Output: a_3.texte
  • Index Cond: (((il_dwh_partie_dossier_1.type_partie)::text = (a_3.code_cle)::text) AND ((a_3.code_type)::text = 'TypePartieDossier'::text))
  • Filter: ((a_3.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=244845
62. 31.600 1,165.805 ↓ 5.9 76,032 1

Hash (cost=12,722.43..12,722.43 rows=12,852 width=32) (actual time=1,165.804..1,165.805 rows=76,032 loops=1)

  • Output: il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id
  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5776kB
  • Buffers: shared hit=1462 read=7717
63. 1,134.205 1,134.205 ↓ 5.9 76,032 1

Index Scan using olm_il_dwh_dossier_date_dl_flg_bcl_dl_flg_inv on bidwh.il_dwh_dossier il_dwh_dossier_1 (cost=0.43..12,722.43 rows=12,852 width=32) (actual time=0.802..1,134.205 rows=76,032 loops=1)

  • Output: il_dwh_dossier_1.numero_dossier, il_dwh_dossier_1.dossier_id, 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_gen_id, il_dwh_dossier_1.contrat_id
  • Index Cond: ((il_dwh_dossier_1.dl_datextract = '2020-02-01'::date) AND (il_dwh_dossier_1.dl_flg_bcl = 0) AND (il_dwh_dossier_1.dl_flg_inv = 1))
  • Filter: ((il_dwh_dossier_1.statut)::text <> 'TERM'::text)
  • Rows Removed by Filter: 414
  • Buffers: shared hit=1462 read=7717
64. 3,589.608 3,589.608 ↓ 3.0 3 81,582

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..10.39 rows=1 width=53) (actual time=0.012..0.044 rows=3 loops=81,582)

  • 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.dl_datextract = '2020-02-01'::date) AND (il_dwh_plan_partie_1.dl_flg_bcl = 0) AND (il_dwh_plan_partie_1.dl_flg_inv = 1) AND (il_dwh_partie_dossier_1.dl_soc_id = il_dwh_plan_partie_1.dl_soc_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=451711 read=46399
65. 15,426.334 22,910.289 ↓ 7.0 7 206,399

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..341.87 rows=1 width=556) (actual time=0.023..0.111 rows=7 loops=206,399)

  • Output: il_dwh_pa_gen_1.type_couv, il_dwh_pa_gen_1.dte_echeance, il_dwh_pa_gen_1.vco_id, 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.assure_part_dossier_id, il_dwh_pa_gen_1.plan_partie_id, (SubPlan 5)
  • Index Cond: (il_dwh_pa_gen_1.plan_partie_id = il_dwh_plan_partie_1.plan_partie_id)
  • Filter: (((il_dwh_pa_gen_1.type_couv)::text <> 'REAM'::text) AND (il_dwh_pa_gen_1.dl_datextract = '2020-02-01'::date) AND (il_dwh_pa_gen_1.dl_flg_bcl = 0) AND (il_dwh_pa_gen_1.dl_flg_inv = 1) AND ((il_dwh_pa_gen_1.etat)::text = 'VAL'::text) AND (il_dwh_plan_partie_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=6251623 read=262504
66.          

SubPlan (for Index Scan)

67. 7,483.955 7,483.955 ↑ 1.0 1 1,496,791

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_4 (cost=0.28..2.50 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1,496,791)

  • Output: a_4.texte
  • Index Cond: (((il_dwh_pa_gen_1.type_couv)::text = (a_4.code_cle)::text) AND ((a_4.code_type)::text = 'TypeNatureTypeCouverture'::text))
  • Filter: ((a_4.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=4496828 read=1
68. 4,490.373 4,490.373 ↑ 1.0 1 1,496,791

Index Scan using bi_vcouv_pkey on biinfra.bi_vcouv bi_vcouv_1 (cost=0.28..2.22 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=1,496,791)

  • 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)
  • Filter: ((bi_vcouv_1.tco_rente_en_cours)::integer = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4490390 read=37
69. 11,670.960 11,670.960 ↑ 1.0 1 1,458,870

Index Scan using olm_il_dwh_pa_gen_prest_pa_gen_id_dl_datextract_etc on bidwh.il_dwh_pa_gen_prest il_dwh_pa_gen_prest_1 (cost=0.57..2.72 rows=1 width=44) (actual time=0.008..0.008 rows=1 loops=1,458,870)

  • 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) AND (il_dwh_pa_gen_prest_1.dl_datextract = il_dwh_pa_gen_1.dl_datextract) AND (il_dwh_pa_gen_prest_1.dl_datextract = '2020-02-01'::date) 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_bcl = 0) 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_flg_inv = 1) AND (il_dwh_pa_gen_prest_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id))
  • Buffers: shared hit=7255738 read=56219
70. 11,670.960 11,670.960 ↑ 1.0 1 1,458,870

Index Scan using olm_il_dwh_pa_gen_valorisation_pa_gen_id_etc on bidwh.il_dwh_pa_gen_valorisation il_dwh_pa_gen_valorisation_1 (cost=0.57..2.73 rows=1 width=29) (actual time=0.008..0.008 rows=1 loops=1,458,870)

  • 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.dl_datextract = '2020-02-01'::date) AND (il_dwh_pa_gen_valorisation_1.pa_gen_id = il_dwh_pa_gen_1.pa_gen_id) AND (il_dwh_pa_gen_valorisation_1.dl_flg_inv = 1) AND (il_dwh_pa_gen_valorisation_1.dl_flg_bcl = 0) AND ((il_dwh_pa_gen_valorisation_1.type_av_ap)::text = 'APRE'::text) AND (il_dwh_pa_gen_valorisation_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id))
  • Buffers: shared hit=6737683 read=72268
71. 5,759.130 5,759.130 ↑ 1.0 1 959,855

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..4.53 rows=1 width=73) (actual time=0.006..0.006 rows=1 loops=959,855)

  • Output: a1_il_dwh_part_dossier_assu_1.part_dossier_id, a1_il_dwh_part_dossier_assu_1.type_eff, a1_il_dwh_part_dossier_assu_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_assu_1.dte_naissance, a1_il_dwh_part_dossier_assu_1.sexe, a1_il_dwh_part_dossier_assu_1.etat_civil, a1_il_dwh_part_dossier_assu_1.dl_datextract, 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 = '2020-02-01'::date) AND (a1_il_dwh_part_dossier_assu_1.dl_flg_bcl = 0) AND (a1_il_dwh_part_dossier_assu_1.dl_flg_inv = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4691854 read=4464
72. 7,678.840 7,678.840 ↑ 1.0 1 959,855

Index Scan using olm_il_dwh_part_dossier_dossier_gen_id on bidwh.il_dwh_part_dossier a1_il_dwh_part_dossier_affi_1 (cost=0.44..17.67 rows=1 width=24) (actual time=0.003..0.008 rows=1 loops=959,855)

  • Output: a1_il_dwh_part_dossier_affi_1.dl_datextract, a1_il_dwh_part_dossier_affi_1.dl_soc_id, a1_il_dwh_part_dossier_affi_1.dl_flg_bcl, a1_il_dwh_part_dossier_affi_1.dl_flg_inv, a1_il_dwh_part_dossier_affi_1.part_dossier_id, a1_il_dwh_part_dossier_affi_1.personne_contact_part_id, a1_il_dwh_part_dossier_affi_1.part_dossier_part_id, a1_il_dwh_part_dossier_affi_1.dossier_id, a1_il_dwh_part_dossier_affi_1.no_gen_dossier, a1_il_dwh_part_dossier_affi_1.dossier_gen_id, a1_il_dwh_part_dossier_affi_1.role, a1_il_dwh_part_dossier_affi_1.description_role, a1_il_dwh_part_dossier_affi_1.personne_contact, a1_il_dwh_part_dossier_affi_1.role_personne_contact, a1_il_dwh_part_dossier_affi_1.description_role_personne_cont, a1_il_dwh_part_dossier_affi_1.lien_parente_personne_contact, a1_il_dwh_part_dossier_affi_1.description_lien_parente, a1_il_dwh_part_dossier_affi_1.oc_principal_id, a1_il_dwh_part_dossier_affi_1.oc_secondaire_id, a1_il_dwh_part_dossier_affi_1.type_part, a1_il_dwh_part_dossier_affi_1.description_type_partenaire, a1_il_dwh_part_dossier_affi_1.nom_ou_raison_sociale, a1_il_dwh_part_dossier_affi_1.nom_complementaire, a1_il_dwh_part_dossier_affi_1.nom_alliance, a1_il_dwh_part_dossier_affi_1.prenom, a1_il_dwh_part_dossier_affi_1.dte_naissance, a1_il_dwh_part_dossier_affi_1.ann_naissance, a1_il_dwh_part_dossier_affi_1.dte_deces, a1_il_dwh_part_dossier_affi_1.sexe, a1_il_dwh_part_dossier_affi_1.description_sexe, a1_il_dwh_part_dossier_affi_1.etat_civil, a1_il_dwh_part_dossier_affi_1.description_etat_civil, a1_il_dwh_part_dossier_affi_1.no_avs, a1_il_dwh_part_dossier_affi_1.langue_correspondance, a1_il_dwh_part_dossier_affi_1.description_langue_corresponda, a1_il_dwh_part_dossier_affi_1.telephone, a1_il_dwh_part_dossier_affi_1.telephone_mobile, a1_il_dwh_part_dossier_affi_1.fax, a1_il_dwh_part_dossier_affi_1.e_mail, a1_il_dwh_part_dossier_affi_1.dte_deb_concubinage, a1_il_dwh_part_dossier_affi_1.indemnite_unique_conc, a1_il_dwh_part_dossier_affi_1.reduc_prest_deces_conc, a1_il_dwh_part_dossier_affi_1.reduc_allocation_deces_conc, a1_il_dwh_part_dossier_affi_1.dte_mar_conj, a1_il_dwh_part_dossier_affi_1.dte_div_conj, a1_il_dwh_part_dossier_affi_1.prest_libre_pass_lpp_mar_conj, a1_il_dwh_part_dossier_affi_1.prest_libre_pass_tot_mar_conj, a1_il_dwh_part_dossier_affi_1.choix_capital_conj, a1_il_dwh_part_dossier_affi_1.indemnite_unique_conj, a1_il_dwh_part_dossier_affi_1.reduc_prest_deces_conj, a1_il_dwh_part_dossier_affi_1.provenance_bande_etat_conj, a1_il_dwh_part_dossier_affi_1.reduc_allocation_deces_conj, a1_il_dwh_part_dossier_affi_1.rentes_non_indexables_conj, a1_il_dwh_part_dossier_affi_1.invalide_enfant, a1_il_dwh_part_dossier_affi_1.a_charge_enfant, a1_il_dwh_part_dossier_affi_1.type_charge_enfant, a1_il_dwh_part_dossier_affi_1.description_type_charge_enfant, a1_il_dwh_part_dossier_affi_1.dte_fin_charge_enfant, a1_il_dwh_part_dossier_affi_1.doublement_rte_enfant, a1_il_dwh_part_dossier_affi_1.index_bande_etat_enfant, a1_il_dwh_part_dossier_affi_1.dte_pacs, a1_il_dwh_part_dossier_affi_1.dte_dis_pacs, a1_il_dwh_part_dossier_affi_1.prest_libre_pass_lpp_pacs, a1_il_dwh_part_dossier_affi_1.prest_libre_pass_tot_pacs, a1_il_dwh_part_dossier_affi_1.choix_capital_pacs, a1_il_dwh_part_dossier_affi_1.indemnite_unique_pacs, a1_il_dwh_part_dossier_affi_1.reduc_prest_deces_pacs, a1_il_dwh_part_dossier_affi_1.rentes_non_indexables_pacs, a1_il_dwh_part_dossier_affi_1.reduc_allocation_deces_pacs, a1_il_dwh_part_dossier_affi_1.provenance_bande_etat_pacs, a1_il_dwh_part_dossier_affi_1.dn_mut_id, a1_il_dwh_part_dossier_affi_1.dn_dte_mut, a1_il_dwh_part_dossier_affi_1.dn_nom_mut, a1_il_dwh_part_dossier_affi_1.cree_par_batch, a1_il_dwh_part_dossier_affi_1.type_eff, a1_il_dwh_part_dossier_affi_1.type_eff_prec, a1_il_dwh_part_dossier_affi_1.effcal, a1_il_dwh_part_dossier_affi_1.numero_contact, a1_il_dwh_part_dossier_affi_1.usr_log_i, a1_il_dwh_part_dossier_affi_1.dte_log_i, a1_il_dwh_part_dossier_affi_1.usr_log_u, a1_il_dwh_part_dossier_affi_1.dte_log_u
  • Index Cond: (a1_il_dwh_part_dossier_affi_1.dossier_gen_id = il_dwh_partie_dossier_1.dossier_gen_id)
  • Filter: ((a1_il_dwh_part_dossier_affi_1.dl_datextract = '2020-02-01'::date) AND (a1_il_dwh_part_dossier_affi_1.dl_flg_bcl = 0) AND (a1_il_dwh_part_dossier_affi_1.dl_flg_inv = 1) AND ((a1_il_dwh_part_dossier_affi_1.role)::text = 'AFFI'::text) AND (il_dwh_partie_dossier_1.dl_soc_id = a1_il_dwh_part_dossier_affi_1.dl_soc_id))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=5935324 read=21181
73. 2,879.565 2,879.565 ↑ 1.0 1 959,855

Seq Scan on bidwhpar.bi_par_flg_bcl bi_par_flg_bcl_1 (cost=0.00..1.34 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=959,855)

  • 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 = 0)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=959855
74. 959.855 959.855 ↑ 1.0 1 959,855

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.001..0.001 rows=1 loops=959,855)

  • 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=959855
75. 63,043.770 621,986.040 ↓ 97.0 97 959,855

Index Scan using il_dwh_contrat_pkey on bidwh.il_dwh_contrat il_dwh_contrat_1 (cost=0.42..6.28 rows=1 width=24) (actual time=0.010..0.648 rows=97 loops=959,855)

  • 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
  • Index Cond: ((il_dwh_contrat_1.dl_datextract = '2020-02-01'::date) AND (il_dwh_contrat_1.dl_soc_id = il_dwh_pa_gen_1.dl_soc_id) AND (il_dwh_contrat_1.dl_flg_bcl = 0) AND (il_dwh_contrat_1.dl_flg_inv = 1))
  • Filter: (il_dwh_contrat_1.no_gen_contrat = (SubPlan 6))
  • Buffers: shared hit=456027737 read=303
76.          

SubPlan (for Index Scan)

77. 93,157.045 558,942.270 ↑ 1.0 1 93,157,045

Aggregate (cost=3.83..3.84 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=93,157,045)

  • Output: max(a_5.no_gen_contrat)
  • Buffers: shared hit=429189432 read=303
78. 465,785.225 465,785.225 ↑ 1.0 1 93,157,045

Index Scan using olm_il_dwh_contrat_ili0065 on bidwh.il_dwh_contrat a_5 (cost=0.42..3.83 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=93,157,045)

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

Index Scan using bi_prod_pkey on biinfra.bi_prod bi_prod_1 (cost=0.14..2.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=959,855)

  • 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)
  • Filter: (bi_prod_1.soc_id = 6)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1919710
80. 707.120 707.120 ↑ 1.0 1 707,120

Seq Scan on biinfra.bi_soc bi_soc_1 (cost=0.00..1.09 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=707,120)

  • 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 = 6)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=707120
81. 1,414.240 1,414.240 ↑ 1.0 1 707,120

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=707,120)

  • Output: bi_dates_1.dte_jour
  • Index Cond: (bi_dates_1.dte_jour = '2020-02-01'::date)
  • Heap Fetches: 1414240
  • Buffers: shared hit=2828480
Planning time : 128.394 ms
Execution time : 804,123.861 ms