explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Oxc

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,618.866 ↑ 199.0 1 1

Subquery Scan on package_performance_by_distributor_cd_vw (cost=3,027.28..57,400,641.87 rows=199 width=32) (actual time=1,618.865..1,618.866 rows=1 loops=1)

2. 2.760 1,618.864 ↑ 199.0 1 1

GroupAggregate (cost=3,027.28..57,400,639.88 rows=199 width=110) (actual time=1,618.864..1,618.864 rows=1 loops=1)

  • Group Key: podt.dist_01_cd
3. 2.796 1,616.085 ↑ 199.0 1 1

GroupAggregate (cost=3,027.28..57,399,775.73 rows=199 width=110) (actual time=1,616.085..1,616.085 rows=1 loops=1)

  • Group Key: podt.dist_01_cd
4. 4.215 1,613.009 ↑ 18.1 11 1

HashAggregate (cost=3,027.28..24,865,525.79 rows=199 width=1,134) (actual time=175.342..1,613.009 rows=11 loops=1)

  • Group Key: podt.dist_01_cd, podt.brand_cd
5. 0.337 10.890 ↑ 1.7 119 1

Merge Left Join (cost=3,022.28..3,024.30 rows=199 width=1,134) (actual time=10.268..10.890 rows=119 loops=1)

  • Merge Cond: ((podt.master_pkg_sku_cd)::text = (podvw.sku)::text)
  • Join Filter: ((podt.dist_01_cd)::text = (podvw.dist_01_cd)::text)
6. 0.243 4.575 ↑ 1.7 119 1

Merge Left Join (cost=1,692.72..1,694.03 rows=199 width=1,102) (actual time=4.299..4.575 rows=119 loops=1)

  • Merge Cond: ((podt.master_pkg_sku_cd)::text = (volvw.sku)::text)
  • Join Filter: ((podt.dist_01_cd)::text = (volvw.dist_01_cd)::text)
7. 0.285 0.892 ↑ 1.7 119 1

Sort (cost=652.26..652.76 rows=199 width=1,070) (actual time=0.870..0.892 rows=119 loops=1)

  • Sort Key: podt.master_pkg_sku_cd
  • Sort Method: quicksort Memory: 39kB
8. 0.048 0.607 ↑ 1.7 119 1

Subquery Scan on podt (cost=640.69..644.67 rows=199 width=1,070) (actual time=0.528..0.607 rows=119 loops=1)

9. 0.150 0.559 ↑ 1.7 119 1

HashAggregate (cost=640.69..642.68 rows=199 width=50) (actual time=0.527..0.559 rows=119 loops=1)

  • Group Key: bc_dist_perf_pkg_pod.dist_01_cd, bc_dist_perf_pkg_pod.brand_cd, bc_dist_perf_pkg_pod.master_pkg_sku_dsc, bc_dist_perf_pkg_pod.master_pkg_sku_cd, bc_dist_perf_pkg_pod.growth_dsc
10. 0.091 0.409 ↓ 1.0 200 1

Append (cost=5.20..638.20 rows=199 width=50) (actual time=0.051..0.409 rows=200 loops=1)

11. 0.157 0.193 ↓ 1.0 119 1

Bitmap Heap Scan on bc_dist_perf_pkg_pod (cost=5.20..382.18 rows=118 width=50) (actual time=0.051..0.193 rows=119 loops=1)

  • Recheck Cond: ((dist_01_cd)::text = '2959017'::text)
  • Heap Blocks: exact=115
12. 0.036 0.036 ↓ 1.0 119 1

Bitmap Index Scan on bc_dist_perf_pkg_pod_dist_01_cd_brand_cd_idx (cost=0.00..5.17 rows=118 width=0) (actual time=0.036..0.036 rows=119 loops=1)

  • Index Cond: ((dist_01_cd)::text = '2959017'::text)
13. 0.099 0.125 ↑ 1.0 81 1

Bitmap Heap Scan on bc_dist_perf_pkg_vol (cost=4.92..254.03 rows=81 width=49) (actual time=0.034..0.125 rows=81 loops=1)

  • Recheck Cond: ((dist_01_cd)::text = '2959017'::text)
  • Heap Blocks: exact=78
14. 0.026 0.026 ↑ 1.0 81 1

Bitmap Index Scan on bc_dist_perf_pkg_vol_dist_01_cd_brand_cd_idx (cost=0.00..4.89 rows=81 width=0) (actual time=0.026..0.026 rows=81 loops=1)

  • Index Cond: ((dist_01_cd)::text = '2959017'::text)
15. 0.344 3.440 ↓ 4.5 81 1

Sort (cost=1,040.46..1,040.50 rows=18 width=49) (actual time=3.425..3.440 rows=81 loops=1)

  • Sort Key: volvw.sku
  • Sort Method: quicksort Memory: 350kB
16. 0.027 3.096 ↓ 4.5 81 1

Subquery Scan on volvw (cost=1,039.63..1,040.08 rows=18 width=49) (actual time=2.928..3.096 rows=81 loops=1)

17. 0.433 3.069 ↓ 4.5 81 1

HashAggregate (cost=1,039.63..1,039.90 rows=18 width=49) (actual time=2.926..3.069 rows=81 loops=1)

  • Group Key: vol.dist_01_cd, vol.master_sku_cd
18. 0.058 2.636 ↓ 9.0 162 1

Append (cost=258.48..1,039.50 rows=18 width=49) (actual time=0.475..2.636 rows=162 loops=1)

19. 1.276 1.360 ↓ 9.0 81 1

HashAggregate (cost=258.48..259.05 rows=9 width=103) (actual time=0.475..1.360 rows=81 loops=1)

  • Group Key: vol.dist_01_cd, vol.master_sku_cd
20. 0.063 0.084 ↑ 1.0 81 1

Bitmap Heap Scan on bc_dist_perf_pkg_vol vol (cost=4.92..254.03 rows=81 width=103) (actual time=0.030..0.084 rows=81 loops=1)

  • Recheck Cond: ((dist_01_cd)::text = '2959017'::text)
  • Heap Blocks: exact=78
21. 0.021 0.021 ↑ 1.0 81 1

Bitmap Index Scan on bc_dist_perf_pkg_vol_dist_01_cd_brand_cd_idx (cost=0.00..4.89 rows=81 width=0) (actual time=0.021..0.021 rows=81 loops=1)

  • Index Cond: ((dist_01_cd)::text = '2959017'::text)
22. 0.311 1.218 ↓ 9.0 81 1

HashAggregate (cost=780.14..780.27 rows=9 width=49) (actual time=1.109..1.218 rows=81 loops=1)

  • Group Key: pkg.dist_01_cd, pkg.master_sku_cd
23. 0.089 0.907 ↓ 3.2 81 1

Nested Loop (cost=5.20..779.95 rows=25 width=49) (actual time=0.047..0.907 rows=81 loops=1)

24. 0.067 0.089 ↑ 1.0 81 1

Bitmap Heap Scan on bc_dist_perf_pkg_vol pkg (cost=4.92..254.03 rows=81 width=22) (actual time=0.029..0.089 rows=81 loops=1)

  • Recheck Cond: ((dist_01_cd)::text = '2959017'::text)
  • Heap Blocks: exact=78
25. 0.022 0.022 ↑ 1.0 81 1

Bitmap Index Scan on bc_dist_perf_pkg_vol_dist_01_cd_brand_cd_idx (cost=0.00..4.89 rows=81 width=0) (actual time=0.022..0.022 rows=81 loops=1)

  • Index Cond: ((dist_01_cd)::text = '2959017'::text)
26. 0.729 0.729 ↑ 1.0 1 81

Index Scan using dist_dma_averages_mv_vol_x_cd_sku_idx on dist_dma_averages_mv_vol parent (cost=0.29..6.48 rows=1 width=188) (actual time=0.008..0.009 rows=1 loops=81)

  • Index Cond: (((cd)::text = (pkg.ult_parent_cd)::text) AND ((sku)::text = (pkg.master_sku_cd)::text))
27. 0.372 5.978 ↓ 9.9 119 1

Sort (cost=1,329.56..1,329.59 rows=12 width=49) (actual time=5.962..5.978 rows=119 loops=1)

  • Sort Key: podvw.sku
  • Sort Method: quicksort Memory: 264kB
28. 0.047 5.606 ↓ 9.9 119 1

Subquery Scan on podvw (cost=1,329.04..1,329.34 rows=12 width=49) (actual time=5.381..5.606 rows=119 loops=1)

29. 2.115 5.559 ↓ 9.9 119 1

HashAggregate (cost=1,329.04..1,329.22 rows=12 width=124) (actual time=5.381..5.559 rows=119 loops=1)

  • Group Key: d.dist_01_cd, d.master_pkg_sku_cd
30. 0.212 3.444 ↓ 1.0 119 1

Nested Loop Left Join (cost=5.62..1,326.39 rows=118 width=124) (actual time=0.085..3.444 rows=119 loops=1)

31. 0.111 0.138 ↓ 1.0 119 1

Bitmap Heap Scan on bc_dist_perf_pkg_pod d (cost=5.20..382.18 rows=118 width=97) (actual time=0.040..0.138 rows=119 loops=1)

  • Recheck Cond: ((dist_01_cd)::text = '2959017'::text)
  • Heap Blocks: exact=115
32. 0.027 0.027 ↓ 1.0 119 1

Bitmap Index Scan on bc_dist_perf_pkg_pod_dist_01_cd_brand_cd_idx (cost=0.00..5.17 rows=118 width=0) (actual time=0.027..0.027 rows=119 loops=1)

  • Index Cond: ((dist_01_cd)::text = '2959017'::text)
33. 3.094 3.094 ↑ 1.0 1 119

