Skip to main content

Is the outer join done in multiple thread?

  • August 24, 2015
  • 3 replies
  • 17 views
Daniel-Wu

I am using the following SQL to check

Select operator_name,count(*) from v_monitor.execution_engine_profiles where transaction_id=XXX

and statement_id=1
and counter_name ='clock time (us)' group by operator_name

 

When use inner join  (hash), the result is:

 

GroupByPipe240
Root1
NewEENode6
ExprEval240
GroupByHash240
ParallelUnion130
Scan245
Join240

 

But if use left join, the result is:

Root1
NewEENode6
ExprEval5
GroupByPipe10
Scan245
Join5
Filter5
Sort5

 

Consider I have a cluster of 5 nodes, so looks like vertica can only sue one thread per node for outer join but use multiple threads each node for inner join.

 

I have SQL wich has many left join and the performane is very bad comparing with sql server, sql server could do outer join in multiple thread mode, why vertica can't use multiple threads for outer join?

    3 replies

    colin_loghin
    Forum|alt.badge.img+2
    • colin_loghin
    • Participating Frequently
    • August 24, 2015

    Can you add the query ( or a simplified version of it ) ? Also, can you check explain local verbose SQLQUERY ? It shows worker thread information for the query. Try it for both inner and outer join query. Publish the results if you can.  It would be useful to know if the operation is singlethreaded or not and whether it could be made multithreaded. My expectation is that left joins are no different than inner joins and could be run multithreaded with multiple threads  splitting the left side of the left outer join. 

    Daniel-Wu
    • Daniel-Wu
    • Author
    • New Participant
    • August 25, 2015

     

    The query is:

    select a.store_key,count(*) from PNG_WALGRN_JOHN.SPD_FACT_PIVOT a left join PNG_WALGRN_JOHN.SPD_FACT_PIVOT b on a.item_key=b.store_key
    where a.period_key=20150713
    group by a.store_key;

     

    That's a left  join, it's run in parrallel of join on each ndoe, but if replace left join with inner jion, then it's run in single thread in in node.

     

    Attached is the local verbose plan.

    Daniel-Wu
    • Daniel-Wu
    • Author
    • New Participant
    • August 27, 2015

    I have provided the explain file, could anyone help? Why inner join could run in parallel inside a node, but left join can't 

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement