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.

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.