Index Scan using bc_dist_dma_total_eff_pods_by_dma_cd_dma_dsc_master_pkg_sku_idx on bc_dist_dma_total_eff_pods_by_sku_premise_mv (cost=0.41..7.99 rows=1 width=606) (actual time=0.024..0.026 rows=1 loops=119)

  • Index Cond: (((d.ult_parent_cd)::text = (dma_cd)::text) AND ((d.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text) AND ((d.premise_type_dsc)::text = (premise_type_dsc)::text))
34.          

SubPlan (forHashAggregate)

35. 0.044 0.110 ↑ 5.0 1 11

Bitmap Heap Scan on brands_mv (cost=4.32..11.31 rows=5 width=418) (actual time=0.010..0.010 rows=1 loops=11)

  • Recheck Cond: ((podt.brand_cd)::text = (brand_cd)::text)
  • Heap Blocks: exact=11
36. 0.066 0.066 ↑ 5.0 1 11

Bitmap Index Scan on brands_mv_brand_cd_brand_dsc_idx (cost=0.00..4.31 rows=5 width=0) (actual time=0.006..0.006 rows=1 loops=11)

  • Index Cond: ((podt.brand_cd)::text = (brand_cd)::text)
37. 0.022 0.055 ↑ 5.0 1 11

Bitmap Heap Scan on brands_mv brands_mv_1 (cost=4.32..11.31 rows=5 width=418) (actual time=0.005..0.005 rows=1 loops=11)

  • Recheck Cond: ((podt.brand_cd)::text = (brand_cd)::text)
  • Heap Blocks: exact=11
38. 0.033 0.033 ↑ 5.0 1 11

Bitmap Index Scan on brands_mv_brand_cd_brand_dsc_idx (cost=0.00..4.31 rows=5 width=0) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: ((podt.brand_cd)::text = (brand_cd)::text)
39. 0.022 851.851 ↑ 24.0 1 11

Subquery Scan on dist (cost=31,260.33..62,535.73 rows=24 width=32) (actual time=77.441..77.441 rows=1 loops=11)

40. 0.132 851.829 ↑ 24.0 1 11

GroupAggregate (cost=31,260.33..62,535.49 rows=24 width=188) (actual time=77.439..77.439 rows=1 loops=11)

  • Group Key: agg_2.entity_01_cd, ((agg_2.brand_cd)::character varying(30))
41. 0.033 851.697 ↑ 12.0 2 11

Append (cost=31,260.33..62,535.01 rows=24 width=188) (actual time=38.763..77.427 rows=2 loops=11)

42. 0.748 426.283 ↑ 12.0 1 11

GroupAggregate (cost=31,260.33..31,266.89 rows=12 width=270) (actual time=38.752..38.753 rows=1 loops=11)

  • Group Key: agg_2.entity_01_cd, (agg_2.brand_cd)::character varying(30)
43. 0.439 425.535 ↑ 6.0 2 11

Merge Left Join (cost=31,260.33..31,265.45 rows=12 width=270) (actual time=38.678..38.685 rows=2 loops=11)

  • Merge Cond: (((agg_2.ult_parent_no)::text = (bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv.ult_parent_cd)::text) AND ((((agg_2.premise_type_dsc)::character varying(200))::text) = (bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv.premise_type_dsc)::text))
  • Join Filter: (((agg_2.brand_cd)::character varying(30))::text = (bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv.brand_cd)::text)
44. 0.110 371.206 ↑ 6.0 2 11

Sort (cost=31,148.38..31,148.41 rows=12 width=316) (actual time=33.745..33.746 rows=2 loops=11)

  • Sort Key: agg_2.ult_parent_no, (((agg_2.premise_type_dsc)::character varying(200))::text)
  • Sort Method: quicksort Memory: 25kB
45. 0.044 371.096 ↑ 6.0 2 11

Subquery Scan on agg_2 (cost=31,147.93..31,148.17 rows=12 width=316) (actual time=33.734..33.736 rows=2 loops=11)

46. 0.209 371.052 ↑ 6.0 2 11

HashAggregate (cost=31,147.93..31,148.05 rows=12 width=1,489) (actual time=33.731..33.732 rows=2 loops=11)

  • Group Key: d_entity_2018_12_27_07_01_12_6.entity_01_cd, d_entity_2018_12_27_07_01_12_6.entity_01_dsc, d_entity_2018_12_27_07_01_12_6.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_6.city_dsc, d_entity_2018_12_27_07_01_12_6.state_cd, d_entity_2018_12_27_07_01_12_6.postal_cd, d_entity_2018_12_27_07_01_12_6.ult_parent_no, d_entity_2018_12_27_07_01_12_6.ult_parent_dsc, d_entity_2018_12_27_07_01_12_6.latitude, d_entity_2018_12_27_07_01_12_6.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_6.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_6.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_12.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_12.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_12.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_6.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_12.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_6.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_12.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_12.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_6.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_12.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_12.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_6.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_6.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_12.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_12.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_12.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_6.depl_r365_eqv_qty_ty * 0.6) > (ee_4.depl_r365_eqv_qty_ty_rk - ee_4.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_6.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_6.depl_r365_eqv_qty_ty * 0.2)) > (ee_4.depl_r365_eqv_qty_ty_rk - ee_4.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_12.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_12.sim_distribution_cmip_ly
47. 0.033 370.843 ↑ 6.0 2 11

Append (cost=14,926.86..31,145.80 rows=12 width=1,489) (actual time=21.127..33.713 rows=2 loops=11)

48. 0.147 370.502 ↑ 4.5 2 11

Nested Loop Left Join (cost=14,926.86..15,596.36 rows=9 width=1,489) (actual time=21.123..33.682 rows=2 loops=11)

  • Join Filter: (((ee_4.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_12.entity_01_cd)::text) AND ((ee_4.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_12.entity_01_cd)::text))
49. 0.043 275.374 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..603.23 rows=1 width=1,493) (actual time=13.087..25.034 rows=2 loops=11)

50. 0.050 1.199 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..94.74 rows=1 width=1,486) (actual time=0.054..0.109 rows=2 loops=11)

51. 0.058 1.111 ↓ 2.0 2 11

Nested Loop (cost=2.41..92.79 rows=1 width=1,464) (actual time=0.049..0.101 rows=2 loops=11)

52. 0.241 0.825 ↓ 2.0 2 11

Hash Left Join (cost=2.12..83.45 rows=1 width=1,336) (actual time=0.037..0.075 rows=2 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_12.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_6.brand_cd)::text)
  • Filter: ((podt.brand_cd)::text = ((COALESCE(d_item_brand_2018_12_27_07_07_56_6.brand_cd, 'TOTAL'::character varying))::character varying(30))::text)
  • Rows Removed by Filter: 17
53. 0.561 0.561 ↓ 1.1 19 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_12 (cost=0.56..81.55 rows=18 width=308) (actual time=0.019..0.051 rows=19 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
54. 0.012 0.023 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.023..0.023 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
55. 0.011 0.011 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_6 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.003..0.011 rows=25 loops=1)

56. 0.076 0.228 ↑ 1.0 1 19

Nested Loop (cost=0.29..9.33 rows=1 width=128) (actual time=0.010..0.012 rows=1 loops=19)

57. 0.019 0.019 ↑ 1.0 1 19

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_6 (cost=0.00..1.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=19)

58. 0.133 0.133 ↑ 1.0 1 19

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_6 (cost=0.29..8.31 rows=1 width=96) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
59. 0.038 0.038 ↓ 0.0 0 19

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_6 (cost=0.14..1.94 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=19)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_12.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
60. 0.019 274.132 ↓ 0.0 0 19

Subquery Scan on p_6 (cost=0.00..508.48 rows=1 width=7) (actual time=14.428..14.428 rows=0 loops=19)

  • Filter: (((p_6.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_12.entity_01_cd)::text = (p_6.entity_01_cd)::text))
61. 0.019 274.113 ↓ 0.0 0 19

Limit (cost=0.00..508.46 rows=1 width=7) (actual time=14.427..14.427 rows=0 loops=19)

62. 274.094 274.094 ↓ 0.0 0 19

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_6 (cost=0.00..6,101.56 rows=12 width=7) (actual time=14.426..14.426 rows=0 loops=19)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_12.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
  • Rows Removed by Filter: 104475
63. 0.076 94.981 ↑ 12.0 1 19

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (actual time=4.653..4.999 rows=1 loops=19)

64. 0.190 0.190 ↑ 4.0 1 19

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_6 (cost=0.42..20.51 rows=4 width=6) (actual time=0.009..0.010 rows=1 loops=19)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
65. 0.035 94.715 ↑ 3.0 1 19

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (actual time=4.640..4.985 rows=1 loops=19)

66. 0.070 94.680 ↑ 3.0 1 10

Result (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.814..9.468 rows=1 loops=10)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
67. 1.260 94.610 ↑ 3.0 1 10

Subquery Scan on ee_4 (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.808..9.461 rows=1 loops=10)

  • Filter: ((ee_4.entity_01_cd)::text = (podt.dist_01_cd)::text)
  • Rows Removed by Filter: 504
68. 4.800 93.350 ↑ 3.2 505 10

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (actual time=8.755..9.335 rows=505 loops=10)

69. 5.700 88.550 ↑ 3.2 505 10

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (actual time=8.748..8.855 rows=505 loops=10)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_13.depl_r365_eqv_qty_ty
  • Sort Method: quicksort Memory: 62kB
70. 2.500 82.850 ↑ 3.2 505 10

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_13 (cost=8,778.48..14,838.62 rows=1,602 width=13) (actual time=8.046..8.285 rows=505 loops=10)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
  • Heap Blocks: exact=840
71. 80.350 80.350 ↑ 3.2 505 10

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (actual time=8.035..8.035 rows=505 loops=10)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
72. 0.011 0.308 ↓ 0.0 0 11

