【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后表的顺序。
简单的说就是规定哪个是驱动表,一般是小表;有的时候优化器会指定大表为驱动表,这就增加了扫描次数;