OR condition query taking too long to execute 3 node vertica cluster
Hi,
I have a 3 node vertica cluster, I have 2 equally segmented tables on all the nodes when I try executing OR query for just a 5million records in each table it is taking around 20minutes to execute query. How can I reduce the execution time.
Problem is only with OR all other scenarios are working fine. I am using INNER JOIN for the tables. I have tried with both projections segmented, 1 unsegmented and 1 segmented but no improvement in the query performance. Please suggest
Except OR, all other conditions are executing within 1 minute for same set of data
Thanks
Amar
0
Comments
There is no obvious answer.
You need to start troubleshooting.
Use EXPLAIN command to look at execution plan. Search for PathID with big cost in relation to other PathIDs.
If you not going to see anything obviously heavy in execution plan then use PROFILE command to profile query.
Once query profiled query EXECUTION_ENGINE_PROFILES table for "clock time (us)" counter_name with ORDER BY descending.
You should see problematic operators on the top.
My rough guess is that query making Vertica to reshuffle all the data for this query.
But I could be wrong, because I do not know anything about your infrastructure, schema design, SQL queries, etc.
Can you share explain plan?