explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k5If

Settings
# exclusive inclusive rows x rows loops node
1. 334.971 28,812.773 ↓ 60,329.0 60,329 1

GroupAggregate (cost=55,667.98..55,692.04 rows=1 width=996) (actual time=28,467.389..28,812.773 rows=60,329 loops=1)

  • Group Key: soc.soc_id, ((SubPlan 1)), od_dwh_pol_gen.pri_type, prod.pro_id, fam_prod.fam_id, (CASE WHEN ((od_dwh_mut_pol.status)::text = 'PCOU'::text) THEN 'Pensionné'::text ELSE 'Actif'::text END), od_dwh_pol.cc_no_police, od_dwh_pol.pol_id, od_dwh_pol.cc_no_police, od_dwh_pa_gen.pa_id, type_couv.type_nat_type_couv, ((SubPlan 2)), od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu2.dte_nais, ((SubPlan 3)), od_dwh_pa_gen.dte_fin_paiement, couv.desc_courte, couv.ged_definition1, couv.ged_definition2, od_dwh_pol_gen.pri_dte_effet, prod_vie.type_prev, od_dwh_pa_gen.delai_attente, od_dwh_pol_gen.delai_attente_libe, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.flg_pa_disparue
  • Buffers: shared hit=9,517,748 read=2,391,948
  • I/O Timings: read=13,735.324
  • Execution Time: 28,820.315 ms(206 lignes)^
2. 669.252 28,477.802 ↓ 60,329.0 60,329 1

Sort (cost=55,667.98..55,667.98 rows=1 width=472) (actual time=28,467.349..28,477.802 rows=60,329 loops=1)

  • Sort Key: soc.soc_id, ((SubPlan 1)), od_dwh_pol_gen.pri_type, prod.pro_id, fam_prod.fam_id, (CASE WHEN ((od_dwh_mut_pol.status)::text = 'PCOU'::text) THEN 'Pensionné'::text ELSE 'Actif'::text END), od_dwh_pol.cc_no_police, od_dwh_pol.pol_id, od_dwh_pa_gen.pa_id, type_couv.type_nat_type_couv, ((SubPlan 2)), od_dwh_pa_gen.dte_deb, od_dwh_pa_gen.dte_fin, a1_od_dwh_part_assu1.sexe, a1_od_dwh_part_assu2.sexe, a1_od_dwh_part_assu1.dte_nais, a1_od_dwh_part_assu2.dte_nais, ((SubPlan 3)), od_dwh_pa_gen.dte_fin_paiement, couv.desc_courte, couv.ged_definition1, couv.ged_definition2, od_dwh_pol_gen.pri_dte_effet, prod_vie.type_prev, od_dwh_pa_gen.delai_attente, od_dwh_pol_gen.delai_attente_libe, od_dwh_pa_gen.princ_compl, od_dwh_pa_gen.flg_pa_disparue
  • Sort Method: quicksort Memory: 32,643kB
  • Buffers: shared hit=9,517,748 read=2,391,948
  • I/O Timings: read=13,735.324
3. 247.740 27,808.550 ↓ 60,329.0 60,329 1

Nested Loop (cost=32,554.39..55,667.97 rows=1 width=472) (actual time=51.269..27,808.550 rows=60,329 loops=1)

  • Buffers: shared hit=9,517,748 read=2,391,948
  • I/O Timings: read=13,735.324
4. 80.495 26,776.533 ↓ 60,329.0 60,329 1

Nested Loop (cost=32,554.11..55,639.80 rows=1 width=262) (actual time=51.220..26,776.533 rows=60,329 loops=1)

  • Buffers: shared hit=8,908,381 read=2,391,920
  • I/O Timings: read=13,735.181
5. 129.669 26,390.623 ↓ 61,083.0 61,083 1

Nested Loop (cost=32,553.68..55,638.67 rows=1 width=272) (actual time=51.202..26,390.623 rows=61,083 loops=1)

  • Buffers: shared hit=8,665,062 read=2,391,447
  • I/O Timings: read=13,732.409
6. 88.387 16,304.425 ↓ 61,083.0 61,083 1

