Explain Plan

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?

Comments

  • 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
  • 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
  • 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
  • Thanks a lot Eugenia !!!! you clear my doubts, Regards, Abhi
  • Thanks for Better Explanation Eugenia.

     

    Regards,

    Pravin

Leave a Comment

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