explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Mlr

Settings
# exclusive inclusive rows x rows loops node
1. 1.202 13,660,310.455 ↓ 546.0 1,092 1

Unique (cost=5,915,898.85..5,915,899.01 rows=2 width=818) (actual time=13,660,309.794..13,660,310.455 rows=1,092 loops=1)

  • Output: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.fam_desc_courte, bi_prod.pro_id, bi_prod.pro_desc_courte, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, (sum(od_dwh_pa_gen.mnt_prest)), ($1), (sum(od_dwh_pa_gen.calc_ass1_surpri)), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, (1), ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Buffers: shared hit=1093514744 read=109916495 written=127798, temp read=906910700 written=245111
2. 16.467 13,660,309.253 ↓ 546.0 1,092 1

Sort (cost=5,915,898.85..5,915,898.85 rows=2 width=818) (actual time=13,660,309.197..13,660,309.253 rows=1,092 loops=1)

  • Output: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.fam_desc_courte, bi_prod.pro_id, bi_prod.pro_desc_courte, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, (sum(od_dwh_pa_gen.mnt_prest)), ($1), (sum(od_dwh_pa_gen.calc_ass1_surpri)), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, (1), ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Sort Key: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.fam_desc_courte, bi_prod.pro_id, bi_prod.pro_desc_courte, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, (sum(od_dwh_pa_gen.mnt_prest)), ($1), (sum(od_dwh_pa_gen.calc_ass1_surpri)), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, (1), ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Sort Method: quicksort Memory: 517kB
  • Buffers: shared hit=1093514744 read=109916495 written=127798, temp read=906910700 written=245111
3. 0.858 13,660,292.786 ↓ 546.0 1,092 1

Append (cost=3,111,518.99..5,915,898.84 rows=2 width=818) (actual time=13,636,601.159..13,660,292.786 rows=1,092 loops=1)

  • Buffers: shared hit=1093514732 read=109916495 written=127798, temp read=906910700 written=245111
4. 0.000 13,636,601.768 ↓ 1,051.0 1,051 1

GroupAggregate (cost=3,111,518.99..3,111,519.08 rows=1 width=464) (actual time=13,636,600.402..13,636,601.768 rows=1,051 loops=1)

  • Output: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_soc.soc_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.fam_desc_courte, bi_prod.pro_id, bi_prod.pro_desc_courte, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, sum(od_dwh_pa_gen.mnt_prest), ($1), sum(od_dwh_pa_gen.calc_ass1_surpri), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, 1, ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Group Key: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_fam_prod.fam_id, bi_prod.pro_id, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, ($1), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Buffers: shared hit=1084452362 read=109416498 written=127798, temp read=906910700 written=245111
5.          

Initplan (for GroupAggregate)

6. 0.000 9,458.817 ↑ 1.0 1 1

