Help needed to improve RANGE JOIN Operation

MS_1

 EXPLAIN
 SELECT
  str.Parameter_name
 ,trm.Week_Number
 ,SUM(CASE WHEN trm.Period_Ind = 'CURR'  THEN COALESCE(str.Sales_Usd_Val  ,0) END)  as  CY_Sales_Usd_Val   
 ,SUM(CASE WHEN trm.Period_Ind = 'PREV'  THEN COALESCE(str.Sales_Usd_Val  ,0) END)  as  PY_Sales_Usd_Val   
 ,SUM(CASE WHEN trm.Period_Ind = 'PPREV' THEN COALESCE(str.Sales_Usd_Val  ,0) END)  as  PPY_Sales_Usd_Val   
 ,SUM(CASE WHEN trm.Period_Ind = 'CURR'  THEN COALESCE(str.Sales_Local_Val,0) END)  as  CY_Sales_Local_Val   
 ,SUM(CASE WHEN trm.Period_Ind = 'PREV'  THEN COALESCE(str.Sales_Local_Val,0) END)  as  PY_Sales_Local_Val   
 ,SUM(CASE WHEN trm.Period_Ind = 'PPREV' THEN COALESCE(str.Sales_Local_Val,0) END)  as  PPY_Sales_Local_Val   
 FROM
 BILLINGS str
 INNER JOIN    
 Data_Range_dim trm
 ON  str.Report_Date BETWEEN trm.Start_Dt and trm.End_Dt
 and str.Site_cd = trm.Site_cd
 WHERE
 trm.Report_Date                     = '2015-11-20'
 and trm.Timeframe             = 'QTD'
 and str.Metric_type         = 'Sales'
 and trm.Status_Indicator         = 'T'
 and str.Region_cd              IN ( 'WW'  )              /* Dynamic Geo Column selection */
 and str.Channel_Cd   IN ( 'ALL' )              /* Dynamic Segment Column selection */
 and str.Prod_Type               = 'ALL'

 and ((str.Parameter_name IN ('TOTAL','STORE','PPU','Traffic','Transaction') )
 OR 
 (str.Parameter_name NOT IN ('TOTAL','STORE','PPU','Traffic','Transaction','Forecast') AND trm.Week_Number = 'TOTAL')
 )                                                   
 GROUP BY
 1,2;

 Access Path:
 +-GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 271K, Rows: 1K] (PATH ID: 1)
 |  Aggregates: sum(CASE WHEN (trm.Period_Ind = 'CURR') THEN coalesce(str.Sales_Usd_Val, 0) ELSE NULL::numeric(1,0) END), sum(CASE WHEN (trm.Period_Ind = 'PREV') THEN coalesce(
str.Sales_Usd_Val, 0) ELSE NULL::numeric(1,0) END), sum(CASE WHEN (trm.Period_Ind = 'PPREV') THEN coalesce(str.Sales_Usd_Val, 0) ELSE NULL::numeric(1,0) END), sum(CASE WHEN (tr
m.Period_Ind = 'CURR') THEN coalesce(str.Sales_Local_Val, 0) ELSE NULL::numeric(1,0) END), sum(CASE WHEN (trm.Period_Ind = 'PREV') THEN coalesce(str.Sales_Local_Val, 0) ELSE NU
LL::numeric(1,0) END), sum(CASE WHEN (trm.Period_Ind = 'PPREV') THEN coalesce(str.Sales_Local_Val, 0) ELSE NULL::numeric(1,0) END)
 |  Group By: str.Parameter_name, trm.Week_Number
 |  Execute on: All Nodes
 | +---> JOIN (RANGE JOIN) [Cost: 54K, Rows: 24M] (PATH ID: 2) Inner (BROADCAST)
 | |      Join Cond: (str.Site_cd = (trm.Site_cd))
 | |      Join Filter: (str.Report_Date >= trm.Start_Dt) AND (str.Report_Date <= trm.End_Dt) AND ((str.Parameter_name = ANY (ARRAY['TOTAL', 'STORE', 'PPU', 'Traffic', 'Transaction'])) 
OR ((str.Parameter_name <> ALL (ARRAY['TOTAL', 'STORE', 'PPU', 'Traffic', 'Transaction', 'Forecast'])) AND (trm.Week_Number = 'TOTAL')))
 | |      Materialize at Output: str.Sales_Usd_Val, str.Sales_Local_Val
 | |      Execute on: All Nodes
 | | +-- Outer -> STORAGE ACCESS for str [Cost: 17K, Rows: 24M] (PATH ID: 3)
 | | |      Projection: BILLINGS_DBD_1_seg_DBD_b0_b0
 | | |      Materialize: str.Report_Date, str.Site_cd, str.Parameter_name
 | | |      Filter: (str.Metric_type = 'Sales')
 | | |      Filter: (str.Prod_Type = 'ALL')
 | | |      Filter: (str.Region_cd = 'WW')
 | | |      Filter: (str.Channel_Cd = 'ALL')
 | | |      Execute on: All Nodes
 | | |      Runtime Filter: (SIP1(HashJoin): str.Site_cd)
 | | +-- Inner -> STORAGE ACCESS for trm [Cost: 369, Rows: 30K] (PATH ID: 4)
 | | |      Projection: Data_Range_dim_DBD_2_seg_DBD_b0_b0
 | | |      Materialize: trm.Period_Ind, trm.Week_Number, trm.Site_cd, trm.Start_Dt, trm.End_Dt
 | | |      Filter: (trm.Report_Date = '2015-11-20'::date)
 | | |      Filter: (trm.Status_Indicator = 'T')
 | | |      Filter: (trm.Timeframe = 'QTD')
 | | |      Execute on: All Nodes

From query_profile table, it is understood that Path 1 (Global Segment) & Path 2 (Join) are causing performance issue. This query is taking around 800 ms which needs to be further optimized as it's being used as a sub query in another query.

Note: I deployed incremental design (DBD suggested) but still no luck.

 

Looking forward for your suggestions.

eli_revach

Hi ,

Please send your projection desigen you have for the two tables .