2.4.2 Access Method Hints

Table of Contents Previous Next


2 SQL Tutorial : 2.4 Optimizer Hints : 2.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.
In addition, the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints of Table 3‑2‑1 can be used.
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 twenty, which results in the creation of twenty branches, each with 100,000 accounts, resulting in a total of 2,000,000 rows in the pgbench_accounts table and twenty 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.
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, pgbench_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 forces a parallel sequential scan instead of use of the index 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 trace_hints configuration parameter as follows:
The SELECT command with the NO_INDEX hint is repeated below to illustrate the additional information produced when the trace_hints configuration parameters is 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.

2 SQL Tutorial : 2.4 Optimizer Hints : 2.4.2 Access Method Hints

Table of Contents Previous Next