This topic describes Access Method Hints.

The following hints determine how the optimizer accesses relations to create a result set.

HintDescription
FULL(table)Performs a full sequential scan on the table.
INDEX(table [ index ] [...])Uses the index on the table to access a relation.
NO_INDEX(table [ index ] [...])Does not use the index on the table to access a relation.

In addition, the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints in Default optimization mode can be used.

Examples

The sample application does not have enough data to describe the effect of optimizer hints. Therefore, the remaining examples in this section use the bank database that is created by the pgbench application. This application is located in the bin subdirectory of PolarDB for PostgreSQL(Compatible with Oracle).

In the following step, a database named bank is created. The database is populated by the pgbench_accounts, pgbench_branches, pgbench_tellers, and pgbench_history tables. The -s 20 option specifies a scaling factor as 20. This factor allows you to create 20 branches. Each branch has 100,000 accounts. Therefore, a total of 2,000,000 rows are generated in the pgbench_accounts table and 20 rows are generated in the pgbench_branches table. Ten tellers are assigned to each branch. As a result, a total of 200 rows are generated in the pgbench_tellers table.

The following example shows how to initialize the pgbench application in the bank database:

creatpolardb -U polardb bank
CREATE DATABASE

pgbench -i -s 20 -U polardb bank

NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 2000000 tuples (5%) done (elapsed 0.11 s, remaining 2.10 s)
200000 of 2000000 tuples (10%) done (elapsed 0.22 s, remaining 1.98 s)
300000 of 2000000 tuples (15%) done (elapsed 0.33 s, remaining 1.84 s)
400000 of 2000000 tuples (20%) done (elapsed 0.42 s, remaining 1.67 s)
500000 of 2000000 tuples (25%) done (elapsed 0.52 s, remaining 1.57 s)
600000 of 2000000 tuples (30%) done (elapsed 0.62 s, remaining 1.45 s)
700000 of 2000000 tuples (35%) done (elapsed 0.73 s, remaining 1.35 s)
800000 of 2000000 tuples (40%) done (elapsed 0.87 s, remaining 1.31 s)
900000 of 2000000 tuples (45%) done (elapsed 0.98 s, remaining 1.19 s)
1000000 of 2000000 tuples (50%) done (elapsed 1.09 s, remaining 1.09 s)
1100000 of 2000000 tuples (55%) done (elapsed 1.22 s, remaining 1.00 s)
1200000 of 2000000 tuples (60%) done (elapsed 1.36 s, remaining 0.91 s)
1300000 of 2000000 tuples (65%) done (elapsed 1.51 s, remaining 0.82 s)
1400000 of 2000000 tuples (70%) done (elapsed 1.65 s, remaining 0.71 s)
1500000 of 2000000 tuples (75%) done (elapsed 1.78 s, remaining 0.59 s)
1600000 of 2000000 tuples (80%) done (elapsed 1.93 s, remaining 0.48 s)
1700000 of 2000000 tuples (85%) done (elapsed 2.10 s, remaining 0.37 s)
1800000 of 2000000 tuples (90%) done (elapsed 2.23 s, remaining 0.25 s)
1900000 of 2000000 tuples (95%) done (elapsed 2.37 s, remaining 0.12 s)
2000000 of 2000000 tuples (100%) done (elapsed 2.48 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

A total of 500,000 transactions are processed. Therefore, the pgbench_history table is populated with 500,000 rows.

pgbench -U polardb -t 500000 bank

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average: 0.000 ms
tps = 1464.338375 (including connections establishing)
tps = 1464.350357 (excluding connections establishing)

The following example shows the table definitions:

\d pgbench_accounts

   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

\d pgbench_branches

   Table "public.pgbench_branches"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 bid      | integer       | not null
 bbalance | integer       |
 filler   | character(88) |
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)

\d pgbench_tellers

    Table "public.pgbench_tellers"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 tid      | integer       | not null
 bid      | integer       |
 tbalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

\d pgbench_history

          Table "public.pgbench_history"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 tid    | integer                     |
 bid    | integer                     |
 aid    | integer                     |
 delta  | integer                     |
 mtime  | timestamp without time zone |
 filler | character(22)               |

The EXPLAIN command shows the plan that is selected by the query planner. In the following example, aid is the primary key column. An indexed search is used on the pgbench_accounts_pkey index.

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid = 100;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..8.45 rows=1 width=97)
   Index Cond: (aid = 100)
(2 rows)

In the following example, the FULL hint is used to force a full sequential scan. No index is used.

EXPLAIN SELECT /*+ FULL(pgbench_accounts) */ * FROM pgbench_accounts WHERE aid = 100;

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..58781.69 rows=1 width=97)
   Filter: (aid = 100)
(2 rows)

In the following example, the NO_INDEX hint is used to force a parallel sequential scan. No index is used.

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_pkey) */ * FROM pgbench_accounts WHERE aid = 100;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather  (cost=1000.00..45094.80 rows=1 width=97)
   Workers Planned: 2
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..44094.70 rows=1 width=97)
         Filter: (aid = 100)
(4 rows)

In addition to the EXPLAIN command in the preceding examples, you can specify the trace_hints configuration parameter to retrieve more detailed information about whether a hint is used by the planner.

SET trace_hints TO on;

In the following example, the SELECT command that has the NO_INDEX hint is repeated to illustrate the additional information that is generated after you specify the trace_hints configuration parameters.

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_pkey) */ * FROM pgbench_accounts WHERE aid = 100;

INFO:  [HINTS] Index Scan of [pgbench_accounts].[pgbench_accounts_pkey] rejected due to NO_INDEX hint.
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather  (cost=1000.00..45094.80 rows=1 width=97)
   Workers Planned: 2
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..44094.70 rows=1 width=97)
         Filter: (aid = 100)
(4 rows)

Take note of the following item: If a hint is ignored, the INFO: [HINTS] line does not appear. This may indicate that some syntax errors or spelling errors exist in the hint. The following example shows that the index name is misspelled.

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_xxx) */ * FROM pgbench_accounts WHERE aid = 100;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..8.45 rows=1 width=97)
   Index Cond: (aid = 100)
(2 rows)