Directed Queries through Optimizer Hints


A less highlighted session from the Big Data Conference discussed an upcoming feature for plan caching, or Directed Queries. This feature will give control which was previously missing around custom optimizations in query executions. There are some parameters which have been available with SET_OPTIMIZER_DIRECTIVES(), but none reach the breath of functionality that will come with this feature.

Notice: This feature is not yet released, it may change before release, and it is not a commitment to deliver for a particular release.

Up until this feature, Vertica would obtain a fresh plan at query execution. The reason being that data can change so frequently that caching didn’t make sense. For a majority of queries and customers, this was not an issue.

In some rare cases of upgrades, the algorithm behind the query optimizer would change and a query would no longer execute or perform efficiently. Additionally, changes in data characteristics could affect execution plans. With Directed Queries, plans will be more stable and allow for custom tuning.


Given the example below, it could be assumed that Vertica would perform a join on online_sales_fact and customer_dimension first:

After a hypothetical upgrade, Vertica now chooses to use store_sales_fact first and the performance of the query degrades. With Directed Queries, the initial plan can be stored:

… then customized:

Directed Queries will store plans in a directed query repository and call these stored plans when a matching query is executed.

Annotated Query

The execution plan will be stored as an annotated SQL query which looks like:

Projection Directives

The feature goes further by allowing projection directives for each join:

Data Distribution & Constants

Lastly, data distribution can be forced at the join input and constants ignored:


The hints presented at the session are below. To try out this upcoming feature, a beta is available through a request form.

JType('')H = Hash
M = Merge
FM = Force if not already sorted
distrib('')L = Local
B = Broadcast
R = Resegment on Join Key
F = Filter
IgnoreConst()defined in predicate

About the author / 

Norbert Krupa

Norbert is the founder of and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.


This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: