Access Method Hints

示例

示例应用程序没有足够的数据来说明optimizer hints的效果，因此本节中的其余示例将使用由位于 PolarDB PostgreSQL版（兼容Oracle） bin 子目录中的 pgbench 应用程序创建的银行数据库。

以下步骤将创建名为 bank 的数据库，该数据库由表 pgbench_accounts、pgbench_branches、pgbench_tellers 和 pgbench_history 填充。–s 20 选项指定的比例因子为 20，从而导致创建 20 个分行，每个具有 100,000 个帐户，在 pgbench_accounts 表中生成 2,000,000 行并在 pgbench_branches 表中生成 20 行。每个分行分配 10 个出纳员，从而在 pgbench_tellers 表中总计生成 200 行。

以下内容在 bank 数据库中初始化 pgbench 应用程序。

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.

然后处理总计 500,000 次交易。这将为 pgbench_history 表填充 500,000 行。

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)

该表定义如下所示：

\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)               |

EXPLAIN 命令显示查询计划程序选择的计划。在以下示例中，aid 是主键列，因此对索引 pgbench_accounts_pkey 使用编索引的搜索。

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)

FULL hint用于强制执行完整顺序扫描，而不是使用索引，如下所示：

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)

NO_INDEX hint强制执行并行顺序扫描，而不是使用索引，如下所示：

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)

除了如之前示例中所示使用 EXPLAIN 命令以外，还可通过设置 trace_hints 配置参数获得有关计划程序是否使用提示的更详细信息，如下所示：

SET trace_hints TO on;

下面重复带 NO_INDEX hint的 SELECT 命令，以说明设置 trace_hints 配置参数后生成的其他信息。

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)

请注意，如果忽略hint，则 INFO: [HINTS] 行将不会显示。这可能表示hint中存在语法错误或一些其他拼写错误，如以下示例中所示，其中索引名称拼写错误。

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)