QUERY spending lot of time in operator "ParallelUnion"

Hi,

Can you share the plan and tell us in wich path_id of the plan the parallel union takes the longest?

Eugenia 

path id = 4 doing parallelunion

JOIN HASH [LeftOuter] [Cost: 7M, Rows: 76M] (PATH ID: 3) Outer (LOCAL ROUND ROBIN) Inner (BROADCAST)

| |      Join Cond: (cust.Cust_Id = aggregate.sold_to_cust_id) AND ((customer.sales_org_cd) = aggregate.sales_org_cd)

| |      Execute on: All Nodes

| | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 5M, Rows: 76M] (PATH ID: 4) Inner (RESEGMENT)

| | |      Join Cond: (aggregate.prod_id = prd_attrb.prod_id)

| | |      Join Filter: (aggregate.region= 'EURO')

| | |      Execute on: All Nodes

| | | +-- Outer -> SELECT [Cost: 4M, Rows: 76M] (PATH ID: 5)

| | | |      Execute on: All Nodes

| | | | +---> STORAGE ACCESS for laggregate_table [Cost: 4M, Rows: 76M] (PATH ID: 6)

You have a re-segmentation there, what means that the data is sent by network while doing the JOIN. There is much more info that you need to compare, the parallel union may just be stall waiting for data. If the query print the 10k in 15 seconds, maybe is doing it with the local data not the whole set. Have you tried to run the query complete with select count(*) from (<query>)a; or using \o /dev/null?

If you saved the whole profile data have you compare other operators? Sometimes I use a query like this:

SELECT path_id, operator_name, count(distinct operator_id) num_operators, count(distinct node_name) num_nodes , sum(DECODE(counter_name, 'bytes received', counter_value, NULL)) AS 'bytes_received' , sum(DECODE(counter_name, 'bytes sent', counter_value, NULL)) AS 'bytes_sent' , sum(DECODE(counter_name, 'execution time (us)', counter_value, NULL)) AS 'execution_time_us' , sum(DECODE(counter_name, 'rows received', counter_value, NULL)) AS 'rows_received' --, sum(DECODE(counter_name, 'rle rows produced', counter_value, NULL)) AS 'rle_rows_produced' , sum(DECODE(counter_name, 'rows produced', counter_value, NULL)) AS 'rows_produced' , sum(DECODE(counter_name, 'consumer stall (us)', counter_value, NULL)) AS 'consumer_stall' , sum(DECODE(counter_name, 'producer stall (us)', counter_value, NULL)) AS 'producer_stall' --, sum(DECODE(counter_name, 'cumulative size of raw temp data (bytes)', counter_value, NULL)) AS 'cumulative size of raw temp data' from dc_execution_engine_profiles where transaction_id=45035996363026792  and statement_id=3 group by 1, 2

that helps me to compare the different counters and operator and find the issues.

ParelellUnion in that path what is doing is combine the segmented received from the other nodes. If you want to avoid the re-segmentation you need to have the projection equal segmented or replicate one the projections. Hope this make sense , if the bottle neck is there it has nothing to do with the CTAS, but rather with processing the whole data set. I hope this make sense.

Eugenia

Thanks  a ton Eugenia.

I believe that it is almost impossible to avoid resegmentation of dimension table data.  The fact table's 10 attributes are joined with 10 dimension tables.    The fact table contains 70 million rows and we are doing a left outer join with all dimensions. 

To avoid re-segmentation of dimension data, I can unsegment all the dimension tables < 100k rows. 

I plan to try that out.    

Also I am going to use your profile query mentioned here to compare profile operators.   I think Vertica documentation does not provide a description of each operators and what it means.   The document has description about counter names.