Vertica database designer - "NON-OPTIMIZABLE" query
Hello,
I am trying to create vertica database designer plan based on list of queries.
All queries are rejected with "NON-OPTIMIZABLE" status.
What is that mean, where I could find definition of query that could be consumed for design plan by vertica designer ?
0
Comments
Hi,
A DBD optimization status of "NON-OPTIMZEABLE" indicates that the "Ideal plan" has no benefits. For example, a query like "SELECT * FROM foo;" can not be optimized.
This is due to the query not having any "Plan Benefits" like "RLE or Value-index on predicate columns", "Merge join / Sort Merge join", "Fully distributed join", etc.
Take a look here:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/PhysicalSchema/DBD/ViewingDatabaseDesignerLogs.htm
The "Plan Benefits" that DBD is looking for in your queries are listed there as "Possible features"...
Jim_Knicely, thanks a lot for your response.
Than how can we create optimal design projections (from vertica database designer) that is based on our top queries that hit the table ?
Per my understanding, it should recommend projection with the fields used in WHERE/GROUP BY sections of top queries. Is it possible with vertica designer ?
Thanks.
Yes, DBD will recommend projections based on "...fields used in WHERE/GROUP BY...". Are you trying a "Comprehensive" design or a "Incremental"? Are you using MC to create the design? Maybe try using admintools with a text file that contains your top queries?
See:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/GettingStartedGuide/UsingDatabaseDeisgner/RunningDBDwithAT.htm