Vertica database designer - "NON-OPTIMIZABLE" query

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