how to help using of Explain for optimization of query
HI,
can any one help how to check the optimization in EXPLAIN query. what are the major concepts that needs to be checked to optimize the query for better performance.
example of query i'm looking into
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
explain SELECT (SELECT Period FROM ReportParametersTmp) AS Period ,imp.Month_Name AS 'Month Name' ,imp.AdvertiserID ,imp.AdvertiserName ,imp.CampaignID ,imp.CampaignName /*impressions count*/ ,CASE WHEN imp.ImpressionCount IS NULL THEN 0 ELSE imp.ImpressionCount END AS ImpressionCount /*clicks count*/ ,CASE WHEN clk.ClickCount IS NULL THEN 0 ELSE clk.ClickCount END AS ClickCount /*CTR*/ ,CASE WHEN clk.ClickCount IS NULL THEN 0 ELSE ROUND(100 * clk.ClickCount / imp.ImpressionCount, 2.0) END AS CTR /*Reach*/ ,CASE WHEN imp.Reach IS NULL THEN 0 ELSE imp.Reach END AS Reach /*Unique Reach*/ ,CASE WHEN urch.UniqueReach IS NULL THEN 0 ELSE urch.UniqueReach END AS UniqueReach /*Cumulative Unique Reach*/ --,CASE WHEN curch.CumulativeUniqueReach IS NULL THEN 0 ELSE curch.CumulativeUniqueReach END AS CumulativeUniqueReach
/*Frequency*/ ,CASE WHEN imp.ImpressionCount IS NULL THEN 0 ELSE ROUND(100 * imp.Reach / imp.ImpressionCount, 2.0) END AS Frequency FROM ( /* Click */ SELECT ddm.Month_Name ,dsm.Advertiser_id AS AdvertiserID --,dsm.Advertiser_name AS AdvertiserName
,dsm.campaign_id AS CampaignID
--,dsm.campaign_name AS CampaignName
,COUNT(1) AS ClickCount
FROM
cybage_xaxis.Ad_Server_Metadata AS dsm
--JOIN cybage_xaxis.zeus_internal_mapping AS zim ON zim.EXTERNAL_ADVERTISER_ID = dsm.Advertiser_id
JOIN cybage_xaxis.zeus_click AS clk ON clk.placement_id = dsm.placement_id AND dsm.creative_id = clk.creative_id
JOIN cybage_xaxis.DateDimension AS ddm ON CAST(clk.click_timestamp AS DATE) = ddm.day_date
WHERE dsm.Advertiser_id IN (SELECT DISTINCT External_Advertiser_ID FROM ReportParameters2Tmp WHERE External_Advertiser_ID IS NOT NULL)
AND clk.organization_id IN (SELECT DISTINCT AgencyID FROM ReportParameters2Tmp WHERE AgencyID IS NOT NULL)
AND dsm.campaign_id IN (SELECT DISTINCT CampaignID FROM ReportParameters2Tmp WHERE CampaignID IS NOT NULL)
AND dsm.site_id IN (SELECT DISTINCT SiteID FROM ReportParameters2Tmp WHERE SiteID IS NOT NULL)
AND dsm.placement_id IN (SELECT DISTINCT PlacementID FROM ReportParameters2Tmp WHERE PlacementID IS NOT NULL)
AND dsm.creative_id IN (SELECT DISTINCT CreativeID FROM ReportParameters2Tmp WHERE CreativeID IS NOT NULL)
AND clk.click_timestamp BETWEEN (SELECT OffsetStartDate FROM ReportParametersTmp) AND (SELECT OffsetEndDate FROM ReportParametersTmp)
/*LEFT JOIN ReportParameters2Tmp AS adv ON zim.Internal_Advertiser_ID = adv.Internal_Advertiser_ID
LEFT JOIN ReportParameters2Tmp AS cmp ON dsm.campaign_id = cmp.CampaignID
LEFT JOIN ReportParameters2Tmp AS sit ON dsm.site_id = sit.SiteID
LEFT JOIN ReportParameters2Tmp AS plc ON dsm.placement_id = plc.PlacementID
LEFT JOIN ReportParameters2Tmp AS crt ON dsm.creative_id = crt.CreativeID*/ GROUP BY ddm.Month_Name ,dsm.Advertiser_id --,dsm.Advertiser_name
,dsm.campaign_id
--,dsm.campaign_name
) AS clk
FULL OUTER JOIN
(
/* Impressions & Reach */ SELECT IL.Month_Name ,IL.AdvertiserID ,IL.AdvertiserName ,IL.CampaignID ,IL.CampaignName ,COUNT(1) AS ImpressionCount ,COUNT(DISTINCT IL.UserID) AS Reach FROM ImpressionLogFilteredTmp AS IL GROUP BY IL.Month_Name ,IL.AdvertiserID ,IL.AdvertiserName ,IL.CampaignID ,IL.CampaignName ) AS imp ON clk.Month_Name = imp.Month_Name AND clk.AdvertiserID = imp.AdvertiserID AND clk.CampaignID = imp.CampaignID JOIN ( /*Unique Reach*/ SELECT IL.Month_Name ,IL.AdvertiserID --,IL.AdvertiserName
,IL.CampaignID
--,IL.CampaignName
,COUNT(DISTINCT UR.UserID) AS UniqueReach
FROM
ImpressionLogFilteredTmp AS IL
JOIN (SELECT imp.UserID
FROM cybage_xaxis.Ad_Server_Metadata AS dsm
JOIN ImpressionLogTmp AS imp ON dsm.placement_id = imp.PlacementID AND dsm.creative_id = imp.CreativeID
WHERE imp.Timestamp BETWEEN (SELECT OffsetStartDate FROM ReportParametersTmp) AND (SELECT OffsetEndDate FROM ReportParametersTmp)
GROUP BY imp.UserID
HAVING COUNT(DISTINCT dsm.campaign_id) = 1) AS UR
ON IL.UserID = UR.UserID
GROUP BY
IL.Month_Name
,IL.AdvertiserID
--,IL.AdvertiserName
,IL.CampaignID
--,IL.CampaignName
) urch ON imp.Month_Name = urch.Month_Name AND imp.AdvertiserID = urch.AdvertiserID AND imp.CampaignID = urch.CampaignID
Access Path:
+-JOIN (CROSS JOIN) [Cost: 177K, Rows: 10K (NO STATISTICS)] (PATH ID: 1) Inner (BROADCAST)
| Execute on: All Nodes
| +-- Outer -> JOIN HASH [Cost: 177K, Rows: 10K (NO STATISTICS)] (PATH ID: 2) Outer (LOCAL ROUND ROBIN) Inner (BROADCAST)
| | Join Cond: (imp.Month_Name = urch.Month_Name) AND (imp.AdvertiserID = urch.AdvertiserID) AND (imp.CampaignID = urch.CampaignID)
| | Execute on: All Nodes
| | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 156K, Rows: 10K (NO STATISTICS)] (PATH ID: 3) Inner (BROADCAST)
| | | Join Cond: (clk.Month_Name = imp.Month_Name) AND (clk.AdvertiserID = imp.AdvertiserID) AND (clk.CampaignID = imp.CampaignID)
| | | Execute on: All Nodes
| | | Runtime Filters: (SIP15(HashJoin): imp.Month_Name), (SIP16(HashJoin): imp.AdvertiserID), (SIP17(HashJoin): imp.CampaignID), (SIP18(HashJoin): imp.Month_Name, imp.AdvertiserID, imp.CampaignID)
| | | +-- Outer -> SELECT [Cost: 8K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | | Execute on: All Nodes
| | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 8K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
| | | | | Aggregates: count(DISTINCT IL.UserID), sum_of_count(<SVAR>)
| | | | | Group By: IL.Month_Name, IL.AdvertiserID, IL.AdvertiserName, IL.CampaignID, IL.CampaignName
| | | | | Execute on: All Nodes
| | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 5K, Rows: 10K (NO STATISTICS)] (PATH ID: 6)
| | | | | | Aggregates: count(1)
| | | | | | Group By: IL.Month_Name, IL.AdvertiserID, IL.AdvertiserName, IL.CampaignID, IL.CampaignName, IL.UserID
| | | | | | Execute on: All Nodes
| | | | | | +---> STORAGE ACCESS for IL [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 7)
| | | | | | | Projection: v_temp_schema.ImpressionLogFilteredTmp_b0
| | | | | | | Materialize: IL.UserID, IL.Month_Name, IL.AdvertiserID, IL.AdvertiserName, IL.CampaignID, IL.CampaignName
| | | | | | | Execute on: All Nodes
| | | +-- Inner -> SELECT [Cost: 148K, Rows: 7K (NO STATISTICS)] (PATH ID: 8)
| | | | Execute on: All Nodes
| | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 148K, Rows: 7K (NO STATISTICS)] (PATH ID: 9)
| | | | | Aggregates: count(1)
| | | | | Group By: ddm.Month_Name, dsm.Advertiser_id, dsm.campaign_id
| | | | | Execute on: All Nodes
| | | | | +---> JOIN HASH [Cost: 147K, Rows: 7K (NO STATISTICS)] (PATH ID: 10) Inner (BROADCAST)
| | | | | | Join Filter: (clk.click_timestamp <= VAL(13))
| | | | | | Materialize at Output: ddm.Month_Name
| | | | | | Execute on: All Nodes
| | | | | | +-- Outer -> JOIN HASH [Cost: 147K, Rows: 7K (NO STATISTICS)] (PATH ID: 11) Inner (BROADCAST)
| | | | | | | Join Filter: (clk.click_timestamp >= VAL(12))
| | | | | | | Execute on: All Nodes
| | | | | | | +-- Outer -> JOIN HASH [Cost: 147K, Rows: 7K (NO STATISTICS)] (PATH ID: 12) Inner (BROADCAST)
| | | | | | | | Join Cond: ((clk.click_timestamp)::date = ddm.day_date)
| | | | | | | | Materialize at Input: dsm.Advertiser_id, dsm.campaign_id, dsm.creative_id, dsm.placement_id, dsm.site_id
| | | | | | | | Execute on: All Nodes
| | | | | | | | +-- Outer -> STORAGE ACCESS for ddm [Cost: 35, Rows: 18K] (PATH ID: 13)
| | | | | | | | | Projection: cybage_xaxis.DateDimension_1_b0
| | | | | | | | | Materialize: ddm.day_date
| | | | | | | | | Execute on: All Nodes
| | | | | | | | | Runtime Filter: (SIP1(HashJoin): ddm.day_date)
| | | | | | | | +-- Inner -> JOIN HASH [Cost: 147K, Rows: 5 (NO STATISTICS)] (PATH ID: 14) Inner (BROADCAST)
| | | | | | | | | Join Cond: (clk.placement_id = dsm.placement_id) AND (dsm.creative_id = clk.creative_id)
| | | | | | | | | Materialize at Input: dsm.placement_id, dsm.creative_id, clk.click_timestamp, clk.organization_id, clk.placement_id, clk.creative_id
| | | | | | | | | Execute on: All Nodes
| | | | | | | | | +-- Outer -> JOIN HASH [Semi] [Cost: 19K, Rows: 7M (NO STATISTICS)] (PATH ID: 15) Inner (BROADCAST)
| | | | | | | | | | Join Cond: (dsm.creative_id = VAL(11))
| | | | | | | | | | Materialize at Input: dsm.creative_id
| | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | +-- Outer -> JOIN HASH [Semi] [Cost: 16K, Rows: 7M (NO STATISTICS)] (PATH ID: 16) Inner (BROADCAST)
| | | | | | | | | | | Join Cond: (dsm.placement_id = VAL(10))
| | | | | | | | | | | Materialize at Input: dsm.placement_id
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +-- Outer -> JOIN HASH [Semi] [Cost: 13K, Rows: 7M (NO STATISTICS)] (PATH ID: 17) Inner (BROADCAST)
| | | | | | | | | | | | Join Cond: (dsm.site_id = VAL(9))
| | | | | | | | | | | | Materialize at Input: dsm.site_id
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +-- Outer -> JOIN HASH [Semi] [Cost: 11K, Rows: 7M (NO STATISTICS)] (PATH ID: 18) Inner (BROADCAST)
| | | | | | | | | | | | | Join Cond: (dsm.campaign_id = VAL(8))
| | | | | | | | | | | | | Materialize at Input: dsm.campaign_id
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | +-- Outer -> JOIN HASH [Semi] [Cost: 9K, Rows: 7M (NO STATISTICS)] (PATH ID: 19) Inner (BROADCAST)
| | | | | | | | | | | | | | Join Cond: (dsm.Advertiser_id = VAL(6))
| | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for dsm [Cost: 8K, Rows: 7M] (PATH ID: 20)
| | | | | | | | | | | | | | | Projection: cybage_xaxis.DFA_Sizmek_Metadata_b0
| | | | | | | | | | | | | | | Materialize: dsm.Advertiser_id
| | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | | Runtime Filters: (SIP9(HashJoin): dsm.Advertiser_id), (SIP8(HashJoin): dsm.campaign_id), (SIP7(HashJoin): dsm.site_id), (SIP6(HashJoin): dsm.placement_id), (SIP5(HashJoin): dsm.creative_id), (SIP2(HashJoin): dsm.placement_id), (SIP3(HashJoin): dsm.creative_id), (SIP4(HashJoin): dsm.placement_id, dsm.creative_id)
| | | | | | | | | | | | | | +-- Inner -> SELECT [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 21)
| | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 22)
| | | | | | | | | | | | | | | | Group By: ReportParameters2Tmp.EXTERNAL_ADVERTISER_ID
| | | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 23)
| | | | | | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | | | | | Materialize: ReportParameters2Tmp.EXTERNAL_ADVERTISER_ID
| | | | | | | | | | | | | | | | | Filter: (ReportParameters2Tmp.EXTERNAL_ADVERTISER_ID IS NOT NULL)
| | | | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | +-- Inner -> SELECT [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 24)
| | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 25)
| | | | | | | | | | | | | | | Group By: ReportParameters2Tmp.CampaignID
| | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 26)
| | | | | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | | | | Materialize: ReportParameters2Tmp.CampaignID
| | | | | | | | | | | | | | | | Filter: (ReportParameters2Tmp.CampaignID IS NOT NULL)
| | | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +-- Inner -> SELECT [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 27)
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 28)
| | | | | | | | | | | | | | Group By: ReportParameters2Tmp.SiteID
| | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 29)
| | | | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | | | Materialize: ReportParameters2Tmp.SiteID
| | | | | | | | | | | | | | | Filter: (ReportParameters2Tmp.SiteID IS NOT NULL)
| | | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +-- Inner -> SELECT [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 30)
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 31)
| | | | | | | | | | | | | Group By: ReportParameters2Tmp.PlacementID
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 32)
| | | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | | Materialize: ReportParameters2Tmp.PlacementID
| | | | | | | | | | | | | | Filter: (ReportParameters2Tmp.PlacementID IS NOT NULL)
| | | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | +-- Inner -> SELECT [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 33)
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 243, Rows: 500 (NO STATISTICS)] (PATH ID: 34)
| | | | | | | | | | | | Group By: ReportParameters2Tmp.CreativeID
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 35)
| | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | Materialize: ReportParameters2Tmp.CreativeID
| | | | | | | | | | | | | Filter: (ReportParameters2Tmp.CreativeID IS NOT NULL)
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | +-- Inner -> JOIN HASH [Semi] [Cost: 5K, Rows: 3M (NO STATISTICS)] (PATH ID: 36) Inner (BROADCAST)
| | | | | | | | | | Join Cond: (clk.organization_id = VAL(7))
| | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | +-- Outer -> STORAGE ACCESS for clk [Cost: 4K, Rows: 3M] (PATH ID: 37)
| | | | | | | | | | | Projection: cybage_xaxis.zeus_click_b0
| | | | | | | | | | | Materialize: clk.organization_id
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | Runtime Filter: (SIP10(HashJoin): clk.organization_id)
| | | | | | | | | | +-- Inner -> SELECT [Cost: 242, Rows: 500 (NO STATISTICS)] (PATH ID: 38)
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +---> GROUPBY PIPELINED (GLOBAL RESEGMENT GROUPS) [Cost: 242, Rows: 500 (NO STATISTICS)] (PATH ID: 39)
| | | | | | | | | | | | Group By: ReportParameters2Tmp.AgencyID
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +---> STORAGE ACCESS for ReportParameters2Tmp [Cost: 222, Rows: 10K (NO STATISTICS)] (PATH ID: 40)
| | | | | | | | | | | | | Projection: v_temp_schema.ReportParameters2Tmp_b0
| | | | | | | | | | | | | Materialize: ReportParameters2Tmp.AgencyID
| | | | | | | | | | | | | Filter: (ReportParameters2Tmp.AgencyID IS NOT NULL)
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | +-- Inner -> SELECT [Cost: 202, Rows: 1 (NO STATISTICS)] (PATH ID: 41)
| | | | | | | | Execute on: All Nodes
| | | | | | | | +---> STORAGE ACCESS for ReportParametersTmp [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 42)
| | | | | | | | | Projection: v_temp_schema.ReportParametersTmp_b0
| | | | | | | | | Materialize: ReportParametersTmp.OffsetStartDate
| | | | | | | | | Execute on: All Nodes
| | | | | | +-- Inner -> SELECT [Cost: 202, Rows: 1 (NO STATISTICS)] (PATH ID: 43)
| | | | | | | Execute on: All Nodes
| | | | | | | +---> STORAGE ACCESS for ReportParametersTmp [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 44)
| | | | | | | | Projection: v_temp_schema.ReportParametersTmp_b0
| | | | | | | | Materialize: ReportParametersTmp.OffsetEndDate
| | | | | | | | Execute on: All Nodes
| | +-- Inner -> SELECT [Cost: 21K, Rows: 10K (NO STATISTICS)] (PATH ID: 45)
| | | Execute on: All Nodes
| | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 21K, Rows: 10K (NO STATISTICS)] (PATH ID: 46)
| | | | Aggregates: count(DISTINCT UR.UserID)
| | | | Group By: IL.Month_Name, IL.AdvertiserID, IL.CampaignID
| | | | Execute on: All Nodes
| | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 20K, Rows: 10K (NO STATISTICS)] (PATH ID: 47)
| | | | | Group By: IL.Month_Name, IL.AdvertiserID, IL.CampaignID, UR.UserID
| | | | | Execute on: All Nodes
| | | | | +---> JOIN HASH [Cost: 20K, Rows: 10K (NO STATISTICS)] (PATH ID: 48) Inner (BROADCAST)
| | | | | | Join Cond: (IL.UserID = UR.UserID)
| | | | | | Materialize at Output: IL.Month_Name, IL.AdvertiserID, IL.CampaignID
| | | | | | Execute on: All Nodes
| | | | | | +-- Outer -> STORAGE ACCESS for IL [Cost: 203, Rows: 10K (NO STATISTICS)] (PATH ID: 49)
| | | | | | | Projection: v_temp_schema.ImpressionLogFilteredTmp_b0
| | | | | | | Materialize: IL.UserID
| | | | | | | Execute on: All Nodes
| | | | | | | Runtime Filter: (SIP14(HashJoin): IL.UserID)
| | | | | | +-- Inner -> SELECT [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 50)
| | | | | | | Execute on: All Nodes
| | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 51)
| | | | | | | | Aggregates: count(DISTINCT dsm.campaign_id)
| | | | | | | | Group By: imp.UserID
| | | | | | | | Filter: (<SVAR> = 1)
| | | | | | | | Execute on: All Nodes
| | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 52)
| | | | | | | | | Group By: imp.UserID, dsm.campaign_id
| | | | | | | | | Execute on: All Nodes
| | | | | | | | | +---> JOIN HASH [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 53) Inner (BROADCAST)
| | | | | | | | | | Join Filter: (imp."Timestamp" <= VAL(5))
| | | | | | | | | | Materialize at Output: dsm.campaign_id
| | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | +-- Outer -> JOIN HASH [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 54) Inner (BROADCAST)
| | | | | | | | | | | Join Filter: (imp."Timestamp" >= VAL(4))
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +-- Outer -> JOIN HASH [Cost: 19K, Rows: 1 (NO STATISTICS)] (PATH ID: 55) Inner (BROADCAST)
| | | | | | | | | | | | Join Cond: (dsm.placement_id = imp.PlacementID) AND (dsm.creative_id = imp.CreativeID)
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for dsm [Cost: 16K, Rows: 7M] (PATH ID: 56)
| | | | | | | | | | | | | Projection: cybage_xaxis.DFA_Sizmek_Metadata_b0
| | | | | | | | | | | | | Materialize: dsm.placement_id, dsm.creative_id
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | | Runtime Filters: (SIP11(HashJoin): dsm.placement_id), (SIP12(HashJoin): dsm.creative_id), (SIP13(HashJoin): dsm.placement_id, dsm.creative_id)
| | | | | | | | | | | | +-- Inner -> STORAGE ACCESS for imp [Cost: 807, Rows: 10K (NO STATISTICS)] (PATH ID: 57)
| | | | | | | | | | | | | Projection: v_temp_schema.ImpressionLogTmp_b0
| | | | | | | | | | | | | Materialize: imp."Timestamp", imp.UserID, imp.PlacementID, imp.CreativeID
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +-- Inner -> SELECT [Cost: 202, Rows: 1 (NO STATISTICS)] (PATH ID: 58)
| | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | | +---> STORAGE ACCESS for ReportParametersTmp [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 59)
| | | | | | | | | | | | | Projection: v_temp_schema.ReportParametersTmp_b0
| | | | | | | | | | | | | Materialize: ReportParametersTmp.OffsetStartDate
| | | | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | +-- Inner -> SELECT [Cost: 202, Rows: 1 (NO STATISTICS)] (PATH ID: 60)
| | | | | | | | | | | Execute on: All Nodes
| | | | | | | | | | | +---> STORAGE ACCESS for ReportParametersTmp [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 61)
| | | | | | | | | | | | Projection: v_temp_schema.ReportParametersTmp_b0
| | | | | | | | | | | | Materialize: ReportParametersTmp.OffsetEndDate
| | | | | | | | | | | | Execute on: All Nodes
| +-- Inner -> SELECT [Cost: 214, Rows: 1 (NO STATISTICS)] (PATH ID: 62)
| | Execute on: All Nodes
| | +---> STORAGE ACCESS for ReportParametersTmp [Cost: 214, Rows: 10K (NO STATISTICS)] (PATH ID: 63)
| | | Projection: v_temp_schema.ReportParametersTmp_b0
| | | Materialize: ReportParametersTmp.Period
| | | Execute on: All Nodes
------------------------------
-----------------------------------------------
PLAN: BASE QUERY PLAN (GraphViz Format)
-----------------------------------------------
digraph G {
graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain SELECT (SELECT Period FROM ReportParametersTmp) AS Period ,imp.Month_Name AS \'Month Name\' ,imp.AdvertiserID ,imp.AdvertiserName ,imp.CampaignID ,imp.CampaignName /*impressions count*/ ,CASE WHEN imp.ImpressionCount IS NULL THEN 0 ELSE imp.ImpressionCount END AS ImpressionCount /*clicks count*/ ,CASE WHEN clk.ClickCount IS NULL THEN 0 ELSE clk.ClickCount END AS ClickCount /*CTR*/ ,CASE WHEN clk.ClickCount IS NULL THEN 0 ELSE ROUND(100 * clk.ClickCount / imp.ImpressionCount, 2.0) END AS CTR /*Reach*/ ,CASE WHEN imp.Reach IS NULL THEN 0 ELSE imp.Reach END AS Reach /*Unique Reach*/ ,CASE WHEN urch.UniqueReach IS NULL THEN 0 ELSE urch.UniqueReach END AS UniqueReach /*Cumulative Unique Reach*/ --,CASE WHEN curch.CumulativeUniqueReach IS NULL THEN 0 ELSE curch.CumulativeUniqueReach END AS CumulativeUniqueReach\n /*Frequency*/ ,CASE WHEN imp.ImpressionCount IS NULL THEN 0 ELSE ROUND(100 * imp.Reach / imp.ImpressionCount, 2.0) END AS Frequency FROM ( /* Click */ SELECT ddm.Month_Name ,dsm.Advertiser_id AS AdvertiserID --,dsm.Advertiser_name AS AdvertiserName\n ,dsm.campaign_id AS CampaignID\n --,dsm.campaign_name AS CampaignName\n ,COUNT(1) AS ClickCount\n FROM\n cybage_xaxis.Ad_Server_Metadata AS dsm\n --JOIN cybage_xaxis.zeus_internal_mapping AS zim ON zim.EXTERNAL_ADVERTISER_ID = dsm.Advertiser_id\n JOIN cybage_xaxis.zeus_click AS clk ON clk.placement_id = dsm.placement_id AND dsm.creative_id = clk.creative_id\n JOIN cybage_xaxis.DateDimension AS ddm ON CAST(clk.click_timestamp AS DATE) = ddm.day_date\n WHERE dsm.Advertiser_id IN (SELECT DISTINCT External_Advertiser_ID FROM ReportParameters2Tmp WHERE External_Advertiser_ID IS NOT NULL)\n AND clk.organization_id IN (SELECT DISTINCT AgencyID FROM ReportParameters2Tmp WHERE AgencyID IS NOT NULL) \n AND dsm.campaign_id IN (SELECT DISTINCT CampaignID FROM ReportParameters2Tmp WHERE CampaignID IS NOT NULL)\n AND dsm.site_id IN (SELECT DISTINCT SiteID FROM ReportParameters2Tmp WHERE SiteID IS NOT NULL)\n AND dsm.placement_id IN (SELECT DISTINCT PlacementID FROM ReportParameters2Tmp WHERE PlacementID IS NOT NULL)\n AND dsm.creative_id IN (SELECT DISTINCT CreativeID FROM Re...\nAll Nodes Vector: \n\n node[0]=v_diap11_node0001 (initiator) Up\n node[1]=v_diap11_node0002 (executor) Up\n node[2]=v_diap11_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
0[label = "Root \nOutBlk=[UncTuple(12)]", color = "green", shape = "house"];
1[label = "NewEENode \nOutBlk=[UncTuple(12)]", color = "green", shape = "box"];
2[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1030\n\nUnc: Varchar(80)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)", color = "green", shape = "box"];
3[label = "Send\nSend to: v_diap11_node0001\nNet id: 1030\n\nUnc: Varchar(80)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)", color = "green", shape = "box"];
4[label = "ExprEval: \n VAL(6)\n imp.Month_Name\n imp.AdvertiserID\n imp.AdvertiserName\n imp.CampaignID\n imp.CampaignName\n CASE WHEN (imp.ImpressionCount IS NULL) THEN 0 ELSE imp.ImpressionCount END\n CASE WHEN (clk.ClickCount IS NULL) THEN 0 ELSE clk.ClickCount END\n CASE WHEN (clk.ClickCount IS NULL) THEN 0 ELSE round((((100 * clk.ClickCount) / imp.ImpressionCount))::float, 2) END\n CASE WHEN (imp.Reach IS NULL) THEN 0 ELSE imp.Reach END\n CASE WHEN (urch.UniqueReach IS NULL) THEN 0 ELSE urch.UniqueReach END\n CASE WHEN (imp.ImpressionCount IS NULL) THEN 0 ELSE round((((100 * imp.Reach) / imp.ImpressionCount))::float, 2) END\nUnc: Varchar(80)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)", color = "green", shape = "box"];
5[label = "Join: Hash-Join: \n(Join Order Restriction 5 x v_temp_schema.ReportParametersTmp) using previous join and subquery (PATH ID: 1)\n[CROSS JOIN]\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(80)", color = "green", shape = "box"];
6[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
7[label = "Join: Hash-Join: \n(v_temp_schema.ImpressionLogFilteredTmp x v_temp_schema.ImpressionLogFilteredTmp) using previous join and subquery (PATH ID: 2)\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
8[label = "ParallelUnionStep: \nRound Robin\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
9[label = "FilterStep: \nSIP15(HashJoin): imp.Month_Name\nSIP16(HashJoin): imp.AdvertiserID\nSIP17(HashJoin): imp.CampaignID\nSIP18(HashJoin): imp.Month_Name, imp.AdvertiserID, imp.CampaignID\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
10[label = "Join: Hash-Join: \n(v_temp_schema.ImpressionLogFilteredTmp x cybage_xaxis.DateDimension) using subquery and subquery (PATH ID: 3)\n[LeftOuter]\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
11[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
12[label = "ExprEval: \n IL.Month_Name\n IL.AdvertiserID\n IL.AdvertiserName\n IL.CampaignID\n IL.CampaignName\n \<SVAR\>\n \<SVAR\>\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
13[label = "GroupByHash(Spill): 5 keys\nAggs:\n count(DISTINCT IL.UserID)\n sum_of_count(\<SVAR\>)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
14[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
15[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1001\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
16[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1001\nResegment\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
17[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
18[label = "GroupByHash: 5 keys\nAggs:\n count(DISTINCT IL.UserID)\n sum_of_count(\<SVAR\>)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
19[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
20[label = "GroupByPipe(HashPrepass): 5 keys\nAggs:\n count(DISTINCT IL.UserID)\n sum_of_count(\<SVAR\>)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
21[label = "GroupByHash(Spill): 6 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
22[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
23[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1000\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
24[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1000\nResegment\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
25[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
26[label = "GroupByHash: 6 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
27[label = "StorageUnionStep: ImpressionLogFilteredTmp_b0\nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "purple", shape = "box"];
28[label = "GroupByPipe(HashPrepass): 6 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "brown", shape = "box"];
29[label = "ExprEval: \n IL.Month_Name\n IL.AdvertiserID\n IL.AdvertiserName\n IL.CampaignID\n IL.CampaignName\n IL.UserID\n 1\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "brown", shape = "box"];
30[label = "ScanStep: ImpressionLogFilteredTmp_b0\nTimestamp (not emitted)\nUserID\nMonth_Name\nAdvertiserID\nAdvertiserName\nCampaignID\nCampaignName\nUnc: Numeric(30,0)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Varchar(300)\nUnc: Integer(8)\nUnc: Varchar(300)", color = "brown", shape = "box"];
31[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1019\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
32[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1019\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
33[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
34[label = "GroupByHash(Spill): 3 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
35[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
36[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1018\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
37[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1018\nResegment\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
38[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
39[label = "GroupByHash: 3 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
40[label = "StorageUnionStep: DateDimension_1_b0\nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"];
41[label = "GroupByPipe(HashPrepass): 3 keys\nAggs:\n count(1)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
42[label = "ExprEval: \n ddm.Month_Name\n dsm.Advertiser_id\n dsm.campaign_id\n 1\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
43[label = "Join: Hash-Join: \n(cybage_xaxis.zeus_click x v_temp_schema.ReportParametersTmp) using previous join and subquery (PATH ID: 10)\n[RANGE JOIN]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
44[label = "Join: Hash-Join: \n(cybage_xaxis.zeus_click x v_temp_schema.ReportParametersTmp) using previous join and subquery (PATH ID: 11)\n[RANGE JOIN]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
45[label = "Join: Hash-Join: \n(cybage_xaxis.DateDimension x cybage_xaxis.zeus_click) using DateDimension_1_b0 and previous join (PATH ID: 12)\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
46[label = "ScanStep: DateDimension_1_b0\nSIP1(HashJoin): ddm.day_date\ndate_key (not emitted)\nday_date\nUnc: Timestamp(8)", color = "brown", shape = "box"];
47[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1015\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
48[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1015\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
49[label = "StorageUnionStep: DFA_Sizmek_Metadata_b0\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"];
50[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x cybage_xaxis.zeus_click) using previous join and previous join (PATH ID: 14)\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
51[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ReportParameters2Tmp) using previous join and subquery (PATH ID: 15)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
52[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ReportParameters2Tmp) using previous join and subquery (PATH ID: 16)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
53[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ReportParameters2Tmp) using previous join and subquery (PATH ID: 17)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
54[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ReportParameters2Tmp) using previous join and subquery (PATH ID: 18)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
55[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ReportParameters2Tmp) using DFA_Sizmek_Metadata_b0 and subquery (PATH ID: 19)\n[Semi]\n\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
56[label = "ScanStep: DFA_Sizmek_Metadata_b0\nSIP9(HashJoin): dsm.Advertiser_id\nSIP8(HashJoin): dsm.campaign_id\nSIP7(HashJoin): dsm.site_id\nSIP6(HashJoin): dsm.placement_id\nSIP5(HashJoin): dsm.creative_id\nSIP2(HashJoin): dsm.placement_id\nSIP3(HashJoin): dsm.creative_id\nSIP4(HashJoin): dsm.placement_id, dsm.creative_id\nAd_Server_id (not emitted)\nAdvertiser_id\nCampaignID (not emitted)\nCreativeID (not emitted)\nPlacementID (not emitted)\nSiteID (not emitted)\nUnc: Integer(8)", color = "brown", shape = "box"];
57[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1003\n\nUnc: Integer(8)", color = "green", shape = "box"];
58[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1003\n\nUnc: Integer(8)", color = "green", shape = "box"];
59[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
60[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
61[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)", color = "green", shape = "box"];
62[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1002\n\nUnc: Integer(8)", color = "green", shape = "box"];
63[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1002\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
64[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
65[label = "GroupByHash: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
66[label = "StorageUnionStep: ReportParameters2Tmp_b0\nLocally Resegment\nUnc: Integer(8)", color = "purple", shape = "box"];
67[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
68[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.EXTERNAL_ADVERTISER_ID IS NOT NULL)\nEXTERNAL_ADVERTISER_ID\nUnc: Integer(8)", color = "brown", shape = "box"];
69[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1005\n\nUnc: Integer(8)", color = "green", shape = "box"];
70[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1005\n\nUnc: Integer(8)", color = "green", shape = "box"];
71[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
72[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
73[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)", color = "green", shape = "box"];
74[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1004\n\nUnc: Integer(8)", color = "green", shape = "box"];
75[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1004\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
76[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
77[label = "GroupByHash: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
78[label = "StorageUnionStep: ReportParameters2Tmp_b0\nLocally Resegment\nUnc: Integer(8)", color = "purple", shape = "box"];
79[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
80[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.CampaignID IS NOT NULL)\nCampaignID\nUnc: Integer(8)", color = "brown", shape = "box"];
81[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1007\n\nUnc: Integer(8)", color = "green", shape = "box"];
82[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1007\n\nUnc: Integer(8)", color = "green", shape = "box"];
83[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
84[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
85[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)", color = "green", shape = "box"];
86[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1006\n\nUnc: Integer(8)", color = "green", shape = "box"];
87[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1006\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
88[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
89[label = "GroupByHash: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
90[label = "StorageUnionStep: ReportParameters2Tmp_b0\nLocally Resegment\nUnc: Integer(8)", color = "purple", shape = "box"];
91[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
92[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.SiteID IS NOT NULL)\nSiteID\nUnc: Integer(8)", color = "brown", shape = "box"];
93[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1009\n\nUnc: Integer(8)", color = "green", shape = "box"];
94[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1009\n\nUnc: Integer(8)", color = "green", shape = "box"];
95[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
96[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
97[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)", color = "green", shape = "box"];
98[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1008\n\nUnc: Integer(8)", color = "green", shape = "box"];
99[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1008\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
100[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
101[label = "GroupByHash: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
102[label = "StorageUnionStep: ReportParameters2Tmp_b0\nLocally Resegment\nUnc: Integer(8)", color = "purple", shape = "box"];
103[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
104[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.PlacementID IS NOT NULL)\nPlacementID\nUnc: Integer(8)", color = "brown", shape = "box"];
105[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1011\n\nUnc: Integer(8)", color = "green", shape = "box"];
106[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1011\n\nUnc: Integer(8)", color = "green", shape = "box"];
107[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
108[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
109[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)", color = "green", shape = "box"];
110[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1010\n\nUnc: Integer(8)", color = "green", shape = "box"];
111[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1010\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
112[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)", color = "green", shape = "box"];
113[label = "GroupByHash: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
114[label = "StorageUnionStep: ReportParameters2Tmp_b0\nLocally Resegment\nUnc: Integer(8)", color = "purple", shape = "box"];
115[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
116[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.CreativeID IS NOT NULL)\nCreativeID\nUnc: Integer(8)", color = "brown", shape = "box"];
117[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1014\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
118[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1014\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
119[label = "StorageUnionStep: zeus_click_b0\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"];
120[label = "Join: Hash-Join: \n(cybage_xaxis.zeus_click x v_temp_schema.ReportParameters2Tmp) using zeus_click_b0 and subquery (PATH ID: 36)\n[Semi]\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
121[label = "ScanStep: zeus_click_b0\nSIP10(HashJoin): clk.organization_id\nclick_timestamp (not emitted)\norganization_id\nUnc: Integer(8)", color = "brown", shape = "box"];
122[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1013\n\nUnc: Integer(8)", color = "green", shape = "box"];
123[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1013\n\nUnc: Integer(8)", color = "green", shape = "box"];
124[label = "GroupByPipe: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
125[label = "Recv(keys = A)\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1012\nMerge\n\nUnc: Integer(8)", color = "green", shape = "box"];
126[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1012\nMerge\nResegment\n\nUnc: Integer(8)", color = "green", shape = "box"];
127[label = "GroupByPipe: 1 keys\nAggs:\nUnc: Integer(8)", color = "green", shape = "box"];
128[label = "StorageMergeStep: ReportParameters2Tmp_b0; 1 sorted\nUnc: Integer(8)", color = "purple", shape = "box"];
129[label = "GroupByPipe: 1 keys\nAggs:\nUnc: Integer(8)", color = "brown", shape = "box"];
130[label = "ScanStep: ReportParameters2Tmp_b0\n(ReportParameters2Tmp.AgencyID IS NOT NULL)\nAgencyID\nUnc: Integer(8)", color = "brown", shape = "box"];
131[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1016\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
132[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1016\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
133[label = "StorageUnionStep: ReportParametersTmp_b0\nUnc: Timestamp(8)", color = "purple", shape = "box"];
134[label = "ScanStep: ReportParametersTmp_b0\nStartDate (not emitted)\nOffsetStartDate\nUnc: Timestamp(8)", color = "brown", shape = "box"];
135[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1017\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
136[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1017\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
137[label = "StorageUnionStep: ReportParametersTmp_b0\nUnc: Timestamp(8)", color = "purple", shape = "box"];
138[label = "ScanStep: ReportParametersTmp_b0\nStartDate (not emitted)\nOffsetEndDate\nUnc: Timestamp(8)", color = "brown", shape = "box"];
139[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1028\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
140[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1028\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
141[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
142[label = "GroupByHash(Spill): 3 keys\nAggs:\n count(DISTINCT UR.UserID)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
143[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
144[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1027\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
145[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1027\nResegment\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
146[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
147[label = "GroupByHash: 3 keys\nAggs:\n count(DISTINCT UR.UserID)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
148[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
149[label = "GroupByPipe(HashPrepass): 3 keys\nAggs:\n count(DISTINCT UR.UserID)\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
150[label = "GroupByHash(Spill): 4 keys\nAggs:\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
151[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
152[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1026\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
153[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1026\nResegment\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
154[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
155[label = "GroupByHash: 4 keys\nAggs:\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "green", shape = "box"];
156[label = "StorageUnionStep: ImpressionLogFilteredTmp_b0\nLocally Resegment\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "purple", shape = "box"];
157[label = "GroupByPipe(HashPrepass): 4 keys\nAggs:\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "brown", shape = "box"];
158[label = "Join: Hash-Join: \n(v_temp_schema.ImpressionLogFilteredTmp x v_temp_schema.ImpressionLogTmp) using ImpressionLogFilteredTmp_b0 and subquery (PATH ID: 48)\n\nUnc: Varchar(15)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Numeric(30,0)", color = "brown", shape = "box"];
159[label = "ScanStep: ImpressionLogFilteredTmp_b0\nSIP14(HashJoin): IL.UserID\nTimestamp (not emitted)\nUserID\nUnc: Numeric(30,0)", color = "brown", shape = "box"];
160[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1025\n\nUnc: Numeric(30,0)", color = "green", shape = "box"];
161[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1025\n\nUnc: Numeric(30,0)", color = "green", shape = "box"];
162[label = "ParallelUnionStep: \nCombine\nUnc: Numeric(30,0)", color = "green", shape = "box"];
163[label = "ExprEval: \n imp.UserID\nUnc: Numeric(30,0)", color = "green", shape = "box"];
164[label = "FilterStep: \n(\<SVAR\> = 1)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
165[label = "GroupByHash(Spill): 1 keys\nAggs:\n count(DISTINCT dsm.campaign_id)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
166[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
167[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1024\n\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
168[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1024\nResegment\n\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
169[label = "ParallelUnionStep: \nCombine\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
170[label = "GroupByHash: 1 keys\nAggs:\n count(DISTINCT dsm.campaign_id)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
171[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
172[label = "GroupByPipe(HashPrepass): 1 keys\nAggs:\n count(DISTINCT dsm.campaign_id)\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
173[label = "GroupByHash(Spill): 2 keys\nAggs:\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
174[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
175[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1023\n\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
176[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1023\nResegment\n\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
177[label = "ParallelUnionStep: \nCombine\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
178[label = "GroupByHash: 2 keys\nAggs:\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "green", shape = "box"];
179[label = "StorageUnionStep: DFA_Sizmek_Metadata_b0\nLocally Resegment\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "purple", shape = "box"];
180[label = "GroupByPipe(HashPrepass): 2 keys\nAggs:\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "brown", shape = "box"];
181[label = "ExprEval: \n imp.UserID\n dsm.campaign_id\nUnc: Numeric(30,0)\nUnc: Integer(8)", color = "brown", shape = "box"];
182[label = "Join: Hash-Join: \n(v_temp_schema.ImpressionLogTmp x v_temp_schema.ReportParametersTmp) using previous join and subquery (PATH ID: 53)\n[RANGE JOIN]\n\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Timestamp(8)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
183[label = "Join: Hash-Join: \n(v_temp_schema.ImpressionLogTmp x v_temp_schema.ReportParametersTmp) using previous join and subquery (PATH ID: 54)\n[RANGE JOIN]\n\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
184[label = "Join: Hash-Join: \n(cybage_xaxis.Ad_Server_Metadata x v_temp_schema.ImpressionLogTmp) using DFA_Sizmek_Metadata_b0 and ImpressionLogTmp_b0 (PATH ID: 55)\n\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Numeric(30,0)", color = "brown", shape = "box"];
185[label = "ScanStep: DFA_Sizmek_Metadata_b0\nSIP11(HashJoin): dsm.placement_id\nSIP12(HashJoin): dsm.creative_id\nSIP13(HashJoin): dsm.placement_id, dsm.creative_id\nAd_Server_id (not emitted)\nCreativeID\nPlacementID\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
186[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1020\n\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
187[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1020\n\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"];
188[label = "StorageUnionStep: ImpressionLogTmp_b0\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"];
189[label = "ScanStep: ImpressionLogTmp_b0\nTimestamp\nUserID\nPlacementID\nCreativeID\nUnc: Timestamp(8)\nUnc: Numeric(30,0)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"];
190[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1021\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
191[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1021\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
192[label = "StorageUnionStep: ReportParametersTmp_b0\nUnc: Timestamp(8)", color = "purple", shape = "box"];
193[label = "ScanStep: ReportParametersTmp_b0\nStartDate (not emitted)\nOffsetStartDate\nUnc: Timestamp(8)", color = "brown", shape = "box"];
194[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1022\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
195[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1022\n\nUnc: Timestamp(8)", color = "green", shape = "box"];
196[label = "StorageUnionStep: ReportParametersTmp_b0\nUnc: Timestamp(8)", color = "purple", shape = "box"];
197[label = "ScanStep: ReportParametersTmp_b0\nStartDate (not emitted)\nOffsetEndDate\nUnc: Timestamp(8)", color = "brown", shape = "box"];
198[label = "Recv\nRecv from: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1029\n\nUnc: Varchar(80)", color = "green", shape = "box"];
199[label = "Send\nSend to: v_diap11_node0001,v_diap11_node0002,v_diap11_node0003\nNet id: 1029\n\nUnc: Varchar(80)", color = "green", shape = "box"];
200[label = "StorageUnionStep: ReportParametersTmp_b0\nUnc: Varchar(80)", color = "purple", shape = "box"];
201[label = "ScanStep: ReportParametersTmp_b0\nStartDate (not emitted)\nPeriod\nUnc: Varchar(80)", color = "brown", shape = "box"];
1->0 [label = "V[0] C=12",color = "black",style="bold", arrowtail="inv"];
2->1 [label = "0",color = "blue"];
3->2 [label = "0",color = "blue"];
4->3 [label = "0",color = "blue"];
5->4 [label = "0",color = "blue"];
6->5 [label = "0",color = "blue"];
7->6 [label = "0",color = "blue"];
8->7 [label = "0",color = "blue"];
9->8 [label = "0",color = "blue"];
10->9 [label = "0",color = "blue"];
11->10 [label = "0",color = "blue"];
12->11 [label = "0",color = "blue"];
13->12 [label = "0",color = "blue"];
14->13 [label = "0",color = "blue"];
15->14 [label = "0",color = "blue"];
16->15 [label = "0",color = "blue"];
17->16 [label = "0",color = "blue"];
18->17 [label = "0",color = "blue"];
19->18 [label = "0",color = "blue"];
20->19 [label = "0",color = "blue"];
21->20 [label = "0",color = "blue"];
22->21 [label = "0",color = "blue"];
23->22 [label = "0",color = "blue"];
24->23 [label = "0",color = "blue"];
25->24 [label = "0",color = "blue"];
26->25 [label = "0",color = "blue"];
27->26 [label = "0",color = "blue"];
28->27 [label = "0",color = "blue"];
29->28 [label = "0",color = "blue"];
30->29 [label = "0",color = "blue"];
31->10 [label = "1",color = "blue"];
32->31 [label = "0",color = "blue"];
33->32 [label = "0",color = "blue"];
34->33 [label = "0",color = "blue"];
35->34 [label = "0",color = "blue"];
36->35 [label = "0",color = "blue"];
37->36 [label = "0",color = "blue"];
38->37 [label = "0",color = "blue"];
39->38 [label = "0",color = "blue"];
40->39 [label = "0",color = "blue"];
41->40 [label = "0",color = "blue"];
42->41 [label = "0",color = "blue"];
43->42 [label = "0",color = "blue"];
44->43 [label = "0",color = "blue"];
45->44 [label = "0",color = "blue"];
46->45 [label = "0",color = "blue"];
47->45 [label = "1",color = "blue"];
48->47 [label = "0",color = "blue"];
49->48 [label = "0",color = "blue"];
50->49 [label = "0",color
Comments
Hi there,
I just wanted to point you to a number of resources we have published on understanding query plans.
In the Developer Community, we've published a blog about this--we've also published a video (released with Vertica 7.0) on how to access Query Plans in Management Console:
Looking Under the Hood at Vertica Queries
Viewing Query Plans in Vertica 7.0.x
You can also find out more about Query Plans in the Vertica documentation:
https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/AdministratorsGuide/QueryManagement/QueryPlans/QueryPlans.htm%3FTocPath%3DAdministrator's%2520Guide%7CManaging%2520Queries%7CQuery%2520Plans%7C_____0
Hope this helps!
Just looking at your explain, I would imagine the biggest issue is the BROADCAST. One BROADCAST is bad - you have 11 of them.
A Broadcast results when data exists on a node that is a different node than where the joined data lives.
In other words, I have a customer table, and a transactions table. Customers have zero to many transactions. I segment customer by customer_id, which means customer Bob lives in node 2. Then, I segment transactions by transaction ID, which means all of Bob's transactions live on all the nodes equally. When I write a query that asks for Bob and all of his transactions, all of the nodes have to gather Bob's transactions so that I can see a complete picture. This might not be horrible if I want to report on just Bob - but if I'm trying to aggregate the transactions of 5 million customers, that's gonna get horrible real fast.
This is Big Data - you have to understand where your data lives, and how it is being joined. To that end, you need a segmentation key that is:
- consistent
- simplistic
A consistent segmentation key is something that is shared across MANY tables. Customer_ID, user_ID, account_ID, are all very commonly used columns that a lot of companies have. At the end of the day, most data is tied to a user, a company, a client, an account, or whatever it is. Those are great examples of a segmentation clause.
A simplistic segmentation key value is short, and easy to understand. Like consistency, a simplistic segmentation key is one that contains just a few columns and is efficient.
If undefined, Vertica will automatically pick a segmentation clause for your tables. It is NEITHER simplstic or consistent. So, it's going to result in very inefficient queries. That's because Vertica's goal is to simply ensure even data distribution. It doesn't know anything about your data.
Just adding my two cents on top of Curtis relpy.
Install MC and use its EXPLAIN tool, it has a grapfical gui taht will allow you to better see how your query works and also will point you to bottle neck ,such as lond scans , spilled joins , broadcast , bad segmentation , bad join keys etc..
hope this helped