explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s1RF

Settings
# exclusive inclusive rows x rows loops node
1. 159.344 26,651,033.107 ↓ 73,233.0 73,233 1

GroupAggregate (cost=21.48..21.51 rows=1 width=52) (actual time=26,650,824.547..26,651,033.107 rows=73,233 loops=1)

  • Output: prod.fam_id, od_dwh_pa_gen.dl_flg_inv, od_dwh_pa_gen.dl_flg_bcl, sum(od_dwh_pa_gen.rev_invent_apres_mut), od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id
  • Group Key: prod.fam_id, od_dwh_pa_gen.dl_flg_inv, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id
  • Buffers: shared hit=5572860181 read=29502
  • I/O Timings: read=125177.397
2. 16,662.198 26,650,873.763 ↓ 500,184.0 500,184 1

Sort (cost=21.48..21.48 rows=1 width=26) (actual time=26,650,824.506..26,650,873.763 rows=500,184 loops=1)

  • Output: prod.fam_id, od_dwh_pa_gen.dl_flg_inv, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, od_dwh_pa_gen.rev_invent_apres_mut
  • Sort Key: prod.fam_id, od_dwh_pa_gen.pol_id
  • Sort Method: quicksort Memory: 51365kB
  • Buffers: shared hit=5572860181 read=29502
  • I/O Timings: read=125177.397
3. 3,167,863.843 26,634,211.565 ↓ 500,184.0 500,184 1

Nested Loop (cost=9.03..21.47 rows=1 width=26) (actual time=53,062.228..26,634,211.565 rows=500,184 loops=1)

  • Output: prod.fam_id, od_dwh_pa_gen.dl_flg_inv, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.pol_id, od_dwh_pa_gen.rev_invent_apres_mut
  • Join Filter: (od_dwh_pol.pol_id = od_dwh_pa_gen.pol_id)
  • Rows Removed by Join Filter: 36812923353
  • Buffers: shared hit=5572860178 read=29502
  • I/O Timings: read=125177.397
4. 203.605 17,946.685 ↓ 73,233.0 73,233 1

Hash Join (cost=8.46..13.61 rows=1 width=16) (actual time=17,741.016..17,946.685 rows=73,233 loops=1)

  • Output: od_dwh_pol.pol_id, od_dwh_pol.dl_datextract, od_dwh_pol.dl_flg_bcl, prod.fam_id
  • Hash Cond: (prod.pro_id = od_dwh_pol.pro_id)
  • Buffers: shared hit=2710 read=2136
  • I/O Timings: read=15450.396
5. 16.837 16.837 ↑ 1.0 41 1

Seq Scan on produit.prod (cost=0.00..4.98 rows=41 width=8) (actual time=14.741..16.837 rows=41 loops=1)

  • Output: prod.pro_id, prod.fam_id, prod.dte_deb_app, prod.dte_fin_app, prod.dn_soc_id, prod.desc_courte, prod.usr_log_i, prod.dte_log_i, prod.usr_log_u, prod.dte_log_u, prod.default_nbr_bvr, prod.ged_definition1, prod.ged_definition2, prod.dernier_num_police_utilise, prod.type_prod, prod.ged_type_caisse
  • Filter: (prod.fam_id = ANY ('{2,6,39}'::integer[]))
  • Rows Removed by Filter: 103
  • Buffers: shared read=3
  • I/O Timings: read=16.015
6. 24.195 17,726.243 ↓ 74,324.0 74,324 1

Hash (cost=8.45..8.45 rows=1 width=16) (actual time=17,726.242..17,726.243 rows=74,324 loops=1)

  • Output: od_dwh_pol.pol_id, od_dwh_pol.dl_datextract, od_dwh_pol.dl_flg_bcl, od_dwh_pol.pro_id
  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4508kB
  • Buffers: shared hit=2710 read=2133
  • I/O Timings: read=15434.381
7. 17,702.048 17,702.048 ↓ 74,324.0 74,324 1

Index Scan using olm_od_dwh_pol_dl_datextract on bidwh.od_dwh_pol (cost=0.43..8.45 rows=1 width=16) (actual time=15.756..17,702.048 rows=74,324 loops=1)

  • Output: od_dwh_pol.pol_id, od_dwh_pol.dl_datextract, od_dwh_pol.dl_flg_bcl, od_dwh_pol.pro_id
  • Index Cond: (od_dwh_pol.dl_datextract = '2019-09-30'::date)
  • Filter: (od_dwh_pol.dl_flg_bcl = 10)
  • Rows Removed by Filter: 150361
  • Buffers: shared hit=2710 read=2133
  • I/O Timings: read=15434.381
8. 23,448,401.037 23,448,401.037 ↓ 502,689.0 502,689 73,233

