Access method hints - PolarDB - Alibaba Cloud Documentation Center

This topic describes Access Method Hints.

Syntax

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

Hint	Description
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.

Example

Prepare basic data. 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.
1. Create a Linux ECS instance. The ECS instance in this example runs the CentOS 7.3 64-bit operating system. For more information, see Create an instance on the Custom Launch tab.
  Note
  We recommend that you create the ECS instance in the same zone and virtual private cloud (VPC) as the PolarDB cluster.
2. Install the PostgreSQL client on the ECS instance. For more information, see the PostgreSQL official documentation.
  Note
  Make sure that the PostgreSQL client version that you install is compatible with the version of the PolarDB for PostgreSQL (Compatible with Oracle) cluster to which you want to connect.
3. Create a privileged account. For more information, see Create a database account.
4. Obtain the cluster endpoint. For more information, see View or apply for an endpoint.
5. If the PolarDB cluster and ECS instance reside in the same zone, use an internal endpoint. Otherwise, apply for a public endpoint. Add the IP address of the ECS instance to the whitelist of the PolarDB cluster. For more information, see Configure a whitelist for a cluster.
6. Create the bank database in the console. For more information, see Database management.
7. Run the following command by using the client on the ECS instance to populate the bank databse by the pgbench_accounts, pgbench_branches, pgbench_tellers and pgbench_history tables.
```
pgbench -h <PolarDB cluster endpoint> -p <port of PolarDB cluster endpoint> -i -s 20 -U <PolarDB database username> bank
```
  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.
  A total of 500,000 transactions are processed. Therefore, the pgbench_history table is populated with 500,000 rows.
```
pgbench -h <PolarDB cluster endpoint> -p <port of PolarDB cluster endpoint> -t 500000 -U <PolarDB database username> bank
```
  Run the following command in the PostgreSQL client to query the table schema. You can query the schema by using other tools. For more information about how to query a table schema by using DMS, see Query and change a schema.
```
\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)               |
```

Execute the following queries.

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;