Vertica database designer - "NON-OPTIMIZABLE" query

alex1192181

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 ?

Jim_Knicely

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"...

alex1192181

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.

Jim_Knicely

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