3.4.2 Access Method Hints
FULL(table) INDEX(table [ index ] [...]) Use index on table to access the relation. NO_INDEX(table [ index ] [...]) Do not use index on table to access the relation.The sample application does not have sufficient data to illustrate the effects of optimizer hints so the remainder of the examples in this section will use a banking database created by the pgbench application located in the PostgresPlus\9.5AS\bin subdirectory.The following steps create a database named, bank, populated by the tables, accounts, branches, tellers, and history. The –s 5 option specifies a scaling factor of five which results in the creation of five branches, each with 100,000 accounts, resulting in a total of 500,000 rows in the accounts table and five rows in the branches table. Ten tellers are assigned to each branch resulting in a total of 50 rows in the tellers table.Note, if using Linux use the export command instead of the SET PATH command as shown below.Ten transactions per client are then processed for eight clients for a total of 80 transactions. This will populate the history table with 80 rows.The EXPLAIN command shows the plan selected by the query planner. In the following example, aid is the primary key column, so an indexed search is used on index, accounts_pkey.The FULL hint is used to force a full sequential scan instead of using the index as shown below:The NO_INDEX hint also forces a sequential scan as shown below:In addition to using the EXPLAIN command as shown in the prior examples, more detailed information regarding whether or not a hint was used by the planner can be obtained by setting the client_min_messages and trace_hints configuration parameters as follows:The SELECT command with the NO_INDEX hint is repeated below to illustrate the additional information produced when the aforementioned configuration parameters are set.Note that if a hint is ignored, the INFO: [HINTS] line will not appear. This may be an indication that there was a syntax error or some other misspelling in the hint as shown in the following example where the index name is misspelled.