When you invoke the DELETE, INSERT, SELECT or UPDATE statement, the server generates a set of execution plans. After analyzing those execution plans, the server selects a plan that returns a result set within the least amount of time. The server selects a plan based on several factors:

  • The estimated execution cost of data handling operations.
  • Parameter values assigned to parameters in the Query Tuning section of the postgresql.conf file.
  • Column statistics that have been gathered by the ANALYZE statement.

The query planner selects the most cost-effective plan. You can use an optimizer hint to set the mode in which the server selects a query plan. An optimizer hint includes one or more directives embedded in a syntax similar to a comment. The syntax immediately follows the DELETE, INSERT, SELECT or UPDATE statement. When the server generates a result set, the server employs or avoids a specific plan based on keywords in the comment.

{ DELETE | INSERT | SELECT | UPDATE } /*+ { hint [ comment ] } [...] */
 statement_body

{ DELETE | INSERT | SELECT | UPDATE } --+ { hint [ comment ] } [...]
 statement_body

Optimizer hints may be included in either of the preceding forms. In both forms, a plus sign (+) must immediately follow the /* or -- opening comment symbols, with no spaces between the signs. Otherwise, the server cannot interpret the following tokens as hints.

If you use the first form, the hint and optional comment may span multiple lines. The second form requires all hints and comments to occupy a single line. The remaining parts of the statement must start on a new line.

Note
  • The database server always tries to use the specified hints.
  • If a planner method parameter is set to disable a certain plan type, this plan is not be used even if the plan is specified in a hint, unless no other options are available to the planner. Examples of planner method parameters are enable_indexscan, enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop. All these parameters are Boolean parameters.
  • The hint is embedded within a comment. If the hint is misspelled, or if any parameter of the hint such as the view, table, or column name is misspelled or does not exist in the SQL statement, the system does not indicate that any type of error has occurred. No syntax error is specified and the entire hint is ignored.
  • If an alias is used for a table or view name in the SQL statement, the alias name rather than the original object name must be used in the hint. For example, in the statement, SELECT /*+ FULL(acct) */ * FROM accounts acct ..., the alias of acct for accounts rather than the table name accounts must be specified in the FULL hint.

Use the EXPLAIN statement to make sure that the hint is correctly formed and the planner uses the hint. For more information about the EXPLAIN statement, see the documentation of PolarDB databases compatible with Oracle.

Optimizer hints cannot be used in production applications where table data changes throughout the life of the application. To make sure that dynamic columns are frequently analyzed with the ANALYZE statement, the column statistics is updated to reflect value changes, and the planner uses the statistics to generate the most cost-effective plan for any specified statement execution. However, optimizer hints generate in the same plan, regardless of how the table data changes.

Parameters

Parameter Description
hint An optimizer hint directive.
comment A string with additional information. The characters that can be included in a comment are restricted. A comment can only contain letters, digits, underscores (_), dollar signs ($), number signs (#), and space characters. These characters must conform to the syntax of an identifier. Any subsequent hint is ignored if the comment is not in this form.
statement_body The remaining part of the DELETE, INSERT, SELECT, or UPDATE statement.

For more information about the optimizer hint directives, see the following topics.