explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5A1y : Optimization for: plan #krZT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,433.855 105,414.134 ↓ 9,237.9 1,154,738 1

Unique (cost=95,998.32..96,007.07 rows=125 width=303) (actual time=77,614.565..105,414.134 rows=1,154,738 loops=1)

  • Output: rtpm.cat_dk, lu_cat.cat_descr, lu_cat.dspl_ordr, f.msr_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, (CASE WHEN (lm.inv_ind = 0) THEN 'Lower Rate is better'::text ELSE 'Higher Rate is better'::text END), lm.msr_typ, lm.msr_fmt, lm.inv_ind, rptp_1.perf_trnd_prd_dk, lu_perf_prd_6.perf_prd_descr, f.cmprtr_dk, f.perf_prd_dk, lu_perf_prd_7.perf_prd_descr, f_1.data_src_cd, (concat((((((date_part('month'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text), ' - ', (((((date_part('month'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text))), f.rpt_prd_dk, lu_rpt_prd.rpt_prd_descr, f_1.ccn_cd, pa12.wjxbfs1, f_2.rt, f_3.prem_val, f_4.rt, f_5.sts_dk, f_3.cms_val, f_2.sts_dk
2. 63,580.808 103,980.279 ↓ 9,237.9 1,154,738 1

Sort (cost=95,998.32..95,998.63 rows=125 width=303) (actual time=77,614.562..103,980.279 rows=1,154,738 loops=1)

  • Output: rtpm.cat_dk, lu_cat.cat_descr, lu_cat.dspl_ordr, f.msr_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, (CASE WHEN (lm.inv_ind = 0) THEN 'Lower Rate is better'::text ELSE 'Higher Rate is better'::text END), lm.msr_typ, lm.msr_fmt, lm.inv_ind, rptp_1.perf_trnd_prd_dk, lu_perf_prd_6.perf_prd_descr, f.cmprtr_dk, f.perf_prd_dk, lu_perf_prd_7.perf_prd_descr, f_1.data_src_cd, (concat((((((date_part('month'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text), ' - ', (((((date_part('month'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text))), f.rpt_prd_dk, lu_rpt_prd.rpt_prd_descr, f_1.ccn_cd, pa12.wjxbfs1, f_2.rt, f_3.prem_val, f_4.rt, f_5.sts_dk, f_3.cms_val, f_2.sts_dk
  • Sort Key: rtpm.cat_dk, lu_cat.cat_descr, lu_cat.dspl_ordr, f.msr_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, (CASE WHEN (lm.inv_ind = 0) THEN 'Lower Rate is better'::text ELSE 'Higher Rate is better'::text END), lm.msr_typ, lm.msr_fmt, lm.inv_ind, rptp_1.perf_trnd_prd_dk, lu_perf_prd_6.perf_prd_descr, f.cmprtr_dk, lu_perf_prd_7.perf_prd_descr, f_1.data_src_cd, (concat((((((date_part('month'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text), ' - ', (((((date_part('month'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text))), f.rpt_prd_dk, lu_rpt_prd.rpt_prd_descr, f_1.ccn_cd, pa12.wjxbfs1, f_2.rt, f_3.prem_val, f_4.rt, f_5.sts_dk, f_3.cms_val, f_2.sts_dk
  • Sort Method: external merge Disk: 303128kB
3. 5,315.020 40,399.471 ↓ 9,237.9 1,154,738 1

Hash Join (cost=80,200.91..95,993.97 rows=125 width=303) (actual time=24,839.146..40,399.471 rows=1,154,738 loops=1)

  • Output: rtpm.cat_dk, lu_cat.cat_descr, lu_cat.dspl_ordr, f.msr_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, CASE WHEN (lm.inv_ind = 0) THEN 'Lower Rate is better'::text ELSE 'Higher Rate is better'::text END, lm.msr_typ, lm.msr_fmt, lm.inv_ind, rptp_1.perf_trnd_prd_dk, lu_perf_prd_6.perf_prd_descr, f.cmprtr_dk, f.perf_prd_dk, lu_perf_prd_7.perf_prd_descr, f_1.data_src_cd, concat((((((date_part('month'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_strt_dt)::timestamp without time zone))::text), ' - ', (((((date_part('month'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (rtpm.perf_prd_end_dt)::timestamp without time zone))::text)), f.rpt_prd_dk, lu_rpt_prd.rpt_prd_descr, f_1.ccn_cd, pa12.wjxbfs1, f_2.rt, f_3.prem_val, f_4.rt, f_5.sts_dk, f_3.cms_val, f_2.sts_dk
  • Hash Cond: (lu_perf_prd.perf_prd_dk = lu_perf_prd_6.perf_prd_dk)
4. 1,030.094 35,084.377 ↓ 2,636.4 1,154,738 1

Hash Join (cost=80,198.00..95,977.54 rows=438 width=259) (actual time=24,839.027..35,084.377 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.perf_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val, f_5.sts_dk, rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.perf_prd_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, lm.inv_ind, lm.msr_typ, lm.msr_fmt, lu_rpt_prd.rpt_prd_descr, lu_cat.cat_descr, lu_cat.dspl_ordr
  • Inner Unique: true
  • Hash Cond: (rtpm.cat_dk = lu_cat.cat_dk)
5. 946.956 34,054.267 ↓ 2,636.4 1,154,738 1

Nested Loop (cost=80,196.84..95,974.53 rows=438 width=237) (actual time=24,839.000..34,054.267 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.perf_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val, f_5.sts_dk, rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.perf_prd_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, lm.inv_ind, lm.msr_typ, lm.msr_fmt, lu_rpt_prd.rpt_prd_descr
  • Join Filter: (f.rpt_prd_dk = lu_rpt_prd.rpt_prd_dk)
6. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on public.lu_rpt_prd (cost=0.00..1.01 rows=1 width=17) (actual time=0.004..0.008 rows=1 loops=1)

  • Output: lu_rpt_prd.rpt_prd_dk, lu_rpt_prd.rpt_prd_descr, lu_rpt_prd.actv_ind
  • Filter: lu_rpt_prd.actv_ind
7. 1,064.521 33,107.303 ↓ 2,636.4 1,154,738 1

Hash Join (cost=80,196.84..95,968.04 rows=438 width=232) (actual time=24,838.990..33,107.303 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val, f_5.sts_dk, rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.perf_prd_dk, rtpm.rpt_prd_dk, lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, lm.inv_ind, lm.msr_typ, lm.msr_fmt
  • Inner Unique: true
  • Hash Cond: (f.msr_dk = lm.msr_dk)
8. 2,858.975 32,042.706 ↓ 2,636.4 1,154,738 1

Hash Left Join (cost=80,192.04..95,962.04 rows=438 width=144) (actual time=24,838.902..32,042.706 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val, f_5.sts_dk, rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.msr_dk, rtpm.perf_prd_dk, rtpm.rpt_prd_dk
  • Hash Cond: (((f_1.ccn_cd)::text = (f_5.ccn_cd)::text) AND (f.cmprtr_dk = lu_cmprtr.cmprtr_dk) AND ((f_1.data_src_cd)::text = (f_5.data_src_cd)::text) AND (f.msr_dk = f_5.msr_dk) AND (f.perf_prd_dk = f_5.perf_prd_dk) AND (rptp_1.perf_trnd_prd_dk = rptp_5.perf_trnd_prd_dk) AND (f.rpt_prd_dk = f_5.rpt_prd_dk))
9. 1,103.601 25,103.086 ↓ 2,636.4 1,154,738 1

Hash Join (cost=68,865.80..84,428.83 rows=438 width=142) (actual time=20,757.554..25,103.086 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val, rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.msr_dk, rtpm.perf_prd_dk, rtpm.rpt_prd_dk
  • Hash Cond: ((lu_perf_prd.perf_prd_dk = rtpm.perf_prd_dk) AND (f.msr_dk = rtpm.msr_dk) AND (f.rpt_prd_dk = rtpm.rpt_prd_dk))
10. 1,132.731 23,998.804 ↓ 1,219.4 1,154,738 1

Hash Left Join (cost=68,841.26..84,346.65 rows=947 width=118) (actual time=20,756.860..23,998.804 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt, f_3.prem_val, f_3.cms_val
  • Hash Cond: ((f.cmprtr_dk = f_3.cmprtr_dk) AND (f.msr_dk = f_3.msr_dk) AND (f.perf_prd_dk = f_3.perf_prd_dk) AND (f.rpt_prd_dk = f_3.rpt_prd_dk))
11. 1,233.632 22,861.400 ↓ 1,219.4 1,154,738 1

Hash Right Join (cost=68,777.94..84,257.87 rows=947 width=106) (actual time=20,752.169..22,861.400 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1, f_4.rt
  • Hash Cond: (((f_4.ccn_cd)::text = (f_1.ccn_cd)::text) AND ((f_4.data_src_cd)::text = (f_1.data_src_cd)::text) AND (f_4.msr_dk = f.msr_dk) AND (f_4.perf_prd_dk = f.perf_prd_dk) AND (f_4.rpt_prd_dk = f.rpt_prd_dk))
12. 279.038 999.399 ↓ 5.3 184,665 1

Hash Join (cost=4.95..14,835.56 rows=34,621 width=27) (actual time=123.027..999.399 rows=184,665 loops=1)

  • Output: f_4.rt, f_4.perf_prd_dk, f_4.ccn_cd, f_4.data_src_cd, f_4.msr_dk, f_4.rpt_prd_dk
  • Inner Unique: true
  • Hash Cond: (f_4.job_dk = lj_4.job_dk)
13. 427.899 720.341 ↓ 8.0 553,995 1

Nested Loop (cost=3.89..14,460.17 rows=69,242 width=35) (actual time=0.125..720.341 rows=553,995 loops=1)

  • Output: f_4.rt, f_4.job_dk, f_4.perf_prd_dk, f_4.ccn_cd, f_4.data_src_cd, f_4.msr_dk, f_4.rpt_prd_dk
14. 0.024 0.074 ↓ 8.0 8 1

HashAggregate (cost=3.46..3.47 rows=1 width=12) (actual time=0.059..0.074 rows=8 loops=1)

  • Output: rptp_4.perf_trnd_prd_dk, rptp_4.perf_prd_dk, lu_perf_prd_4.perf_prd_dk
  • Group Key: rptp_4.perf_prd_dk
15. 0.015 0.050 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.039..0.050 rows=8 loops=1)

  • Output: rptp_4.perf_trnd_prd_dk, rptp_4.perf_prd_dk, lu_perf_prd_4.perf_prd_dk
  • Hash Cond: (lu_perf_prd_4.perf_prd_dk = rptp_4.perf_trnd_prd_dk)
16. 0.012 0.012 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_4 (cost=0.00..1.28 rows=8 width=4) (actual time=0.008..0.012 rows=8 loops=1)

  • Output: lu_perf_prd_4.perf_prd_dk, lu_perf_prd_4.perf_prd_descr, lu_perf_prd_4.perf_prd_type, lu_perf_prd_4.actv_ind
  • Filter: lu_perf_prd_4.actv_ind
  • Rows Removed by Filter: 20
17. 0.006 0.023 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.023..0.023 rows=12 loops=1)

  • Output: rptp_4.perf_trnd_prd_dk, rptp_4.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.017 0.017 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_4 (cost=0.00..2.12 rows=1 width=8) (actual time=0.008..0.017 rows=12 loops=1)

  • Output: rptp_4.perf_trnd_prd_dk, rptp_4.perf_prd_dk
  • Filter: (rptp_4.perf_trnd_prd_dk = rptp_4.perf_prd_dk)
  • Rows Removed by Filter: 78
19. 292.368 292.368 ↓ 1.0 69,249 8

Index Scan using fct_ccn_msr_data_src_cd_perf_prd_dk_idx on public.fct_ccn_msr f_4 (cost=0.43..13,764.28 rows=69,242 width=35) (actual time=0.023..36.546 rows=69,249 loops=8)

  • Output: f_4.ccn_dk, f_4.ccn_cd, f_4.rpt_prd_dk, f_4.perf_prd_dk, f_4.msr_dk, f_4.cases, f_4.pct, f_4.rt, f_4.data_src_cd, f_4.data_src_as_of_val, f_4.job_dk, f_4.sts_dk
  • Index Cond: (((f_4.data_src_cd)::text = 'HC'::text) AND (f_4.perf_prd_dk = lu_perf_prd_4.perf_prd_dk))
20. 0.004 0.020 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.020..0.020 rows=2 loops=1)

  • Output: lj_4.job_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.016 0.016 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_4 (cost=0.00..1.04 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1)

  • Output: lj_4.job_dk
  • Filter: lj_4.actv_ind
  • Rows Removed by Filter: 2
22. 835.623 20,628.369 ↓ 1,219.4 1,154,738 1

Hash (cost=68,751.68..68,751.68 rows=947 width=101) (actual time=20,628.368..20,628.369 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1
  • Buckets: 131072 (originally 1024) Batches: 16 (originally 1) Memory Usage: 9217kB
23. 1,740.875 19,792.746 ↓ 1,219.4 1,154,738 1

Hash Left Join (cost=41,957.11..68,751.68 rows=947 width=101) (actual time=14,618.911..19,792.746 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk, pa12.wjxbfs1
  • Inner Unique: true
  • Hash Cond: (((f_1.ccn_cd)::text = (pa12.ccn_cd)::text) AND (f.msr_dk = pa12.msr_dk) AND (f.perf_prd_dk = pa12.perf_prd_dk) AND (f.rpt_prd_dk = pa12.rpt_prd_dk))
24. 1,324.527 14,780.084 ↓ 1,219.4 1,154,738 1

Hash Right Join (cost=10,184.51..36,969.14 rows=947 width=69) (actual time=11,345.417..14,780.084 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk, f_2.rt, f_2.sts_dk
  • Hash Cond: (((f_2.ccn_cd)::text = (f_1.ccn_cd)::text) AND ((f_2.data_src_cd)::text = (f_1.data_src_cd)::text) AND (f_2.msr_dk = f.msr_dk) AND (f_2.perf_prd_dk = f.perf_prd_dk) AND (f_2.rpt_prd_dk = f.rpt_prd_dk))
25. 588.156 2,151.097 ↓ 5.3 392,704 1

Hash Join (cost=2,319.02..27,722.62 rows=73,636 width=29) (actual time=40.611..2,151.097 rows=392,704 loops=1)

  • Output: f_2.rt, f_2.sts_dk, f_2.perf_prd_dk, f_2.ccn_cd, f_2.data_src_cd, f_2.msr_dk, f_2.rpt_prd_dk
  • Inner Unique: true
  • Hash Cond: (f_2.job_dk = lj_2.job_dk)
26. 842.670 1,562.928 ↓ 8.0 1,178,112 1

Nested Loop (cost=2,317.95..26,925.37 rows=147,271 width=37) (actual time=5.396..1,562.928 rows=1,178,112 loops=1)

  • Output: f_2.rt, f_2.sts_dk, f_2.job_dk, f_2.perf_prd_dk, f_2.ccn_cd, f_2.data_src_cd, f_2.msr_dk, f_2.rpt_prd_dk
27. 0.032 0.082 ↓ 8.0 8 1

HashAggregate (cost=3.46..3.47 rows=1 width=12) (actual time=0.059..0.082 rows=8 loops=1)

  • Output: rptp_2.perf_trnd_prd_dk, rptp_2.perf_prd_dk, lu_perf_prd_2.perf_prd_dk
  • Group Key: rptp_2.perf_prd_dk
28. 0.014 0.050 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.040..0.050 rows=8 loops=1)

  • Output: rptp_2.perf_trnd_prd_dk, rptp_2.perf_prd_dk, lu_perf_prd_2.perf_prd_dk
  • Hash Cond: (lu_perf_prd_2.perf_prd_dk = rptp_2.perf_trnd_prd_dk)
29. 0.015 0.015 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_2 (cost=0.00..1.28 rows=8 width=4) (actual time=0.011..0.015 rows=8 loops=1)

  • Output: lu_perf_prd_2.perf_prd_dk, lu_perf_prd_2.perf_prd_descr, lu_perf_prd_2.perf_prd_type, lu_perf_prd_2.actv_ind
  • Filter: lu_perf_prd_2.actv_ind
  • Rows Removed by Filter: 20
30. 0.006 0.021 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.021..0.021 rows=12 loops=1)

  • Output: rptp_2.perf_trnd_prd_dk, rptp_2.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.015 0.015 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_2 (cost=0.00..2.12 rows=1 width=8) (actual time=0.006..0.015 rows=12 loops=1)

  • Output: rptp_2.perf_trnd_prd_dk, rptp_2.perf_prd_dk
  • Filter: (rptp_2.perf_trnd_prd_dk = rptp_2.perf_prd_dk)
  • Rows Removed by Filter: 78
32. 661.168 720.176 ↑ 1.0 147,264 8

Bitmap Heap Scan on public.fct_ccn_msr f_2 (cost=2,314.49..25,449.19 rows=147,271 width=37) (actual time=7.662..90.022 rows=147,264 loops=8)

  • Output: f_2.ccn_dk, f_2.ccn_cd, f_2.rpt_prd_dk, f_2.perf_prd_dk, f_2.msr_dk, f_2.cases, f_2.pct, f_2.rt, f_2.data_src_cd, f_2.data_src_as_of_val, f_2.job_dk, f_2.sts_dk
  • Recheck Cond: (f_2.perf_prd_dk = lu_perf_prd_2.perf_prd_dk)
  • Filter: ((f_2.data_src_cd)::text <> 'HC'::text)
  • Rows Removed by Filter: 69249
  • Heap Blocks: exact=19894
33. 59.008 59.008 ↑ 1.0 216,513 8

Bitmap Index Scan on fct_ccn_msr_perf_prd_dk_idx (cost=0.00..2,277.67 rows=216,513 width=0) (actual time=7.376..7.376 rows=216,513 loops=8)

  • Index Cond: (f_2.perf_prd_dk = lu_perf_prd_2.perf_prd_dk)
34. 0.003 0.013 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.013..0.013 rows=2 loops=1)

  • Output: lj_2.job_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_2 (cost=0.00..1.04 rows=2 width=8) (actual time=0.008..0.010 rows=2 loops=1)

  • Output: lj_2.job_dk
  • Filter: lj_2.actv_ind
  • Rows Removed by Filter: 2
36. 918.196 11,304.460 ↓ 1,219.4 1,154,738 1

Hash (cost=7,844.18..7,844.18 rows=947 width=62) (actual time=11,304.460..11,304.460 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk
  • Buckets: 131072 (originally 1024) Batches: 16 (originally 1) Memory Usage: 9217kB
37. 844.632 10,386.264 ↓ 1,219.4 1,154,738 1

Hash Join (cost=103.74..7,844.18 rows=947 width=62) (actual time=125.264..10,386.264 rows=1,154,738 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk
  • Hash Cond: ((lu_perf_prd.perf_prd_dk = f.perf_prd_dk) AND (f_1.msr_dk = f.msr_dk) AND (f_1.rpt_prd_dk = f.rpt_prd_dk))
38. 425.002 9,538.690 ↓ 522.5 577,369 1

Nested Loop (cost=23.38..7,646.62 rows=1,105 width=46) (actual time=122.255..9,538.690 rows=577,369 loops=1)

  • Output: f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk, lu_perf_prd_1.perf_prd_dk
39. 0.036 0.036 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_1 (cost=0.00..2.12 rows=1 width=8) (actual time=0.009..0.036 rows=12 loops=1)

  • Output: rptp_1.perf_trnd_prd_dk, rptp_1.perf_prd_dk
  • Filter: (rptp_1.perf_prd_dk = rptp_1.perf_trnd_prd_dk)
  • Rows Removed by Filter: 78
40. 2,433.345 9,113.652 ↓ 348.7 48,114 12

Nested Loop (cost=23.38..7,643.11 rows=138 width=38) (actual time=78.750..759.471 rows=48,114 loops=12)

  • Output: f_1.data_src_cd, f_1.ccn_cd, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, lu_perf_prd_1.perf_prd_dk
  • Inner Unique: true
  • Join Filter: (f_1.job_dk = lj_1.job_dk)
  • Rows Removed by Join Filter: 240570
41. 1,301.728 4,948.200 ↓ 523.0 144,342 12

Nested Loop (cost=23.38..7,634.48 rows=276 width=46) (actual time=0.031..412.350 rows=144,342 loops=12)

  • Output: f_1.data_src_cd, f_1.ccn_cd, f_1.job_dk, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk, lu_perf_prd_1.perf_prd_dk
  • Join Filter: (rptp_1.perf_prd_dk = lu_perf_prd.perf_prd_dk)
42. 0.120 0.120 ↑ 1.0 1 12

Index Scan using lu_perf_prd_pk on public.lu_perf_prd lu_perf_prd_1 (cost=0.14..2.36 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=12)

  • Output: lu_perf_prd_1.perf_prd_dk, lu_perf_prd_1.perf_prd_descr, lu_perf_prd_1.perf_prd_type, lu_perf_prd_1.actv_ind
  • Index Cond: (lu_perf_prd_1.perf_prd_dk = rptp_1.perf_prd_dk)
  • Filter: lu_perf_prd_1.actv_ind
  • Rows Removed by Filter: 0
43. 2,180.360 3,646.352 ↓ 223.9 216,513 8

Merge Join (cost=23.24..7,620.04 rows=967 width=42) (actual time=0.029..455.794 rows=216,513 loops=8)

  • Output: f_1.data_src_cd, f_1.ccn_cd, f_1.job_dk, f_1.msr_dk, f_1.perf_prd_dk, f_1.rpt_prd_dk, rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk
  • Merge Cond: (f_1.perf_prd_dk = lu_perf_prd.perf_prd_dk)
  • Join Filter: (lu_perf_prd_1.perf_prd_dk = lu_perf_prd.perf_prd_dk)
44. 852.344 852.344 ↑ 1.0 216,513 8

Index Scan using fct_ccn_msr_perf_prd_dk_idx on public.fct_ccn_msr f_1 (cost=0.43..6,959.27 rows=216,513 width=30) (actual time=0.012..106.543 rows=216,513 loops=8)

  • Output: f_1.ccn_dk, f_1.ccn_cd, f_1.rpt_prd_dk, f_1.perf_prd_dk, f_1.msr_dk, f_1.cases, f_1.pct, f_1.rt, f_1.data_src_cd, f_1.data_src_as_of_val, f_1.job_dk, f_1.sts_dk
  • Index Cond: (f_1.perf_prd_dk = lu_perf_prd_1.perf_prd_dk)
45. 613.576 613.648 ↓ 198,110.0 198,110 8

Sort (cost=3.48..3.48 rows=1 width=12) (actual time=0.011..76.706 rows=198,110 loops=8)

  • Output: rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk
  • Sort Key: rptp.perf_trnd_prd_dk
  • Sort Method: quicksort Memory: 25kB
46. 0.013 0.072 ↓ 8.0 8 1

HashAggregate (cost=3.46..3.47 rows=1 width=12) (actual time=0.068..0.072 rows=8 loops=1)

  • Output: rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk
  • Group Key: rptp.perf_prd_dk
47. 0.020 0.059 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.048..0.059 rows=8 loops=1)

  • Output: rptp.perf_trnd_prd_dk, rptp.perf_prd_dk, lu_perf_prd.perf_prd_dk
  • Hash Cond: (lu_perf_prd.perf_prd_dk = rptp.perf_trnd_prd_dk)
48. 0.012 0.012 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd (cost=0.00..1.28 rows=8 width=4) (actual time=0.008..0.012 rows=8 loops=1)

  • Output: lu_perf_prd.perf_prd_dk, lu_perf_prd.perf_prd_descr, lu_perf_prd.perf_prd_type, lu_perf_prd.actv_ind
  • Filter: lu_perf_prd.actv_ind
  • Rows Removed by Filter: 20
49. 0.010 0.027 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.026..0.027 rows=12 loops=1)

  • Output: rptp.perf_trnd_prd_dk, rptp.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.017 0.017 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp (cost=0.00..2.12 rows=1 width=8) (actual time=0.004..0.017 rows=12 loops=1)

  • Output: rptp.perf_trnd_prd_dk, rptp.perf_prd_dk
  • Filter: (rptp.perf_trnd_prd_dk = rptp.perf_prd_dk)
  • Rows Removed by Filter: 78
51. 1,732.100 1,732.107 ↑ 1.0 2 1,732,107

Materialize (cost=0.00..1.05 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=1,732,107)

  • Output: lj_1.job_dk
52. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_1 (cost=0.00..1.04 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=1)

  • Output: lj_1.job_dk
  • Filter: lj_1.actv_ind
  • Rows Removed by Filter: 2
53. 0.449 2.942 ↑ 1.5 832 1

Hash (cost=58.52..58.52 rows=1,248 width=16) (actual time=2.941..2.942 rows=832 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk
  • Buckets: 2048 Batches: 1 Memory Usage: 55kB
54. 1.509 2.493 ↑ 1.5 832 1

Hash Join (cost=1.06..58.52 rows=1,248 width=16) (actual time=1.406..2.493 rows=832 loops=1)

  • Output: f.msr_dk, f.cmprtr_dk, f.perf_prd_dk, f.rpt_prd_dk
  • Inner Unique: true
  • Hash Cond: (f.job_dk = lj.job_dk)
55. 0.967 0.967 ↑ 1.0 2,496 1

Seq Scan on public.fct_msr f (cost=0.00..43.96 rows=2,496 width=24) (actual time=0.011..0.967 rows=2,496 loops=1)

  • Output: f.rpt_prd_dk, f.perf_prd_dk, f.msr_dk, f.cmprtr_dk, f.prem_val, f.cms_val, f.job_dk
56. 0.007 0.017 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.017..0.017 rows=2 loops=1)

  • Output: lj.job_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on public.lu_job lj (cost=0.00..1.04 rows=2 width=8) (actual time=0.008..0.010 rows=2 loops=1)

  • Output: lj.job_dk
  • Filter: lj.actv_ind
  • Rows Removed by Filter: 2
58. 251.510 3,271.787 ↓ 5.3 392,704 1

Hash (cost=30,299.88..30,299.88 rows=73,636 width=51) (actual time=3,271.786..3,271.787 rows=392,704 loops=1)

  • Output: pa12.wjxbfs1, pa12.ccn_cd, pa12.msr_dk, pa12.perf_prd_dk, pa12.rpt_prd_dk
  • Buckets: 262144 (originally 131072) Batches: 4 (originally 1) Memory Usage: 8193kB
59. 274.921 3,020.277 ↓ 5.3 392,704 1

Subquery Scan on pa12 (cost=28,643.07..30,299.88 rows=73,636 width=51) (actual time=2,475.101..3,020.277 rows=392,704 loops=1)

  • Output: pa12.wjxbfs1, pa12.ccn_cd, pa12.msr_dk, pa12.perf_prd_dk, pa12.rpt_prd_dk
60. 584.290 2,745.356 ↓ 5.3 392,704 1

HashAggregate (cost=28,643.07..29,563.52 rows=73,636 width=51) (actual time=2,475.099..2,745.356 rows=392,704 loops=1)

  • Output: f_6.perf_prd_dk, f_6.rpt_prd_dk, f_6.msr_dk, f_6.ccn_cd, (max(f_6.pct) * 100.0)
  • Group Key: f_6.perf_prd_dk, f_6.rpt_prd_dk, f_6.msr_dk, f_6.ccn_cd
61. 594.093 2,161.066 ↓ 5.3 392,704 1

Hash Join (cost=2,319.02..27,722.62 rows=73,636 width=25) (actual time=42.687..2,161.066 rows=392,704 loops=1)

  • Output: f_6.perf_prd_dk, f_6.rpt_prd_dk, f_6.msr_dk, f_6.ccn_cd, f_6.pct
  • Inner Unique: true
  • Hash Cond: (f_6.job_dk = lj_6.job_dk)
62. 837.170 1,566.948 ↓ 8.0 1,178,112 1

Nested Loop (cost=2,317.95..26,925.37 rows=147,271 width=33) (actual time=5.631..1,566.948 rows=1,178,112 loops=1)

  • Output: f_6.perf_prd_dk, f_6.rpt_prd_dk, f_6.msr_dk, f_6.ccn_cd, f_6.pct, f_6.job_dk
63. 0.032 0.098 ↓ 8.0 8 1

HashAggregate (cost=3.46..3.47 rows=1 width=12) (actual time=0.076..0.098 rows=8 loops=1)

  • Output: rptp_6.perf_trnd_prd_dk, rptp_6.perf_prd_dk, lu_perf_prd_8.perf_prd_dk
  • Group Key: rptp_6.perf_prd_dk
64. 0.020 0.066 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.055..0.066 rows=8 loops=1)

  • Output: rptp_6.perf_trnd_prd_dk, rptp_6.perf_prd_dk, lu_perf_prd_8.perf_prd_dk
  • Hash Cond: (lu_perf_prd_8.perf_prd_dk = rptp_6.perf_trnd_prd_dk)
65. 0.013 0.013 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_8 (cost=0.00..1.28 rows=8 width=4) (actual time=0.008..0.013 rows=8 loops=1)

  • Output: lu_perf_prd_8.perf_prd_dk, lu_perf_prd_8.perf_prd_descr, lu_perf_prd_8.perf_prd_type, lu_perf_prd_8.actv_ind
  • Filter: lu_perf_prd_8.actv_ind
  • Rows Removed by Filter: 20
66. 0.009 0.033 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.033..0.033 rows=12 loops=1)

  • Output: rptp_6.perf_trnd_prd_dk, rptp_6.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.024 0.024 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_6 (cost=0.00..2.12 rows=1 width=8) (actual time=0.012..0.024 rows=12 loops=1)

  • Output: rptp_6.perf_trnd_prd_dk, rptp_6.perf_prd_dk
  • Filter: (rptp_6.perf_trnd_prd_dk = rptp_6.perf_prd_dk)
  • Rows Removed by Filter: 78
68. 668.464 729.680 ↑ 1.0 147,264 8

Bitmap Heap Scan on public.fct_ccn_msr f_6 (cost=2,314.49..25,449.19 rows=147,271 width=33) (actual time=7.951..91.210 rows=147,264 loops=8)

  • Output: f_6.ccn_dk, f_6.ccn_cd, f_6.rpt_prd_dk, f_6.perf_prd_dk, f_6.msr_dk, f_6.cases, f_6.pct, f_6.rt, f_6.data_src_cd, f_6.data_src_as_of_val, f_6.job_dk, f_6.sts_dk
  • Recheck Cond: (f_6.perf_prd_dk = lu_perf_prd_8.perf_prd_dk)
  • Filter: ((f_6.data_src_cd)::text <> 'HC'::text)
  • Rows Removed by Filter: 69249
  • Heap Blocks: exact=19894
69. 61.216 61.216 ↑ 1.0 216,513 8

Bitmap Index Scan on fct_ccn_msr_perf_prd_dk_idx (cost=0.00..2,277.67 rows=216,513 width=0) (actual time=7.652..7.652 rows=216,513 loops=8)

  • Index Cond: (f_6.perf_prd_dk = lu_perf_prd_8.perf_prd_dk)
70. 0.006 0.025 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.025..0.025 rows=2 loops=1)

  • Output: lj_6.job_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.019 0.019 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_6 (cost=0.00..1.04 rows=2 width=8) (actual time=0.017..0.019 rows=2 loops=1)

  • Output: lj_6.job_dk
  • Filter: lj_6.actv_ind
  • Rows Removed by Filter: 2
72. 0.414 4.673 ↓ 5.3 832 1

Hash (cost=60.20..60.20 rows=156 width=28) (actual time=4.673..4.673 rows=832 loops=1)

  • Output: f_3.prem_val, f_3.cms_val, f_3.perf_prd_dk, f_3.cmprtr_dk, f_3.msr_dk, f_3.rpt_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
73. 1.223 4.259 ↓ 5.3 832 1

Hash Join (cost=4.54..60.20 rows=156 width=28) (actual time=2.678..4.259 rows=832 loops=1)

  • Output: f_3.prem_val, f_3.cms_val, f_3.perf_prd_dk, f_3.cmprtr_dk, f_3.msr_dk, f_3.rpt_prd_dk
  • Inner Unique: true
  • Hash Cond: (f_3.job_dk = lj_3.job_dk)
74. 2.057 3.020 ↓ 8.0 2,496 1

Hash Semi Join (cost=3.47..57.45 rows=312 width=36) (actual time=0.064..3.020 rows=2,496 loops=1)

  • Output: f_3.prem_val, f_3.cms_val, f_3.job_dk, f_3.perf_prd_dk, f_3.cmprtr_dk, f_3.msr_dk, f_3.rpt_prd_dk
  • Hash Cond: (f_3.perf_prd_dk = lu_perf_prd_3.perf_prd_dk)
75. 0.910 0.910 ↑ 1.0 2,496 1

Seq Scan on public.fct_msr f_3 (cost=0.00..43.96 rows=2,496 width=36) (actual time=0.005..0.910 rows=2,496 loops=1)

  • Output: f_3.rpt_prd_dk, f_3.perf_prd_dk, f_3.msr_dk, f_3.cmprtr_dk, f_3.prem_val, f_3.cms_val, f_3.job_dk
76. 0.005 0.053 ↓ 8.0 8 1

Hash (cost=3.46..3.46 rows=1 width=12) (actual time=0.052..0.053 rows=8 loops=1)

  • Output: rptp_3.perf_trnd_prd_dk, rptp_3.perf_prd_dk, lu_perf_prd_3.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
77. 0.012 0.048 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.037..0.048 rows=8 loops=1)

  • Output: rptp_3.perf_trnd_prd_dk, rptp_3.perf_prd_dk, lu_perf_prd_3.perf_prd_dk
  • Hash Cond: (lu_perf_prd_3.perf_prd_dk = rptp_3.perf_trnd_prd_dk)
78. 0.011 0.011 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_3 (cost=0.00..1.28 rows=8 width=4) (actual time=0.006..0.011 rows=8 loops=1)

  • Output: lu_perf_prd_3.perf_prd_dk, lu_perf_prd_3.perf_prd_descr, lu_perf_prd_3.perf_prd_type, lu_perf_prd_3.actv_ind
  • Filter: lu_perf_prd_3.actv_ind
  • Rows Removed by Filter: 20
79. 0.006 0.025 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.025..0.025 rows=12 loops=1)

  • Output: rptp_3.perf_trnd_prd_dk, rptp_3.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.019 0.019 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_3 (cost=0.00..2.12 rows=1 width=8) (actual time=0.010..0.019 rows=12 loops=1)

  • Output: rptp_3.perf_trnd_prd_dk, rptp_3.perf_prd_dk
  • Filter: (rptp_3.perf_trnd_prd_dk = rptp_3.perf_prd_dk)
  • Rows Removed by Filter: 78
81. 0.003 0.016 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.016..0.016 rows=2 loops=1)

  • Output: lj_3.job_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 0.013 0.013 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_3 (cost=0.00..1.04 rows=2 width=8) (actual time=0.011..0.013 rows=2 loops=1)

  • Output: lj_3.job_dk
  • Filter: lj_3.actv_ind
  • Rows Removed by Filter: 2
83. 0.359 0.681 ↑ 1.0 674 1

Hash (cost=12.74..12.74 rows=674 width=24) (actual time=0.681..0.681 rows=674 loops=1)

  • Output: rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.msr_dk, rtpm.perf_prd_dk, rtpm.rpt_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
84. 0.322 0.322 ↑ 1.0 674 1

Seq Scan on public.lu_rpt_perf_prd_msr rtpm (cost=0.00..12.74 rows=674 width=24) (actual time=0.007..0.322 rows=674 loops=1)

  • Output: rtpm.cat_dk, rtpm.perf_prd_strt_dt, rtpm.perf_prd_end_dt, rtpm.msr_dk, rtpm.perf_prd_dk, rtpm.rpt_prd_dk
85. 769.715 4,080.645 ↓ 29.7 1,154,738 1

Hash (cost=10,255.39..10,255.39 rows=38,940 width=32) (actual time=4,080.645..4,080.645 rows=1,154,738 loops=1)

  • Output: f_5.sts_dk, f_5.perf_prd_dk, f_5.ccn_cd, f_5.data_src_cd, f_5.msr_dk, f_5.rpt_prd_dk, rptp_5.perf_trnd_prd_dk, lu_cmprtr.cmprtr_dk
  • Buckets: 262144 (originally 65536) Batches: 16 (originally 1) Memory Usage: 8778kB
86. 1,134.317 3,310.930 ↓ 29.7 1,154,738 1

Hash Join (cost=4.73..10,255.39 rows=38,940 width=32) (actual time=36.478..3,310.930 rows=1,154,738 loops=1)

  • Output: f_5.sts_dk, f_5.perf_prd_dk, f_5.ccn_cd, f_5.data_src_cd, f_5.msr_dk, f_5.rpt_prd_dk, rptp_5.perf_trnd_prd_dk, lu_cmprtr.cmprtr_dk
  • Hash Cond: (f_5.job_dk = lj_5.job_dk)
87. 1,386.378 2,176.583 ↓ 44.5 1,732,107 1

Nested Loop (cost=2.57..9,669.14 rows=38,939 width=36) (actual time=0.069..2,176.583 rows=1,732,107 loops=1)

  • Output: f_5.sts_dk, f_5.job_dk, f_5.perf_prd_dk, f_5.ccn_cd, f_5.data_src_cd, f_5.msr_dk, f_5.rpt_prd_dk, rptp_5.perf_trnd_prd_dk
  • Join Filter: (rptp_5.perf_prd_dk = f_5.perf_prd_dk)
88. 0.029 0.085 ↓ 8.0 8 1

Hash Join (cost=2.14..3.46 rows=1 width=12) (actual time=0.042..0.085 rows=8 loops=1)

  • Output: rptp_5.perf_trnd_prd_dk, rptp_5.perf_prd_dk, lu_perf_prd_5.perf_prd_dk
  • Hash Cond: (lu_perf_prd_5.perf_prd_dk = rptp_5.perf_prd_dk)
89. 0.035 0.035 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_5 (cost=0.00..1.28 rows=8 width=4) (actual time=0.010..0.035 rows=8 loops=1)

  • Output: lu_perf_prd_5.perf_prd_dk, lu_perf_prd_5.perf_prd_descr, lu_perf_prd_5.perf_prd_type, lu_perf_prd_5.actv_ind
  • Filter: lu_perf_prd_5.actv_ind
  • Rows Removed by Filter: 20
90. 0.007 0.021 ↓ 12.0 12 1

Hash (cost=2.12..2.12 rows=1 width=8) (actual time=0.020..0.021 rows=12 loops=1)

  • Output: rptp_5.perf_trnd_prd_dk, rptp_5.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.014 0.014 ↓ 12.0 12 1

Seq Scan on public.rel_perf_trnd_prd rptp_5 (cost=0.00..2.12 rows=1 width=8) (actual time=0.006..0.014 rows=12 loops=1)

  • Output: rptp_5.perf_trnd_prd_dk, rptp_5.perf_prd_dk
  • Filter: (rptp_5.perf_prd_dk = rptp_5.perf_trnd_prd_dk)
  • Rows Removed by Filter: 78
92. 790.120 790.120 ↑ 1.0 216,513 8

Index Scan using fct_ccn_msr_perf_prd_dk_idx on public.fct_ccn_msr f_5 (cost=0.43..6,959.27 rows=216,513 width=32) (actual time=0.016..98.765 rows=216,513 loops=8)

  • Output: f_5.ccn_dk, f_5.ccn_cd, f_5.rpt_prd_dk, f_5.perf_prd_dk, f_5.msr_dk, f_5.cases, f_5.pct, f_5.rt, f_5.data_src_cd, f_5.data_src_as_of_val, f_5.job_dk, f_5.sts_dk
  • Index Cond: (f_5.perf_prd_dk = lu_perf_prd_5.perf_prd_dk)
93. 0.003 0.030 ↑ 1.0 4 1

Hash (cost=2.12..2.12 rows=4 width=12) (actual time=0.030..0.030 rows=4 loops=1)

  • Output: lj_5.job_dk, lu_cmprtr.cmprtr_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.008 0.027 ↑ 1.0 4 1

Nested Loop (cost=0.00..2.12 rows=4 width=12) (actual time=0.017..0.027 rows=4 loops=1)

  • Output: lj_5.job_dk, lu_cmprtr.cmprtr_dk
95. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on public.lu_job lj_5 (cost=0.00..1.04 rows=2 width=8) (actual time=0.007..0.009 rows=2 loops=1)

  • Output: lj_5.job_dk, lj_5.job_id, lj_5.ts, lj_5.actv_ind
  • Filter: lj_5.actv_ind
  • Rows Removed by Filter: 2
96. 0.004 0.010 ↑ 1.0 2 2

Materialize (cost=0.00..1.03 rows=2 width=4) (actual time=0.004..0.005 rows=2 loops=2)

  • Output: lu_cmprtr.cmprtr_dk
97. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on public.lu_cmprtr (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)

  • Output: lu_cmprtr.cmprtr_dk
98. 0.039 0.076 ↑ 1.5 53 1

Hash (cost=3.80..3.80 rows=80 width=100) (actual time=0.076..0.076 rows=53 loops=1)

  • Output: lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, lm.inv_ind, lm.msr_typ, lm.msr_fmt, lm.msr_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
99. 0.037 0.037 ↑ 1.5 53 1

Seq Scan on public.lu_msr lm (cost=0.00..3.80 rows=80 width=100) (actual time=0.008..0.037 rows=53 loops=1)

  • Output: lm.msr_descr, lm.msr_cd, lm.msr_shrt_descr, lm.inv_ind, lm.msr_typ, lm.msr_fmt, lm.msr_dk
100. 0.007 0.016 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=26) (actual time=0.016..0.016 rows=7 loops=1)

  • Output: lu_cat.cat_descr, lu_cat.dspl_ordr, lu_cat.cat_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.009 0.009 ↑ 1.0 7 1

Seq Scan on public.lu_cat (cost=0.00..1.07 rows=7 width=26) (actual time=0.006..0.009 rows=7 loops=1)

  • Output: lu_cat.cat_descr, lu_cat.dspl_ordr, lu_cat.cat_dk
102. 0.006 0.074 ↑ 1.0 8 1

Hash (cost=2.81..2.81 rows=8 width=24) (actual time=0.074..0.074 rows=8 loops=1)

  • Output: lu_perf_prd_6.perf_prd_descr, lu_perf_prd_6.perf_prd_dk, lu_perf_prd_7.perf_prd_descr, lu_perf_prd_7.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.024 0.068 ↑ 1.0 8 1

Hash Join (cost=1.38..2.81 rows=8 width=24) (actual time=0.045..0.068 rows=8 loops=1)

  • Output: lu_perf_prd_6.perf_prd_descr, lu_perf_prd_6.perf_prd_dk, lu_perf_prd_7.perf_prd_descr, lu_perf_prd_7.perf_prd_dk
  • Inner Unique: true
  • Hash Cond: (lu_perf_prd_6.perf_prd_dk = lu_perf_prd_7.perf_prd_dk)
104. 0.027 0.027 ↑ 1.0 28 1

Seq Scan on public.lu_perf_prd lu_perf_prd_6 (cost=0.00..1.35 rows=28 width=12) (actual time=0.015..0.027 rows=28 loops=1)

  • Output: lu_perf_prd_6.perf_prd_dk, lu_perf_prd_6.perf_prd_descr, lu_perf_prd_6.perf_prd_type, lu_perf_prd_6.actv_ind
  • Filter: ((lu_perf_prd_6.perf_prd_type)::text = 'Q'::text)
105. 0.010 0.017 ↑ 1.0 8 1

Hash (cost=1.28..1.28 rows=8 width=12) (actual time=0.017..0.017 rows=8 loops=1)

  • Output: lu_perf_prd_7.perf_prd_descr, lu_perf_prd_7.perf_prd_dk
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
106. 0.007 0.007 ↑ 1.0 8 1

Seq Scan on public.lu_perf_prd lu_perf_prd_7 (cost=0.00..1.28 rows=8 width=12) (actual time=0.003..0.007 rows=8 loops=1)

  • Output: lu_perf_prd_7.perf_prd_descr, lu_perf_prd_7.perf_prd_dk
  • Filter: lu_perf_prd_7.actv_ind
  • Rows Removed by Filter: 20
Planning time : 181.698 ms