Nested Loop Left Join (cost=14,926.86..15,549.32 rows=3 width=1,489) (actual time=0.028..0.028 rows=0 loops=11)

  • Join Filter: (((ee_5.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_14.entity_01_cd)::text) AND ((ee_5.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_14.entity_01_cd)::text))
73. 0.000 0.297 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..556.41 rows=1 width=1,493) (actual time=0.027..0.027 rows=0 loops=11)

74. 0.022 0.297 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..47.92 rows=1 width=1,486) (actual time=0.027..0.027 rows=0 loops=11)

75. 0.011 0.275 ↓ 0.0 0 11

Nested Loop (cost=2.41..43.75 rows=1 width=1,464) (actual time=0.025..0.025 rows=0 loops=11)

76. 0.059 0.264 ↓ 0.0 0 11

Hash Left Join (cost=2.12..34.41 rows=1 width=1,336) (actual time=0.024..0.024 rows=0 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_14.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_7.brand_cd)::text)
  • Filter: ((podt.brand_cd)::text = ((COALESCE(d_item_brand_2018_12_27_07_07_56_7.brand_cd, 'TOTAL'::character varying))::character varying(30))::text)
  • Rows Removed by Filter: 3
77. 0.187 0.187 ↑ 2.0 3 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_14 (cost=0.56..32.74 rows=6 width=308) (actual time=0.015..0.017 rows=3 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: ((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[]))
  • Rows Removed by Filter: 1
78. 0.010 0.018 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.018..0.018 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
79. 0.008 0.008 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_7 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.002..0.008 rows=25 loops=1)

80. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..9.33 rows=1 width=128) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_7 (cost=0.00..1.01 rows=1 width=32) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_7 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
83. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_7 (cost=0.14..4.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_14.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
84. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_7 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_7.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_14.entity_01_cd)::text = (p_7.entity_01_cd)::text))
85. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_7 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_14.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
87. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_7 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
89. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
91. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_5 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_5.entity_01_cd)::text = (podt.dist_01_cd)::text)
92. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_15.depl_r365_eqv_qty_ty
94. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_15 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
95. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
96. 0.350 53.890 ↑ 3.4 64 10

Materialize (cost=111.95..115.25 rows=220 width=52) (actual time=5.345..5.389 rows=64 loops=10)

97. 8.870 53.540 ↑ 3.4 64 10

Sort (cost=111.95..112.50 rows=220 width=139) (actual time=5.341..5.354 rows=64 loops=10)

  • Sort Key: bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv.ult_parent_cd, bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv.premise_type_dsc
  • Sort Method: quicksort Memory: 408kB
98. 44.270 44.670 ↓ 1.6 359 10

Bitmap Heap Scan on bc_dist_average_simp_pods_by_premise_brand_dma_cd_mv (cost=5.99..103.39 rows=220 width=139) (actual time=0.075..4.467 rows=359 loops=10)

  • Recheck Cond: ((brand_cd)::text = (podt.brand_cd)::text)
  • Heap Blocks: exact=911
99. 0.400 0.400 ↓ 1.6 359 10

Bitmap Index Scan on bc_dist_average_simp_pods_by_premise_brand_dma_cd__brand_cd_idx (cost=0.00..5.93 rows=220 width=0) (actual time=0.040..0.040 rows=359 loops=10)

  • Index Cond: ((brand_cd)::text = (podt.brand_cd)::text)
100. 0.781 425.381 ↑ 12.0 1 11

GroupAggregate (cost=31,261.33..31,267.89 rows=12 width=270) (actual time=38.670..38.671 rows=1 loops=11)

  • Group Key: agg_3.entity_01_cd, (agg_3.brand_cd)::character varying(30)
101. 0.416 424.600 ↑ 6.0 2 11

Merge Left Join (cost=31,261.33..31,266.45 rows=12 width=270) (actual time=38.592..38.600 rows=2 loops=11)

  • Merge Cond: (((agg_3.ult_parent_no)::text = (bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv.ult_parent_cd)::text) AND ((((agg_3.premise_type_dsc)::character varying(200))::text) = (bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv.premise_type_dsc)::text))
  • Join Filter: (((agg_3.brand_cd)::character varying(30))::text = (bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv.brand_cd)::text)
102. 0.099 370.194 ↑ 6.0 2 11

Sort (cost=31,148.38..31,148.41 rows=12 width=316) (actual time=33.653..33.654 rows=2 loops=11)

  • Sort Key: agg_3.ult_parent_no, (((agg_3.premise_type_dsc)::character varying(200))::text)
  • Sort Method: quicksort Memory: 25kB
103. 0.055 370.095 ↑ 6.0 2 11

Subquery Scan on agg_3 (cost=31,147.93..31,148.17 rows=12 width=316) (actual time=33.643..33.645 rows=2 loops=11)

104. 0.187 370.040 ↑ 6.0 2 11

HashAggregate (cost=31,147.93..31,148.05 rows=12 width=1,489) (actual time=33.639..33.640 rows=2 loops=11)

  • Group Key: d_entity_2018_12_27_07_01_12_8.entity_01_cd, d_entity_2018_12_27_07_01_12_8.entity_01_dsc, d_entity_2018_12_27_07_01_12_8.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_8.city_dsc, d_entity_2018_12_27_07_01_12_8.state_cd, d_entity_2018_12_27_07_01_12_8.postal_cd, d_entity_2018_12_27_07_01_12_8.ult_parent_no, d_entity_2018_12_27_07_01_12_8.ult_parent_dsc, d_entity_2018_12_27_07_01_12_8.latitude, d_entity_2018_12_27_07_01_12_8.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_8.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_8.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_16.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_16.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_16.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_8.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_16.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_8.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_16.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_16.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_8.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_16.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_16.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_8.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_8.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_16.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_16.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_16.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_8.depl_r365_eqv_qty_ty * 0.6) > (ee_6.depl_r365_eqv_qty_ty_rk - ee_6.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_8.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_8.depl_r365_eqv_qty_ty * 0.2)) > (ee_6.depl_r365_eqv_qty_ty_rk - ee_6.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_16.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_16.sim_distribution_cmip_ly
105. 0.044 369.853 ↑ 6.0 2 11

Append (cost=14,926.86..31,145.80 rows=12 width=1,489) (actual time=21.071..33.623 rows=2 loops=11)

106. 0.138 369.490 ↑ 4.5 2 11

Nested Loop Left Join (cost=14,926.86..15,596.36 rows=9 width=1,489) (actual time=21.068..33.590 rows=2 loops=11)

  • Join Filter: (((ee_6.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_16.entity_01_cd)::text) AND ((ee_6.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_16.entity_01_cd)::text))
107. 0.074 275.682 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..603.23 rows=1 width=1,493) (actual time=13.152..25.062 rows=2 loops=11)

108. 0.050 1.210 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..94.74 rows=1 width=1,486) (actual time=0.056..0.110 rows=2 loops=11)

109. 0.058 1.122 ↓ 2.0 2 11

Nested Loop (cost=2.41..92.79 rows=1 width=1,464) (actual time=0.051..0.102 rows=2 loops=11)

110. 0.245 0.836 ↓ 2.0 2 11

Hash Left Join (cost=2.12..83.45 rows=1 width=1,336) (actual time=0.038..0.076 rows=2 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_16.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_8.brand_cd)::text)
  • Filter: ((podt.brand_cd)::text = ((COALESCE(d_item_brand_2018_12_27_07_07_56_8.brand_cd, 'TOTAL'::character varying))::character varying(30))::text)
  • Rows Removed by Filter: 17
111. 0.572 0.572 ↓ 1.1 19 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_16 (cost=0.56..81.55 rows=18 width=308) (actual time=0.020..0.052 rows=19 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
112. 0.010 0.019 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.019..0.019 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
113. 0.009 0.009 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_8 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.001..0.009 rows=25 loops=1)

114. 0.076 0.228 ↑ 1.0 1 19

Nested Loop (cost=0.29..9.33 rows=1 width=128) (actual time=0.010..0.012 rows=1 loops=19)

115. 0.019 0.019 ↑ 1.0 1 19

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_8 (cost=0.00..1.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=19)

116. 0.133 0.133 ↑ 1.0 1 19

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_8 (cost=0.29..8.31 rows=1 width=96) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
117. 0.038 0.038 ↓ 0.0 0 19

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_8 (cost=0.14..1.94 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=19)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_16.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
118. 0.000 274.398 ↓ 0.0 0 19

Subquery Scan on p_8 (cost=0.00..508.48 rows=1 width=7) (actual time=14.442..14.442 rows=0 loops=19)

  • Filter: (((p_8.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_16.entity_01_cd)::text = (p_8.entity_01_cd)::text))
119. 0.038 274.398 ↓ 0.0 0 19

Limit (cost=0.00..508.46 rows=1 width=7) (actual time=14.442..14.442 rows=0 loops=19)

120. 274.360 274.360 ↓ 0.0 0 19

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_8 (cost=0.00..6,101.56 rows=12 width=7) (actual time=14.440..14.440 rows=0 loops=19)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_16.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
  • Rows Removed by Filter: 104475
121. 0.076 93.670 ↑ 12.0 1 19

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (actual time=4.584..4.930 rows=1 loops=19)

122. 0.190 0.190 ↑ 4.0 1 19

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_8 (cost=0.42..20.51 rows=4 width=6) (actual time=0.009..0.010 rows=1 loops=19)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
123. 0.034 93.404 ↑ 3.0 1 19

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (actual time=4.570..4.916 rows=1 loops=19)

124. 0.060 93.370 ↑ 3.0 1 10

Result (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.682..9.337 rows=1 loops=10)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
125. 1.230 93.310 ↑ 3.0 1 10

Subquery Scan on ee_6 (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.676..9.331 rows=1 loops=10)

  • Filter: ((ee_6.entity_01_cd)::text = (podt.dist_01_cd)::text)
  • Rows Removed by Filter: 504
