8.5 Query Optimization Hints

Table of Contents Previous Next


8 Performance Analysis and Tuning : 8.5 Query Optimization 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.
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 EDB Postgres documentation set for information on the EXPLAIN command.
In general, optimizer hints should not be used in production applications. Typically, the table data changes throughout the life of the application. By ensuring that the more 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.

8 Performance Analysis and Tuning : 8.5 Query Optimization Hints

Table of Contents Previous Next