Explain Plan

abhishek_1

When we EXPLAIN any query, we get to know the projections that is being used, whether group by hash or group by pipeline, statistics status etc..... But I want to understand what does the "Cost" and "Rows" in explain plan means? How does vertica determine the cost?

[Deleted User]

The cost is just a calculation that Vertica does with statistics and considering how much I/O, CPU, memory, network the plan will use based on the plan operators and order that vertica will implement. It is why it is important that you have updated statistics so the optimizer can make the best calculation possible and decide for the less costly plan. Hope this answer your question. Eugenia

abhishek_1

Thanks Eugenia !!!! What about the rows? Does it mean the number of rows the optimizer estimates it will return? Does it mean if we have lesser number of rows in explain plan then the query execution time will be fast? Regards, Abhi

[Deleted User]

The number of rows help to calculate the cost and also to determine the join order. Optimizer will always try to do the joins that cut the number of tuples for example, it will prefer m-1 joins, small input size first, etc to cut down early the number of tuples. This does not change the query execution. The optimizer uses this to chose what is the best plan to run the query. Then the execution engine will start to work with the optimizer plan. The number of rows and cost are just estimation and our optimizer team works pretty hard to get the calculation as precise as it can be but still are estimations. If you really want to see the rows produced in all the plans you can profile the query by doing profile .. query.. that will store everything in the execution_engine_profile table and there is a counter_name that is rows produced. Hope that helps and not confuse you more , Eugenia

abhishek_1

Thanks a lot Eugenia !!!! you clear my doubts, Regards, Abhi

lsilecchia

Thanks for Better Explanation Eugenia.

 

Regards,

Pravin