Nested Loop Left Join (cost=32,553.24..55,629.32 rows=1 width=254) (actual time=50.977..16,304.425 rows=61,083 loops=1)

  • Buffers: shared hit=7,516,086 read=1,229,170
  • I/O Timings: read=8,466.736
7. 105.864 16,216.038 ↓ 61,083.0 61,083 1

Nested Loop (cost=32,552.81..55,628.19 rows=1 width=252) (actual time=50.969..16,216.038 rows=61,083 loops=1)

  • Buffers: shared hit=7,514,231 read=1,229,165
  • I/O Timings: read=8,466.693
8. 7,547.172 16,049.091 ↓ 61,083.0 61,083 1

Gather (cost=32,552.81..55,627.13 rows=1 width=256) (actual time=50.960..16,049.091 rows=61,083 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=7,453,148 read=1,229,165
  • I/O Timings: read=8,466.693
9. 22.146 8,501.919 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,552.81..54,627.03 rows=1 width=256) (actual time=25.890..8,501.919 rows=20,361 loops=3)

  • Buffers: shared hit=7,453,148 read=1,229,165
  • I/O Timings: read=8,466.693
10. 36.382 8,459.412 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,552.67..54,626.86 rows=1 width=256) (actual time=25.848..8,459.412 rows=20,361 loops=3)

  • Buffers: shared hit=7,392,061 read=1,229,164
  • I/O Timings: read=8,466.677
11. 27.511 8,382.308 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,552.53..54,626.69 rows=1 width=237) (actual time=25.785..8,382.308 rows=20,361 loops=3)

  • Buffers: shared hit=7,269,894 read=1,229,163
  • I/O Timings: read=8,466.663
12. 36.721 8,314.075 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,552.25..54,626.37 rows=1 width=167) (actual time=25.743..8,314.075 rows=20,361 loops=3)

  • Buffers: shared hit=7,086,659 read=1,229,147
  • I/O Timings: read=8,466.536
13. 12.738 8,216.271 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,551.97..54,626.07 rows=1 width=163) (actual time=25.681..8,216.271 rows=20,361 loops=3)

  • Buffers: shared hit=6,903,415 read=1,229,137
  • I/O Timings: read=8,466.398
14. 33.465 5,780.574 ↓ 20,361.0 20,361 3 / 3

Nested Loop (cost=31,551.41..54,619.84 rows=1 width=155) (actual time=23.956..5,780.574 rows=20,361 loops=3)

  • Join Filter: ((od_dwh_pol.pol_id = od_dwh_pa_gen.pol_id) AND (od_dwh_mut_pol.mupo_gen = od_dwh_pa_gen.mupo_gen))
  • Buffers: shared hit=3,068,128 read=1,206,930
  • I/O Timings: read=7,432.336
15. 11.196 2,236.745 ↓ 4,091.5 8,183 3 / 3

Nested Loop (cost=31,550.84..54,148.23 rows=2 width=70) (actual time=23.271..2,236.745 rows=8,183 loops=3)

  • Buffers: shared hit=1,952,027 read=369,183
  • I/O Timings: read=2,057.134
16. 9.569 2,201.001 ↓ 4,091.5 8,183 3 / 3

Hash Left Join (cost=31,550.84..54,145.79 rows=2 width=70) (actual time=23.255..2,201.001 rows=8,183 loops=3)

  • Hash Cond: (fam_prod.soc_id = soc.soc_id)
  • Buffers: shared hit=1,927,479 read=369,183
  • I/O Timings: read=2,057.134
17. 14.199 2,191.412 ↓ 4,091.5 8,183 3 / 3

Nested Loop (cost=31,549.69..54,144.63 rows=2 width=70) (actual time=23.218..2,191.412 rows=8,183 loops=3)

  • Join Filter: (prod.pro_id = prod_vie.pro_id)
  • Buffers: shared hit=1,927,476 read=369,183
  • I/O Timings: read=2,057.134
18. 31.039 2,160.848 ↓ 4,091.5 8,183 3 / 3

