【openGauss】openGauss多重索引hint及leading函数的使用

---

一、创建测试表

/* 创建测试表 */
/* 建表 - tzq_log_t */
drop   table    if     exists tzq_log_t;
drop   sequence if     exists tzq_log_s;
create sequence               tzq_log_s;
create table                  tzq_log_t (
  log_id int8 not null default nextval('tzq_log_s'),
  log_title varchar(300),
  log_type_code varchar(300),
  log_content varchar(300),
  log_user int8 not null default -1,
  status varchar(20) default '1',
  delete_flag varchar(1) not null default 'N',
  creation_date TIMESTAMP(0) not null default CURRENT_TIMESTAMP,
  constraint tzq_log_pk primary key (log_id)
);
/* 索引 */
create index idx_tzq_log_1 on tzq_log_t (log_user, log_type_code);
/* 刷数 */
insert into tzq_log_t (log_title, log_type_code, log_content, log_user) select '第一篇日志', 'LT001', '今天天气真好，适合出行。', 1;

/* 建表 - tzq_user_t*/
drop   table    if     exists tzq_user_t;
drop   sequence if     exists tzq_user_s;
create sequence               tzq_user_s;
create table                  tzq_user_t (
  user_id int8 not null default nextval('tzq_user_s'),
  username varchar(300),
  user_type varchar(30),
  status varchar(20) default '1',
  delete_flag varchar(1) not null default 'N',
  creation_date TIMESTAMP(0) not null default CURRENT_TIMESTAMP,
  constraint tzq_user_pk primary key (user_id)
);
/* 索引 */
create index idx_tzq_user_1 on tzq_user_t (username);
/* 刷数 */
insert into tzq_user_t (username, user_type) select '张三', '普通用户';

/* 建表 - tzq_log_type_t */
drop   table    if     exists tzq_log_type_t;
drop   sequence if     exists tzq_log_type_s;
create sequence               tzq_log_type_s;
create table                  tzq_log_type_t (
  log_type_id int8 not null default nextval('tzq_log_type_s'),
  log_type_name varchar(300),
  log_type_code varchar(300),
  status varchar(20) default '1',
  delete_flag varchar(1) not null default 'N',
  creation_date TIMESTAMP(0) not null default CURRENT_TIMESTAMP,
  constraint tzq_log_type_pk primary key (log_type_id)
);
/* 索引 */
create index idx_tzq_log_type_1 on tzq_log_type_t (log_type_code);
/* 刷数 */
insert into tzq_log_type_t (log_type_name, log_type_code) select '日常', 'LT001';

/* 查询测试表数据 */
select * from tzq_log_t;
select * from tzq_user_t;
select * from tzq_log_type_t;

二、复杂SQL查询：openGauss多重索引hint

复杂SQL查询，用到了强制走索引hint（indexscan(表别名 索引名)），及leading函数。

SELECT /*+ indexscan(t1 idx_tzq_log_1) indexscan(t2 idx_tzq_user_1) indexscan(t3 idx_tzq_log_type_1) leading(((t3 t2) t1))*/
       t1.log_title
      ,t2.username
      ,t3.log_type_name
  FROM tzq_log_t      t1
      ,tzq_user_t     t2
      ,tzq_log_type_t t3
 WHERE 1=1
   And t1.log_user      = t2.user_id
   And t1.log_type_code = t3.log_type_code
   and t1.delete_flag   = 'N'
   and t2.delete_flag   = 'N'
   and t3.delete_flag   = 'N';

查询结果：

三、查看执行计划

执行下面的SQL来查看执行计划：

/* 复杂SQL查询 */
SET explain_perf_mode=normal; -- pretty
explain(analyze true,verbose true,costs true,buffers true,timing true,format text)
SELECT /*+ indexscan(t1 idx_tzq_log_1) indexscan(t2 idx_tzq_user_1) indexscan(t3 idx_tzq_log_type_1) leading(((t3 t2) t1))*/
       t1.log_title
      ,t2.username
      ,t3.log_type_name
  FROM tzq_log_t      t1
      ,tzq_user_t     t2
      ,tzq_log_type_t t3
 WHERE 1=1
   And t1.log_user      = t2.user_id
   And t1.log_type_code = t3.log_type_code
   and t1.delete_flag   = 'N'
   and t2.delete_flag   = 'N'
   and t3.delete_flag   = 'N';

执行计划的结果：

Nested Loop  (cost=0.00..69.24 rows=1 width=1548) (actual time=0.050..0.054 rows=1 loops=1)
  Output: t1.log_title, t2.username, t3.log_type_name
  Join Filter: ((t3.log_type_code)::text = (t1.log_type_code)::text)
  ->  Nested Loop  (cost=0.00..60.95 rows=1 width=1556) (actual time=0.038..0.040 rows=1 loops=1)
        Output: t2.username, t2.user_id, t3.log_type_name, t3.log_type_code
        ->  Index Scan using idx_tzq_log_type_1 on tzq.tzq_log_type_t t3  (cost=0.00..49.49 rows=1 width=1032) (actual time=0.025..0.025 rows=1 loops=1)
              Output: t3.log_type_id, t3.log_type_name, t3.log_type_code, t3.status, t3.delete_flag, t3.creation_date
              Filter: ((t3.delete_flag)::text = 'N'::text)
              (Buffers: shared hit=2)
        ->  Seq Scan on tzq.tzq_user_t t2  (cost=0.00..11.45 rows=1 width=524) (actual time=0.006..0.007 rows=1 loops=1)
              Output: t2.user_id, t2.username, t2.user_type, t2.status, t2.delete_flag, t2.creation_date
              Filter: ((t2.delete_flag)::text = 'N'::text)
              (Buffers: shared hit=1)
  ->  Index Scan using idx_tzq_log_1 on tzq.tzq_log_t t1  (cost=0.00..8.27 rows=1 width=1040) (actual time=0.009..0.011 rows=1 loops=1)
        Output: t1.log_id, t1.log_title, t1.log_type_code, t1.log_content, t1.log_user, t1.status, t1.delete_flag, t1.creation_date
        Index Cond: (t1.log_user = t2.user_id)
        Filter: ((t1.delete_flag)::text = 'N'::text)
        (Buffers: shared hit=2)
Total runtime: 0.234 ms

四、openGauss hint用法解释

/*+ indexscan(t1 idx_tzq_log_1) indexscan(t2 idx_tzq_user_1) indexscan(t3 idx_tzq_log_type_1) leading(((t3 t2) t1)) */

执行计划的顺序：

• 1、indexscan(t1 idx_tzq_log_1)：是强制执行计划走指定的索引。
• 2、leading(((t3 t2) t1))：是指定表的执行计划先后顺序。
  将指定的表做为链接次序中的首表，leading提示后面可以跟多个表名，它用来表示在相关的表关联中，以哪个表作为驱动表。使用leading提示后，优化器将不会再考虑from后表的顺序。
  简单的说就是规定哪个是驱动表，一般是小表；有的时候优化器会指定大表为驱动表，这就增加了扫描次数；