Optimizer selection of projection
hi
I have defined various projections for my testing. I see the optimizer is choosing the projection that has no encoding specified, as the cost is showing up low. I don't know why. I have applied encoding as per the DBD.
How can I check for cost for each of the projections that optimizer would have calculated when it has to pick one. I did not see the projection names in dc_design_query_plan_costs.
Any help, greatly appreciated
thanks
0
Comments
How big is the table? How big is your cluster? What query are you running?
This is a very generic question. Assuming your "unused" projection has the same columns as the one Vertica is using, you could use the name of the projection in your query to figure out the cost. I would not recommend this for production use. Just for testing purposes only.
Thank you. its a 15 node cluster and the table is part of a complex view. I was thinking if we have any dc tables in Vertica, that stores the analysis of Explain results, to be able to see the cost tied to each of the projections being considered before optimizer picks one.
Definitely your solution will work, but in my case, I need to redefine the view definition, everytime I need to test the cost for various combination of projections for my testing.
AFAIK. You cant store them in a table but you could defn dump them to a file.
If you want to see the cost for your queries you can use execution engine profiles.
Check this out.
https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/EXECUTION_ENGINE_PROFILES.htm