Nested Loop (cost=31,549.54..54,144.28 rows=2 width=69) (actual time=23.183..2,160.848 rows=8,183 loops=3)

  • Join Filter: (od_dwh_pol.pol_id = od_dwh_pol_gen.pol_id)
  • Buffers: shared hit=1,878,382 read=369,179
  • I/O Timings: read=2,057.082
19. 12.063 949.585 ↓ 177.7 21,856 3 / 3

Nested Loop (cost=31,549.11..53,273.38 rows=123 width=46) (actual time=22.841..949.585 rows=21,856 loops=3)

  • Buffers: shared hit=1,386,129 read=31,426
  • I/O Timings: read=255.704
20. 14.183 45.502 ↓ 12.3 8,184 3 / 3

Merge Join (cost=31,548.68..31,585.18 rows=664 width=34) (actual time=22.497..45.502 rows=8,184 loops=3)

  • Merge Cond: (od_dwh_pol.pro_id = prod.pro_id)
  • Buffers: shared hit=109 read=1,892
  • I/O Timings: read=9.972
21. 11.132 27.767 ↓ 4.8 25,328 3 / 3

Sort (cost=31,541.73..31,555.00 rows=5,310 width=22) (actual time=22.119..27.767 rows=25,328 loops=3)

  • Sort Key: od_dwh_pol.pro_id
  • Sort Method: quicksort Memory: 6,268kB
  • Worker 0: Sort Method: quicksort Memory: 662kB
  • Worker 1: Sort Method: quicksort Memory: 1,120kB
  • Buffers: shared hit=10 read=1,891
  • I/O Timings: read=9.963
22. 11.976 16.635 ↓ 4.8 25,328 3 / 3

Parallel Bitmap Heap Scan on od_dwh_pol (cost=3,742.35..31,213.19 rows=5,310 width=22) (actual time=4.820..16.635 rows=25,328 loops=3)

  • Recheck Cond: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1))
  • Heap Blocks: exact=1,050
  • Buffers: shared hit=2 read=1,891
  • I/O Timings: read=9.963
23. 4.659 4.659 ↓ 6.0 75,985 1 / 3

Bitmap Index Scan on od_dwh_pol_pkey (cost=0.00..3,739.16 rows=12,744 width=0) (actual time=13.976..13.976 rows=75,985 loops=1)

  • Index Cond: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1))
  • Buffers: shared read=576
  • I/O Timings: read=3.068
24. 3.200 3.552 ↓ 455.4 8,197 3 / 3

Sort (cost=6.95..7.00 rows=18 width=12) (actual time=0.368..3.552 rows=8,197 loops=3)

  • Sort Key: prod.pro_id
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=99 read=1
  • I/O Timings: read=0.009
25. 0.266 0.352 ↓ 2.3 41 3 / 3

Hash Join (cost=1.71..6.57 rows=18 width=12) (actual time=0.297..0.352 rows=41 loops=3)

  • Hash Cond: (prod.fam_id = fam_prod.fam_id)
  • Buffers: shared hit=99 read=1
  • I/O Timings: read=0.009
26. 0.047 0.047 ↑ 1.0 144 3 / 3

Seq Scan on prod (cost=0.00..4.44 rows=144 width=8) (actual time=0.023..0.047 rows=144 loops=3)

  • Buffers: shared hit=9
27. 0.005 0.039 ↑ 1.0 5 3 / 3

Hash (cost=1.65..1.65 rows=5 width=8) (actual time=0.039..0.039 rows=5 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.009
28. 0.034 0.034 ↑ 1.0 5 3 / 3

Seq Scan on fam_prod (cost=0.00..1.65 rows=5 width=8) (actual time=0.028..0.034 rows=5 loops=3)

  • Filter: (fam_id = ANY ('{2,3,7,8,39}'::integer[]))
  • Rows Removed by Filter: 35
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.009
29. 892.020 892.020 ↓ 3.0 3 24,551 / 3

Index Scan using olm_od_dwh_mut_pol_pol_id on od_dwh_mut_pol (cost=0.43..32.65 rows=1 width=20) (actual time=0.069..0.109 rows=3 loops=24,551)

  • Index Cond: (pol_id = od_dwh_pol.pol_id)
  • Filter: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1) AND (peg_id = 18))
  • Rows Removed by Filter: 67
  • Buffers: shared hit=1,386,020 read=29,534
  • I/O Timings: read=245.732
