The following hints influence how the optimizer accesses relations to create the result set.
Perform a full sequential scan on table.
INDEX(table [ index ] [...])
Use index on table to access the relation.
NO_INDEX(table [ index ] [...])
Don't use index on table to access the relation.
In addition, you can use the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints.
INDEX and NO_INDEX hints for the partitioned table internally expand to include the corresponding inherited child indexes and apply in later processing.
About the examples
The sample application doesn't have enough data to show the effects of optimizer hints. Thus the remainder of these examples use a banking database created by the pgbench application located in the EDB Postgres Advanced Server bin subdirectory.
Example: Create a sample database and tables
The following steps create a database named, bank populated by the tables pgbench_accounts, pgbench_branches, pgbench_tellers, and pgbench_history. The –s 20 option specifies a scaling factor of 20, which results in the creation of 20 branches. Each branch has 100,000 accounts. The result is a total of 2,000,000 rows in the pgbench_accounts table and 20 rows in the pgbench_branches table. Ten tellers are assigned to each branch resulting, in a total of 200 rows in the pgbench_tellers table.
The following initializes the pgbench application in the bank database.
A total of 500,00 transactions are then processed. These transactions populate the pgbench_history table with 500,000 rows.
The following are the table definitions:
The EXPLAIN command shows the plan selected by the query planner. In this example, aid is the primary key column, so an indexed search is used on index pgbench_accounts_pkey:
Example: FULL hint
The FULL hint forces a full sequential scan instead of using the index:
Example: NO_INDEX hint
The NO_INDEX hint forces a parallel sequential scan instead of using the index:
Example: Tracing optimizer hints
You can obtain more detailed information than the EXPLAIN command provides about whether the planner used a hint. To do so, set the trace_hints configuration parameter as follows:
The SELECT command with the NO_INDEX hint shows the additional information produced when you set the trace_hints configuration parameters:
Example: Hint ignored
If a hint is ignored, the INFO: [HINTS] line doesn't appear. This might indicate a syntax error or some other misspelling in the hint. In this example, the index name is misspelled.