Default optimization modes v16
You can choose an optimization mode as the default setting for an EDB Postgres Advanced Server database cluster. You can also change this setting on a per-session basis by using the ALTER SESSION
command as well as in individual DELETE
, SELECT
, and UPDATE
commands in an optimizer hint. The configuration parameter that controls these default modes is OPTIMIZER_MODE
.
The table shows the possible values.
Hint | Description |
---|---|
ALL_ROWS | Optimizes for retrieving all rows of the result set. |
CHOOSE | Does no default optimization based on assumed number of rows to retrieve from the result set. This is the default. |
FIRST_ROWS | Optimizes for retrieving only the first row of the result set. |
FIRST_ROWS_10 | Optimizes for retrieving the first 10 rows of the results set. |
FIRST_ROWS_100 | Optimizes for retrieving the first 100 rows of the result set. |
FIRST_ROWS_1000 | Optimizes for retrieving the first 1000 rows of the result set. |
FIRST_ROWS(n) | Optimizes for retrieving the first n rows of the result set. You can't use this form as the object of the ALTER SESSION SET OPTIMIZER_MODE command. You can use it only in the form of a hint in a SQL command. |
These optimization modes are based on the assumption that the client submitting the SQL command is interested in viewing only the first n rows of the result set and not the remainder of the result set. Resources allocated to the query are adjusted as such.
Example: Specifying the number of rows to retrieve in the result set
Alter the current session to optimize for retrieval of the first 10 rows of the result set:
Example: Showing the current value of the OPTIMIZER_MODE parameter
You can show the current value of the OPTIMIZER_MODE
parameter by using the SHOW
command. This command depends on the utility. In PSQL, use the SHOW
command as follows:
The SHOW
command compatible with Oracle databases has the following syntax:
This example shows an optimization mode used in a SELECT
command as a hint: