explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AunA

Settings
# exclusive inclusive rows x rows loops node
1. 342.442 36,309.538 ↓ 60,329.0 60,329 1

GroupAggregate (cost=55,667.98..55,692.04 rows=1 width=996) (actual time=35,955.415..36,309.538 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,262,402 read=2,647,234 written=310
  • Execution Time: 36,317.438 ms(163 lignes)
2. 696.040 35,967.096 ↓ 60,329.0 60,329 1

Sort (cost=55,667.98..55,667.98 rows=1 width=472) (actual time=35,955.360..35,967.096 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,262,402 read=2,647,234 written=310
3. 229.749 35,271.056 ↓ 60,329.0 60,329 1

Nested Loop (cost=32,554.39..55,667.97 rows=1 width=472) (actual time=94.143..35,271.056 rows=60,329 loops=1)

  • Buffers: shared hit=9,262,399 read=2,647,234 written=310
4. 81.600 34,196.701 ↓ 60,329.0 60,329 1

Nested Loop (cost=32,554.11..55,639.80 rows=1 width=262) (actual time=93.335..34,196.701 rows=60,329 loops=1)

  • Buffers: shared hit=8,653,032 read=2,647,206 written=310
5. 100.782 33,626.437 ↓ 61,083.0 61,083 1

Nested Loop (cost=32,553.68..55,638.67 rows=1 width=272) (actual time=89.529..33,626.437 rows=61,083 loops=1)

  • Buffers: shared hit=8,411,608 read=2,644,838 written=310
6. 90.847 19,659.814 ↓ 61,083.0 61,083 1

Nested Loop Left Join (cost=32,553.24..55,629.32 rows=1 width=254) (actual time=89.300..19,659.814 rows=61,083 loops=1)

  • Buffers: shared hit=7,246,960 read=1,498,233 written=163
7. 124.576 19,568.967 ↓ 61,083.0 61,083 1

Nested Loop (cost=32,552.81..55,628.19 rows=1 width=252) (actual time=89.293..19,568.967 rows=61,083 loops=1)

  • Buffers: shared hit=7,245,120 read=1,498,213 written=163
8. 752.371 19,383.308 ↓ 61,083.0 61,083 1

Gather (cost=32,552.81..55,627.13 rows=1 width=256) (actual time=89.283..19,383.308 rows=61,083 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
  • Buffers: shared hit=7,184,037 read=1,498,213 written=163
9. 38.763 18,630.937 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,552.81..54,627.03 rows=1 width=256) (actual time=80.078..18,630.937 rows=30,542 loops=2)

  • Buffers: shared hit=7,184,037 read=1,498,213 written=163
10. 33.034 18,561.633 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,552.67..54,626.86 rows=1 width=256) (actual time=80.052..18,561.633 rows=30,542 loops=2)

  • Buffers: shared hit=7,122,951 read=1,498,213 written=163
11. 49.225 18,436.975 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,552.53..54,626.69 rows=1 width=237) (actual time=80.018..18,436.975 rows=30,542 loops=2)

  • Buffers: shared hit=7,000,784 read=1,498,213 written=163
12. 52.542 18,326.667 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,552.25..54,626.37 rows=1 width=167) (actual time=79.987..18,326.667 rows=30,542 loops=2)

  • Buffers: shared hit=6,817,543 read=1,498,204 written=163
13. 48.789 18,182.501 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,551.97..54,626.07 rows=1 width=163) (actual time=79.938..18,182.501 rows=30,542 loops=2)

  • Buffers: shared hit=6,634,297 read=1,498,197 written=163
14. 61.318 14,590.898 ↓ 30,542.0 30,542 2 / 2

Nested Loop (cost=31,551.41..54,619.84 rows=1 width=155) (actual time=79.242..14,590.898 rows=30,542 loops=2)

  • 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=2,800,095 read=1,474,906 written=160
15. 24.101 6,207.808 ↓ 6,137.0 12,274 2 / 2

Nested Loop (cost=31,550.84..54,148.23 rows=2 width=70) (actual time=77.503..6,207.808 rows=12,274 loops=2)

  • Buffers: shared hit=1,858,019 read=463,135 written=55
16. 17.765 6,146.885 ↓ 6,137.0 12,274 2 / 2

Hash Left Join (cost=31,550.84..54,145.79 rows=2 width=70) (actual time=77.487..6,146.885 rows=12,274 loops=2)

  • Hash Cond: (fam_prod.soc_id = soc.soc_id)
  • Buffers: shared hit=1,833,471 read=463,135 written=55
17. 25.111 6,128.867 ↓ 6,137.0 12,274 2 / 2

Nested Loop (cost=31,549.69..54,144.63 rows=2 width=70) (actual time=77.216..6,128.867 rows=12,274 loops=2)

  • Join Filter: (prod.pro_id = prod_vie.pro_id)
  • Buffers: shared hit=1,833,470 read=463,134 written=55
18. 41.298 6,079.208 ↓ 6,137.0 12,274 2 / 2

Nested Loop (cost=31,549.54..54,144.28 rows=2 width=69) (actual time=77.005..6,079.208 rows=12,274 loops=2)

  • Join Filter: (od_dwh_pol.pol_id = od_dwh_pol_gen.pol_id)
  • Buffers: shared hit=1,784,375 read=463,132 written=55
19. 23.480 2,890.646 ↓ 266.5 32,784 2 / 2

Nested Loop (cost=31,549.11..53,273.38 rows=123 width=46) (actual time=76.089..2,890.646 rows=32,784 loops=2)

  • Buffers: shared hit=1,356,255 read=61,247 written=6
20. 25.149 92.903 ↓ 18.5 12,276 2 / 2

Merge Join (cost=31,548.68..31,585.18 rows=664 width=34) (actual time=53.824..92.903 rows=12,276 loops=2)

  • Merge Cond: (od_dwh_pol.pro_id = prod.pro_id)
  • Buffers: shared hit=57 read=1,892
21. 18.441 61.805 ↓ 7.2 37,992 2 / 2

Sort (cost=31,541.73..31,555.00 rows=5,310 width=22) (actual time=53.177..61.805 rows=37,992 loops=2)

  • Sort Key: od_dwh_pol.pro_id
  • Sort Method: quicksort Memory: 5,103kB
  • Worker 0: Sort Method: quicksort Memory: 3,138kB
  • Buffers: shared hit=6 read=1,891
22. 35.056 43.364 ↓ 7.2 37,992 2 / 2

Parallel Bitmap Heap Scan on od_dwh_pol (cost=3,742.35..31,213.19 rows=5,310 width=22) (actual time=12.202..43.364 rows=37,992 loops=2)

  • Recheck Cond: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1))
  • Heap Blocks: exact=791
  • Buffers: shared hit=2 read=1,891
23. 8.308 8.308 ↓ 6.0 75,985 1 / 2

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

  • Index Cond: ((dl_datextract = '2018-12-31'::date) AND (dl_flg_bcl = 1))
  • Buffers: shared read=576
24. 5.325 5.949 ↓ 682.6 12,287 2 / 2

Sort (cost=6.95..7.00 rows=18 width=12) (actual time=0.639..5.949 rows=12,287 loops=2)

  • Sort Key: prod.pro_id
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=51 read=1
25. 0.169 0.624 ↓ 2.3 41 2 / 2

Hash Join (cost=1.71..6.57 rows=18 width=12) (actual time=0.575..0.624 rows=41 loops=2)

  • Hash Cond: (prod.fam_id = fam_prod.fam_id)
  • Buffers: shared hit=51 read=1
26. 0.035 0.035 ↑ 1.0 144 2 / 2

Seq Scan on prod (cost=0.00..4.44 rows=144 width=8) (actual time=0.015..0.035 rows=144 loops=2)

  • Buffers: shared hit=6
27. 0.004 0.420 ↑ 1.0 5 2 / 2

Hash (cost=1.65..1.65 rows=5 width=8) (actual time=0.420..0.420 rows=5 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1 read=1
28. 0.416 0.416 ↑ 1.0 5 2 / 2

Seq Scan on fam_prod (cost=0.00..1.65 rows=5 width=8) (actual time=0.412..0.416 rows=5 loops=2)

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

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.147..0.226 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,356,198 read=59,355 written=6
30. 3,147.264 3,147.264 ↓ 0.0 0 65,568 / 2

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.089..0.096 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=428,120 read=401,885 written=49
31. 24.548 24.548 ↑ 1.0 1 24,548 / 2

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,095 read=2
32. 0.004 0.253 ↑ 1.0 7 2 / 2

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.253..0.253 rows=7 loops=2)

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

Seq Scan on soc (cost=0.00..1.07 rows=7 width=4) (actual time=0.248..0.249 rows=7 loops=2)

  • Buffers: shared hit=1 read=1
34. 36.822 36.822 ↑ 1.0 1 24,548 / 2

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. 8,321.772 8,321.772 ↓ 2.0 2 24,548 / 2

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.433..0.678 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=942,076 read=1,011,771 written=105
36. 3,542.814 3,542.814 ↑ 1.0 1 61,083 / 2

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.090..0.116 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,834,202 read=23,291 written=3
37. 91.624 91.624 ↑ 1.0 1 61,083 / 2

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,246 read=7
38. 61.083 61.083 ↑ 1.0 1 61,083 / 2

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,241 read=9
39. 91.624 91.624 ↑ 1.0 1 61,083 / 2

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

  • Index Cond: ((type_nat_type_couv)::text = (couv.type_nat_type_couv)::text)
  • Buffers: shared hit=122,167
40. 30.541 30.541 ↑ 1.0 1 61,083 / 2

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,086
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,840 read=20
43. 13,865.841 13,865.841 ↑ 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.196..0.227 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,164,648 read=1,146,605 written=147
44. 488.664 488.664 ↑ 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.008..0.008 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=241,424 read=2,368
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. 361.974 361.974 ↑ 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.006..0.006 rows=1 loops=60,329)

  • Index Cond: ((cou_id = couv.cou_id) AND ((lan)::text = 'FR'::text))
  • Buffers: shared hit=180,960 read=27
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,761 read=1
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 : 186.302 ms