Index Scan using olm_od_dwh_pa_gen_dl_datextract on bidwh.od_dwh_pa_gen (cost=0.56..7.84 rows=1 width=22) (actual time=0.017..320.189 rows=502,689 loops=73,233)

  • Output: od_dwh_pa_gen.dl_datextract, od_dwh_pa_gen.dl_soc_id, od_dwh_pa_gen.dl_flg_bcl, od_dwh_pa_gen.dl_flg_inv, od_dwh_pa_gen.pa_id, od_dwh_pa_gen.pa_gen, od_dwh_pa_gen.pol_id, od_dwh_pa_gen.mupo_gen, od_dwh_pa_gen.etat, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin, od_dwh_pa_gen.dte_fin_paiement, od_dwh_pa_gen.mnt_tx_reass, od_dwh_pa_gen.mode_capital, od_dwh_pa_gen.type_pri, od_dwh_pa_gen.mnt_prest, od_dwh_pa_gen.type_prest, od_dwh_pa_gen.dte_fin_prest, od_dwh_pa_gen.dte_fin_differe, od_dwh_pa_gen.dte_base_calc, od_dwh_pa_gen.age_ass1, od_dwh_pa_gen.part_assure1, od_dwh_pa_gen.age_ass2, od_dwh_pa_gen.part_assure2, od_dwh_pa_gen.tx_ass1_revers, od_dwh_pa_gen.tx_ass2_revers, od_dwh_pa_gen.tx_ass1_prob_r_aggrave, od_dwh_pa_gen.tx_ass2_prob_r_aggrave, od_dwh_pa_gen.delai_attente, od_dwh_pa_gen.dte_effet_pe, od_dwh_pa_gen.duree_decroissance, od_dwh_pa_gen.pal_pa_id, od_dwh_pa_gen.pal_pa_gen, od_dwh_pa_gen.pal2_pa_id, od_dwh_pa_gen.pal2_pa_gen, od_dwh_pa_gen.rte_freq_paiement, od_dwh_pa_gen.rte_sub_pa_id, od_dwh_pa_gen.rte_sub_pa_gen, od_dwh_pa_gen.sub_pa_id, od_dwh_pa_gen.sub_pa_generation, od_dwh_pa_gen.vpv_id, od_dwh_pa_gen.cc_vpv_id, od_dwh_pa_gen.tpde_classe_risque, od_dwh_pa_gen.cc_vco_boucl_id, od_dwh_pa_gen.rev_pure_apres_mut, od_dwh_pa_gen.rev_invent_apres_mut, od_dwh_pa_gen.rev_com_apres_mut, od_dwh_pa_gen.som_risq_dec_int_apres_mut, od_dwh_pa_gen.som_risq_inv_int_apres_mut, od_dwh_pa_gen.som_risq_dec_reas_apres_mut, od_dwh_pa_gen.som_risq_inv_reas_apres_mut, od_dwh_pa_gen.frais_dispatche, od_dwh_pa_gen.calc_pri_tot_proratee, od_dwh_pa_gen.val_rac_apres_mut, od_dwh_pa_gen.val_dec_apres_mut, od_dwh_pa_gen.val_trans, od_dwh_pa_gen.val_age_effet_1t, od_dwh_pa_gen.val_age_effet_2t, od_dwh_pa_gen.val_age_effet_moy, od_dwh_pa_gen.val_age_eche_1t, od_dwh_pa_gen.val_age_eche_2t, od_dwh_pa_gen.val_age_eche_moy, od_dwh_pa_gen.calc_ass1_surpri, od_dwh_pa_gen.calc_ass2_surpri, od_dwh_pa_gen.calcul_rabais_somm, od_dwh_pa_gen.calc_surpri_special, od_dwh_pa_gen.transfert_opp3, od_dwh_pa_gen.rte, od_dwh_pa_gen.calc_frais_acqu_activ_ap, od_dwh_pa_gen.calc_report_prime_pure_ap, od_dwh_pa_gen.calc_report_prime_invent_ap, od_dwh_pa_gen.calc_report_prime_commer_ap, od_dwh_pa_gen.calc_pri_invent, od_dwh_pa_gen.calc_pri_commer, od_dwh_pa_gen.calc_pri_pure, od_dwh_pa_gen.calc_corr_except, od_dwh_pa_gen.calc_corr_except_out, od_dwh_pa_gen.calc_pri_tot_an, od_dwh_pa_gen.val_inv_apres_mut, od_dwh_pa_gen.val_vie_apres_mut, od_dwh_pa_gen.val_rachat_fiscal_avant_mut, od_dwh_pa_gen.val_rachat_fiscal_apres_mut, od_dwh_pa_gen.rev_boucl_pure_apres_mut, od_dwh_pa_gen.rev_boucl_com_apres_mut, od_dwh_pa_gen.rev_boucl_invent_apres_mut, od_dwh_pa_gen.flg_pa_disparue, od_dwh_pa_gen.profession_swiss_re_id, od_dwh_pa_gen.cc_profession_swiss_re, od_dwh_pa_gen.classe_risque_profession, od_dwh_pa_gen.cca_id, od_dwh_pa_gen.capital_deces_minimum, od_dwh_pa_gen.calcul_report_prestation_apres_mut, od_dwh_pa_gen.interet_deces_garanti
  • Index Cond: (od_dwh_pa_gen.dl_datextract = '2019-09-30'::date)
  • Filter: ((od_dwh_pa_gen.dl_flg_bcl = 10) AND (od_dwh_pa_gen.dl_flg_inv = 1))
  • Rows Removed by Filter: 1022233
  • Buffers: shared hit=5572857468 read=27366
  • I/O Timings: read=109727.001
Planning time : 551.944 ms
Execution time : 26,651,045.133 ms