文档

Access Method Hints

更新时间:

本文介绍Access Method Hints。

语法

以下hints影响优化器如何访问关系以创建结果集。

提示

说明

FULL(table)

对table执行完整顺序扫描。

INDEX(table [ index ] [...])

使用table中的index访问关系。

NO_INDEX(table [ index ] [...])

不使用table中的index访问关系。

此外,可以使用默认优化模式的 ALL_ROWS、FIRST_ROWS 和 FIRST_ROWS(n) 提示。

示例

  1. 准备基础数据。示例应用程序没有足够的数据来说明optimizer hints的效果,因此本节中的其余示例将使用压力测试工具pgbench应用程序创建的bank数据库。

    1. 创建一个Linux操作系统的ECS实例,本案例使用的ECS为CentOS 7.6 64位操作系统。详情请参考自定义购买ECS实例

      说明

      建议ECS实例和PolarDB集群在同一可用区和VPC。

    2. 在ECS实例中安装PostgreSQL客户端,详情请参考PostgreSQL官方文档

      说明

      请确保安装的PostgreSQL客户端版本与连接的PolarDB PostgreSQL版(兼容Oracle)集群版本兼容。

    3. 创建高权限账户,详细操作请参考创建账号

    4. 获取集群地址,详细操作请参考查看连接地址

    5. 如果PolarDB集群和ECS在同一可用区,可直接使用私网地址,否则需要申请公网地址。将ECS实例的地址添加到PolarDB集群白名单中,请参考设置集群白名单

    6. 在控制台创建测试数据库bank,详细步骤请参考创建数据库

    7. 在ECS客户端执行如下语句,为bank数据库填充pgbench_accounts、pgbench_branches、pgbench_tellers和pgbench_history表。

      pgbench -h <PolarDB集群地址> -p <PolarDB集群地址的端口> -i -s 20 -U <PolarDB数据库用户名> bank

      其中,–s选项指定的比例因子为20,创建20个分行,每个具有100,000个账户,在pgbench_accounts表中生成2,000,000行并在pgbench_branches表中生成20行。每个分行分配10个出纳员,从而在pgbench_tellers表中总计生成200行。

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

      pgbench -h <PolarDB集群地址> -p <PolarDB集群地址的端口> -t 500000 -U <PolarDB数据库用户名> bank

      以下使用psql查询表结构,您也可以使用其他工具查询,使用DMS查询请参考查询表结构

      \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)               |
  2. 执行如下查询。

    • 使用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)