30. 1,180.224 1,180.224 ↓ 0.0 0 65,568 / 3

Index Scan using olm_od_dwh_pa_gen_pol_id_mupo_gen on od_dwh_pol_gen (cost=0.43..7.07 rows=1 width=31) (actual time=0.051..0.054 rows=0 loops=65,568)

  • Index Cond: ((pol_id = od_dwh_mut_pol.pol_id) AND (mupo_gen = od_dwh_mut_pol.mupo_gen))
  • Filter: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_inv = 1) AND (dl_flg_bcl = 1))
  • Rows Removed by Filter: 9
  • Buffers: shared hit=492,253 read=337,753
  • I/O Timings: read=1,801.378
31. 16.365 16.365 ↑ 1.0 1 24,548 / 3

Index Scan using p_prv on prod_vie (cost=0.14..0.16 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=24,548)

  • Index Cond: (pro_id = od_dwh_pol.pro_id)
  • Buffers: shared hit=49,094 read=4
  • I/O Timings: read=0.052
32. 0.006 0.020 ↑ 1.0 7 3 / 3

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.019..0.020 rows=7 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
33. 0.014 0.014 ↑ 1.0 7 3 / 3

Seq Scan on soc (cost=0.00..1.07 rows=7 width=4) (actual time=0.013..0.014 rows=7 loops=3)

  • Buffers: shared hit=3
34. 24.548 24.548 ↑ 1.0 1 24,548 / 3

Seq Scan on bi_par_flg_bcl (cost=0.00..1.21 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=24,548)

  • Filter: (dl_flg_bcl = 1)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=24,548
35. 3,510.364 3,510.364 ↓ 2.0 2 24,548 / 3

Index Scan using olm_od_dwh_pa_gen_pol_id on od_dwh_pa_gen (cost=0.56..235.79 rows=1 width=113) (actual time=0.285..0.429 rows=2 loops=24,548)

  • Index Cond: (pol_id = od_dwh_pol_gen.pol_id)
  • Filter: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1) AND (od_dwh_pol_gen.mupo_gen = mupo_gen))
  • Rows Removed by Filter: 172
  • Buffers: shared hit=1,116,101 read=837,747
  • I/O Timings: read=5,375.202
36. 2,422.959 2,422.959 ↑ 1.0 1 61,083 / 3

Index Scan using olm_od_dwh_pa_pa_id on od_dwh_pa (cost=0.56..6.21 rows=1 width=16) (actual time=0.092..0.119 rows=1 loops=61,083)

  • Index Cond: (pa_id = od_dwh_pa_gen.pa_id)
  • Filter: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1))
  • Rows Removed by Filter: 58
  • Buffers: shared hit=3,835,287 read=22,207
  • I/O Timings: read=1,034.062
37. 61.083 61.083 ↑ 1.0 1 61,083 / 3

Index Scan using p_vco on vcouv (cost=0.28..0.30 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=61,083)

  • Index Cond: (vco_id = od_dwh_pa.vco_id)
  • Buffers: shared hit=183,244 read=10
  • I/O Timings: read=0.138
38. 40.722 40.722 ↑ 1.0 1 61,083 / 3

Index Scan using p_cou on couv (cost=0.28..0.32 rows=1 width=74) (actual time=0.002..0.002 rows=1 loops=61,083)

  • Index Cond: (cou_id = vcouv.cou_id)
  • Buffers: shared hit=183,235 read=16
  • I/O Timings: read=0.127
39. 40.722 40.722 ↑ 1.0 1 61,083 / 3

Index Scan using p_tyc on type_couv (cost=0.14..0.17 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=61,083)

  • Index Cond: ((type_nat_type_couv)::text = (couv.type_nat_type_couv)::text)
  • Buffers: shared hit=122,167 read=1
  • I/O Timings: read=0.013
40. 20.361 20.361 ↑ 1.0 1 61,083 / 3