126. 4.860 92.080 ↑ 3.2 505 10

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (actual time=8.622..9.208 rows=505 loops=10)

127. 5.750 87.220 ↑ 3.2 505 10

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (actual time=8.616..8.722 rows=505 loops=10)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_17.depl_r365_eqv_qty_ty
  • Sort Method: quicksort Memory: 62kB
128. 2.380 81.470 ↑ 3.2 505 10

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_17 (cost=8,778.48..14,838.62 rows=1,602 width=13) (actual time=7.919..8.147 rows=505 loops=10)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
  • Heap Blocks: exact=840
129. 79.090 79.090 ↑ 3.2 505 10

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (actual time=7.909..7.909 rows=505 loops=10)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
130. 0.011 0.319 ↓ 0.0 0 11

Nested Loop Left Join (cost=14,926.86..15,549.32 rows=3 width=1,489) (actual time=0.029..0.029 rows=0 loops=11)

  • Join Filter: (((ee_7.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_18.entity_01_cd)::text) AND ((ee_7.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_18.entity_01_cd)::text))
131. 0.011 0.308 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..556.41 rows=1 width=1,493) (actual time=0.028..0.028 rows=0 loops=11)

132. 0.011 0.297 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..47.92 rows=1 width=1,486) (actual time=0.027..0.027 rows=0 loops=11)

133. 0.011 0.286 ↓ 0.0 0 11

Nested Loop (cost=2.41..43.75 rows=1 width=1,464) (actual time=0.026..0.026 rows=0 loops=11)

134. 0.067 0.275 ↓ 0.0 0 11

Hash Left Join (cost=2.12..34.41 rows=1 width=1,336) (actual time=0.025..0.025 rows=0 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_18.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_9.brand_cd)::text)
  • Filter: ((podt.brand_cd)::text = ((COALESCE(d_item_brand_2018_12_27_07_07_56_9.brand_cd, 'TOTAL'::character varying))::character varying(30))::text)
  • Rows Removed by Filter: 3
135. 0.187 0.187 ↑ 2.0 3 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_18 (cost=0.56..32.74 rows=6 width=308) (actual time=0.015..0.017 rows=3 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: ((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[]))
  • Rows Removed by Filter: 1
136. 0.011 0.021 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.021..0.021 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
137. 0.010 0.010 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_9 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.003..0.010 rows=25 loops=1)

138. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..9.33 rows=1 width=128) (never executed)

139. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_9 (cost=0.00..1.01 rows=1 width=32) (never executed)

140. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_9 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
141. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_9 (cost=0.14..4.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_18.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
142. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_9 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_9.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_18.entity_01_cd)::text = (p_9.entity_01_cd)::text))
143. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

144. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_9 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_18.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
145. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

146. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_9 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
147. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

148. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
149. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_7 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_7.entity_01_cd)::text = (podt.dist_01_cd)::text)
150. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_19.depl_r365_eqv_qty_ty
152. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_19 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
153. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
154. 0.340 53.990 ↑ 3.4 64 10

Materialize (cost=112.95..116.25 rows=220 width=52) (actual time=5.355..5.399 rows=64 loops=10)

155. 8.780 53.650 ↑ 3.4 64 10

Sort (cost=112.95..113.50 rows=220 width=139) (actual time=5.352..5.365 rows=64 loops=10)

  • Sort Key: bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv.ult_parent_cd, bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv.premise_type_dsc
  • Sort Method: quicksort Memory: 408kB
156. 44.480 44.870 ↓ 1.6 359 10

Bitmap Heap Scan on bc_dist_average_eff_pods_by_premise_brand_dma_cd_mv (cost=5.99..104.39 rows=220 width=139) (actual time=0.075..4.487 rows=359 loops=10)

  • Recheck Cond: ((brand_cd)::text = (podt.brand_cd)::text)
  • Heap Blocks: exact=912
157. 0.390 0.390 ↓ 1.6 359 10

Bitmap Index Scan on bc_dist_average_eff_pods_by_premise_brand_dma_cd_m_brand_cd_idx (cost=0.00..5.93 rows=220 width=0) (actual time=0.039..0.039 rows=359 loops=10)

  • Index Cond: ((brand_cd)::text = (podt.brand_cd)::text)
158. 0.022 745.888 ↑ 17.0 1 11

Subquery Scan on brand_perf (cost=31,147.82..62,378.81 rows=17 width=32) (actual time=67.807..67.808 rows=1 loops=11)

159. 0.253 745.866 ↑ 17.0 1 11

GroupAggregate (cost=31,147.82..62,378.64 rows=17 width=188) (actual time=67.806..67.806 rows=1 loops=11)

  • Group Key: agg_4.entity_01_cd, ((agg_4.brand_cd)::character varying(30))
160. 0.044 745.613 ↑ 8.5 2 11

Append (cost=31,147.82..62,378.17 rows=17 width=188) (actual time=33.830..67.783 rows=2 loops=11)

161. 0.363 372.031 ↑ 11.0 1 11

GroupAggregate (cost=31,147.82..31,149.39 rows=11 width=478) (actual time=33.821..33.821 rows=1 loops=11)

  • Group Key: agg_4.entity_01_cd, (agg_4.brand_cd)::character varying(30)
162. 0.055 371.668 ↑ 5.5 2 11

Subquery Scan on agg_4 (cost=31,147.82..31,148.07 rows=11 width=478) (actual time=33.786..33.788 rows=2 loops=11)

163. 0.198 371.613 ↑ 5.5 2 11

HashAggregate (cost=31,147.82..31,147.93 rows=11 width=1,489) (actual time=33.782..33.783 rows=2 loops=11)

  • Group Key: d_entity_2018_12_27_07_01_12_10.entity_01_cd, d_entity_2018_12_27_07_01_12_10.entity_01_dsc, d_entity_2018_12_27_07_01_12_10.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_10.city_dsc, d_entity_2018_12_27_07_01_12_10.state_cd, d_entity_2018_12_27_07_01_12_10.postal_cd, d_entity_2018_12_27_07_01_12_10.ult_parent_no, d_entity_2018_12_27_07_01_12_10.ult_parent_dsc, d_entity_2018_12_27_07_01_12_10.latitude, d_entity_2018_12_27_07_01_12_10.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_10.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_10.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_20.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_20.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_20.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_10.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_20.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_10.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_20.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_20.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_10.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_20.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_20.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_10.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_10.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_20.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_20.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_20.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_10.depl_r365_eqv_qty_ty * 0.6) > (ee_8.depl_r365_eqv_qty_ty_rk - ee_8.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_10.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_10.depl_r365_eqv_qty_ty * 0.2)) > (ee_8.depl_r365_eqv_qty_ty_rk - ee_8.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_20.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_20.sim_distribution_cmip_ly
164. 0.033 371.415 ↑ 5.5 2 11

Append (cost=14,926.86..31,145.87 rows=11 width=1,489) (actual time=21.333..33.765 rows=2 loops=11)

165. 0.159 371.052 ↑ 4.0 2 11

Nested Loop Left Join (cost=14,926.86..15,596.41 rows=8 width=1,489) (actual time=21.330..33.732 rows=2 loops=11)

  • Join Filter: (((ee_8.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_20.entity_01_cd)::text) AND ((ee_8.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_20.entity_01_cd)::text))
166. 0.048 278.135 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..603.32 rows=1 width=1,493) (actual time=13.493..25.285 rows=2 loops=11)

167. 0.061 1.276 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..94.83 rows=1 width=1,486) (actual time=0.059..0.116 rows=2 loops=11)

168. 0.080 1.177 ↓ 2.0 2 11

Nested Loop (cost=2.41..92.88 rows=1 width=1,464) (actual time=0.054..0.107 rows=2 loops=11)

169. 0.287 0.869 ↓ 2.0 2 11