Finalize Aggregate (cost=756,023.71..756,023.72 rows=1 width=32) (actual time=9,458.817..9,458.817 rows=1 loops=1)

  • Output: max(CASE WHEN ((a.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END)
  • Buffers: shared hit=272941 read=8864
7. 15.173 9,465.175 ↓ 1.2 5 1

Gather (cost=756,023.29..756,023.70 rows=4 width=32) (actual time=9,458.305..9,465.175 rows=5 loops=1)

  • Output: (PARTIAL max(CASE WHEN ((a.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=272941 read=8864
8. 4,062.623 9,450.002 ↑ 1.0 1 5 / 5

Partial Aggregate (cost=755,023.29..755,023.30 rows=1 width=32) (actual time=9,450.001..9,450.002 rows=1 loops=5)

  • Output: PARTIAL max(CASE WHEN ((a.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END)
  • Buffers: shared hit=272941 read=8864
  • Worker 0: actual time=9447.679..9447.679 rows=1 loops=1
  • Buffers: shared hit=53394 read=1435
  • Worker 1: actual time=9448.883..9448.883 rows=1 loops=1
  • Buffers: shared hit=54546 read=2026
  • Worker 2: actual time=9448.275..9448.276 rows=1 loops=1
  • Buffers: shared hit=54640 read=1528
  • Worker 3: actual time=9448.823..9448.823 rows=1 loops=1
  • Buffers: shared hit=54837 read=1903
9. 1,794.631 5,387.379 ↑ 1.2 8,717,149 5 / 5

Hash Join (cost=215.11..445,570.17 rows=10,763,587 width=9) (actual time=2.447..5,387.379 rows=8,717,149 loops=5)

  • Output: a.type_nat_type_couv, a.type_pri
  • Inner Unique: true
  • Hash Cond: (b.cou_id = a.cou_id)
  • Buffers: shared hit=272941 read=8864
  • Worker 0: actual time=2.147..5378.983 rows=8516779 loops=1
  • Buffers: shared hit=53394 read=1435
  • Worker 1: actual time=2.490..5394.858 rows=8720058 loops=1
  • Buffers: shared hit=54546 read=2026
  • Worker 2: actual time=2.145..5401.928 rows=8718681 loops=1
  • Buffers: shared hit=54640 read=1528
  • Worker 3: actual time=2.584..5358.925 rows=8748267 loops=1
  • Buffers: shared hit=54837 read=1903
10. 1,966.269 3,592.347 ↑ 1.2 8,717,149 5 / 5

Hash Join (cost=180.27..417,129.91 rows=10,763,587 width=4) (actual time=1.870..3,592.347 rows=8,717,149 loops=5)

  • Output: b.cou_id
  • Inner Unique: true
  • Hash Cond: (c.vco_id = b.vco_id)
  • Buffers: shared hit=272648 read=8864
  • Worker 0: actual time=1.568..3548.403 rows=8516779 loops=1
  • Buffers: shared hit=53325 read=1435
  • Worker 1: actual time=1.849..3626.394 rows=8720058 loops=1
  • Buffers: shared hit=54477 read=2026
  • Worker 2: actual time=1.581..3631.723 rows=8718681 loops=1
  • Buffers: shared hit=54571 read=1528
  • Worker 3: actual time=1.935..3584.167 rows=8748267 loops=1
  • Buffers: shared hit=54768 read=1903
11. 1,624.251 1,624.251 ↑ 1.2 8,717,149 5 / 5

Parallel Seq Scan on bidwh.od_dwh_pa c (cost=0.00..388,662.87 rows=10,763,587 width=4) (actual time=0.015..1,624.251 rows=8,717,149 loops=5)

  • Output: c.dl_datextract, c.dl_soc_id, c.dl_flg_bcl, c.pa_id, c.vco_id
  • Filter: ((c.dl_datextract IS NOT NULL) AND (c.dl_flg_bcl IS NOT NULL) AND (c.pa_id IS NOT NULL))
  • Buffers: shared hit=272163 read=8864
  • Worker 0: actual time=0.015..1607.081 rows=8516779 loops=1
  • Buffers: shared hit=53228 read=1435
  • Worker 1: actual time=0.016..1644.207 rows=8720058 loops=1
  • Buffers: shared hit=54380 read=2026
  • Worker 2: actual time=0.016..1654.905 rows=8718681 loops=1
  • Buffers: shared hit=54474 read=1528
  • Worker 3: actual time=0.015..1618.931 rows=8748267 loops=1
  • Buffers: shared hit=54671 read=1903
12. 0.630 1.827 ↑ 1.0 3,701 5 / 5

Hash (cost=134.01..134.01 rows=3,701 width=8) (actual time=1.826..1.827 rows=3,701 loops=5)

  • Output: b.cou_id, b.vco_id
  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
  • Buffers: shared hit=485
  • Worker 0: actual time=1.524..1.525 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 1: actual time=1.804..1.804 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 2: actual time=1.536..1.537 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 3: actual time=1.891..1.892 rows=3701 loops=1
  • Buffers: shared hit=97
13. 1.197 1.197 ↑ 1.0 3,701 5 / 5

Seq Scan on produit.vcouv b (cost=0.00..134.01 rows=3,701 width=8) (actual time=0.014..1.197 rows=3,701 loops=5)

  • Output: b.cou_id, b.vco_id
  • Buffers: shared hit=485
  • Worker 0: actual time=0.015..0.972 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 1: actual time=0.016..1.228 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 2: actual time=0.014..0.994 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 3: actual time=0.017..1.224 rows=3701 loops=1
  • Buffers: shared hit=97
14. 0.142 0.401 ↑ 1.0 793 5 / 5

Hash (cost=24.93..24.93 rows=793 width=13) (actual time=0.401..0.401 rows=793 loops=5)

  • Output: a.type_nat_type_couv, a.type_pri, a.cou_id
  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
  • Buffers: shared hit=85
  • Worker 0: actual time=0.382..0.382 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 1: actual time=0.415..0.415 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 2: actual time=0.362..0.362 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 3: actual time=0.409..0.409 rows=793 loops=1
  • Buffers: shared hit=17
15. 0.259 0.259 ↑ 1.0 793 5 / 5

Seq Scan on produit.couv a (cost=0.00..24.93 rows=793 width=13) (actual time=0.022..0.259 rows=793 loops=5)

  • Output: a.type_nat_type_couv, a.type_pri, a.cou_id
  • Buffers: shared hit=85
  • Worker 0: actual time=0.022..0.242 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 1: actual time=0.027..0.271 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 2: actual time=0.021..0.227 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 3: actual time=0.029..0.263 rows=793 loops=1
  • Buffers: shared hit=17
16. 9.395 13,636,596.514 ↓ 1,051.0 1,051 1

Sort (cost=2,355,495.27..2,355,495.27 rows=1 width=405) (actual time=13,636,596.425..13,636,596.514 rows=1,051 loops=1)

  • Output: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_fam_prod.fam_id, bi_prod.pro_id, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, ($1), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen, bi_soc.soc_desc_courte, bi_fam_prod.fam_desc_courte, bi_prod.pro_desc_courte, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri
  • Sort Key: bi_dates.dte_jour, bi_fam_prod.fam_id, bi_prod.pro_id, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, ($1), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=1084452362 read=109416498 written=127798, temp read=906910700 written=245111
17. 1.705 13,636,587.119 ↓ 1,051.0 1,051 1

Nested Loop (cost=3.14..2,355,495.26 rows=1 width=405) (actual time=4,358,324.760..13,636,587.119 rows=1,051 loops=1)

  • Output: bi_dates.dte_jour, bi_par_flg_bcl.dl_flg_bcl, bi_soc.soc_id, bi_fam_prod.fam_id, bi_prod.pro_id, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, ($1), a1_od_dwh_part_assu1.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, ((SubPlan 2)), od_dwh_mut_pol.mupo_gen, od_dwh_pa_gen.pa_gen, bi_soc.soc_desc_courte, bi_fam_prod.fam_desc_courte, bi_prod.pro_desc_courte, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri
  • Buffers: shared hit=1084452362 read=109416498 written=127798, temp read=906910700 written=245111
18. 1,511.103 13,636,583.312 ↓ 1,051.0 1,051 1

Nested Loop (cost=3.14..2,355,494.16 rows=1 width=327) (actual time=4,358,324.749..13,636,583.312 rows=1,051 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_par_flg_bcl.dl_flg_bcl, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, bi_prod.pro_id, bi_prod.pro_desc_courte, bi_fam_prod.fam_id, bi_fam_prod.fam_desc_courte, bi_fam_prod.soc_id, bi_dates.dte_jour, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Inner Unique: true
  • Join Filter: (bi_prod.fam_id = bi_fam_prod.fam_id)
  • Rows Removed by Join Filter: 4026794
  • Buffers: shared hit=1084451314 read=109416495 written=127798, temp read=906910700 written=245111
19. 9,958.274 13,631,043.194 ↓ 1,343,005.0 1,343,005 1

Nested Loop (cost=3.14..2,355,492.65 rows=1 width=245) (actual time=3,661,972.665..13,631,043.194 rows=1,343,005 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_par_flg_bcl.dl_flg_bcl, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, bi_prod.pro_id, bi_prod.pro_desc_courte, bi_prod.fam_id, bi_dates.dte_jour, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Inner Unique: true
  • Join Filter: (od_dwh_pol.pro_id = bi_prod.pro_id)
  • Rows Removed by Join Filter: 123463467
  • Buffers: shared hit=1083108313 read=109416491 written=127798, temp read=906910700 written=245111
20. 6,453.233 13,608,997.875 ↓ 1,343,005.0 1,343,005 1

Nested Loop (cost=3.14..2,355,480.38 rows=1 width=220) (actual time=3,661,972.590..13,608,997.875 rows=1,343,005 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, bi_par_flg_bcl.dl_flg_bcl, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_pol.pro_id, bi_dates.dte_jour, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Inner Unique: true
  • Join Filter: (((bi_dates.dte_jour = '2018-12-31'::date) AND (bi_par_flg_inv.dl_flg_inv = 1) AND ((od_dwh_pa_gen.etat)::text = 'VAL'::text)) OR ((bi_dates.dte_jour = '2019-12-31'::date) AND (od_dwh_mut_pol.mut_id = ANY ('{4,5,6,7,8,9,24,25,26,29,35,36,37,38,39,40,41,42,43,45,46,47,49,66}'::integer[]))))
  • Rows Removed by Join Filter: 2457187
  • Buffers: shared hit=1075117098 read=109416403 written=127798, temp read=906910700 written=245111
21. 2,211.379 13,592,042.150 ↓ 1,166,943.6 10,502,492 1

Nested Loop (cost=2.86..2,355,477.19 rows=9 width=248) (actual time=3,661,815.933..13,592,042.150 rows=10,502,492 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, bi_par_flg_bcl.dl_flg_bcl, bi_par_flg_inv.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.mut_id, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_pol.dl_datextract, od_dwh_pol.pro_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Buffers: shared hit=1043609873 read=109416152 written=127798, temp read=906910700 written=245111
22. 4,580.482 13,537,318.311 ↓ 300,071.2 10,502,492 1

Nested Loop (cost=2.43..2,355,460.61 rows=35 width=246) (actual time=3,661,813.612..13,537,318.311 rows=10,502,492 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.pol_id, bi_par_flg_bcl.dl_flg_bcl, bi_par_flg_inv.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Inner Unique: true
  • Join Filter: (od_dwh_pol_gen.dl_flg_inv = bi_par_flg_inv.dl_flg_inv)
  • Rows Removed by Join Filter: 3270523
  • Buffers: shared hit=1001706962 read=109218818 written=127798, temp read=906910700 written=245111
23. 2,345.037 13,532,737.829 ↓ 300,071.2 10,502,492 1

Nested Loop (cost=2.43..2,355,457.79 rows=35 width=246) (actual time=3,661,812.970..13,532,737.829 rows=10,502,492 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, bi_par_flg_bcl.dl_flg_bcl, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Buffers: shared hit=1001706961 read=109218818 written=127798, temp read=906910700 written=245111
24. 0.868 0.868 ↑ 1.0 1 1

Seq Scan on bidwhpar.bi_par_flg_bcl (cost=0.00..1.34 rows=1 width=4) (actual time=0.016..0.868 rows=1 loops=1)

  • Output: bi_par_flg_bcl.dl_flg_bcl, bi_par_flg_bcl.dl_flg_bcl_desc
  • Filter: (bi_par_flg_bcl.dl_flg_bcl = 1)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
25. 3,222,897.842 13,530,391.924 ↓ 300,071.2 10,502,492 1

Nested Loop (cost=2.43..2,355,456.10 rows=35 width=246) (actual time=3,661,812.950..13,530,391.924 rows=10,502,492 loops=1)

  • Output: bi_vcouv.type_nat_type_couv, bi_vcouv.tco_desc_courte, bi_vcouv.cta_nom_fr, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Join Filter: (od_dwh_pa.vco_id = bi_vcouv.vco_id)
  • Rows Removed by Join Filter: 38859220400
  • Buffers: shared hit=1001706960 read=109218818 written=127798, temp read=906910700 written=245111
26. 46.238 46.238 ↑ 1.0 3,701 1

Seq Scan on biinfra.bi_vcouv (cost=0.00..301.01 rows=3,701 width=52) (actual time=0.013..46.238 rows=3,701 loops=1)

  • 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
  • Buffers: shared hit=92 read=172
27. 6,727,306.995 10,307,447.844 ↓ 300,071.2 10,502,492 3,701

Materialize (cost=2.43..2,353,212.15 rows=35 width=202) (actual time=2.566..2,785.044 rows=10,502,492 loops=3,701)

  • Output: od_dwh_pa.vco_id, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Buffers: shared hit=1001706868 read=109218646 written=127798, temp read=906910700 written=245111
28. 10,083.053 3,580,140.849 ↓ 300,071.2 10,502,492 1

Nested Loop (cost=2.43..2,353,211.98 rows=35 width=202) (actual time=9,475.318..3,580,140.849 rows=10,502,492 loops=1)

  • Output: od_dwh_pa.vco_id, od_dwh_pa.dl_datextract, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Buffers: shared hit=1001706868 read=109218646 written=127798
29. 12,958.367 2,351,768.724 ↓ 75,017.8 10,502,492 1

Nested Loop (cost=1.86..2,352,879.91 rows=140 width=198) (actual time=9,475.075..2,351,768.724 rows=10,502,492 loops=1)

  • Output: od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.pol_id, a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_datextract, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 2))
  • Inner Unique: true
  • Buffers: shared hit=202089453 read=104489357 written=95156
30. 4,325.845 2,286,295.297 ↓ 19,892.1 10,503,012 1

Nested Loop (cost=1.43..2,352,619.01 rows=528 width=160) (actual time=9,460.418..2,286,295.297 rows=10,503,012 loops=1)

  • Output: od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.part_assure1, od_dwh_pa_gen.dl_soc_id, od_dwh_pa_gen.pol_id, od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ($1), ((SubPlan 2))
  • Buffers: shared hit=160116784 read=104410806 written=95035
31. 2,229.798 186,250.713 ↓ 119.7 1,068,699 1

Nested Loop (cost=0.86..2,089,326.45 rows=8,926 width=80) (actual time=0.220..186,250.713 rows=1,068,699 loops=1)

  • Output: od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.mupo_gen, od_dwh_pol_gen.dl_flg_inv, od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, ((SubPlan 2))
  • Buffers: shared hit=7414709 read=5142193 written=1147
32. 5,548.182 14,097.774 ↓ 2.0 1,068,699 1

Index Scan using olm_od_dwh_mut_pol_dl_flg_bcl on bidwh.od_dwh_mut_pol (cost=0.43..1,453,802.39 rows=523,918 width=60) (actual time=0.157..14,097.774 rows=1,068,699 loops=1)

  • Output: od_dwh_mut_pol.dte_mut, od_dwh_mut_pol.etat, od_dwh_mut_pol.mupo_gen, od_dwh_mut_pol.dl_datextract, od_dwh_mut_pol.dl_flg_bcl, od_dwh_mut_pol.pol_id, od_dwh_mut_pol.mut_id, (SubPlan 2)
  • Index Cond: (od_dwh_mut_pol.dl_flg_bcl = 1)
  • Filter: ((od_dwh_mut_pol.etat)::text = 'VAL'::text)
  • Rows Removed by Filter: 15134
  • Buffers: shared hit=3205985 read=19500 written=9
33.          

SubPlan (for Index Scan)

34. 2,137.398 8,549.592 ↑ 1.0 1 1,068,699

Aggregate (cost=2.50..2.51 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1,068,699)

  • Output: max((a_1.nom_mut)::text)
  • Buffers: shared hit=3205985 read=112
35. 6,412.194 6,412.194 ↑ 1.0 1 1,068,699

Index Scan using qsys_mut_lan_00001 on produit.mut_lan a_1 (cost=0.28..2.50 rows=1 width=27) (actual time=0.005..0.006 rows=1 loops=1,068,699)

  • Output: a_1.mut_id, a_1.lan, a_1.nom_mut, a_1.ged_mut_1, a_1.ged_mut_2
  • Index Cond: ((a_1.mut_id = od_dwh_mut_pol.mut_id) AND ((a_1.lan)::text = 'FR'::text))
  • Buffers: shared hit=3205985 read=112
36. 169,923.141 169,923.141 ↑ 1.0 1 1,068,699

Index Scan using olm_od_dwh_pa_gen_pol_id_mupo_gen on bidwh.od_dwh_pol_gen (cost=0.43..1.20 rows=1 width=20) (actual time=0.144..0.159 rows=1 loops=1,068,699)

  • Output: od_dwh_pol_gen.dl_datextract, od_dwh_pol_gen.dl_soc_id, od_dwh_pol_gen.dl_flg_bcl, od_dwh_pol_gen.dl_flg_inv, od_dwh_pol_gen.pol_id, od_dwh_pol_gen.mupo_gen, od_dwh_pol_gen.vpv_id_base, od_dwh_pol_gen.no_pension, od_dwh_pol_gen.no_avenant, od_dwh_pol_gen.benef_deces, od_dwh_pol_gen.benef_vie, od_dwh_pol_gen.benef_irrevoc, od_dwh_pol_gen.confidentiel, od_dwh_pol_gen.pers_rp, od_dwh_pol_gen.fin_monnaie, od_dwh_pol_gen.prov_code, od_dwh_pol_gen.prov_no, od_dwh_pol_gen.pri_primaute_pp, od_dwh_pol_gen.pri_type, od_dwh_pol_gen.pri_freq_paiement, od_dwh_pol_gen.pri_frais_fixes, od_dwh_pol_gen.pri_soumis_drt_timbre, od_dwh_pol_gen.pri_avec_drt_timbre, od_dwh_pol_gen.pri_type_pe, od_dwh_pol_gen.pri_dte_valo_deces, od_dwh_pol_gen.pri_dte_effet, od_dwh_pol_gen.pri_dte_anni, od_dwh_pol_gen.pri_dte_echeance, od_dwh_pol_gen.pri_somme_pp, od_dwh_pol_gen.fin_pret_pol, od_dwh_pol_gen.fin_dte_pret_pol, od_dwh_pol_gen.fin_pret_desc, od_dwh_pol_gen.fin_mise_gage, od_dwh_pol_gen.fin_type_nant, od_dwh_pol_gen.fisc_adapt_opp3, od_dwh_pol_gen.fisc_adapt_opp3_desc, od_dwh_pol_gen.fisc_mnt_opp3_declare, od_dwh_pol_gen.fisc_mnt_opp3_max_sou, od_dwh_pol_gen.fisc_rachetable, od_dwh_pol_gen.fisc_impot_anticipe, od_dwh_pol_gen.fisc_impot_source, od_dwh_pol_gen.fisc_impot_desc_libre, od_dwh_pol_gen.lba_identite, od_dwh_pol_gen.lba_prov, od_dwh_pol_gen.type_annonce, od_dwh_pol_gen.motif_rachat, od_dwh_pol_gen.mnt_rachat_partiel, od_dwh_pol_gen.motif_resiliation, od_dwh_pol_gen.rte_capital, od_dwh_pol_gen.cause_annul, od_dwh_pol_gen.delai_attente_libe, od_dwh_pol_gen.ajour_dte_fin, od_dwh_pol_gen.ajour_annee, od_dwh_pol_gen.code_voir_dossier, od_dwh_pol_gen.tutelle, od_dwh_pol_gen.clause_pilote, od_dwh_pol_gen.clause_resident, od_dwh_pol_gen.code_gerance, od_dwh_pol_gen.no_conseiller, od_dwh_pol_gen.pri_primaute_pu, od_dwh_pol_gen.pri_somme_pu, od_dwh_pol_gen.cc_plan_de_repartition_existe, od_dwh_pol_gen.fisc_cga_id, od_dwh_pol_gen.fisc_cga_dte_deb, od_dwh_pol_gen.accuse_recept_auto, od_dwh_pol_gen.ajournement, od_dwh_pol_gen.cause_mise_pension_manu, od_dwh_pol_gen.dn_incapacite, od_dwh_pol_gen.confirm_mise_pension, od_dwh_pol_gen.date_naissance_verifiee, od_dwh_pol_gen.deces_premiere_tete, od_dwh_pol_gen.dte_proch_ajournement, od_dwh_pol_gen.dte_proch_boucl, od_dwh_pol_gen.dte_proch_prelev, od_dwh_pol_gen.dte_proch_rebalanc, od_dwh_pol_gen.duree_pe, od_dwh_pol_gen.etat_versement, od_dwh_pol_gen.frequence_desiree, od_dwh_pol_gen.mise_en_pension, od_dwh_pol_gen.mise_pension_manu, od_dwh_pol_gen.no_encaissement, od_dwh_pol_gen.no_fe_vers_add, od_dwh_pol_gen.pas_correspondance, od_dwh_pol_gen.pas_extrait_compte, od_dwh_pol_gen.profil_choisi, od_dwh_pol_gen.rebalanc_auto, od_dwh_pol_gen.redressement_envoi_prest, od_dwh_pol_gen.regroupement, od_dwh_pol_gen.stipulation_beneficiaire, od_dwh_pol_gen.type_rachat, od_dwh_pol_gen.res_id, od_dwh_pol_gen.reassurance, od_dwh_pol_gen.rachat_cap_reserve, od_dwh_pol_gen.versement_immediat_pe, od_dwh_pol_gen.fin_dte_nant, od_dwh_pol_gen.procuration, od_dwh_pol_gen.variante_desiree_id, od_dwh_pol_gen.val_transf, od_dwh_pol_gen.corr_except_pp, od_dwh_pol_gen.corr_except_pu, od_dwh_pol_gen.frais_gestion_pp, od_dwh_pol_gen.frais_gestion_pu, od_dwh_pol_gen.majoration_rente, od_dwh_pol_gen.total_vers_annee_ap_mut, od_dwh_pol_gen.val_rachat_apres_mut, od_dwh_pol_gen.val_dec_apres_mut, od_dwh_pol_gen.som_risq_dec_int, od_dwh_pol_gen.som_risq_inv_int, od_dwh_pol_gen.som_risq_dec_reas, od_dwh_pol_gen.som_risq_inv_reas, od_dwh_pol_gen.fisc_type, od_dwh_pol_gen.taux_redressement, od_dwh_pol_gen.base_financement_flexible, od_dwh_pol_gen.date_calcul_avoir, od_dwh_pol_gen.date_precedent_calcul_avoir, od_dwh_pol_gen.type_base_financement_flexible
  • Index Cond: ((od_dwh_pol_gen.pol_id = od_dwh_mut_pol.pol_id) AND (od_dwh_pol_gen.mupo_gen = od_dwh_mut_pol.mupo_gen))
  • Filter: ((od_dwh_pol_gen.dl_flg_bcl = 1) AND (od_dwh_mut_pol.dl_datextract = od_dwh_pol_gen.dl_datextract))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=4208724 read=5122693 written=1138
37. 2,095,718.739 2,095,718.739 ↓ 10.0 10 1,068,699

Index Scan using olm_od_dwh_pa_gen_pol_id on bidwh.od_dwh_pa_gen (cost=0.56..29.49 rows=1 width=88) (actual time=1.197..1.961 rows=10 loops=1,068,699)

  • Output: od_dwh_pa_gen.pa_id, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.part_assure1, od_dwh_pa_gen.dl_soc_id, od_dwh_pa_gen.pol_id, od_dwh_pa_gen.mupo_gen, $1
  • Index Cond: (od_dwh_pa_gen.pol_id = od_dwh_pol_gen.pol_id)
  • Filter: ((od_dwh_pa_gen.dl_flg_bcl = 1) AND (od_dwh_pol_gen.dl_datextract = od_dwh_pa_gen.dl_datextract) AND (od_dwh_pol_gen.mupo_gen = od_dwh_pa_gen.mupo_gen))
  • Rows Removed by Filter: 1402
  • Buffers: shared hit=152702075 read=99268613 written=93888
38. 52,515.060 52,515.060 ↑ 1.0 1 10,503,012

Index Scan using od_dwh_part_pkey on bidwh.od_dwh_part a1_od_dwh_part_assu1 (cost=0.43..0.49 rows=1 width=54) (actual time=0.005..0.005 rows=1 loops=10,503,012)

  • Output: a1_od_dwh_part_assu1.part_id, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu1.dl_flg_bcl, a1_od_dwh_part_assu1.dl_soc_id, a1_od_dwh_part_assu1.dl_datextract, replace(btrim(replace(((((btrim((a1_od_dwh_part_assu1.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu1.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)
  • Index Cond: ((a1_od_dwh_part_assu1.dl_datextract = od_dwh_pa_gen.dl_datextract) AND (a1_od_dwh_part_assu1.dl_soc_id = od_dwh_pa_gen.dl_soc_id) AND (a1_od_dwh_part_assu1.dl_flg_bcl = 1) AND (a1_od_dwh_part_assu1.part_id = od_dwh_pa_gen.part_assure1))
  • Buffers: shared hit=41972669 read=78551 written=121
39. 1,218,289.072 1,218,289.072 ↑ 1.0 1 10,502,492

Index Scan using olm_od_dwh_pa_pa_id on bidwh.od_dwh_pa (cost=0.56..2.36 rows=1 width=16) (actual time=0.086..0.116 rows=1 loops=10,502,492)

  • Output: od_dwh_pa.dl_datextract, od_dwh_pa.dl_soc_id, od_dwh_pa.dl_flg_bcl, od_dwh_pa.pa_id, od_dwh_pa.vco_id
  • Index Cond: (od_dwh_pa.pa_id = od_dwh_pa_gen.pa_id)
  • Filter: ((od_dwh_pa.dl_flg_bcl = 1) AND (od_dwh_pa_gen.dl_datextract = od_dwh_pa.dl_datextract))
  • Rows Removed by Filter: 71
  • Buffers: shared hit=799617415 read=4729289 written=32642
40. 0.000 0.000 ↑ 4.0 1 10,502,492

Materialize (cost=0.00..1.06 rows=4 width=4) (actual time=0.000..0.000 rows=1 loops=10,502,492)

  • Output: bi_par_flg_inv.dl_flg_inv
  • Buffers: shared hit=1
41. 0.632 0.632 ↑ 2.0 2 1

Seq Scan on bidwhpar.bi_par_flg_inv (cost=0.00..1.04 rows=4 width=4) (actual time=0.631..0.632 rows=2 loops=1)

  • Output: bi_par_flg_inv.dl_flg_inv
  • Buffers: shared hit=1
42. 52,512.460 52,512.460 ↑ 1.0 1 10,502,492

Index Scan using olm_ody0122_2 on bidwh.od_dwh_pol (cost=0.43..0.46 rows=1 width=22) (actual time=0.004..0.005 rows=1 loops=10,502,492)

  • Output: od_dwh_pol.dl_datextract, od_dwh_pol.dl_soc_id, od_dwh_pol.dl_flg_bcl, od_dwh_pol.pol_id, od_dwh_pol.pro_id, od_dwh_pol.cc_no_police, od_dwh_pol.pol_id_orig, od_dwh_pol.pol_id_dest, od_dwh_pol.pri_cpt_dep, od_dwh_pol.dte_reception_propo, od_dwh_pol.dte_signature_propo, od_dwh_pol.no_propo_ulysse
  • Index Cond: ((od_dwh_pol.pol_id = od_dwh_pa_gen.pol_id) AND (od_dwh_pol.dl_flg_bcl = 1) AND (od_dwh_pol.dl_datextract = od_dwh_pa_gen.dl_datextract))
  • Buffers: shared hit=41902911 read=197334
43. 10,502.492 10,502.492 ↓ 0.0 0 10,502,492

Index Only Scan using bi_dates_pkey on biinfra.bi_dates (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10,502,492)

  • Output: bi_dates.dte_jour
  • Index Cond: (bi_dates.dte_jour = od_dwh_pa_gen.dl_datextract)
  • Filter: ((bi_dates.dte_jour = '2018-12-31'::date) OR (bi_dates.dte_jour = '2019-12-31'::date))
  • Rows Removed by Filter: 1
  • Heap Fetches: 10502492
  • Buffers: shared hit=31507225 read=251
44. 12,087.045 12,087.045 ↑ 1.6 93 1,343,005

Seq Scan on biinfra.bi_prod (cost=0.00..10.45 rows=145 width=29) (actual time=0.001..0.009 rows=93 loops=1,343,005)

  • 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
  • Buffers: shared hit=7991215 read=88
45. 4,029.015 4,029.015 ↓ 3.0 3 1,343,005

Seq Scan on biinfra.bi_fam_prod (cost=0.00..1.50 rows=1 width=86) (actual time=0.003..0.003 rows=3 loops=1,343,005)

  • Output: bi_fam_prod.fam_id, bi_fam_prod.soc_id, bi_fam_prod.fam_desc_courte, bi_fam_prod.fam_type_famille, bi_fam_prod.fam_app_gest_id, bi_fam_prod.fam_dernier_num_police_utilise, bi_fam_prod.fal_nom_fam_fr, bi_fam_prod.fal_desc_longue_fr, bi_fam_prod.apg_nom_app, bi_fam_prod.apg_desc_courte, bi_fam_prod.apg_type_app
  • Filter: (bi_fam_prod.soc_id = 4)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=1343001 read=4
46. 2.102 2.102 ↑ 1.0 1 1,051

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

  • 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 = 4)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1048 read=3
47. 0.000 23,690.160 ↓ 41.0 41 1

GroupAggregate (cost=2,804,379.64..2,804,379.73 rows=1 width=464) (actual time=23,690.106..23,690.160 rows=41 loops=1)

  • Output: bi_dates_1.dte_jour, bi_par_flg_bcl_1.dl_flg_bcl, bi_soc_1.soc_id, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_id, bi_fam_prod_1.fam_desc_courte, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, sum(od_dwh_pa_gen_1.mnt_prest), ($4), sum(od_dwh_pa_gen_1.calc_ass2_surpri), a1_od_dwh_part_assu2.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, 2, ((SubPlan 4)), od_dwh_mut_pol_1.mupo_gen, od_dwh_pa_gen_1.pa_gen
  • Group Key: bi_dates_1.dte_jour, bi_par_flg_bcl_1.dl_flg_bcl, bi_soc_1.soc_id, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, ($4), a1_od_dwh_part_assu2.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, ((SubPlan 4)), od_dwh_mut_pol_1.mupo_gen, od_dwh_pa_gen_1.pa_gen
  • Buffers: shared hit=9062370 read=499997
48.          

Initplan (for GroupAggregate)

49. 0.349 9,558.283 ↑ 1.0 1 1

Finalize Aggregate (cost=756,023.71..756,023.72 rows=1 width=32) (actual time=9,558.283..9,558.283 rows=1 loops=1)

  • Output: max(CASE WHEN ((a_2.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a_2.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a_2.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a_2.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END)
  • Buffers: shared hit=268868 read=12937
50. 83.805 9,557.934 ↓ 1.2 5 1

Gather (cost=756,023.29..756,023.70 rows=4 width=32) (actual time=9,557.601..9,557.934 rows=5 loops=1)

  • Output: (PARTIAL max(CASE WHEN ((a_2.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a_2.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a_2.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a_2.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=268868 read=12937
51. 4,092.372 9,474.129 ↑ 1.0 1 5 / 5

Partial Aggregate (cost=755,023.29..755,023.30 rows=1 width=32) (actual time=9,474.129..9,474.129 rows=1 loops=5)

  • Output: PARTIAL max(CASE WHEN ((a_2.type_nat_type_couv)::text = ANY ('{DDEC,DCON,MIXT,CRET}'::text[])) THEN 'Général'::text WHEN ((a_2.type_nat_type_couv)::text = ANY ('{LPAI,RIG,LASI,RENR,RENL}'::text[])) THEN 'Incapacité'::text WHEN ((a_2.type_nat_type_couv)::text = 'DACC'::text) THEN 'Accident'::text WHEN ((a_2.type_nat_type_couv)::text = 'LPAD'::text) THEN 'Libération Payeur Décès'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'PERI'::text)) THEN 'Epargne Fonds Périodique'::text WHEN (((a_2.type_nat_type_couv)::text = ANY ('{DCFO,EPFO}'::text[])) AND ((a_2.type_pri)::text = 'UNIQ'::text)) THEN 'Epargne Fonds Unique'::text ELSE 'Autre'::text END)
  • Buffers: shared hit=268868 read=12937
  • Worker 0: actual time=9468.604..9468.604 rows=1 loops=1
  • Buffers: shared hit=54277 read=2398
  • Worker 1: actual time=9468.571..9468.571 rows=1 loops=1
  • Buffers: shared hit=51678 read=2618
  • Worker 2: actual time=9468.574..9468.574 rows=1 loops=1
  • Buffers: shared hit=53169 read=2537
  • Worker 3: actual time=9468.509..9468.509 rows=1 loops=1
  • Buffers: shared hit=54487 read=2443
52. 1,839.105 5,381.757 ↑ 1.2 8,717,149 5 / 5

Hash Join (cost=215.11..445,570.17 rows=10,763,587 width=9) (actual time=4.757..5,381.757 rows=8,717,149 loops=5)

  • Output: a_2.type_nat_type_couv, a_2.type_pri
  • Inner Unique: true
  • Hash Cond: (b_1.cou_id = a_2.cou_id)
  • Buffers: shared hit=268868 read=12937
  • Worker 0: actual time=2.972..5382.618 rows=8800624 loops=1
  • Buffers: shared hit=54277 read=2398
  • Worker 1: actual time=2.916..5395.783 rows=8370814 loops=1
  • Buffers: shared hit=51678 read=2618
  • Worker 2: actual time=3.344..5386.127 rows=8619143 loops=1
  • Buffers: shared hit=53169 read=2537
  • Worker 3: actual time=2.814..5385.515 rows=8822301 loops=1
  • Buffers: shared hit=54487 read=2443
53. 1,980.870 3,541.809 ↑ 1.2 8,717,149 5 / 5

Hash Join (cost=180.27..417,129.91 rows=10,763,587 width=4) (actual time=2.603..3,541.809 rows=8,717,149 loops=5)

  • Output: b_1.cou_id
  • Inner Unique: true
  • Hash Cond: (c_1.vco_id = b_1.vco_id)
  • Buffers: shared hit=268595 read=12917
  • Worker 0: actual time=1.853..3560.575 rows=8800624 loops=1
  • Buffers: shared hit=54210 read=2396
  • Worker 1: actual time=1.719..3539.813 rows=8370814 loops=1
  • Buffers: shared hit=51609 read=2618
  • Worker 2: actual time=1.669..3566.125 rows=8619143 loops=1
  • Buffers: shared hit=53101 read=2536
  • Worker 3: actual time=1.777..3569.148 rows=8822301 loops=1
  • Buffers: shared hit=54418 read=2443
54. 1,558.475 1,558.475 ↑ 1.2 8,717,149 5 / 5

Parallel Seq Scan on bidwh.od_dwh_pa c_1 (cost=0.00..388,662.87 rows=10,763,587 width=4) (actual time=0.106..1,558.475 rows=8,717,149 loops=5)

  • Output: c_1.dl_datextract, c_1.dl_soc_id, c_1.dl_flg_bcl, c_1.pa_id, c_1.vco_id
  • Filter: ((c_1.dl_datextract IS NOT NULL) AND (c_1.dl_flg_bcl IS NOT NULL) AND (c_1.pa_id IS NOT NULL))
  • Buffers: shared hit=268207 read=12820
  • Worker 0: actual time=0.017..1587.385 rows=8800624 loops=1
  • Buffers: shared hit=54113 read=2396
  • Worker 1: actual time=0.016..1598.068 rows=8370814 loops=1
  • Buffers: shared hit=51512 read=2618
  • Worker 2: actual time=0.015..1593.889 rows=8619143 loops=1
  • Buffers: shared hit=53004 read=2536
  • Worker 3: actual time=0.042..1593.846 rows=8822301 loops=1
  • Buffers: shared hit=54321 read=2443
55. 0.592 2.464 ↑ 1.0 3,701 5 / 5

Hash (cost=134.01..134.01 rows=3,701 width=8) (actual time=2.464..2.464 rows=3,701 loops=5)

  • Output: b_1.cou_id, b_1.vco_id
  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
  • Buffers: shared hit=388 read=97
  • Worker 0: actual time=1.805..1.805 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 1: actual time=1.675..1.675 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 2: actual time=1.626..1.626 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 3: actual time=1.701..1.701 rows=3701 loops=1
  • Buffers: shared hit=97
56. 1.872 1.872 ↑ 1.0 3,701 5 / 5

Seq Scan on produit.vcouv b_1 (cost=0.00..134.01 rows=3,701 width=8) (actual time=0.450..1.872 rows=3,701 loops=5)

  • Output: b_1.cou_id, b_1.vco_id
  • Buffers: shared hit=388 read=97
  • Worker 0: actual time=0.018..1.184 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 1: actual time=0.016..1.062 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 2: actual time=0.014..1.028 rows=3701 loops=1
  • Buffers: shared hit=97
  • Worker 3: actual time=0.017..1.138 rows=3701 loops=1
  • Buffers: shared hit=97
57. 0.161 0.843 ↑ 1.0 793 5 / 5

Hash (cost=24.93..24.93 rows=793 width=13) (actual time=0.843..0.843 rows=793 loops=5)

  • Output: a_2.type_nat_type_couv, a_2.type_pri, a_2.cou_id
  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
  • Buffers: shared hit=68 read=17
  • Worker 0: actual time=0.409..0.409 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 1: actual time=0.480..0.480 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 2: actual time=0.399..0.399 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 3: actual time=0.380..0.380 rows=793 loops=1
  • Buffers: shared hit=17
58. 0.682 0.682 ↑ 1.0 793 5 / 5

Seq Scan on produit.couv a_2 (cost=0.00..24.93 rows=793 width=13) (actual time=0.302..0.682 rows=793 loops=5)

  • Output: a_2.type_nat_type_couv, a_2.type_pri, a_2.cou_id
  • Buffers: shared hit=68 read=17
  • Worker 0: actual time=0.024..0.264 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 1: actual time=0.025..0.310 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 2: actual time=0.035..0.242 rows=793 loops=1
  • Buffers: shared hit=17
  • Worker 3: actual time=0.024..0.234 rows=793 loops=1
  • Buffers: shared hit=17
59. 4.172 23,688.296 ↓ 41.0 41 1

Sort (cost=2,048,355.92..2,048,355.92 rows=1 width=405) (actual time=23,688.293..23,688.296 rows=41 loops=1)

  • Output: bi_dates_1.dte_jour, bi_par_flg_bcl_1.dl_flg_bcl, bi_soc_1.soc_id, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, ($4), a1_od_dwh_part_assu2.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, ((SubPlan 4)), od_dwh_mut_pol_1.mupo_gen, od_dwh_pa_gen_1.pa_gen, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_desc_courte, bi_prod_1.pro_desc_courte, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri
  • Sort Key: bi_dates_1.dte_jour, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_mut_pol_1.dte_mut, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, ($4), a1_od_dwh_part_assu2.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, ((SubPlan 4)), od_dwh_mut_pol_1.mupo_gen, od_dwh_pa_gen_1.pa_gen
  • Sort Method: quicksort Memory: 41kB
  • Buffers: shared hit=9062370 read=499997
60. 2.908 23,684.124 ↓ 41.0 41 1

Nested Loop (cost=98,366.74..2,048,355.91 rows=1 width=405) (actual time=11,768.970..23,684.124 rows=41 loops=1)

  • Output: bi_dates_1.dte_jour, bi_par_flg_bcl_1.dl_flg_bcl, bi_soc_1.soc_id, bi_fam_prod_1.fam_id, bi_prod_1.pro_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, ($4), a1_od_dwh_part_assu2.part_id, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, ((SubPlan 4)), od_dwh_mut_pol_1.mupo_gen, od_dwh_pa_gen_1.pa_gen, bi_soc_1.soc_desc_courte, bi_fam_prod_1.fam_desc_courte, bi_prod_1.pro_desc_courte, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri
  • Inner Unique: true
  • Join Filter: (((bi_dates_1.dte_jour = '2018-12-31'::date) AND (bi_par_flg_inv_1.dl_flg_inv = 1) AND ((od_dwh_pa_gen_1.etat)::text = 'VAL'::text)) OR ((bi_dates_1.dte_jour = '2019-12-31'::date) AND (od_dwh_mut_pol_1.mut_id = ANY ('{4,5,6,7,8,9,24,25,26,29,35,36,37,38,39,40,41,42,43,45,46,47,49,66}'::integer[]))))
  • Rows Removed by Join Filter: 384
  • Buffers: shared hit=9062370 read=499997
61. 2.282 23,677.691 ↓ 705.0 705 1

Nested Loop (cost=98,366.46..2,048,355.55 rows=1 width=433) (actual time=10,158.808..23,677.691 rows=705 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_inv_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.mut_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_pol_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_fam_prod_1.fam_id, bi_fam_prod_1.fam_desc_courte, bi_soc_1.soc_id, bi_soc_1.soc_desc_courte, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Buffers: shared hit=9060255 read=499997
62. 47.479 23,673.294 ↓ 705.0 705 1

Nested Loop (cost=98,366.46..2,048,354.45 rows=1 width=355) (actual time=10,157.576..23,673.294 rows=705 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_inv_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.mut_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_pol_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_fam_prod_1.fam_id, bi_fam_prod_1.fam_desc_courte, bi_fam_prod_1.soc_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Inner Unique: true
  • Buffers: shared hit=9059551 read=499996
63. 72.096 23,539.376 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,366.32..2,048,354.23 rows=1 width=273) (actual time=10,000.473..23,539.376 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_inv_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.mut_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_pol_1.dl_datextract, bi_prod_1.pro_id, bi_prod_1.pro_desc_courte, bi_prod_1.fam_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Inner Unique: true
  • Buffers: shared hit=8886674 read=499995
64. 65.593 23,380.841 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,366.17..2,048,354.07 rows=1 width=248) (actual time=9,998.099..23,380.841 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_inv_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.mut_id, od_dwh_pol_1.pol_id, od_dwh_pol_1.cc_no_police, od_dwh_pol_1.dl_datextract, od_dwh_pol_1.pro_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Buffers: shared hit=8713797 read=499994
65. 29.403 22,364.419 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,365.74..2,048,353.59 rows=1 width=246) (actual time=9,996.727..22,364.419 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.pol_id, bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_inv_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.dl_flg_bcl, od_dwh_mut_pol_1.pol_id, od_dwh_mut_pol_1.mut_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Inner Unique: true
  • Buffers: shared hit=8372048 read=495426
66. 79.331 22,248.577 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,365.61..2,048,353.44 rows=1 width=246) (actual time=9,994.641..22,248.577 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.dl_flg_inv, bi_par_flg_bcl_1.dl_flg_bcl, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.dl_flg_bcl, od_dwh_mut_pol_1.pol_id, od_dwh_mut_pol_1.mut_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Buffers: shared hit=8285610 read=495424
67. 146.781 21,909.929 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,365.61..2,048,352.09 rows=1 width=246) (actual time=9,994.135..21,909.929 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_1.dl_datextract, bi_vcouv_1.type_nat_type_couv, bi_vcouv_1.tco_desc_courte, bi_vcouv_1.cta_nom_fr, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.dl_flg_bcl, od_dwh_mut_pol_1.pol_id, od_dwh_mut_pol_1.mut_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Inner Unique: true
  • Buffers: shared hit=8199171 read=495424
68. 96.304 21,590.270 ↓ 86,439.0 86,439 1

Nested Loop (cost=98,365.33..2,048,351.79 rows=1 width=202) (actual time=9,991.423..21,590.270 rows=86,439 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_1.dl_datextract, od_dwh_pa_1.vco_id, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.dl_flg_inv, od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.dl_flg_bcl, od_dwh_mut_pol_1.pol_id, od_dwh_mut_pol_1.mut_id, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)), ((SubPlan 4))
  • Buffers: shared hit=7939922 read=495356
69. 2,544.874 14,071.256 ↓ 1,164.3 87,326 1

Gather (cost=98,364.90..2,048,127.22 rows=75 width=150) (actual time=9,977.329..14,071.256 rows=87,326 loops=1)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_gen_1.mupo_gen, od_dwh_pa_1.dl_datextract, od_dwh_pa_1.vco_id, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.mupo_gen, od_dwh_pol_gen_1.dl_flg_inv, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Workers Planned: 4
  • Params Evaluated: $4
  • Workers Launched: 4
  • Buffers: shared hit=6981876 read=439718
70. 24.322 11,526.382 ↓ 919.2 17,465 5 / 5

Nested Loop (cost=97,364.90..2,047,119.72 rows=19 width=150) (actual time=333.222..11,526.382 rows=17,465 loops=5)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_gen_1.mupo_gen, od_dwh_pa_1.dl_datextract, od_dwh_pa_1.vco_id, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.mupo_gen, od_dwh_pol_gen_1.dl_flg_inv, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=6713008 read=426781
  • Worker 0: actual time=322.776..13158.813 rows=19340 loops=1
  • Buffers: shared hit=1496633 read=98593
  • Worker 1: actual time=331.877..13102.113 rows=19917 loops=1
  • Buffers: shared hit=1529785 read=96123
  • Worker 2: actual time=319.814..13611.923 rows=20945 loops=1
  • Buffers: shared hit=1601990 read=102008
  • Worker 3: actual time=321.598..13338.177 rows=21351 loops=1
  • Buffers: shared hit=1594093 read=100760
71. 18.518 9,283.980 ↓ 232.9 17,465 5 / 5

Nested Loop (cost=97,364.33..2,046,941.82 rows=75 width=146) (actual time=331.511..9,283.980 rows=17,465 loops=5)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_flg_bcl, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_gen_1.mupo_gen, od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.mupo_gen, od_dwh_pol_gen_1.dl_flg_inv, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buffers: shared hit=771466 read=418746
  • Worker 0: actual time=322.352..10597.448 rows=19340 loops=1
  • Buffers: shared hit=175888 read=96539
  • Worker 1: actual time=330.873..10545.381 rows=19917 loops=1
  • Buffers: shared hit=173702 read=94484
  • Worker 2: actual time=318.022..10863.638 rows=20945 loops=1
  • Buffers: shared hit=180230 read=100123
  • Worker 3: actual time=319.973..10681.670 rows=21351 loops=1
  • Buffers: shared hit=185226 read=98793
72. 154.821 2,384.173 ↓ 2.4 17,465 5 / 5

Parallel Hash Join (cost=97,363.90..2,043,164.56 rows=7,285 width=126) (actual time=320.285..2,384.173 rows=17,465 loops=5)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_datextract, od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.dl_flg_bcl, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_gen_1.mupo_gen, ($4), (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Inner Unique: true
  • Hash Cond: ((od_dwh_pa_gen_1.part_assure2 = a1_od_dwh_part_assu2.part_id) AND (od_dwh_pa_gen_1.dl_soc_id = a1_od_dwh_part_assu2.dl_soc_id) AND (od_dwh_pa_gen_1.dl_datextract = a1_od_dwh_part_assu2.dl_datextract))
  • Buffers: shared hit=134930 read=300910
  • Worker 0: actual time=310.148..2730.283 rows=19340 loops=1
  • Buffers: shared hit=31219 read=69754
  • Worker 1: actual time=328.843..2675.264 rows=19917 loops=1
  • Buffers: shared hit=30405 read=67574
  • Worker 2: actual time=304.634..2835.495 rows=20945 loops=1
  • Buffers: shared hit=32655 read=72394
  • Worker 3: actual time=310.769..2731.502 rows=21351 loops=1
  • Buffers: shared hit=32203 read=71305
73. 1,927.703 1,927.703 ↑ 1.2 2,116,818 5 / 5

Parallel Index Scan using olm_od_dwh_pa_gen_dl_flg_bcl on bidwh.od_dwh_pa_gen od_dwh_pa_gen_1 (cost=0.56..1,925,181.08 rows=2,618,432 width=88) (actual time=4.931..1,927.703 rows=2,116,818 loops=5)

  • Output: od_dwh_pa_gen_1.pa_id, od_dwh_pa_gen_1.etat, od_dwh_pa_gen_1.princ_compl, od_dwh_pa_gen_1.dte_deb, od_dwh_pa_gen_1.dte_fin_prest, od_dwh_pa_gen_1.mnt_prest, od_dwh_pa_gen_1.calc_ass2_surpri, od_dwh_pa_gen_1.pa_gen, od_dwh_pa_gen_1.part_assure2, od_dwh_pa_gen_1.dl_flg_bcl, od_dwh_pa_gen_1.dl_soc_id, od_dwh_pa_gen_1.dl_datextract, od_dwh_pa_gen_1.pol_id, od_dwh_pa_gen_1.mupo_gen, $4
  • Index Cond: (od_dwh_pa_gen_1.dl_flg_bcl = 1)
  • Buffers: shared hit=131469 read=290667
  • Worker 0: actual time=4.906..2255.442 rows=2463546 loops=1
  • Buffers: shared hit=30493 read=67399
  • Worker 1: actual time=2.953..2205.689 rows=2383431 loops=1
  • Buffers: shared hit=29696 read=65403
  • Worker 2: actual time=4.922..2351.904 rows=2588352 loops=1
  • Buffers: shared hit=31919 read=70159
  • Worker 3: actual time=5.974..2247.474 rows=2514484 loops=1
  • Buffers: shared hit=31447 read=68962
74. 51.928 301.649 ↑ 1.6 92,714 5 / 5

Parallel Hash (cost=94,747.66..94,747.66 rows=149,467 width=54) (actual time=301.649..301.649 rows=92,714 loops=5)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_flg_bcl, a1_od_dwh_part_assu2.dl_soc_id, a1_od_dwh_part_assu2.dl_datextract, (replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text))
  • Buckets: 524288 Batches: 1 Memory Usage: 38784kB
  • Buffers: shared hit=3230 read=10242
  • Worker 0: actual time=298.560..298.560 rows=103469 loops=1
  • Buffers: shared hit=668 read=2355
  • Worker 1: actual time=298.542..298.542 rows=97722 loops=1
  • Buffers: shared hit=651 read=2171
  • Worker 2: actual time=298.575..298.576 rows=99918 loops=1
  • Buffers: shared hit=679 read=2234
  • Worker 3: actual time=298.564..298.565 rows=104676 loops=1
  • Buffers: shared hit=698 read=2343
75. 249.721 249.721 ↑ 1.6 92,714 5 / 5

Parallel Index Scan using olm_od_dwh_part_dl_flg_bcl on bidwh.od_dwh_part a1_od_dwh_part_assu2 (cost=0.43..94,747.66 rows=149,467 width=54) (actual time=2.522..249.721 rows=92,714 loops=5)

  • Output: a1_od_dwh_part_assu2.part_id, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu2.dte_nais, a1_od_dwh_part_assu2.dl_flg_bcl, a1_od_dwh_part_assu2.dl_soc_id, a1_od_dwh_part_assu2.dl_datextract, replace(btrim(replace(((((btrim((a1_od_dwh_part_assu2.nom)::text) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.nom_complementaire, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_od_dwh_part_assu2.prenom, ' '::character varying))::text)), ' '::text, ' '::text)), ' '::text, ' '::text)
  • Index Cond: (a1_od_dwh_part_assu2.dl_flg_bcl = 1)
  • Filter: (a1_od_dwh_part_assu2.part_id IS NOT NULL)
  • Buffers: shared hit=3230 read=10242
  • Worker 0: actual time=1.780..236.214 rows=103469 loops=1
  • Buffers: shared hit=668 read=2355
  • Worker 1: actual time=1.678..242.595 rows=97722 loops=1
  • Buffers: shared hit=651 read=2171
  • Worker 2: actual time=1.619..242.025 rows=99918 loops=1
  • Buffers: shared hit=679 read=2234
  • Worker 3: actual time=1.719..240.775 rows=104676 loops=1
  • Buffers: shared hit=698 read=2343
76. 6,881.289 6,881.289 ↑ 1.0 1 87,326 / 5

Index Scan using olm_od_dwh_pa_gen_pol_id_mupo_gen on bidwh.od_dwh_pol_gen od_dwh_pol_gen_1 (cost=0.43..0.51 rows=1 width=20) (actual time=0.346..0.394 rows=1 loops=87,326)

  • Output: od_dwh_pol_gen_1.dl_datextract, od_dwh_pol_gen_1.dl_soc_id, od_dwh_pol_gen_1.dl_flg_bcl, od_dwh_pol_gen_1.dl_flg_inv, od_dwh_pol_gen_1.pol_id, od_dwh_pol_gen_1.mupo_gen, od_dwh_pol_gen_1.vpv_id_base, od_dwh_pol_gen_1.no_pension, od_dwh_pol_gen_1.no_avenant, od_dwh_pol_gen_1.benef_deces, od_dwh_pol_gen_1.benef_vie, od_dwh_pol_gen_1.benef_irrevoc, od_dwh_pol_gen_1.confidentiel, od_dwh_pol_gen_1.pers_rp, od_dwh_pol_gen_1.fin_monnaie, od_dwh_pol_gen_1.prov_code, od_dwh_pol_gen_1.prov_no, od_dwh_pol_gen_1.pri_primaute_pp, od_dwh_pol_gen_1.pri_type, od_dwh_pol_gen_1.pri_freq_paiement, od_dwh_pol_gen_1.pri_frais_fixes, od_dwh_pol_gen_1.pri_soumis_drt_timbre, od_dwh_pol_gen_1.pri_avec_drt_timbre, od_dwh_pol_gen_1.pri_type_pe, od_dwh_pol_gen_1.pri_dte_valo_deces, od_dwh_pol_gen_1.pri_dte_effet, od_dwh_pol_gen_1.pri_dte_anni, od_dwh_pol_gen_1.pri_dte_echeance, od_dwh_pol_gen_1.pri_somme_pp, od_dwh_pol_gen_1.fin_pret_pol, od_dwh_pol_gen_1.fin_dte_pret_pol, od_dwh_pol_gen_1.fin_pret_desc, od_dwh_pol_gen_1.fin_mise_gage, od_dwh_pol_gen_1.fin_type_nant, od_dwh_pol_gen_1.fisc_adapt_opp3, od_dwh_pol_gen_1.fisc_adapt_opp3_desc, od_dwh_pol_gen_1.fisc_mnt_opp3_declare, od_dwh_pol_gen_1.fisc_mnt_opp3_max_sou, od_dwh_pol_gen_1.fisc_rachetable, od_dwh_pol_gen_1.fisc_impot_anticipe, od_dwh_pol_gen_1.fisc_impot_source, od_dwh_pol_gen_1.fisc_impot_desc_libre, od_dwh_pol_gen_1.lba_identite, od_dwh_pol_gen_1.lba_prov, od_dwh_pol_gen_1.type_annonce, od_dwh_pol_gen_1.motif_rachat, od_dwh_pol_gen_1.mnt_rachat_partiel, od_dwh_pol_gen_1.motif_resiliation, od_dwh_pol_gen_1.rte_capital, od_dwh_pol_gen_1.cause_annul, od_dwh_pol_gen_1.delai_attente_libe, od_dwh_pol_gen_1.ajour_dte_fin, od_dwh_pol_gen_1.ajour_annee, od_dwh_pol_gen_1.code_voir_dossier, od_dwh_pol_gen_1.tutelle, od_dwh_pol_gen_1.clause_pilote, od_dwh_pol_gen_1.clause_resident, od_dwh_pol_gen_1.code_gerance, od_dwh_pol_gen_1.no_conseiller, od_dwh_pol_gen_1.pri_primaute_pu, od_dwh_pol_gen_1.pri_somme_pu, od_dwh_pol_gen_1.cc_plan_de_repartition_existe, od_dwh_pol_gen_1.fisc_cga_id, od_dwh_pol_gen_1.fisc_cga_dte_deb, od_dwh_pol_gen_1.accuse_recept_auto, od_dwh_pol_gen_1.ajournement, od_dwh_pol_gen_1.cause_mise_pension_manu, od_dwh_pol_gen_1.dn_incapacite, od_dwh_pol_gen_1.confirm_mise_pension, od_dwh_pol_gen_1.date_naissance_verifiee, od_dwh_pol_gen_1.deces_premiere_tete, od_dwh_pol_gen_1.dte_proch_ajournement, od_dwh_pol_gen_1.dte_proch_boucl, od_dwh_pol_gen_1.dte_proch_prelev, od_dwh_pol_gen_1.dte_proch_rebalanc, od_dwh_pol_gen_1.duree_pe, od_dwh_pol_gen_1.etat_versement, od_dwh_pol_gen_1.frequence_desiree, od_dwh_pol_gen_1.mise_en_pension, od_dwh_pol_gen_1.mise_pension_manu, od_dwh_pol_gen_1.no_encaissement, od_dwh_pol_gen_1.no_fe_vers_add, od_dwh_pol_gen_1.pas_correspondance, od_dwh_pol_gen_1.pas_extrait_compte, od_dwh_pol_gen_1.profil_choisi, od_dwh_pol_gen_1.rebalanc_auto, od_dwh_pol_gen_1.redressement_envoi_prest, od_dwh_pol_gen_1.regroupement, od_dwh_pol_gen_1.stipulation_beneficiaire, od_dwh_pol_gen_1.type_rachat, od_dwh_pol_gen_1.res_id, od_dwh_pol_gen_1.reassurance, od_dwh_pol_gen_1.rachat_cap_reserve, od_dwh_pol_gen_1.versement_immediat_pe, od_dwh_pol_gen_1.fin_dte_nant, od_dwh_pol_gen_1.procuration, od_dwh_pol_gen_1.variante_desiree_id, od_dwh_pol_gen_1.val_transf, od_dwh_pol_gen_1.corr_except_pp, od_dwh_pol_gen_1.corr_except_pu, od_dwh_pol_gen_1.frais_gestion_pp, od_dwh_pol_gen_1.frais_gestion_pu, od_dwh_pol_gen_1.majoration_rente, od_dwh_pol_gen_1.total_vers_annee_ap_mut, od_dwh_pol_gen_1.val_rachat_apres_mut, od_dwh_pol_gen_1.val_dec_apres_mut, od_dwh_pol_gen_1.som_risq_dec_int, od_dwh_pol_gen_1.som_risq_inv_int, od_dwh_pol_gen_1.som_risq_dec_reas, od_dwh_pol_gen_1.som_risq_inv_reas, od_dwh_pol_gen_1.fisc_type, od_dwh_pol_gen_1.taux_redressement, od_dwh_pol_gen_1.base_financement_flexible, od_dwh_pol_gen_1.date_calcul_avoir, od_dwh_pol_gen_1.date_precedent_calcul_avoir, od_dwh_pol_gen_1.type_base_financement_flexible
  • Index Cond: ((od_dwh_pol_gen_1.pol_id = od_dwh_pa_gen_1.pol_id) AND (od_dwh_pol_gen_1.mupo_gen = od_dwh_pa_gen_1.mupo_gen))
  • Filter: ((od_dwh_pol_gen_1.dl_flg_bcl = 1) AND (od_dwh_pa_gen_1.dl_datextract = od_dwh_pol_gen_1.dl_datextract))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=636536 read=117836
  • Worker 0: actual time=0.357..0.406 rows=1 loops=19340
  • Buffers: shared hit=144669 read=26785
  • Worker 1: actual time=0.349..0.394 rows=1 loops=19917
  • Buffers: shared hit=143297 read=26910
  • Worker 2: actual time=0.338..0.382 rows=1 loops=20945
  • Buffers: shared hit=147575 read=27729
  • Worker 3: actual time=0.326..0.371 rows=1 loops=21351
  • Buffers: shared hit=153023 read=27488
77. 2,218.080 2,218.080 ↑ 1.0 1 87,326 / 5

Index Scan using olm_od_dwh_pa_pa_id on bidwh.od_dwh_pa od_dwh_pa_1 (cost=0.56..2.36 rows=1 width=16) (actual time=0.092..0.127 rows=1 loops=87,326)

  • Output: od_dwh_pa_1.dl_datextract, od_dwh_pa_1.dl_soc_id, od_dwh_pa_1.dl_flg_bcl, od_dwh_pa_1.pa_id, od_dwh_pa_1.vco_id
  • Index Cond: (od_dwh_pa_1.pa_id = od_dwh_pa_gen_1.pa_id)
  • Filter: ((od_dwh_pa_1.dl_flg_bcl = 1) AND (od_dwh_pa_gen_1.dl_datextract = od_dwh_pa_1.dl_datextract))
  • Rows Removed by Filter: 63
  • Buffers: shared hit=5941542 read=8035
  • Worker 0: actual time=0.095..0.131 rows=1 loops=19340
  • Buffers: shared hit=1320745 read=2054
  • Worker 1: actual time=0.092..0.127 rows=1 loops=19917
  • Buffers: shared hit=1356083 read=1639
  • Worker 2: actual time=0.094..0.130 rows=1 loops=20945
  • Buffers: shared hit=1421760 read=1885
  • Worker 3: actual time=0.090..0.123 rows=1 loops=21351
  • Buffers: shared hit=1408867 read=1967
78. 7,163.393 7,422.710 ↑ 1.0 1 87,326

Index Scan using olm_od_dwh_mut_pol_id on bidwh.od_dwh_mut_pol od_dwh_mut_pol_1 (cost=0.43..2.98 rows=1 width=60) (actual time=0.082..0.085 rows=1 loops=87,326)

  • Output: od_dwh_mut_pol_1.dte_mut, od_dwh_mut_pol_1.etat, od_dwh_mut_pol_1.mupo_gen, od_dwh_mut_pol_1.dl_datextract, od_dwh_mut_pol_1.dl_flg_bcl, od_dwh_mut_pol_1.pol_id, od_dwh_mut_pol_1.mut_id, (SubPlan 4)
  • Index Cond: ((od_dwh_mut_pol_1.pol_id = od_dwh_pa_gen_1.pol_id) AND (od_dwh_mut_pol_1.mupo_gen = od_dwh_pa_gen_1.mupo_gen))
  • Filter: ((od_dwh_mut_pol_1.dl_flg_bcl = 1) AND ((od_dwh_mut_pol_1.etat)::text = 'VAL'::text) AND (od_dwh_pa_gen_1.dl_datextract = od_dwh_mut_pol_1.dl_datextract))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=958046 read=55638
79.          

SubPlan (for Index Scan)

80. 86.439 259.317 ↑ 1.0 1 86,439

Aggregate (cost=2.50..2.51 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=86,439)

  • Output: max((a_3.nom_mut)::text)
  • Buffers: shared hit=259317
81. 172.878 172.878 ↑ 1.0 1 86,439

Index Scan using qsys_mut_lan_00001 on produit.mut_lan a_3 (cost=0.28..2.50 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=86,439)

  • Output: a_3.mut_id, a_3.lan, a_3.nom_mut, a_3.ged_mut_1, a_3.ged_mut_2
  • Index Cond: ((a_3.mut_id = od_dwh_mut_pol_1.mut_id) AND ((a_3.lan)::text = 'FR'::text))
  • Buffers: shared hit=259317
82. 172.878 172.878 ↑ 1.0 1 86,439

Index Scan using bi_vcouv_pkey on biinfra.bi_vcouv bi_vcouv_1 (cost=0.28..0.30 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=86,439)

  • 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 = od_dwh_pa_1.vco_id)
  • Buffers: shared hit=259249 read=68
83. 259.317 259.317 ↑ 1.0 1 86,439

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=86,439)

  • Output: bi_par_flg_bcl_1.dl_flg_bcl, bi_par_flg_bcl_1.dl_flg_bcl_desc
  • Filter: (bi_par_flg_bcl_1.dl_flg_bcl = 1)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=86439
84. 86.439 86.439 ↑ 1.0 1 86,439

Index Only Scan using olm_bi_par_flg_inv_dl_flg_inv on bidwhpar.bi_par_flg_inv bi_par_flg_inv_1 (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=86,439)

  • Output: bi_par_flg_inv_1.dl_flg_inv
  • Index Cond: (bi_par_flg_inv_1.dl_flg_inv = od_dwh_pol_gen_1.dl_flg_inv)
  • Heap Fetches: 0
  • Buffers: shared hit=86438 read=2
85. 950.829 950.829 ↑ 1.0 1 86,439

Index Scan using olm_ody0122_2 on bidwh.od_dwh_pol od_dwh_pol_1 (cost=0.43..0.46 rows=1 width=22) (actual time=0.011..0.011 rows=1 loops=86,439)

  • Output: od_dwh_pol_1.dl_datextract, od_dwh_pol_1.dl_soc_id, od_dwh_pol_1.dl_flg_bcl, od_dwh_pol_1.pol_id, od_dwh_pol_1.pro_id, od_dwh_pol_1.cc_no_police, od_dwh_pol_1.pol_id_orig, od_dwh_pol_1.pol_id_dest, od_dwh_pol_1.pri_cpt_dep, od_dwh_pol_1.dte_reception_propo, od_dwh_pol_1.dte_signature_propo, od_dwh_pol_1.no_propo_ulysse
  • Index Cond: ((od_dwh_pol_1.pol_id = od_dwh_pa_gen_1.pol_id) AND (od_dwh_pol_1.dl_flg_bcl = 1) AND (od_dwh_pol_1.dl_datextract = od_dwh_pa_gen_1.dl_datextract))
  • Buffers: shared hit=341749 read=4568
86. 86.439 86.439 ↑ 1.0 1 86,439

Index Scan using bi_prod_pkey on biinfra.bi_prod bi_prod_1 (cost=0.14..0.16 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=86,439)

  • 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 = od_dwh_pol_1.pro_id)
  • Buffers: shared hit=172877 read=1
87. 86.439 86.439 ↓ 0.0 0 86,439

Index Scan using bi_fam_prod_pkey on biinfra.bi_fam_prod bi_fam_prod_1 (cost=0.14..0.18 rows=1 width=86) (actual time=0.001..0.001 rows=0 loops=86,439)

  • Output: bi_fam_prod_1.fam_id, bi_fam_prod_1.soc_id, bi_fam_prod_1.fam_desc_courte, bi_fam_prod_1.fam_type_famille, bi_fam_prod_1.fam_app_gest_id, bi_fam_prod_1.fam_dernier_num_police_utilise, bi_fam_prod_1.fal_nom_fam_fr, bi_fam_prod_1.fal_desc_longue_fr, bi_fam_prod_1.apg_nom_app, bi_fam_prod_1.apg_desc_courte, bi_fam_prod_1.apg_type_app
  • Index Cond: (bi_fam_prod_1.fam_id = bi_prod_1.fam_id)
  • Filter: (bi_fam_prod_1.soc_id = 4)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=172877 read=1
88. 2.115 2.115 ↑ 1.0 1 705

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

  • 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 = 4)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=704 read=1
89. 3.525 3.525 ↑ 1.0 1 705

Index Only Scan using bi_dates_pkey on biinfra.bi_dates bi_dates_1 (cost=0.28..0.31 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=705)

  • Output: bi_dates_1.dte_jour
  • Index Cond: (bi_dates_1.dte_jour = od_dwh_pa_gen_1.dl_datextract)
  • Filter: ((bi_dates_1.dte_jour = '2018-12-31'::date) OR (bi_dates_1.dte_jour = '2019-12-31'::date))
  • Rows Removed by Filter: 0
  • Heap Fetches: 705
  • Buffers: shared hit=2115
Planning time : 149.055 ms
Execution time : 13,660,579.458 ms