Index Only Scan using p_cta on couche_tarifaire (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=61,083)

  • Index Cond: (cta_id = vcouv.cta_id)
  • Heap Fetches: 0
  • Buffers: shared hit=61,087 read=1
  • I/O Timings: read=0.017
41. 61.083 61.083 ↑ 1.0 1 61,083

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

  • Filter: (dl_flg_inv = 1)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=61,083
42. 0.000 0.000 ↓ 0.0 0 61,083

Index Scan using od_dwh_part_pkey on od_dwh_part a1_od_dwh_part_assu2 (cost=0.43..1.13 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=61,083)

  • Index Cond: ((od_dwh_pa_gen.dl_datextract = dl_datextract) AND (dl_datextract = '2018-12-31'::date) AND (od_dwh_pa_gen.dl_soc_id = dl_soc_id) AND (od_dwh_pa_gen.dl_flg_bcl = dl_flg_bcl) AND (dl_flg_bcl = 1) AND (od_dwh_pa_gen.part_assure2 = part_id))
  • Buffers: shared hit=1,855 read=5
  • I/O Timings: read=0.042
43. 9,956.529 9,956.529 ↑ 1.0 1 61,083

Index Scan using olm_od_dwh_fct_pa_gen_pa_id on od_dwh_fct_pa_gen (cost=0.44..9.34 rows=1 width=50) (actual time=0.141..0.163 rows=1 loops=61,083)

  • Index Cond: (pa_id = od_dwh_pa_gen.pa_id)
  • Filter: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1) AND (od_dwh_pa_gen.dl_flg_inv = dl_flg_inv) AND (od_dwh_pa_gen.pa_gen = pa_gen))
  • Rows Removed by Filter: 34
  • Buffers: shared hit=1,148,976 read=1,162,277
  • I/O Timings: read=5,265.674
44. 305.415 305.415 ↑ 1.0 1 61,083

Index Scan using od_dwh_part_pkey on od_dwh_part a1_od_dwh_part_assu1 (cost=0.43..1.13 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=61,083)

  • Index Cond: ((dl_datextract = '2018-12-31'::date) AND (dl_soc_id = od_dwh_pa_gen.dl_soc_id) AND (dl_flg_bcl = 1) AND (part_id = od_dwh_pa_gen.part_assure1))
  • Buffers: shared hit=243,319 read=473
  • I/O Timings: read=2.771
45. 120.658 120.658 ↑ 1.0 1 60,329

Index Only Scan using bi_bi_dates_dte_jour on bi_dates (cost=0.28..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=60,329)

  • Index Cond: (dte_jour = '2018-12-31'::date)
  • Heap Fetches: 0
  • Buffers: shared hit=120,659
46.          

SubPlan (for Nested Loop)

47. 301.645 301.645 ↑ 1.0 1 60,329

Index Scan using p_cou_lan on couv_lan a (cost=0.28..8.31 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=60,329)

  • Index Cond: ((cou_id = couv.cou_id) AND ((lan)::text = 'FR'::text))
  • Buffers: shared hit=180,961 read=26
  • I/O Timings: read=0.136
48. 180.987 241.316 ↑ 1.0 1 60,329

Bitmap Heap Scan on couche_tarifaire_lan a_1 (cost=4.18..7.24 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=60,329)

  • Recheck Cond: (cta_id = couche_tarifaire.cta_id)
  • Filter: ((lan)::text = 'FR'::text)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=66,433
  • Buffers: shared hit=126,760 read=2
  • I/O Timings: read=0.007
49. 60.329 60.329 ↑ 1.0 4 60,329

Bitmap Index Scan on idx_couche_tarifaire_lan_cta_id (cost=0.00..4.18 rows=4 width=0) (actual time=0.001..0.001 rows=4 loops=60,329)

  • Index Cond: (cta_id = couche_tarifaire.cta_id)
  • Buffers: shared hit=60,329
50. 120.658 120.658 ↑ 1.0 1 60,329

Index Scan using p_cou_lan on couv_lan a_2 (cost=0.28..8.30 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=60,329)

  • Index Cond: ((cou_id = couv.cou_id) AND ((lan)::text = 'FR'::text))
  • Buffers: shared hit=180,987
Planning time : 20.201 ms