Hash Left Join (cost=2.12..83.54 rows=1 width=1,336) (actual time=0.040..0.079 rows=2 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_20.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_10.brand_cd)::text)
  • Filter: ((((COALESCE(d_item_brand_2018_12_27_07_07_56_10.brand_cd, 'TOTAL'::character varying))::character varying(30))::text <> 'TOTAL'::text) AND (((COALESCE(d_item_brand_2018_12_27_07_07_56_10.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = (podt.brand_cd)::text))
  • Rows Removed by Filter: 17
170. 0.561 0.561 ↓ 1.1 19 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_20 (cost=0.56..81.55 rows=18 width=308) (actual time=0.020..0.051 rows=19 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
171. 0.011 0.021 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.021..0.021 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
172. 0.010 0.010 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_10 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.002..0.010 rows=25 loops=1)

173. 0.057 0.228 ↑ 1.0 1 19

Nested Loop (cost=0.29..9.33 rows=1 width=128) (actual time=0.010..0.012 rows=1 loops=19)

174. 0.019 0.019 ↑ 1.0 1 19

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_10 (cost=0.00..1.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=19)

175. 0.152 0.152 ↑ 1.0 1 19

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_10 (cost=0.29..8.31 rows=1 width=96) (actual time=0.007..0.008 rows=1 loops=19)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
176. 0.038 0.038 ↓ 0.0 0 19

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_10 (cost=0.14..1.94 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=19)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_20.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
177. 0.019 276.811 ↓ 0.0 0 19

Subquery Scan on p_10 (cost=0.00..508.48 rows=1 width=7) (actual time=14.569..14.569 rows=0 loops=19)

  • Filter: (((p_10.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_20.entity_01_cd)::text = (p_10.entity_01_cd)::text))
178. 0.019 276.792 ↓ 0.0 0 19

Limit (cost=0.00..508.46 rows=1 width=7) (actual time=14.568..14.568 rows=0 loops=19)

179. 276.773 276.773 ↓ 0.0 0 19

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_10 (cost=0.00..6,101.56 rows=12 width=7) (actual time=14.567..14.567 rows=0 loops=19)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_20.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
  • Rows Removed by Filter: 104475
180. 0.095 92.758 ↑ 12.0 1 19

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (actual time=4.538..4.882 rows=1 loops=19)

181. 0.190 0.190 ↑ 4.0 1 19

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_10 (cost=0.42..20.51 rows=4 width=6) (actual time=0.009..0.010 rows=1 loops=19)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
182. 0.023 92.473 ↑ 3.0 1 19

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (actual time=4.525..4.867 rows=1 loops=19)

183. 0.060 92.450 ↑ 3.0 1 10

Result (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.595..9.245 rows=1 loops=10)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
184. 1.270 92.390 ↑ 3.0 1 10

Subquery Scan on ee_8 (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.588..9.239 rows=1 loops=10)

  • Filter: ((ee_8.entity_01_cd)::text = (podt.dist_01_cd)::text)
  • Rows Removed by Filter: 504
185. 4.730 91.120 ↑ 3.2 505 10

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (actual time=8.536..9.112 rows=505 loops=10)

186. 5.750 86.390 ↑ 3.2 505 10

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (actual time=8.529..8.639 rows=505 loops=10)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_21.depl_r365_eqv_qty_ty
  • Sort Method: quicksort Memory: 62kB
187. 2.390 80.640 ↑ 3.2 505 10

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_21 (cost=8,778.48..14,838.62 rows=1,602 width=13) (actual time=7.835..8.064 rows=505 loops=10)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
  • Heap Blocks: exact=840
188. 78.250 78.250 ↑ 3.2 505 10

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (actual time=7.825..7.825 rows=505 loops=10)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
189. 0.022 0.330 ↓ 0.0 0 11

Nested Loop Left Join (cost=14,926.86..15,549.35 rows=3 width=1,489) (actual time=0.030..0.030 rows=0 loops=11)

  • Join Filter: (((ee_9.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_22.entity_01_cd)::text) AND ((ee_9.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_22.entity_01_cd)::text))
190. 0.011 0.308 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..556.44 rows=1 width=1,493) (actual time=0.028..0.028 rows=0 loops=11)

191. 0.011 0.297 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..47.95 rows=1 width=1,486) (actual time=0.027..0.027 rows=0 loops=11)

192. 0.011 0.286 ↓ 0.0 0 11

Nested Loop (cost=2.41..43.78 rows=1 width=1,464) (actual time=0.026..0.026 rows=0 loops=11)

193. 0.067 0.275 ↓ 0.0 0 11

Hash Left Join (cost=2.12..34.44 rows=1 width=1,336) (actual time=0.025..0.025 rows=0 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_22.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_11.brand_cd)::text)
  • Filter: ((((COALESCE(d_item_brand_2018_12_27_07_07_56_11.brand_cd, 'TOTAL'::character varying))::character varying(30))::text <> 'TOTAL'::text) AND (((COALESCE(d_item_brand_2018_12_27_07_07_56_11.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = (podt.brand_cd)::text))
  • Rows Removed by Filter: 3
194. 0.187 0.187 ↑ 2.0 3 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_22 (cost=0.56..32.74 rows=6 width=308) (actual time=0.015..0.017 rows=3 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: ((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[]))
  • Rows Removed by Filter: 1
195. 0.013 0.021 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.021..0.021 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
196. 0.008 0.008 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_11 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.001..0.008 rows=25 loops=1)

197. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..9.33 rows=1 width=128) (never executed)

198. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_11 (cost=0.00..1.01 rows=1 width=32) (never executed)

199. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_11 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
200. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_11 (cost=0.14..4.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_22.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
201. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_11 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_11.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_22.entity_01_cd)::text = (p_11.entity_01_cd)::text))
202. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

203. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_11 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_22.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
204. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

205. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_11 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
206. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

207. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
208. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_9 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_9.entity_01_cd)::text = (podt.dist_01_cd)::text)
209. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

210. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_23.depl_r365_eqv_qty_ty
211. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_23 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
212. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
213. 0.143 373.538 ↑ 6.0 1 11

GroupAggregate (cost=31,148.10..31,228.61 rows=6 width=142) (actual time=33.958..33.958 rows=1 loops=11)

  • Group Key: d_entity_2018_12_27_07_01_12_12.entity_01_cd, ((COALESCE(d_item_brand_2018_12_27_07_07_56_12.brand_cd, 'TOTAL'::character varying)))::character varying(30)
214. 0.079 373.395 ↑ 3.0 2 11

Nested Loop (cost=31,148.10..31,228.46 rows=6 width=142) (actual time=33.937..33.945 rows=2 loops=11)

215. 0.198 373.164 ↑ 5.5 2 11

HashAggregate (cost=31,147.82..31,147.93 rows=11 width=1,489) (actual time=33.923..33.924 rows=2 loops=11)

  • Group Key: d_entity_2018_12_27_07_01_12_12.entity_01_cd, d_entity_2018_12_27_07_01_12_12.entity_01_dsc, d_entity_2018_12_27_07_01_12_12.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_12.city_dsc, d_entity_2018_12_27_07_01_12_12.state_cd, d_entity_2018_12_27_07_01_12_12.postal_cd, d_entity_2018_12_27_07_01_12_12.ult_parent_no, d_entity_2018_12_27_07_01_12_12.ult_parent_dsc, d_entity_2018_12_27_07_01_12_12.latitude, d_entity_2018_12_27_07_01_12_12.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_12.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_12.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_24.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_24.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_24.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_12.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_24.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_12.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_24.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_24.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_12.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_24.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_24.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_12.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_12.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_24.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_24.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_24.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_12.depl_r365_eqv_qty_ty * 0.6) > (ee_10.depl_r365_eqv_qty_ty_rk - ee_10.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_12.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_12.depl_r365_eqv_qty_ty * 0.2)) > (ee_10.depl_r365_eqv_qty_ty_rk - ee_10.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_24.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_24.sim_distribution_cmip_ly
216. 0.033 372.966 ↑ 5.5 2 11

Append (cost=14,926.86..31,145.87 rows=11 width=1,489) (actual time=21.158..33.906 rows=2 loops=11)

217. 0.158 372.614 ↑ 4.0 2 11

Nested Loop Left Join (cost=14,926.86..15,596.41 rows=8 width=1,489) (actual time=21.155..33.874 rows=2 loops=11)

  • Join Filter: (((ee_10.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_24.entity_01_cd)::text) AND ((ee_10.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_24.entity_01_cd)::text))
218. 0.059 278.520 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..603.32 rows=1 width=1,493) (actual time=13.215..25.320 rows=2 loops=11)

219. 0.050 1.232 ↓ 2.0 2 11

Nested Loop Left Join (cost=2.55..94.83 rows=1 width=1,486) (actual time=0.055..0.112 rows=2 loops=11)

220. 0.069 1.144 ↓ 2.0 2 11

Nested Loop (cost=2.41..92.88 rows=1 width=1,464) (actual time=0.050..0.104 rows=2 loops=11)

221. 0.255 0.847 ↓ 2.0 2 11

