When you execute the DELETE, INSERT, SELECT, or UPDATE statement, PolarDB for PostgreSQL(Compatible with Oracle) generates a set of execution plans. PolarDB for PostgreSQL(Compatible with Oracle) analyzes these execution plans and then selects the execution plan that can return a result set within the least amount of time.
PolarDB for PostgreSQL(Compatible with Oracle) selects an execution plan based on the following factors:
- The estimated execution cost of data processing
- Values that are assigned to the parameters in the Query Tuning section of the postgresql.conf file
- Column statistics that are gathered by the ANALYZE statement
In most cases, PolarDB for PostgreSQL(Compatible with Oracle) selects the execution plan that consumes the fewest resources. You can use an optimizer hint to affect the selection.
An optimizer hint is a directive that immediately follows the DELETE, INSERT, SELECT, or UPDATE statement. An optimizer hint is embedded in a syntax similar to a comment. When PolarDB for PostgreSQL(Compatible with Oracle) generates a result set, PolarDB O Edition selects or avoids a specific execution plan based on the keywords in the comment.
Syntax
Optimizer hints can be included in one of the following formats:
{ DELETE | INSERT | SELECT | UPDATE } /*+ { hint [ comment ] } [...] */
statement_body
{ DELETE | INSERT | SELECT | UPDATE } --+ { hint [ comment ] } [...]
statement_body- A plus sign (+) must immediately follow the /* or -- opening comment symbol. Make sure that no space characters exist between the plus sign (+) and the opening comment symbol. Otherwise, PolarDB O Edition cannot interpret the corresponding content as a hint.
- In the preceding two formats, hints and comments are displayed in a different manner. In the first format, hints and comments can span multiple lines. In the second format, hints and comments must be placed on one line. However, in both formats, the statement_body part must start on a new line.
- We recommend that you use hints together with the EXPLAIN statement to ensure that the format of hints is valid.
Parameters
| Parameter | Description |
|---|---|
| hint | An optimizer hint directive. |
| comment | A string with additional information. The characters that can be included in a comment are limited. In most cases, a comment can contain only letters, digits, underscores (_), dollar signs ($), number signs (#), and space characters. These characters must conform to the syntax of an identifier. If a comment does not meet these requirements, PolarDB O Edition ignores the subsequent hints. |
| statement_body | The remaining part of the DELETE, INSERT, SELECT, or UPDATE statement. |
Additional considerations
- If you configure a planner method parameter to disable a plan type, an execution plan of this type is not used regardless of whether the execution plan is specified in a hint. In this case, the execution plan is used only if no other execution plans are available. The following parameters are examples of planner method parameters: enable_indexscan, enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop. All of these parameters are of the Boolean type.
- A hint is embedded in a comment. If the hint is misspelled or a 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 prompt syntax errors. The system only ignores the entire hint.
- If an alias is used as a table or view name in an SQL statement, the alias name instead of the original object name must be used in the hints. For example, in the
SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acctstatement, you must specify the alias acct instead of the table name accounts in the FULL hint. - We recommend that you do not use optimizer hints in production environments because table data frequently changes in production environments.
For more information about optimizer hints, see the following topics.