explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DSxV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 0.956 ↓ 0.0 0 1

Insert on pmix_trns_mntc.pmix_trns (cost=18,947.66..19,547.52 rows=204 width=1,169) (actual time=0.956..0.956 rows=0 loops=1)

2.          

CTE in_pmix

3. 0.025 0.953 ↓ 0.0 0 1

Hash Join (cost=11,545.08..18,946.97 rows=218 width=2,593) (actual time=0.953..0.953 rows=0 loops=1)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id, acw.as_cal_week_id, (yy.nm)::numeric(4,0), mm.nm, mm.mm_cnt, qtr.nm, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, s2.mfg_prdt_sold_to_cust_id, s2.cd, s2.entr_usr_id, s2.entr_ts, s2.last_updt_usr_id, s2.last_updt_ts, re.rpt_enty_id, re.pty_rol_id, re.cd, re.sts_cd, re.entr_ts, re.entr_usr_id, re.last_updt_ts, re.last_updt_usr_id, re.nm, re.rpt_ingr_uom_id, re.rpt_ingr_curr_id, re.rpt_enty_type_id, re.mstr_enty_id, re.frt_calc_ind, re.pmix_mrnt_trns_incl_ind, re.use_xfer_trns_for_sale_ind, re.lnk_sale_exp_to_sale_trns_ind, re.geog_loc_id, re.sale_prog_id
  • Hash Cond: (ih.plant = re.cd)
4. 0.012 0.028 ↓ 0.0 0 1

Hash Join (cost=11,433.11..18,830.36 rows=218 width=2,457) (actual time=0.028..0.028 rows=0 loops=1)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id, acw.as_cal_week_id, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm, s2.mfg_prdt_sold_to_cust_id, s2.cd, s2.entr_usr_id, s2.entr_ts, s2.last_updt_usr_id, s2.last_updt_ts
  • Hash Cond: ((s2.cd)::bpchar = id.sold_to_code)
5. 0.004 0.004 ↑ 176,743.0 1 1

Seq Scan on pmix_trns_mntc.mfg_prdt_sold_to_cust s2 (cost=0.00..3,197.43 rows=176,743 width=556) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: s2.mfg_prdt_sold_to_cust_id, s2.cd, s2.entr_usr_id, s2.entr_ts, s2.last_updt_usr_id, s2.last_updt_ts
6. 0.000 0.012 ↓ 0.0 0 1

Hash (cost=11,430.38..11,430.38 rows=218 width=1,901) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id, acw.as_cal_week_id, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.000 0.012 ↓ 0.0 0 1

Hash Join (cost=116.80..11,430.38 rows=218 width=1,901) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id, acw.as_cal_week_id, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
  • Hash Cond: (((ih.trans_year)::numeric = (yy.nm)::numeric(4,0)) AND ((ih.trans_week)::numeric = acw.week_cnt))
8. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=11.67..11,259.94 rows=236 width=1,841) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id
  • Join Filter: (id.incoming_pmix_hdr_id = ih.incoming_pmix_hdr_id)
9. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.04 rows=1 width=666) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count, ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind
  • Join Filter: (ih.incoming_pmix_batch_id = ib.incoming_pmix_batch_id)
10. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on pmix_trns_mntc.incoming_pmix_hdr ih (cost=0.00..1.01 rows=1 width=590) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: ih.incoming_pmix_hdr_id, ih.incoming_pmix_batch_id, ih.plant, ih.trans_year, ih.trans_week, ih.trans_code, ih.entr_ts, ih.last_updt_ts, ih.last_updt_usr_id, ih.entr_usr_id, ih.hdr_rec_count
  • Filter: (ih.trans_code = '90'::bpchar)
  • Rows Removed by Filter: 1
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on pmix_trns_mntc.incoming_pmix_batch ib (cost=0.00..1.01 rows=1 width=76) (never executed)

  • Output: ib.incoming_pmix_batch_id, ib.batch_name, ib.origin, ib.entr_ts, ib.last_updt_ts, ib.entr_usr_id, ib.last_updt_usr_id, ib.ready_to_proc_ind, ib.being_proc_ind, ib.batch_rec_count, ib.pre_validated_ind
  • Filter: ((ib.batch_name)::text = 'CPMIHYB4'::text)
12. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=11.67..11,254.95 rows=236 width=1,175) (never executed)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales, pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id
  • Hash Cond: ((pc.cd)::bpchar = id.product_code)
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on pmix_trns_mntc.prdt_cd pc (cost=0.00..6,283.77 rows=330,477 width=559) (never executed)

  • Output: pc.prdt_cd_id, pc.cd, pc.entr_ts, pc.entr_usr_id, pc.last_updt_ts, pc.last_updt_usr_id
14. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.72..8.72 rows=236 width=616) (never executed)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on pmix_trns_mntc.incoming_pmix_dtl id (cost=0.00..8.72 rows=236 width=616) (never executed)

  • Output: id.incoming_pmix_dtl_id, id.incoming_pmix_hdr_id, id.product_code, id.sold_to_code, id.unit_qty, id.dollar_amt, id.entr_ts, id.last_updt_ts, id.last_updt_usr_id, id.entr_usr_id, id.not_on_sales
  • Filter: ((length(btrim((id.sold_to_code)::text)) <> 0) OR (id.sold_to_code IS NOT NULL))
16. 0.000 0.000 ↓ 0.0 0

Hash (cost=81.64..81.64 rows=1,566 width=60) (never executed)

  • Output: acw.as_cal_week_id, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
17. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=27.45..81.64 rows=1,566 width=60) (never executed)

  • Output: acw.as_cal_week_id, acw.week_cnt, acw.strt_dt, acw.end_dt, acw.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
  • Hash Cond: (acw.cal_ccyy_mm_id = yymm.cal_ccyy_mm_id)
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on constm.as_cal_week acw (cost=0.00..32.66 rows=1,566 width=29) (never executed)

  • Output: acw.as_cal_week_id, acw.cal_ccyy_mm_id, acw.strt_dt, acw.end_dt, acw.week_cnt, acw.sts_cd, acw.entr_usr_id, acw.entr_ts, acw.last_updt_usr_id, acw.last_updt_ts
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=22.65..22.65 rows=384 width=39) (never executed)

  • Output: yymm.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
20. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.25..22.65 rows=384 width=39) (never executed)

  • Output: yymm.cal_ccyy_mm_id, yy.nm, mm.nm, mm.mm_cnt, qtr.nm
  • Hash Cond: (yymm.cal_ccyy_id = yy.cal_ccyy_id)
21. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.52..15.65 rows=384 width=42) (never executed)

  • Output: yymm.cal_ccyy_mm_id, yymm.cal_ccyy_id, mm.nm, mm.mm_cnt, qtr.nm
  • Hash Cond: (yymm.cal_mm_id = mm.cal_mm_id)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on constm.cal_ccyy_mm yymm (cost=0.00..7.84 rows=384 width=24) (never executed)

  • Output: yymm.cal_ccyy_mm_id, yymm.cal_ccyy_id, yymm.cal_mm_id, yymm.last_updt_ts, yymm.entr_ts, yymm.sts_cd, yymm.entr_usr_id, yymm.last_updt_usr_id
23. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.38..2.38 rows=12 width=34) (never executed)

  • Output: mm.nm, mm.mm_cnt, mm.cal_mm_id, qtr.nm
24. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.09..2.38 rows=12 width=34) (never executed)

  • Output: mm.nm, mm.mm_cnt, mm.cal_mm_id, qtr.nm
  • Hash Cond: (mm.cal_qtr_id = qtr.cal_qtr_id)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on constm.cal_mm mm (cost=0.00..1.12 rows=12 width=28) (never executed)

  • Output: mm.cal_mm_id, mm.cal_qtr_id, mm.nm, mm.last_updt_ts, mm.entr_ts, mm.sts_cd, mm.entr_usr_id, mm.last_updt_usr_id, mm.mm_cnt
26. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=4 width=22) (never executed)

  • Output: qtr.nm, qtr.cal_qtr_id
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on constm.cal_qtr qtr (cost=0.00..1.04 rows=4 width=22) (never executed)

  • Output: qtr.nm, qtr.cal_qtr_id
28. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.32..1.32 rows=32 width=13) (never executed)

  • Output: yy.nm, yy.cal_ccyy_id
29. 0.000 0.000 ↓ 0.0 0

Seq Scan on constm.cal_ccyy yy (cost=0.00..1.32 rows=32 width=13) (never executed)

  • Output: yy.nm, yy.cal_ccyy_id
30. 0.691 0.900 ↑ 1.0 2,310 1

Hash (cost=83.10..83.10 rows=2,310 width=129) (actual time=0.900..0.900 rows=2,310 loops=1)

  • Output: re.rpt_enty_id, re.pty_rol_id, re.cd, re.sts_cd, re.entr_ts, re.entr_usr_id, re.last_updt_ts, re.last_updt_usr_id, re.nm, re.rpt_ingr_uom_id, re.rpt_ingr_curr_id, re.rpt_enty_type_id, re.mstr_enty_id, re.frt_calc_ind, re.pmix_mrnt_trns_incl_ind, re.use_xfer_trns_for_sale_ind, re.lnk_sale_exp_to_sale_trns_ind, re.geog_loc_id, re.sale_prog_id
  • Buckets: 4096 Batches: 1 Memory Usage: 396kB
31. 0.209 0.209 ↑ 1.0 2,310 1