Hash Left Join (cost=2.12..83.54 rows=1 width=1,336) (actual time=0.037..0.077 rows=2 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_24.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_12.brand_cd)::text)
  • Filter: ((((COALESCE(d_item_brand_2018_12_27_07_07_56_12.brand_cd, 'TOTAL'::character varying))::character varying(30))::text <> 'TOTAL'::text) AND (((COALESCE(d_item_brand_2018_12_27_07_07_56_12.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = (podt.brand_cd)::text))
  • Rows Removed by Filter: 17
222. 0.572 0.572 ↓ 1.1 19 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_24 (cost=0.56..81.55 rows=18 width=308) (actual time=0.018..0.052 rows=19 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
223. 0.011 0.020 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.020..0.020 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
224. 0.009 0.009 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_12 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.001..0.009 rows=25 loops=1)

225. 0.076 0.228 ↑ 1.0 1 19

Nested Loop (cost=0.29..9.33 rows=1 width=128) (actual time=0.010..0.012 rows=1 loops=19)

226. 0.019 0.019 ↑ 1.0 1 19

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_12 (cost=0.00..1.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=19)

227. 0.133 0.133 ↑ 1.0 1 19

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_12 (cost=0.29..8.31 rows=1 width=96) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
228. 0.038 0.038 ↓ 0.0 0 19

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_12 (cost=0.14..1.94 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=19)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_24.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
229. 0.019 277.229 ↓ 0.0 0 19

Subquery Scan on p_12 (cost=0.00..508.48 rows=1 width=7) (actual time=14.591..14.591 rows=0 loops=19)

  • Filter: (((p_12.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_24.entity_01_cd)::text = (p_12.entity_01_cd)::text))
230. 0.019 277.210 ↓ 0.0 0 19

Limit (cost=0.00..508.46 rows=1 width=7) (actual time=14.590..14.590 rows=0 loops=19)

231. 277.191 277.191 ↓ 0.0 0 19

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_12 (cost=0.00..6,101.56 rows=12 width=7) (actual time=14.589..14.589 rows=0 loops=19)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_24.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
  • Rows Removed by Filter: 104475
232. 0.057 93.936 ↑ 12.0 1 19

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (actual time=4.598..4.944 rows=1 loops=19)

233. 0.190 0.190 ↑ 4.0 1 19

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_12 (cost=0.42..20.51 rows=4 width=6) (actual time=0.009..0.010 rows=1 loops=19)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
234. 0.039 93.689 ↑ 3.0 1 19

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (actual time=4.585..4.931 rows=1 loops=19)

235. 0.060 93.650 ↑ 3.0 1 10

Result (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.710..9.365 rows=1 loops=10)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
236. 1.260 93.590 ↑ 3.0 1 10

Subquery Scan on ee_10 (cost=14,923.89..14,971.95 rows=3 width=45) (actual time=8.704..9.359 rows=1 loops=10)

  • Filter: ((ee_10.entity_01_cd)::text = (podt.dist_01_cd)::text)
  • Rows Removed by Filter: 504
237. 4.830 92.330 ↑ 3.2 505 10

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (actual time=8.650..9.233 rows=505 loops=10)

238. 5.790 87.500 ↑ 3.2 505 10

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (actual time=8.644..8.750 rows=505 loops=10)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_25.depl_r365_eqv_qty_ty
  • Sort Method: quicksort Memory: 62kB
239. 2.450 81.710 ↑ 3.2 505 10

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_25 (cost=8,778.48..14,838.62 rows=1,602 width=13) (actual time=7.936..8.171 rows=505 loops=10)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
  • Heap Blocks: exact=840
240. 79.260 79.260 ↑ 3.2 505 10

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (actual time=7.926..7.926 rows=505 loops=10)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
241. 0.011 0.319 ↓ 0.0 0 11

Nested Loop Left Join (cost=14,926.86..15,549.35 rows=3 width=1,489) (actual time=0.029..0.029 rows=0 loops=11)

  • Join Filter: (((ee_11.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_26.entity_01_cd)::text) AND ((ee_11.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_26.entity_01_cd)::text))
242. 0.011 0.308 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..556.44 rows=1 width=1,493) (actual time=0.028..0.028 rows=0 loops=11)

243. 0.022 0.297 ↓ 0.0 0 11

Nested Loop Left Join (cost=2.55..47.95 rows=1 width=1,486) (actual time=0.027..0.027 rows=0 loops=11)

244. 0.011 0.275 ↓ 0.0 0 11

Nested Loop (cost=2.41..43.78 rows=1 width=1,464) (actual time=0.025..0.025 rows=0 loops=11)

245. 0.059 0.264 ↓ 0.0 0 11

Hash Left Join (cost=2.12..34.44 rows=1 width=1,336) (actual time=0.024..0.024 rows=0 loops=11)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_26.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_13.brand_cd)::text)
  • Filter: ((((COALESCE(d_item_brand_2018_12_27_07_07_56_13.brand_cd, 'TOTAL'::character varying))::character varying(30))::text <> 'TOTAL'::text) AND (((COALESCE(d_item_brand_2018_12_27_07_07_56_13.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = (podt.brand_cd)::text))
  • Rows Removed by Filter: 3
246. 0.187 0.187 ↑ 2.0 3 11

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_26 (cost=0.56..32.74 rows=6 width=308) (actual time=0.015..0.017 rows=3 loops=11)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: ((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[]))
  • Rows Removed by Filter: 1
247. 0.010 0.018 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=1,032) (actual time=0.018..0.018 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
248. 0.008 0.008 ↑ 1.0 25 1

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_13 (cost=0.00..1.25 rows=25 width=1,032) (actual time=0.002..0.008 rows=25 loops=1)

249. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..9.33 rows=1 width=128) (never executed)

250. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_13 (cost=0.00..1.01 rows=1 width=32) (never executed)

251. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_13 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
252. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_13 (cost=0.14..4.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_26.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
253. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_13 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_13.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_26.entity_01_cd)::text = (p_13.entity_01_cd)::text))
254. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

255. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_13 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_26.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
256. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

257. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_13 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
258. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

259. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
260. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_11 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_11.entity_01_cd)::text = (podt.dist_01_cd)::text)
261. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

262. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_27.depl_r365_eqv_qty_ty
263. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_27 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
264. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
265. 0.152 0.152 ↑ 1.0 1 19

Index Scan using dist_performance_measures_dma_ult_parent_cd_ult_parent_dsc_idx1 on dist_performance_measures_dma_totals_vol_by_brand_mv parent_1 (cost=0.28..7.30 rows=1 width=41) (actual time=0.007..0.008 rows=1 loops=19)

  • Index Cond: (((ult_parent_cd)::text = (d_entity_2018_12_27_07_01_12_12.ult_parent_no)::text) AND ((brand_cd)::text = (podt.brand_cd)::text))
266.          

SubPlan (forGroupAggregate)

267. 0.001 0.280 ↓ 0.0 0 1

Subquery Scan on dist_beer_performance_by_dist_vol_vw (cost=31,155.67..163,488.66 rows=4 width=32) (actual time=0.280..0.280 rows=0 loops=1)

268. 0.001 0.279 ↓ 0.0 0 1

GroupAggregate (cost=31,155.67..163,488.62 rows=4 width=110) (actual time=0.279..0.279 rows=0 loops=1)

  • Group Key: agg.entity_01_cd
269. 0.002 0.278 ↓ 0.0 0 1

Append (cost=31,155.67..163,488.52 rows=4 width=110) (actual time=0.278..0.278 rows=0 loops=1)

270. 0.001 0.132 ↓ 0.0 0 1

Subquery Scan on agg (cost=31,155.67..31,156.05 rows=2 width=446) (actual time=0.132..0.132 rows=0 loops=1)

271. 0.001 0.131 ↓ 0.0 0 1

Unique (cost=31,155.67..31,156.03 rows=2 width=1,489) (actual time=0.131..0.131 rows=0 loops=1)

272. 0.036 0.130 ↓ 0.0 0 1

Sort (cost=31,155.67..31,155.67 rows=2 width=1,489) (actual time=0.130..0.130 rows=0 loops=1)

  • Sort Key: d_entity_2018_12_27_07_01_12.entity_01_cd, d_entity_2018_12_27_07_01_12.entity_01_dsc, d_entity_2018_12_27_07_01_12.addr_line_01_dsc, d_entity_2018_12_27_07_01_12.city_dsc, d_entity_2018_12_27_07_01_12.state_cd, d_entity_2018_12_27_07_01_12.postal_cd, d_entity_2018_12_27_07_01_12.ult_parent_no, d_entity_2018_12_27_07_01_12.ult_parent_dsc, d_entity_2018_12_27_07_01_12.latitude, d_entity_2018_12_27_07_01_12.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08.depl_r365_eqv_qty_ty * 0.6) > (ee.depl_r365_eqv_qty_ty_rk - ee.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08.depl_r365_eqv_qty_ty * 0.2)) > (ee.depl_r365_eqv_qty_ty_rk - ee.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05.sim_distribution_cmip_ly
  • Sort Method: quicksort Memory: 25kB
273. 0.001 0.094 ↓ 0.0 0 1

Append (cost=14,925.30..31,155.66 rows=2 width=1,489) (actual time=0.094..0.094 rows=0 loops=1)

274. 0.001 0.064 ↓ 0.0 0 1

Nested Loop (cost=14,925.30..15,602.30 rows=1 width=1,489) (actual time=0.064..0.064 rows=0 loops=1)

275. 0.002 0.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,601.24 rows=1 width=1,457) (actual time=0.063..0.063 rows=0 loops=1)

276. 0.001 0.061 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,092.75 rows=1 width=1,458) (actual time=0.061..0.061 rows=0 loops=1)

277. 0.001 0.060 ↓ 0.0 0 1

Nested Loop (cost=14,925.15..15,084.58 rows=1 width=1,436) (actual time=0.060..0.060 rows=0 loops=1)

278. 0.001 0.059 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,924.86..15,076.26 rows=1 width=1,340) (actual time=0.059..0.059 rows=0 loops=1)

  • Join Filter: (((ee.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05.entity_01_cd)::text) AND ((ee.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05.entity_01_cd)::text))
279. 0.005 0.058 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..83.46 rows=1 width=1,336) (actual time=0.058..0.058 rows=0 loops=1)

  • Join Filter: ((f_position_entity_agg_2018_12_27_08_46_05.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
280. 0.053 0.053 ↓ 0.0 0 1

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 (cost=0.56..81.78 rows=1 width=308) (actual time=0.053..0.053 rows=0 loops=1)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
  • Filter: (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text)
  • Rows Removed by Filter: 19
281. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

282. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

283. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
284. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

285. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
286. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee.entity_01_cd)::text = (podt.dist_01_cd)::text)
287. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

288. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_1.depl_r365_eqv_qty_ty
289. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_1 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
290. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
291. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
292. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 (cost=0.14..8.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
293. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05.entity_01_cd)::text = (p.entity_01_cd)::text))
294. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

295. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
296. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 (cost=0.00..1.01 rows=1 width=32) (never executed)

297. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=14,925.30..15,553.34 rows=1 width=1,489) (actual time=0.029..0.029 rows=0 loops=1)

298. 0.000 0.028 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,552.29 rows=1 width=1,457) (actual time=0.028..0.028 rows=0 loops=1)

299. 0.002 0.028 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,043.80 rows=1 width=1,458) (actual time=0.028..0.028 rows=0 loops=1)

300. 0.001 0.026 ↓ 0.0 0 1

Nested Loop (cost=14,925.15..15,035.62 rows=1 width=1,436) (actual time=0.026..0.026 rows=0 loops=1)

301. 0.001 0.025 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,924.86..15,027.31 rows=1 width=1,340) (actual time=0.025..0.025 rows=0 loops=1)

  • Join Filter: (((ee_1.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_2.entity_01_cd)::text) AND ((ee_1.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_2.entity_01_cd)::text))
302. 0.002 0.024 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..34.51 rows=1 width=1,336) (actual time=0.024..0.024 rows=0 loops=1)

  • Join Filter: ((f_position_entity_agg_2018_12_27_08_46_05_2.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_1.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56_1.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
303. 0.022 0.022 ↓ 0.0 0 1

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_2 (cost=0.56..32.82 rows=1 width=308) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: (((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[])) AND (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text))
  • Rows Removed by Filter: 4
304. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_1 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

305. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

306. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_1 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
307. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

308. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
309. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_1 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_1.entity_01_cd)::text = (podt.dist_01_cd)::text)
310. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

311. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_3.depl_r365_eqv_qty_ty
312. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_3 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
313. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
314. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_1 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
315. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_1 (cost=0.14..8.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_2.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
316. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_1 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_1.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_2.entity_01_cd)::text = (p_1.entity_01_cd)::text))
317. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

318. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_1 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_2.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
319. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_1 (cost=0.00..1.01 rows=1 width=32) (never executed)

320. 0.001 0.144 ↓ 0.0 0 1

GroupAggregate (cost=132,332.12..132,332.42 rows=2 width=110) (actual time=0.144..0.144 rows=0 loops=1)

  • Group Key: agg_1.entity_01_cd
321. 0.007 0.143 ↓ 0.0 0 1

Hash Join (cost=132,332.12..132,332.38 rows=2 width=110) (actual time=0.143..0.143 rows=0 loops=1)

  • Hash Cond: ((d_entity_2018_12_27_07_01_12_2.ult_parent_no)::text = (agg_1.ult_parent_no)::text)
322. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=101,176.05..101,176.25 rows=3 width=864) (never executed)

  • Group Key: d_entity_2018_12_27_07_01_12_2.ult_parent_no, d_entity_2018_12_27_07_01_12_2.ult_parent_dsc
323. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=101,175.82..101,175.85 rows=3 width=1,489) (never executed)

  • Group Key: d_entity_2018_12_27_07_01_12_2.entity_01_cd, d_entity_2018_12_27_07_01_12_2.entity_01_dsc, d_entity_2018_12_27_07_01_12_2.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_2.city_dsc, d_entity_2018_12_27_07_01_12_2.state_cd, d_entity_2018_12_27_07_01_12_2.postal_cd, d_entity_2018_12_27_07_01_12_2.ult_parent_no, d_entity_2018_12_27_07_01_12_2.ult_parent_dsc, d_entity_2018_12_27_07_01_12_2.latitude, d_entity_2018_12_27_07_01_12_2.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_2.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_2.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_4.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_4.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_4.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_2.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_4.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_2.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_4.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_4.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_2.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_4.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_4.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_2.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_2.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_4.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_4.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_4.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_2.depl_r365_eqv_qty_ty * 0.6) > ((sum(f_position_entity_agg_2018_12_27_08_46_05_5.depl_r365_eqv_qty_ty) OVER (?)) - f_position_entity_agg_2018_12_27_08_46_05_5.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_2.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_2.depl_r365_eqv_qty_ty * 0.2)) > ((sum(f_position_entity_agg_2018_12_27_08_46_05_5.depl_r365_eqv_qty_ty) OVER (?)) - f_position_entity_agg_2018_12_27_08_46_05_5.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_4.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_4.sim_distribution_cmip_ly
324. 0.000 0.000 ↓ 0.0 0

Append (cost=31,994.85..101,175.29 rows=3 width=1,489) (never executed)

325. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=31,994.85..64,817.56 rows=2 width=1,489) (never executed)

326. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_2 (cost=0.00..1.01 rows=1 width=32) (never executed)

327. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=31,994.85..64,816.46 rows=2 width=1,457) (never executed)

328. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=31,994.85..63,799.49 rows=2 width=1,458) (never executed)

329. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=31,994.70..63,797.75 rows=2 width=1,436) (never executed)

330. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=31,994.41..63,782.15 rows=2 width=1,340) (never executed)

  • Hash Cond: (((f_position_entity_agg_2018_12_27_08_46_05_4.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_5.entity_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_4.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_5.entity_01_cd)::text))
331. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=16,829.73..48,617.24 rows=2 width=1,336) (never executed)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_4.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_2.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56_2.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
332. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_4 (cost=16,828.17..48,614.82 rows=46 width=308) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
  • Filter: (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text)
333. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..16,828.16 rows=9,248 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
334. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.25..1.25 rows=25 width=1,032) (never executed)

335. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_2 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

336. 0.000 0.000 ↓ 0.0 0

Hash (cost=15,068.56..15,068.56 rows=6,408 width=12) (never executed)

337. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..15,068.56 rows=6,408 width=12) (never executed)

338. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

339. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_5.depl_r365_eqv_qty_ty
340. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_5 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
341. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
342. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..20.53 rows=4 width=6) (never executed)

343. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_2 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
344. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_2 (cost=0.29..7.79 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_4.entity_01_cd)::text)
345. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_2 (cost=0.14..0.86 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_4.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
346. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_2 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: ((f_position_entity_agg_2018_12_27_08_46_05_4.entity_01_cd)::text = (p_2.entity_01_cd)::text)
347. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

348. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_2 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_4.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
349. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=23,237.68..36,357.69 rows=1 width=1,489) (never executed)

350. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=23,237.68..36,356.64 rows=1 width=1,457) (never executed)

351. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=23,237.68..35,848.15 rows=1 width=1,458) (never executed)

352. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=23,237.54..35,845.98 rows=1 width=1,436) (never executed)

353. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=23,237.25..35,837.66 rows=1 width=1,340) (never executed)

  • Join Filter: (((f_position_entity_agg_2018_12_27_08_46_05_7.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_6.entity_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_7.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_6.entity_01_cd)::text))
354. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=8,312.94..20,672.98 rows=1 width=1,336) (never executed)

  • Hash Cond: ((f_position_entity_agg_2018_12_27_08_46_05_6.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_3.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56_3.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
355. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_6 (cost=8,311.38..20,671.11 rows=16 width=308) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: (((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[])) AND (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text))
356. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,311.38 rows=3,354 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text))
357. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.25..1.25 rows=25 width=1,032) (never executed)

358. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_3 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

359. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..15,068.56 rows=6,408 width=12) (never executed)

360. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

361. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_7.depl_r365_eqv_qty_ty
362. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_7 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
363. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
364. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..20.53 rows=4 width=6) (never executed)

365. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_3 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
366. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_3 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_6.entity_01_cd)::text)
367. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_3 (cost=0.14..2.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_6.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
368. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_3 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: ((f_position_entity_agg_2018_12_27_08_46_05_6.entity_01_cd)::text = (p_3.entity_01_cd)::text)
369. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

370. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_3 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_6.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
371. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_3 (cost=0.00..1.01 rows=1 width=32) (never executed)

372. 0.001 0.136 ↓ 0.0 0 1

Hash (cost=31,156.05..31,156.05 rows=2 width=156) (actual time=0.136..0.136 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
373. 0.001 0.135 ↓ 0.0 0 1

Subquery Scan on agg_1 (cost=31,155.67..31,156.05 rows=2 width=156) (actual time=0.135..0.135 rows=0 loops=1)

374. 0.001 0.134 ↓ 0.0 0 1

Unique (cost=31,155.67..31,156.03 rows=2 width=1,489) (actual time=0.134..0.134 rows=0 loops=1)

375. 0.044 0.133 ↓ 0.0 0 1

Sort (cost=31,155.67..31,155.67 rows=2 width=1,489) (actual time=0.133..0.133 rows=0 loops=1)

  • Sort Key: d_entity_2018_12_27_07_01_12_4.entity_01_cd, d_entity_2018_12_27_07_01_12_4.entity_01_dsc, d_entity_2018_12_27_07_01_12_4.addr_line_01_dsc, d_entity_2018_12_27_07_01_12_4.city_dsc, d_entity_2018_12_27_07_01_12_4.state_cd, d_entity_2018_12_27_07_01_12_4.postal_cd, d_entity_2018_12_27_07_01_12_4.ult_parent_no, d_entity_2018_12_27_07_01_12_4.ult_parent_dsc, d_entity_2018_12_27_07_01_12_4.latitude, d_entity_2018_12_27_07_01_12_4.longitude, (COALESCE(d_item_brand_2018_12_27_07_07_56_4.brand_cd, 'TOTAL'::character varying)), (COALESCE(d_item_brand_2018_12_27_07_07_56_4.brand_dsc, 'TOTAL'::character varying)), (CASE WHEN ((f_position_entity_agg_2018_12_27_08_46_05_8.premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_8.premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_8.premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END), (CASE WHEN (d_item_sku_pkg_2018_12_27_07_05_22_4.master_pkg_sku_dsc IS NULL) THEN 'TOTAL'::character varying ELSE f_position_entity_agg_2018_12_27_08_46_05_8.master_pkg_sku_cd END), (COALESCE(d_item_sku_pkg_2018_12_27_07_05_22_4.master_pkg_sku_dsc, 'TOTAL'::character varying)), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_8.first_sold_dt IS NULL) THEN 'U'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_8.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_4.l365_beg_ty_id) THEN 'O'::text ELSE 'E'::text END), (CASE WHEN (f_position_entity_agg_2018_12_27_08_46_05_8.first_sold_dt IS NULL) THEN 'UNKNOWN'::text WHEN ((f_position_entity_agg_2018_12_27_08_46_05_8.first_sold_dt)::numeric < l_time_period_cbbd_2018_12_27_07_04_41_4.l365_beg_ty_id) THEN 'ORGANIC'::text ELSE 'EXPANSION'::text END), (CASE WHEN (p_4.entity_01_cd IS NULL) THEN 'N'::text ELSE 'Y'::text END), f_position_entity_agg_2018_12_27_08_46_05_8.depl_l30_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l30_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l60_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l60_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l90_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l90_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l120_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_l120_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cytm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cytm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cytd_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cytd_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_lcm_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_lcm_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cmip_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cmip_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cmip1_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_cmip2_eqv_qty_ly, f_position_entity_agg_2018_12_27_08_46_05_8.depl_r365_eqv_qty_ty, f_position_entity_agg_2018_12_27_08_46_05_8.depl_r365_eqv_qty_ly, (CASE WHEN ((f_position_agg_2018_12_27_08_44_08_4.depl_r365_eqv_qty_ty * 0.6) > (ee_2.depl_r365_eqv_qty_ty_rk - ee_2.depl_r365_eqv_qty_ty)) THEN 1 WHEN (((f_position_agg_2018_12_27_08_44_08_4.depl_r365_eqv_qty_ty * 0.6) + (f_position_agg_2018_12_27_08_44_08_4.depl_r365_eqv_qty_ty * 0.2)) > (ee_2.depl_r365_eqv_qty_ty_rk - ee_2.depl_r365_eqv_qty_ty)) THEN 2 ELSE 3 END), f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_8.eff_distribution_cmip_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l30_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l30_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l60_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l60_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l90_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l90_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l120_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l120_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l365_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_l365_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_cytd_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_cytd_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_lcm_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_lcm_ly, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_cmip_ty, f_position_entity_agg_2018_12_27_08_46_05_8.sim_distribution_cmip_ly
  • Sort Method: quicksort Memory: 25kB
376. 0.002 0.089 ↓ 0.0 0 1

Append (cost=14,925.30..31,155.66 rows=2 width=1,489) (actual time=0.089..0.089 rows=0 loops=1)

377. 0.001 0.059 ↓ 0.0 0 1

Nested Loop (cost=14,925.30..15,602.30 rows=1 width=1,489) (actual time=0.059..0.059 rows=0 loops=1)

378. 0.000 0.058 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,601.24 rows=1 width=1,457) (actual time=0.058..0.058 rows=0 loops=1)

