Directed queries

Directed queries encapsulate information that the optimizer can use to create a query plan. Directed queries can serve the following goals:

• Preserve current query plans before a scheduled upgrade. In most instances, queries perform more efficiently after a Vertica upgrade. In the few cases where this is not so, you can use directed queries that you created before upgrading, to recreate query plans from the earlier version.
• Enable you to create query plans that improve optimizer performance. Occasionally, you might want to influence the optimizer to make better choices in executing a given query. For example, you can choose a different projection, or force a different join order. In this case, you can use a directed query to create a query plan that preempts any plan that the optimizer might otherwise create.
• Redirect an input query to a query that uses different semantics—for example, map a join query to a SELECT statement that queries a flattened table.

Directed query components

A directed query pairs two components:

• Input query: A query that triggers use of this directed query when it is active.
• Annotated query: A SQL statement with embedded optimizer hints, which instruct the optimizer how to create a query plan for the specified input query. These hints specify important query plan elements, such as join order and projection choices.

  Tip

  You can also use most optimizer hints directly in vsql. For details, see Hints.

Vertica provides two methods for creating directed queries:

• The optimizer can generate an annotated query from a given input query and pair the two as a directed query.
• You can write your own annotated query and pair it with an input query.

For a description of both methods, see Creating directed queries.