Optimizer hints v14

When you invoke a DELETE, INSERT, SELECT, or UPDATE command, the server generates a set of execution plans. After analyzing those execution plans, the server selects a plan that generally returns the result set in the least amount of time. The server's choice of plan depends 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 were gathered by the ANALYZE command

As a rule, the query planner selects the least expensive plan. You can use an optimizer hint to influence the server as it selects a query plan. An optimizer hint is one or more directives embedded in a comment-like syntax that immediately follows a DELETE, INSERT, SELECT or UPDATE command. Keywords in the comment instruct the server to use or avoid a specific plan when producing the result set.

Synopsis

You can incude optimizer hints in either of these forms:

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

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

In both forms, a plus sign (+) must immediately follow the /* or -- opening comment symbols, with no intervening space. Otherwise the server doesn't interpret the tokens that follow as hints.

If you're using the first form, the hint and optional comment might span multiple lines. In the second form, all hints and comments must occupy a single line. The rest of the statement must start on a new line.

Description

Note:

• The database server always tries to use the specified hints if at all possible.
• If a planner method parameter is set so as to disable a certain plan type, then this plan isn't used even if it is specified in a hint, unless there are no other possible options for the planner. Examples of planner method parameters are enable_indexscan, enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop. These are all Boolean parameters.
• The hint is embedded in a comment. As a consequence, if the hint is misspelled or if any parameter to a hint such as view, table, or column name is misspelled or nonexistent in the SQL command, there's no indication that an error occurred. No syntax error is given. The entire hint is silently ignored.
• If an alias is used for a table name in the SQL command, then you must use the alias name in the hint, not the original table name. For example, in the command SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acct, you must specify the alias for accounts in the FULL hint, not in the table name accounts.

Use the EXPLAIN command to ensure that the hint is correctly formed and the planner is using the hint.

In general, don't use optimizer hints in a production application, where table data changes throughout the life of the application. By ensuring that dynamic columns are analyzed frequently via the ANALYZE command, the column statistics are updated to reflect value changes. The planner uses such information to produce the lowest-cost plan for any given command execution. Use of optimizer hints defeats the purpose of this process and results in the same plan regardless of how the table data changes.

Parameters

hint

An optimizer hint directive.

comment

A string with additional information. Comments have restrictions as to what characters you can include. Generally, comment can consist only of alphabetic, numeric, the underscore, dollar sign, number sign, and space characters. These must also conform to the syntax of an identifier. Any subsequent hint is ignored if the comment isn't in this form.

statement_body

The remainder of the DELETE, INSERT, SELECT, or UPDATE command.

• On this page
• Synopsis
• Description
• Parameters