Seq Scan on pty_rol_rpt_enty.rpt_enty re (cost=0.00..83.10 rows=2,310 width=129) (actual time=0.007..0.209 rows=2,310 loops=1)

  • Output: re.rpt_enty_id, re.pty_rol_id, re.cd, re.sts_cd, re.entr_ts, re.entr_usr_id, re.last_updt_ts, re.last_updt_usr_id, re.nm, re.rpt_ingr_uom_id, re.rpt_ingr_curr_id, re.rpt_enty_type_id, re.mstr_enty_id, re.frt_calc_ind, re.pmix_mrnt_trns_incl_ind, re.use_xfer_trns_for_sale_ind, re.lnk_sale_exp_to_sale_trns_ind, re.geog_loc_id, re.sale_prog_id
32. 0.000 0.955 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.70..600.55 rows=204 width=1,169) (actual time=0.955..0.955 rows=0 loops=1)

  • Output: nextval('pmix_trns_mntc.pmix_trns_pmix_tranasction_id_seq'::regclass), '10'::bigint, in_pmix.rpt_enty_id, in_pmix.as_cal_week_id, false, 0.00000::numeric(17,5), 0.00000::numeric(17,5), in_pmix.dollar_amt, in_pmix.dollar_amt, 'SQL_MASS_INSERT_PROC_STEPF'::character varying(256), now(), NULL::character varying(256), NULL::timestamp without time zone, in_pmix.prdt_cd_id, in_pmix.mfg_prdt_sold_to_cust_id
33. 0.955 0.955 ↓ 0.0 0 1

CTE Scan on in_pmix (cost=0.00..4.36 rows=218 width=48) (actual time=0.955..0.955 rows=0 loops=1)

  • Output: in_pmix.incoming_pmix_dtl_id, in_pmix.incoming_pmix_hdr_id, in_pmix.product_code, in_pmix.sold_to_code, in_pmix.unit_qty, in_pmix.dollar_amt, in_pmix.entr_ts, in_pmix.last_updt_ts, in_pmix.last_updt_usr_id, in_pmix.entr_usr_id, in_pmix.not_on_sales, in_pmix.incoming_pmix_hdr_id_1, in_pmix.incoming_pmix_batch_id, in_pmix.plant, in_pmix.trans_year, in_pmix.trans_week, in_pmix.trans_code, in_pmix.entr_ts_1, in_pmix.last_updt_ts_1, in_pmix.last_updt_usr_id_1, in_pmix.entr_usr_id_1, in_pmix.hdr_rec_count, in_pmix.incoming_pmix_batch_id_1, in_pmix.batch_name, in_pmix.origin, in_pmix.entr_ts_2, in_pmix.last_updt_ts_2, in_pmix.entr_usr_id_2, in_pmix.last_updt_usr_id_2, in_pmix.ready_to_proc_ind, in_pmix.being_proc_ind, in_pmix.batch_rec_count, in_pmix.pre_validated_ind, in_pmix.prdt_cd_id, in_pmix.cd, in_pmix.entr_ts_3, in_pmix.entr_usr_id_3, in_pmix.last_updt_ts_3, in_pmix.last_updt_usr_id_3, in_pmix.as_cal_week_id, in_pmix.year, in_pmix.month, in_pmix.mm, in_pmix.quarter, in_pmix.week, in_pmix.strt_dt, in_pmix.end_dt, in_pmix.cal_ccyy_mm_id, in_pmix.mfg_prdt_sold_to_cust_id, in_pmix.cd_1, in_pmix.entr_usr_id_4, in_pmix.entr_ts_4, in_pmix.last_updt_usr_id_4, in_pmix.last_updt_ts_4, in_pmix.rpt_enty_id, in_pmix.pty_rol_id, in_pmix.cd_2, in_pmix.sts_cd, in_pmix.entr_ts_5, in_pmix.entr_usr_id_5, in_pmix.last_updt_ts_5, in_pmix.last_updt_usr_id_5, in_pmix.nm, in_pmix.rpt_ingr_uom_id, in_pmix.rpt_ingr_curr_id, in_pmix.rpt_enty_type_id, in_pmix.mstr_enty_id, in_pmix.frt_calc_ind, in_pmix.pmix_mrnt_trns_incl_ind, in_pmix.use_xfer_trns_for_sale_ind, in_pmix.lnk_sale_exp_to_sale_trns_ind, in_pmix.geog_loc_id, in_pmix.sale_prog_id
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ak_pmix_trns_mult_mfg_prdt_sold_to_cust_not_null on pmix_trns_mntc.pmix_trns p (cost=0.70..2.72 rows=1 width=32) (never executed)

  • Output: p.rpt_enty_id, p.as_cal_week_id, p.pmix_trns_type_id, p.prdt_cd_id, p.mfg_prdt_sold_to_cust_id
  • Index Cond: ((p.rpt_enty_id = in_pmix.rpt_enty_id) AND (p.as_cal_week_id = in_pmix.as_cal_week_id) AND (p.pmix_trns_type_id = 10) AND (p.prdt_cd_id = in_pmix.prdt_cd_id) AND (p.mfg_prdt_sold_to_cust_id = in_pmix.mfg_prdt_sold_to_cust_id))
  • Heap Fetches: 0