2.4 Optimizer Hints

Table of Contents Previous Next


2 SQL Tutorial : 2.4 Optimizer Hints

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 will (generally) return the result set in the least amount of time. The server's choice of plan is dependent upon several factors:
Parameter values assigned to parameters in the Query Tuning section of the postgresql.conf file.
As a rule, the query planner will select the least expensive plan. You can use an optimizer hint to influence the server as it selects a query plan. An optimizer hint is a directive (or multiple 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 employ or avoid a specific plan when producing the result set.
statement_body
statement_body
Optimizer hints may be included in either of the forms shown above. Note that in both forms, a plus sign (+) must immediately follow the /* or -- opening comment symbols, with no intervening space, or the server will not interpret the following tokens as hints.
Remember that the hint is embedded within 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 non-existent in the SQL command, there will be no indication that any sort of error has occurred. No syntax error will be given and the entire hint is simply ignored.
If an alias is used for a table or view name in the SQL command, then the alias name, not the original object name, must be used in the hint. For example, in the command, SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acct, the alias for accounts, must be specified in the FULL hint, not the table name, accounts.
Use the EXPLAIN command to ensure that the hint is correctly formed and the planner is using the hint. See the Advanced Server documentation set for information on the EXPLAIN command.
In general, optimizer hints should not be used in production applications (where table data changes throughout the life of the application). By ensuring that dynamic columns are ANALYZEd frequently, the column statistics will be updated to reflect value changes, and the planner will use such information to produce the least cost plan for any given command execution. Use of optimizer hints defeats the purpose of this process and will result in the same plan regardless of how the table data changes.
A string with additional information. Note that there are restrictions as to what characters may be included in the comment. Generally, comment may only consist 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 will be ignored if the comment is not in this form.
The remainder of the DELETE, INSERT, SELECT, or UPDATE command.

2 SQL Tutorial : 2.4 Optimizer Hints

Table of Contents Previous Next