execution time of updates and projections

Hallo, I am Katrin and developer for 24/7 media. During the last few weeks, we had a few problems with starting and stopping the database. Your support helped us. Thank's a lot. After solving the issues, your support recommended: "Delete concerns – Even I am not much aware of it but below was told to me while we had recovery taking longer. 8/29/13 12:36 AM Shaun Carney: I think I may have been mistaken - sort order is identical but still delete concerns - that means delete/update statements are using a column in the WHERE clause that isn't in the sort order of either projection then" So I changed the projection for one of our tables, which is updated very often. But now, the update on this table takes more than 3 times longer as before. What can we do to get a better update performance? Here are the details: We have two tables: pts_raw_data_click_detail and pts_raw_data_delivery_detail. Their counts are: pts_raw_data_click_detail: overall count: 47,393,844 daily count: ca. 150,000 pts_raw_data_delivery_detail: overall count: 28,637,325,768 daily count: ca. 80,000,000 Every hour we update a column in pts_raw_data_delivery_detail with counts from pts_raw_data_click_detail. I will attach the ddl, explain of the update statement and the former projections, with which the update performed better. Please find a solution. Thank you. Best regards Katrin

Comments

  • -- DML CREATE TABLE oaslt.pts_raw_data_click_detail ( view_date date NOT NULL, view_hour int, view_time timestamp, user1_id int, user2_id int, view1_id int, view2_id int, panel_name varchar(100), panel_type varchar(32), event_name varchar(100), num_sequence int, time_view int, time_diff int, click_x int, click_y int, custom_data varchar(1000), client_id_num int, event_id_num int, campaign_id_ext int, random_id float, row_id IDENTITY ) PARTITION BY ((concat(concat(((date_part('year', (pts_raw_data_click_detail.view_date)::timestamp))::int)::varchar, CASE WHEN ((date_part('month', (pts_raw_data_click_detail.view_date)::timestamp))::int > 9) THEN '' ELSE '0' END), ((date_part('month', (pts_raw_data_click_detail.view_date)::timestamp))::int)::varchar))::float); CREATE TABLE oaslt.pts_raw_data_delivery_detail ( view_date date NOT NULL, view_hour int, view_time timestamp, deliver_mode char(1), cost numeric(10,5), subsite_id_num int, publisher_id_num int, host_id_num int, click_count int, targeting_group_id int, new_user_id int, reach_considered int, user1_id int, user2_id int, view1_id int, view2_id int, currency varchar(3), campaign_id_num int, client_id_num int, ad_group_id_num int, ad_id_num int, creative_id_num int, network_id_num int, random_id float, row_id IDENTITY ( 25000000000 , 1 ) , gmtrefererh_id_num int ) PARTITION BY ((concat(concat(((date_part('year', (pts_raw_data_delivery_detail.view_date)::timestamp))::int)::varchar, CASE WHEN ((date_part('month', (pts_raw_data_delivery_detail.view_date)::timestamp))::int > 9) THEN '' ELSE '0' END), ((date_part('month', (pts_raw_data_delivery_detail.view_date)::timestamp))::int)::varchar))::float); CREATE PROJECTION oaslt.pts_raw_data_click_detail_no1 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, user1_id, user2_id, view1_id, view2_id, panel_name ENCODING RLE, panel_type ENCODING RLE, event_name ENCODING RLE, num_sequence ENCODING DELTAVAL, time_view ENCODING DELTAVAL, time_diff ENCODING DELTAVAL, click_x ENCODING RLE, click_y ENCODING RLE, custom_data ENCODING RLE, client_id_num ENCODING RLE, event_id_num ENCODING BLOCK_DICT, campaign_id_ext ENCODING BLOCK_DICT, random_id, row_id ) AS SELECT pts_raw_data_click_detail.view_date, pts_raw_data_click_detail.view_hour, pts_raw_data_click_detail.view_time, pts_raw_data_click_detail.user1_id, pts_raw_data_click_detail.user2_id, pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.panel_name, pts_raw_data_click_detail.panel_type, pts_raw_data_click_detail.event_name, pts_raw_data_click_detail.num_sequence, pts_raw_data_click_detail.time_view, pts_raw_data_click_detail.time_diff, pts_raw_data_click_detail.click_x, pts_raw_data_click_detail.click_y, pts_raw_data_click_detail.custom_data, pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.event_id_num, pts_raw_data_click_detail.campaign_id_ext, pts_raw_data_click_detail.random_id, pts_raw_data_click_detail.row_id FROM oaslt.pts_raw_data_click_detail ORDER BY pts_raw_data_click_detail.view_date, pts_raw_data_click_detail.panel_name, pts_raw_data_click_detail.panel_type, pts_raw_data_click_detail.event_name, pts_raw_data_click_detail.custom_data, pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.view_hour, pts_raw_data_click_detail.click_x, pts_raw_data_click_detail.row_id SEGMENTED BY hash(pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.event_id_num) ALL NODES ; CREATE PROJECTION oaslt.pts_raw_data_click_detail_no2 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, user1_id, user2_id, view1_id, view2_id, panel_name ENCODING RLE, panel_type ENCODING RLE, event_name ENCODING RLE, num_sequence ENCODING DELTAVAL, time_view ENCODING DELTAVAL, time_diff ENCODING DELTAVAL, click_x ENCODING RLE, click_y ENCODING RLE, custom_data ENCODING RLE, client_id_num ENCODING RLE, event_id_num ENCODING BLOCK_DICT, campaign_id_ext ENCODING BLOCK_DICT, random_id, row_id ) AS SELECT pts_raw_data_click_detail.view_date, pts_raw_data_click_detail.view_hour, pts_raw_data_click_detail.view_time, pts_raw_data_click_detail.user1_id, pts_raw_data_click_detail.user2_id, pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.panel_name, pts_raw_data_click_detail.panel_type, pts_raw_data_click_detail.event_name, pts_raw_data_click_detail.num_sequence, pts_raw_data_click_detail.time_view, pts_raw_data_click_detail.time_diff, pts_raw_data_click_detail.click_x, pts_raw_data_click_detail.click_y, pts_raw_data_click_detail.custom_data, pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.event_id_num, pts_raw_data_click_detail.campaign_id_ext, pts_raw_data_click_detail.random_id, pts_raw_data_click_detail.row_id FROM oaslt.pts_raw_data_click_detail ORDER BY pts_raw_data_click_detail.view_date, pts_raw_data_click_detail.panel_name, pts_raw_data_click_detail.panel_type, pts_raw_data_click_detail.event_name, pts_raw_data_click_detail.custom_data, pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.view_hour, pts_raw_data_click_detail.click_x, pts_raw_data_click_detail.row_id SEGMENTED BY hash(pts_raw_data_click_detail.client_id_num, pts_raw_data_click_detail.event_id_num) ALL NODES OFFSET 1; CREATE PROJECTION oaslt.pts_raw_data_delivery_detail_no1 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, deliver_mode ENCODING RLE, cost, subsite_id_num ENCODING BLOCK_DICT, publisher_id_num ENCODING RLE, host_id_num ENCODING BLOCK_DICT, click_count ENCODING RLE, targeting_group_id ENCODING BLOCK_DICT, new_user_id ENCODING RLE, reach_considered ENCODING DELTAVAL, user1_id, user2_id, view1_id, view2_id, currency ENCODING RLE, campaign_id_num ENCODING RLE, client_id_num ENCODING RLE, ad_group_id_num ENCODING RLE, ad_id_num ENCODING BLOCK_DICT, creative_id_num ENCODING RLE, network_id_num ENCODING RLE, random_id, row_id, gmtrefererh_id_num ENCODING RLE ) AS SELECT pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.cost, pts_raw_data_delivery_detail.subsite_id_num, pts_raw_data_delivery_detail.publisher_id_num, pts_raw_data_delivery_detail.host_id_num, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.targeting_group_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.reach_considered, pts_raw_data_delivery_detail.user1_id, pts_raw_data_delivery_detail.user2_id, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.ad_group_id_num, pts_raw_data_delivery_detail.ad_id_num, pts_raw_data_delivery_detail.creative_id_num, pts_raw_data_delivery_detail.network_id_num, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.row_id, pts_raw_data_delivery_detail.gmtrefererh_id_num FROM oaslt.pts_raw_data_delivery_detail ORDER BY pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num SEGMENTED BY hash(pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num) ALL NODES ; CREATE PROJECTION oaslt.pts_raw_data_delivery_detail_no2 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, deliver_mode ENCODING RLE, cost, subsite_id_num ENCODING BLOCK_DICT, publisher_id_num ENCODING RLE, host_id_num ENCODING BLOCK_DICT, click_count ENCODING RLE, targeting_group_id ENCODING BLOCK_DICT, new_user_id ENCODING RLE, reach_considered ENCODING DELTAVAL, user1_id, user2_id, view1_id, view2_id, currency ENCODING RLE, campaign_id_num ENCODING RLE, client_id_num ENCODING RLE, ad_group_id_num ENCODING RLE, ad_id_num ENCODING BLOCK_DICT, creative_id_num ENCODING RLE, network_id_num ENCODING RLE, random_id, row_id, gmtrefererh_id_num ENCODING RLE ) AS SELECT pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.cost, pts_raw_data_delivery_detail.subsite_id_num, pts_raw_data_delivery_detail.publisher_id_num, pts_raw_data_delivery_detail.host_id_num, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.targeting_group_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.reach_considered, pts_raw_data_delivery_detail.user1_id, pts_raw_data_delivery_detail.user2_id, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.ad_group_id_num, pts_raw_data_delivery_detail.ad_id_num, pts_raw_data_delivery_detail.creative_id_num, pts_raw_data_delivery_detail.network_id_num, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.row_id, pts_raw_data_delivery_detail.gmtrefererh_id_num FROM oaslt.pts_raw_data_delivery_detail ORDER BY pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num SEGMENTED BY hash(pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num) ALL NODES OFFSET 1; SELECT MARK_DESIGN_KSAFE(1);
  • -- explain kvogelba=> explain kvogelba-> update oaslt.pts_raw_data_delivery_detail kvogelba-> set click_count = cl.clicks kvogelba-> from ( kvogelba(> select kvogelba(> d.view_time, kvogelba(> d.view1_id, kvogelba(> d.view2_id, kvogelba(> c.clicks kvogelba(> from oaslt.pts_raw_data_delivery_detail d kvogelba(> join ( kvogelba(> ( kvogelba(> select kvogelba(> view1_id, kvogelba(> view2_id, kvogelba(> count(*) as clicks kvogelba(> from oaslt.pts_raw_data_click_detail kvogelba(> where kvogelba(> view_date >= '2013-10-07' kvogelba(> and view_date < ('2013-10-07'::date +15) kvogelba(> and view1_id > 1 kvogelba(> and view2_id > -1 kvogelba(> group by view1_id, view2_id kvogelba(> ) kvogelba(> union kvogelba(> ( kvogelba(> select kvogelba(> view1_id, kvogelba(> view2_id, kvogelba(> count(*) as clicks kvogelba(> from oaslt.pts_raw_data_click_detail kvogelba(> where kvogelba(> view_date = '2013-10-07' kvogelba(> and view1_id = 1 kvogelba(> and view2_id > -1 kvogelba(> group by view1_id, view2_id kvogelba(> ) kvogelba(> ) c on (d.view1_id = c.view1_id and d.view2_id = c.view2_id) kvogelba(> where kvogelba(> d.view_date = '2013-10-07' kvogelba(> and d.view1_id > 0 kvogelba(> and d.view2_id > -1 kvogelba(> and (d.client_id_num, d.campaign_id_num) <> (1, 2094) kvogelba(> ) cl kvogelba-> where kvogelba-> oaslt.pts_raw_data_delivery_detail.view1_id = cl.view1_id kvogelba-> and oaslt.pts_raw_data_delivery_detail.view2_id = cl.view2_id kvogelba-> and oaslt.pts_raw_data_delivery_detail.view_time = cl.view_time kvogelba-> and (oaslt.pts_raw_data_delivery_detail.client_id_num, oaslt.pts_raw_data_delivery_detail.campaign_id_num) <> (1, 2094); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain update oaslt.pts_raw_data_delivery_detail set click_count = cl.clicks from ( select d.view_time, d.view1_id, d.view2_id, c.clicks from oaslt.pts_raw_data_delivery_detail d join ( ( select view1_id, view2_id, count(*) as clicks from oaslt.pts_raw_data_click_detail where view_date >= '2013-10-07' and view_date < ('2013-10-07'::date +15) and view1_id > 1 and view2_id > -1 group by view1_id, view2_id ) union ( select view1_id, view2_id, count(*) as clicks from oaslt.pts_raw_data_click_detail where view_date = '2013-10-07' and view1_id = 1 and view2_id > -1 group by view1_id, view2_id ) ) c on (d.view1_id = c.view1_id and d.view2_id = c.view2_id) where d.view_date = '2013-10-07' and d.view1_id > 0 and d.view2_id > -1 and (d.client_id_num, d.campaign_id_num) <> (1, 2094) ) cl where oaslt.pts_raw_data_delivery_detail.view1_id = cl.view1_id and oaslt.pts_raw_data_delivery_detail.view2_id = cl.view2_id and oaslt.pts_raw_data_delivery_detail.view_time = cl.view_time and (oaslt.pts_raw_data_delivery_detail.client_id_num, oaslt.pts_raw_data_delivery_detail.campaign_id_num) <> (1, 2094); Access Path: +-DML UPDATE [Cost: 0, Rows: 0] | Target Projection: oaslt.pts_raw_data_delivery_detail_no2 (NO DELETE) | Target Projection: oaslt.pts_raw_data_delivery_detail_no1 (NO DELETE) | Target Prep: | Execute on: All Nodes | +---> JOIN HASH [Semi] [Cost: 625M, Rows: 14B] (PATH ID: 1) Inner (BROADCAST) | | Join Cond: (pts_raw_data_delivery_detail.view1_id = VAL(2)) AND (pts_raw_data_delivery_detail.view2_id = VAL(2)) AND (pts_raw_data_delivery_detail.view_time = VAL(2)) | | Materialize at Output: pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.cost, pts_raw_data_delivery_detail.subsite_id_num, pts_raw_data_delivery_detail.publisher_id_num, pts_raw_data_delivery_detail.host_id_num, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.targeting_group_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.reach_considered, pts_raw_data_delivery_detail.user1_id, pts_raw_data_delivery_detail.user2_id, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.ad_group_id_num, pts_raw_data_delivery_detail.ad_id_num, pts_raw_data_delivery_detail.creative_id_num, pts_raw_data_delivery_detail.network_id_num, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.row_id, pts_raw_data_delivery_detail.gmtrefererh_id_num, pts_raw_data_delivery_detail.epoch | | Execute on: All Nodes | | +-- Outer -> STORAGE ACCESS for [Cost: 149M, Rows: 29B] (PATH ID: 2) | | | Projection: oaslt.pts_raw_data_delivery_detail_no2 | | | Materialize: pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id | | | Filter: ((pts_raw_data_delivery_detail.client_id_num <> 1) OR (pts_raw_data_delivery_detail.campaign_id_num <> 2094)) | | | Execute on: All Nodes | | | Runtime Filters: (SIP7(HashJoin): pts_raw_data_delivery_detail.view1_id), (SIP8(HashJoin): pts_raw_data_delivery_detail.view2_id), (SIP9(HashJoin): pts_raw_data_delivery_detail.view_time), (SIP10(HashJoin): pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time) | | +-- Inner -> SELECT [Cost: 1M, Rows: 144M] (PATH ID: 3) | | | Execute on: All Nodes | | | +---> JOIN HASH [Cost: 1M, Rows: 144M] (PATH ID: 4) Inner (BROADCAST) | | | | Join Cond: (d.view1_id = c.view1_id) AND (d.view2_id = c.view2_id) | | | | Materialize at Output: d.view_time | | | | Execute on: All Nodes | | | | +-- Outer -> STORAGE ACCESS for d [Cost: 861K, Rows: 144M] (PATH ID: 5) | | | | | Projection: oaslt.pts_raw_data_delivery_detail_no2 | | | | | Materialize: d.client_id_num, d.campaign_id_num, d.view1_id, d.view2_id | | | | | Filter: (d.view_date = '2013-10-07'::date) | | | | | Filter: (d.view1_id > 0) | | | | | Filter: (d.view2_id > (-1)) | | | | | Filter: ((d.client_id_num <> 1) OR (d.campaign_id_num <> 2094)) | | | | | Execute on: All Nodes | | | | | Runtime Filters: (SIP4(HashJoin): d.view1_id), (SIP5(HashJoin): d.view2_id), (SIP6(HashJoin): d.view1_id, d.view2_id) | | | | +-- Inner -> SELECT [Cost: 5K, Rows: 3] (PATH ID: 6) | | | | | Filter: (c.view1_id > 0) | | | | | Filter: (c.view2_id > (-1)) | | | | | Execute on: All Nodes | | | | | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5K, Rows: 3] (PATH ID: 7) | | | | | | Group By: "*SELECT* 1".view1_id, "*SELECT* 1".view2_id, "*SELECT* 1".clicks | | | | | | Execute on: All Nodes | | | | | | +---> UNION [Cost: 5K, Rows: 225K] (PATH ID: 8) | | | | | | | Execute on: All Nodes | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 3K, Rows: 224K] (PATH ID: 10) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 224K] (PATH ID: 11) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: ((pts_raw_data_click_detail.view_date >= '2013-10-07'::date) AND (pts_raw_data_click_detail.view_date < '2013-10-22'::date)) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id > 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 1K, Rows: 1K] (PATH ID: 13) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 1K] (PATH ID: 14) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: (pts_raw_data_click_detail.view_date = '2013-10-07'::date) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id = 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes +-Target Projection: oaslt.pts_raw_data_delivery_detail_no2 (DELETE ON CONTAINER) | Target Prep: | Execute on: All Nodes | +---> JOIN HASH [Semi] [Cost: 359M, Rows: 14B] (PATH ID: 1) Inner (BROADCAST) | | Join Cond: (pts_raw_data_delivery_detail.view1_id = cl.view1_id) AND (pts_raw_data_delivery_detail.view2_id = cl.view2_id) AND (pts_raw_data_delivery_detail.view_time = cl.view_time) | | Execute on: All Nodes | | +-- Outer -> STORAGE ACCESS for pts_raw_data_delivery_detail [Cost: 149M, Rows: 29B] (PATH ID: 2) | | | Projection: oaslt.pts_raw_data_delivery_detail_no2 | | | Materialize: pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time | | | Filter: ((pts_raw_data_delivery_detail.client_id_num <> 1) OR (pts_raw_data_delivery_detail.campaign_id_num <> 2094)) | | | Execute on: All Nodes | | | Runtime Filters: (SIP14(HashJoin): pts_raw_data_delivery_detail.view1_id), (SIP15(HashJoin): pts_raw_data_delivery_detail.view2_id), (SIP16(HashJoin): pts_raw_data_delivery_detail.view_time), (SIP17(HashJoin): pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time) | | +-- Inner -> SELECT [Cost: 1M, Rows: 144M] (PATH ID: 3) | | | Execute on: All Nodes | | | +---> JOIN HASH [Cost: 1M, Rows: 144M] (PATH ID: 4) Inner (BROADCAST) | | | | Join Cond: (d.view1_id = c.view1_id) AND (d.view2_id = c.view2_id) | | | | Materialize at Output: d.view_time | | | | Execute on: All Nodes | | | | +-- Outer -> STORAGE ACCESS for d [Cost: 861K, Rows: 144M] (PATH ID: 5) | | | | | Projection: oaslt.pts_raw_data_delivery_detail_no2 | | | | | Materialize: d.client_id_num, d.campaign_id_num, d.view1_id, d.view2_id | | | | | Filter: (d.view_date = '2013-10-07'::date) | | | | | Filter: (d.view1_id > 0) | | | | | Filter: (d.view2_id > (-1)) | | | | | Filter: ((d.client_id_num <> 1) OR (d.campaign_id_num <> 2094)) | | | | | Execute on: All Nodes | | | | | Runtime Filters: (SIP11(HashJoin): d.view1_id), (SIP12(HashJoin): d.view2_id), (SIP13(HashJoin): d.view1_id, d.view2_id) | | | | +-- Inner -> SELECT [Cost: 5K, Rows: 3] (PATH ID: 6) | | | | | Execute on: All Nodes | | | | | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5K, Rows: 3] (PATH ID: 7) | | | | | | Group By: "*SELECT* 1".view1_id, "*SELECT* 1".view2_id, "*SELECT* 1".clicks | | | | | | Execute on: All Nodes | | | | | | +---> UNION [Cost: 5K, Rows: 225K] (PATH ID: 8) | | | | | | | Execute on: All Nodes | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 3K, Rows: 224K] (PATH ID: 10) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 224K] (PATH ID: 11) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: ((pts_raw_data_click_detail.view_date >= '2013-10-07'::date) AND (pts_raw_data_click_detail.view_date < '2013-10-22'::date)) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id > 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 1K, Rows: 1K] (PATH ID: 13) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 1K] (PATH ID: 14) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: (pts_raw_data_click_detail.view_date = '2013-10-07'::date) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id = 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes +-Target Projection: oaslt.pts_raw_data_delivery_detail_no1 (DELETE ON CONTAINER) | Target Prep: | Execute on: All Nodes | +---> JOIN HASH [Semi] [Cost: 360M, Rows: 14B] (PATH ID: 1) Inner (BROADCAST) | | Join Cond: (pts_raw_data_delivery_detail.view1_id = cl.view1_id) AND (pts_raw_data_delivery_detail.view2_id = cl.view2_id) AND (pts_raw_data_delivery_detail.view_time = cl.view_time) | | Execute on: All Nodes | | +-- Outer -> STORAGE ACCESS for pts_raw_data_delivery_detail [Cost: 150M, Rows: 29B] (PATH ID: 2) | | | Projection: oaslt.pts_raw_data_delivery_detail_no1 | | | Materialize: pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time | | | Filter: ((pts_raw_data_delivery_detail.client_id_num <> 1) OR (pts_raw_data_delivery_detail.campaign_id_num <> 2094)) | | | Execute on: All Nodes | | | Runtime Filters: (SIP21(HashJoin): pts_raw_data_delivery_detail.view1_id), (SIP22(HashJoin): pts_raw_data_delivery_detail.view2_id), (SIP23(HashJoin): pts_raw_data_delivery_detail.view_time), (SIP24(HashJoin): pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time) | | +-- Inner -> SELECT [Cost: 1M, Rows: 144M] (PATH ID: 3) | | | Execute on: All Nodes | | | +---> JOIN HASH [Cost: 1M, Rows: 144M] (PATH ID: 4) Inner (BROADCAST) | | | | Join Cond: (d.view1_id = c.view1_id) AND (d.view2_id = c.view2_id) | | | | Materialize at Output: d.view_time | | | | Execute on: All Nodes | | | | +-- Outer -> STORAGE ACCESS for d [Cost: 861K, Rows: 144M] (PATH ID: 5) | | | | | Projection: oaslt.pts_raw_data_delivery_detail_no2 | | | | | Materialize: d.client_id_num, d.campaign_id_num, d.view1_id, d.view2_id | | | | | Filter: (d.view_date = '2013-10-07'::date) | | | | | Filter: (d.view1_id > 0) | | | | | Filter: (d.view2_id > (-1)) | | | | | Filter: ((d.client_id_num <> 1) OR (d.campaign_id_num <> 2094)) | | | | | Execute on: All Nodes | | | | | Runtime Filters: (SIP18(HashJoin): d.view1_id), (SIP19(HashJoin): d.view2_id), (SIP20(HashJoin): d.view1_id, d.view2_id) | | | | +-- Inner -> SELECT [Cost: 5K, Rows: 3] (PATH ID: 6) | | | | | Execute on: All Nodes | | | | | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5K, Rows: 3] (PATH ID: 7) | | | | | | Group By: "*SELECT* 1".view1_id, "*SELECT* 1".view2_id, "*SELECT* 1".clicks | | | | | | Execute on: All Nodes | | | | | | +---> UNION [Cost: 5K, Rows: 225K] (PATH ID: 8) | | | | | | | Execute on: All Nodes | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 3K, Rows: 224K] (PATH ID: 10) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 224K] (PATH ID: 11) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: ((pts_raw_data_click_detail.view_date >= '2013-10-07'::date) AND (pts_raw_data_click_detail.view_date < '2013-10-22'::date)) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id > 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes | | | | | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 1K, Rows: 1K] (PATH ID: 13) | | | | | | | | Aggregates: count(*) | | | | | | | | Group By: pts_raw_data_click_detail.view1_id, pts_raw_data_click_detail.view2_id | | | | | | | | Execute on: All Nodes | | | | | | | | +---> STORAGE ACCESS for pts_raw_data_click_detail [Cost: 1K, Rows: 1K] (PATH ID: 14) | | | | | | | | | Projection: oaslt.pts_raw_data_click_detail_no1 | | | | | | | | | Materialize: pts_raw_data_click_detail.view2_id, pts_raw_data_click_detail.view1_id | | | | | | | | | Filter: (pts_raw_data_click_detail.view_date = '2013-10-07'::date) | | | | | | | | | Filter: (pts_raw_data_click_detail.view1_id = 1) | | | | | | | | | Filter: (pts_raw_data_click_detail.view2_id > (-1)) | | | | | | | | | Execute on: All Nodes ------------------------------ ----------------------------------------------- PLAN: BASE QUERY PLAN (GraphViz Format) ----------------------------------------------- digraph G { graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain\nupdate oaslt.pts_raw_data_delivery_detail\nset click_count = cl.clicks \nfrom (\n select\n d.view_time,\n d.view1_id,\n d.view2_id,\n c.clicks\n from oaslt.pts_raw_data_delivery_detail d\n join (\n (\n select\n view1_id,\n view2_id,\n count(*) as clicks\n from oaslt.pts_raw_data_click_detail\n where\n view_date \>= \'2013-10-07\'\n and view_date \< (\'2013-10-07\'::date +15)\n and view1_id \> 1\n and view2_id \> -1\n group by view1_id, view2_id\n )\n union\n (\n select\n view1_id,\n view2_id,\n count(*) as clicks\n from oaslt.pts_raw_data_click_detail\n where\n view_date = \'2013-10-07\'\n and view1_id = 1\n and view2_id \> -1\n group by view1_id, view2_id\n )\n ) c on (d.view1_id = c.view1_id and d.view2_id = c.view2_id)\n where\n d.view_date = \'2013-10-07\'\n and d.view1_id \> 0\n and d.view2_id \> -1\n and (d.client_id_num, d.campaign_id_num) \<\> (1, 2094)\n) cl\nwhere \n oaslt.pts_raw_data_delivery_detail.view1_id = cl.view1_id\n and oaslt.pts_raw_data_delivery_detail.view2_id = cl.view2_id\n and oaslt.pts_raw_data_delivery_detail.view_time = cl.view_time\n and (oaslt.pts_raw_data_delivery_detail.client_id_num, oaslt.pts_raw_data_delivery_detail.campaign_id_num) \<\> (1, 2094);\n\nAll Nodes Vector: \n\n node[0]=v_oaslt_dwh_node0001 (initiator) Up\n node[1]=v_oaslt_dwh_node0002 (executor) Up\n node[2]=v_oaslt_dwh_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out] 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"]; 1[label = "NewEENode \nOutBlk=[UncTuple(29)]", color = "green", shape = "box"]; 2[label = "ExprEval: \n pts_raw_data_delivery_detail.view_date\n pts_raw_data_delivery_detail.view_hour\n pts_raw_data_delivery_detail.view_time\n pts_raw_data_delivery_detail.deliver_mode\n pts_raw_data_delivery_detail.cost\n pts_raw_data_delivery_detail.subsite_id_num\n pts_raw_data_delivery_detail.publisher_id_num\n pts_raw_data_delivery_detail.host_id_num\n pts_raw_data_delivery_detail.click_count\n pts_raw_data_delivery_detail.targeting_group_id\n pts_raw_data_delivery_detail.new_user_id\n pts_raw_data_delivery_detail.reach_considered\n pts_raw_data_delivery_detail.user1_id\n pts_raw_data_delivery_detail.user2_id\n pts_raw_data_delivery_detail.view1_id\n pts_raw_data_delivery_detail.view2_id\n pts_raw_data_delivery_detail.currency\n pts_raw_data_delivery_detail.campaign_id_num\n pts_raw_data_delivery_detail.client_id_num\n pts_raw_data_delivery_detail.ad_group_id_num\n pts_raw_data_delivery_detail.ad_id_num\n pts_raw_data_delivery_detail.creative_id_num\n pts_raw_data_delivery_detail.network_id_num\n pts_raw_data_delivery_detail.random_id\n pts_raw_data_delivery_detail.row_id\n pts_raw_data_delivery_detail.gmtrefererh_id_num\n pts_raw_data_delivery_detail.epoch\n VAL(2)\n (-9223372036854775808)\nUnc: Date(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Char(1)\nUnc: Numeric(10,5)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(3)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 3[label = "StorageMergeStep: pts_raw_data_delivery_detail_no2; 6 sorted\nUnc: Date(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Char(1)\nUnc: Numeric(10,5)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(3)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"]; 4[label = "Join: Hash-Join: \n(oaslt.pts_raw_data_delivery_detail x oaslt.pts_raw_data_delivery_detail) using pts_raw_data_delivery_detail_no2 and subquery (PATH ID: 1)\n[Semi]\n\nUnc: Date(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Char(1)\nUnc: Numeric(10,5)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Varchar(3)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Float(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 5[label = "ScanStep: pts_raw_data_delivery_detail_no2\n((pts_raw_data_delivery_detail.client_id_num \<\> 1) OR (pts_raw_data_delivery_detail.campaign_id_num \<\> 2094))\nSIP7(HashJoin): pts_raw_data_delivery_detail.view1_id\nSIP8(HashJoin): pts_raw_data_delivery_detail.view2_id\nSIP9(HashJoin): pts_raw_data_delivery_detail.view_time\nSIP10(HashJoin): pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.view_time\nview_time\nview1_id\nview2_id\nclient_id_num RLE\ncampaign_id_num RLE\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nRLE: Integer(8)\nRLE: Integer(8)", color = "brown", shape = "box"]; 6[label = "Recv\nRecv from: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1003\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 7[label = "Send\nSend to: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1003\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 8[label = "StorageUnionStep: pts_raw_data_delivery_detail_no2\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"]; 9[label = "ExprEval: \n d.view_time\n d.view1_id\n d.view2_id\n c.clicks\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 10[label = "Join: Hash-Join: \n(oaslt.pts_raw_data_delivery_detail x oaslt.pts_raw_data_click_detail) using pts_raw_data_delivery_detail_no2 and subquery (PATH ID: 4)\n\nUnc: Timestamp(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 11[label = "ScanStep: pts_raw_data_delivery_detail_no2\n(d.view_date = \'2013-10-07\'::date)\n(d.view1_id \> 0)\n(d.view2_id \> (-1))\n((d.client_id_num \<\> 1) OR (d.campaign_id_num \<\> 2094))\nSIP4(HashJoin): d.view1_id\nSIP5(HashJoin): d.view2_id\nSIP6(HashJoin): d.view1_id, d.view2_id\nview_date RLE (not emitted)\nview1_id\nview2_id\nclient_id_num RLE\ncampaign_id_num RLE\nUnc: Integer(8)\nUnc: Integer(8)\nRLE: Integer(8)\nRLE: Integer(8)", color = "brown", shape = "box"]; 12[label = "Recv\nRecv from: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1002\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 13[label = "Send\nSend to: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1002\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 14[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 15[label = "FilterStep: \n(c.view1_id \> 0)\n(c.view2_id \> (-1))\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 16[label = "GroupByHash(Spill): 3 keys\nAggs:\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 17[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 18[label = "UnionAll\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 19[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 20[label = "GroupByHash(Spill): 2 keys\nAggs:\n count(*)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 21[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 22[label = "Recv\nRecv from: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1000\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 23[label = "Send\nSend to: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1000\nResegment\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 24[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 25[label = "GroupByHash: 2 keys\nAggs:\n count(*)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 26[label = "StorageUnionStep: pts_raw_data_click_detail_no1\nLocally Resegment\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "purple", shape = "box"]; 27[label = "GroupByPipe(HashPrepass): 2 keys\nAggs:\n count(*)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 28[label = "ExprEval: \n pts_raw_data_click_detail.view1_id\n pts_raw_data_click_detail.view2_id\n 1\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 29[label = "ScanStep: pts_raw_data_click_detail_no1\n((pts_raw_data_click_detail.view_date \>= \'2013-10-07\'::date) AND (pts_raw_data_click_detail.view_date \< \'2013-10-22\'::date))\n(pts_raw_data_click_detail.view1_id \> 1)\n(pts_raw_data_click_detail.view2_id \> (-1))\nview_date RLE (not emitted)\nview1_id\nview2_id\nUnc: Integer(8)\nUnc: Integer(8)", color = "brown", shape = "box"]; 30[label = "ParallelUnionStep: \nCombine\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 31[label = "GroupByHash(Spill): 2 keys\nAggs:\n count(*)\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 32[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 33[label = "Recv\nRecv from: v_oaslt_dwh_node0001,v_oaslt_dwh_node0002,v_oaslt_dwh_node0003\nNet id: 1001\n\nUnc: Integer(8)\nUnc: Integer(8)\nUnc: Integer(8)", color = "green", shape = "box"]; 34[label = "Send\nSend to: v_oaslt_dwh_node0001,v_oaslt_dwh_node000
  • -- former projections CREATE PROJECTION oaslt.pts_raw_data_delivery_detail_no1 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, deliver_mode ENCODING RLE, cost, subsite_id_num ENCODING BLOCK_DICT, publisher_id_num ENCODING RLE, host_id_num ENCODING BLOCK_DICT, click_count ENCODING RLE, targeting_group_id ENCODING BLOCK_DICT, new_user_id ENCODING RLE, reach_considered ENCODING DELTAVAL, user1_id, user2_id, view1_id, view2_id, currency ENCODING RLE, campaign_id_num ENCODING RLE, client_id_num ENCODING RLE, ad_group_id_num ENCODING RLE, ad_id_num ENCODING BLOCK_DICT, creative_id_num ENCODING RLE, network_id_num ENCODING RLE, random_id, row_id, gmtrefererh_id_num ENCODING RLE ) AS SELECT pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.cost, pts_raw_data_delivery_detail.subsite_id_num, pts_raw_data_delivery_detail.publisher_id_num, pts_raw_data_delivery_detail.host_id_num, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.targeting_group_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.reach_considered, pts_raw_data_delivery_detail.user1_id, pts_raw_data_delivery_detail.user2_id, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.ad_group_id_num, pts_raw_data_delivery_detail.ad_id_num, pts_raw_data_delivery_detail.creative_id_num, pts_raw_data_delivery_detail.network_id_num, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.row_id, pts_raw_data_delivery_detail.gmtrefererh_id_num FROM oaslt.pts_raw_data_delivery_detail ORDER BY pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.row_id SEGMENTED BY hash(pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.publisher_id_num) ALL NODES OFFSET 0; CREATE PROJECTION oaslt.pts_raw_data_delivery_detail_no2 ( view_date ENCODING RLE, view_hour ENCODING RLE, view_time ENCODING COMMONDELTA_COMP, deliver_mode ENCODING RLE, cost, subsite_id_num ENCODING BLOCK_DICT, publisher_id_num ENCODING RLE, host_id_num ENCODING BLOCK_DICT, click_count ENCODING RLE, targeting_group_id ENCODING BLOCK_DICT, new_user_id ENCODING RLE, reach_considered ENCODING DELTAVAL, user1_id, user2_id, view1_id, view2_id, currency ENCODING RLE, campaign_id_num ENCODING RLE, client_id_num ENCODING RLE, ad_group_id_num ENCODING RLE, ad_id_num ENCODING BLOCK_DICT, creative_id_num ENCODING RLE, network_id_num ENCODING RLE, random_id, row_id, gmtrefererh_id_num ENCODING RLE ) AS SELECT pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.view_time, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.cost, pts_raw_data_delivery_detail.subsite_id_num, pts_raw_data_delivery_detail.publisher_id_num, pts_raw_data_delivery_detail.host_id_num, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.targeting_group_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.reach_considered, pts_raw_data_delivery_detail.user1_id, pts_raw_data_delivery_detail.user2_id, pts_raw_data_delivery_detail.view1_id, pts_raw_data_delivery_detail.view2_id, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.ad_group_id_num, pts_raw_data_delivery_detail.ad_id_num, pts_raw_data_delivery_detail.creative_id_num, pts_raw_data_delivery_detail.network_id_num, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.row_id, pts_raw_data_delivery_detail.gmtrefererh_id_num FROM oaslt.pts_raw_data_delivery_detail ORDER BY pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.view_date, pts_raw_data_delivery_detail.click_count, pts_raw_data_delivery_detail.currency, pts_raw_data_delivery_detail.random_id, pts_raw_data_delivery_detail.new_user_id, pts_raw_data_delivery_detail.deliver_mode, pts_raw_data_delivery_detail.view_hour, pts_raw_data_delivery_detail.row_id SEGMENTED BY hash(pts_raw_data_delivery_detail.client_id_num, pts_raw_data_delivery_detail.campaign_id_num, pts_raw_data_delivery_detail.publisher_id_num) ALL NODES OFFSET 1;
  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee

    Recovery is the action required for a cluster to restore K-safety after a crash. Large numbers of deleted records can degrade the performance of a recovery. To improve recovery performance,we nee to purge the deleted rows.

    In HP Vertica, delete operations do not remove rows from physical storage. Unlike most databases, the DELETE command in HP Vertica marks rows as deletedso that they remain available to historical queries. These deleted rows are called historical data. Retention of historical data also applies to the UPDATE command, which is actually a combined DELETE and INSERT operation.
    The cost of retaining deleted data in physical storage can be measured in terms of:
    ----------------------------------------------------------------------------------------------------------------
    -Disk space for the deleted rows and delete markers-A performance penalty for reading and skipping over deleted data.

    => So after every heavy delete & update(update is delete + insert in vertica), it s recommended to purge the data also.
    1.
    To check for a high number of delete vectors you can use the following query:----------------------------------------------------------------------------
    select node_name,projection_name,storage_oid,count(*) from delete_vectors group by node_name,projection_name,storage_oid order by 4 desc limit 20; 
    2.
    Advanced the AHM and purged the tables with hundreds of delete vector containers: 
    select make_ahm_now(); --or select make_ahm_now(true); -- this allows the ahm to advance when a node is down
    select purge_table('schemaname.tablename'); ... (and other tables)


    =>You can optimize youe delete/update statments by checking this documetation link below:
    https://my.vertica.com/docs/6.1.x/HTML/index.htm#12675.htm

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file