379. 0.002 0.058 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,092.75 rows=1 width=1,458) (actual time=0.058..0.058 rows=0 loops=1)

380. 0.001 0.056 ↓ 0.0 0 1

Nested Loop (cost=14,925.15..15,084.58 rows=1 width=1,436) (actual time=0.056..0.056 rows=0 loops=1)

381. 0.001 0.055 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,924.86..15,076.26 rows=1 width=1,340) (actual time=0.055..0.055 rows=0 loops=1)

  • Join Filter: (((ee_2.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_8.entity_01_cd)::text) AND ((ee_2.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_8.entity_01_cd)::text))
382. 0.003 0.054 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..83.46 rows=1 width=1,336) (actual time=0.054..0.054 rows=0 loops=1)

  • Join Filter: ((f_position_entity_agg_2018_12_27_08_46_05_8.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_4.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56_4.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
383. 0.051 0.051 ↓ 0.0 0 1

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_8 (cost=0.56..81.78 rows=1 width=308) (actual time=0.051..0.051 rows=0 loops=1)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'BRAND'::text) AND ((premise_type_cd)::text = ANY ('{O,F}'::text[])))
  • Filter: (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text)
  • Rows Removed by Filter: 19
384. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_4 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

385. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

386. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_4 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
387. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

388. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
389. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_2 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_2.entity_01_cd)::text = (podt.dist_01_cd)::text)
390. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

391. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_9.depl_r365_eqv_qty_ty
392. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_9 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
393. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
394. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_4 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
395. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_4 (cost=0.14..8.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_8.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
396. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_4 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_4.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_8.entity_01_cd)::text = (p_4.entity_01_cd)::text))
397. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

398. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_4 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_8.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
399. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_4 (cost=0.00..1.01 rows=1 width=32) (never executed)

400. 0.002 0.028 ↓ 0.0 0 1

Nested Loop (cost=14,925.30..15,553.34 rows=1 width=1,489) (actual time=0.028..0.028 rows=0 loops=1)

401. 0.001 0.026 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,552.29 rows=1 width=1,457) (actual time=0.026..0.026 rows=0 loops=1)

402. 0.000 0.025 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.30..15,043.80 rows=1 width=1,458) (actual time=0.025..0.025 rows=0 loops=1)

403. 0.001 0.025 ↓ 0.0 0 1

Nested Loop (cost=14,925.15..15,035.62 rows=1 width=1,436) (actual time=0.025..0.025 rows=0 loops=1)

404. 0.002 0.024 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,924.86..15,027.31 rows=1 width=1,340) (actual time=0.024..0.024 rows=0 loops=1)

  • Join Filter: (((ee_3.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_10.entity_01_cd)::text) AND ((ee_3.entity_01_cd)::text = (f_position_entity_agg_2018_12_27_08_46_05_10.entity_01_cd)::text))
405. 0.002 0.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..34.51 rows=1 width=1,336) (actual time=0.022..0.022 rows=0 loops=1)

  • Join Filter: ((f_position_entity_agg_2018_12_27_08_46_05_10.brand_cd)::text = (d_item_brand_2018_12_27_07_07_56_5.brand_cd)::text)
  • Filter: (((COALESCE(d_item_brand_2018_12_27_07_07_56_5.brand_cd, 'TOTAL'::character varying))::character varying(30))::text = 'TOTAL'::text)
406. 0.020 0.020 ↓ 0.0 0 1

Index Scan using f_position_entity_agg_2018_12_27_08_46_05_uk on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_10 (cost=0.56..32.82 rows=1 width=308) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: ((position_id = 0) AND ((entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((aggregation_level)::text = 'TOTAL'::text))
  • Filter: (((premise_type_cd)::text = ANY ('{O,F,ALL}'::text[])) AND (((CASE WHEN ((premise_type_cd)::text = 'ALL'::text) THEN 'ALL'::text WHEN ((premise_type_cd)::text = 'O'::text) THEN 'ON PREMISE'::text WHEN ((premise_type_cd)::text = 'F'::text) THEN 'OFF PREMISE'::text ELSE NULL::text END)::character varying(200))::text = 'TOTAL'::text))
  • Rows Removed by Filter: 4
407. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_brand_2018_12_27_07_07_56 d_item_brand_2018_12_27_07_07_56_5 (cost=0.00..1.25 rows=25 width=1,032) (never executed)

408. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14,924.31..14,992.62 rows=12 width=12) (never executed)

409. 0.000 0.000 ↓ 0.0 0

Index Scan using f_position_agg_2018_12_27_08_44_08_uk on f_position_agg_2018_12_27_08_44_08 f_position_agg_2018_12_27_08_44_08_5 (cost=0.42..20.51 rows=4 width=6) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
410. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14,923.89..14,971.96 rows=3 width=45) (never executed)

411. 0.000 0.000 ↓ 0.0 0

Result (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • One-Time Filter: ((podt.dist_01_cd)::text = (podt.dist_01_cd)::text)
412. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ee_3 (cost=14,923.89..14,971.95 rows=3 width=45) (never executed)

  • Filter: ((ee_3.entity_01_cd)::text = (podt.dist_01_cd)::text)
413. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=14,923.89..14,951.92 rows=1,602 width=13) (never executed)

414. 0.000 0.000 ↓ 0.0 0

Sort (cost=14,923.89..14,927.89 rows=1,602 width=13) (never executed)

  • Sort Key: f_position_entity_agg_2018_12_27_08_46_05_11.depl_r365_eqv_qty_ty
415. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f_position_entity_agg_2018_12_27_08_46_05 f_position_entity_agg_2018_12_27_08_46_05_11 (cost=8,778.48..14,838.62 rows=1,602 width=13) (never executed)

  • Recheck Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
416. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f_position_entity_agg_2018_12_27_08_46_05_uk (cost=0.00..8,778.08 rows=1,602 width=0) (never executed)

  • Index Cond: ((position_id = 0) AND ((aggregation_level)::text = 'TOTAL'::text) AND ((premise_type_cd)::text = 'ALL'::text))
417. 0.000 0.000 ↓ 0.0 0

Index Scan using d_entity_2018_12_27_07_01_12_entity_01_cd_idx on d_entity_2018_12_27_07_01_12 d_entity_2018_12_27_07_01_12_5 (cost=0.29..8.31 rows=1 width=96) (never executed)

  • Index Cond: ((entity_01_cd)::text = (podt.dist_01_cd)::text)
418. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_sku_pkg_2018_12_27_07_05_22_pk on d_item_sku_pkg_2018_12_27_07_05_22 d_item_sku_pkg_2018_12_27_07_05_22_5 (cost=0.14..8.16 rows=1 width=31) (never executed)

  • Index Cond: ((f_position_entity_agg_2018_12_27_08_46_05_10.master_pkg_sku_cd)::text = (master_pkg_sku_cd)::text)
419. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p_5 (cost=0.00..508.48 rows=1 width=7) (never executed)

  • Filter: (((p_5.entity_01_cd)::text = (podt.dist_01_cd)::text) AND ((f_position_entity_agg_2018_12_27_08_46_05_10.entity_01_cd)::text = (p_5.entity_01_cd)::text))
420. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..508.46 rows=1 width=7) (never executed)

421. 0.000 0.000 ↓ 0.0 0

Seq Scan on x_priority_package_2018_12_27_06_56_20 x_priority_package_2018_12_27_06_56_20_5 (cost=0.00..6,101.56 rows=12 width=7) (never executed)

  • Filter: (((f_position_entity_agg_2018_12_27_08_46_05_10.entity_01_cd)::text = (entity_01_cd)::text) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) >= (eff_dt_id)::numeric) AND (to_number(to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text), '99999999'::text) <= (exp_dt_id)::numeric))
422. 0.000 0.000 ↓ 0.0 0

Seq Scan on l_time_period_cbbd_2018_12_27_07_04_41 l_time_period_cbbd_2018_12_27_07_04_41_5 (cost=0.00..1.01 rows=1 width=32) (never executed)

423.          

SubPlan (forGroupAggregate)

424. 0.003 0.019 ↑ 1.0 1 1

Limit (cost=0.29..4.31 rows=1 width=29) (actual time=0.019..0.019 rows=1 loops=1)

425. 0.016 0.016 ↑ 114.0 1 1

Index Scan using bc_dist_perf_pkg_new_dist_01_cd_idx on bc_dist_perf_pkg pkg_1 (cost=0.29..458.28 rows=114 width=29) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: ((podt.dist_01_cd)::text = (dist_01_cd)::text)