The following hints influence how the optimizer accesses relations to create the result set.
Hint
Description
FULL(table)
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.
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.
This example creates 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 creates 20 branches, each with 100,000 accounts. This results in 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:
The FULL hint is used to force a full sequential scan instead of using the index:
The NO_INDEX hint forces a parallel sequential scan instead of using the index:
In addition to using the EXPLAIN command, you can get more detailed information about whether a hint was used by the planner by setting the trace_hints configuration parameter:
The SELECT command with the NO_INDEX hint shows the additional information produced when you set the trace_hints configuration parameters:
If a hint is ignored, the INFO: [HINTS] line doesn't appear. This might be indicate a syntax error or some other misspelling in the hint as shown in this example. The index name is misspelled.