The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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