Index-advisor：索引推荐

单query索引推荐

功能描述

单query索引推荐功能基于查询语句的语义信息和数据库的统计信息，对用户输入的单条查询语句生成推荐的索引。单query索引推荐功能支持用户在数据库中直接进行操作，本功能涉及的函数接口如下：

gs_index_advise

功能描述：针对单条查询语句生成推荐索引。

参数说明：SQL语句字符串。 参数类型是文本型，如果参数中存在如单引号（'）等特殊字符，可以使用单引号（'）进行转义。

注意事项

• 本功能仅支持单条SELECT类型的语句，不支持其他类型的SQL语句。
• 本功能暂不支持列存表、段页式表、普通视图、物化视图、全局临时表以及密态数据库。

使用示例

使用上述函数，获取针对query生成的推荐索引，推荐结果由索引的表名和列名组成。

示例1：普通表索引推荐。

1、创建测试表并插入数据。

create table tb_ai_01 (c_w_id integer not null,c_d_id integer not null,c_id integer not null,c_discount decimal(4,4),c_credit char(2),
c_last varchar(16),c_first varchar(16),c_credit_lim decimal(12,2),c_balance decimal(12,2),
c_ytd_payment decimal(12,2),c_payment_cnt integer,c_delivery_cnt integer,c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_middle char(2),c_data varchar(500));

insert into tb_ai_01 values(generate_series(1, 10),generate_series(1, 1000,1),generate_series(1, 10000000));

2、分析表。

analyze tb_ai_01;

3、执行单索引推荐。

select "table", "column" from gs_index_advise('SELECT c_d_id from tb_ai_01 where c_w_id > 10 and c_w_id < 20');

返回结果为：

     table      |  column  
----------------+----------
    tb_ai_01    | c_w_id
(1 row)

上述结果表明：应当在tb_ai_01的 c_w_id 列上创建索引。

4、在tb_ai_01的 c_w_id 列上创建索引，再次执行索引推荐语句。

CREATE INDEX idx on tb_ai_01(c_w_id);
select "table", "column" from gs_index_advise('SELECT c_d_id from tb_ai_01 where c_w_id > 10 and c_w_id < 20');

返回结果如下，创建索引之后不显示推荐索引：

     table      |  column  
----------------+----------
    tb_ai_01    | 
(1 row)

5、某些SQL语句，也可能被推荐创建联合索引，例如：

select "table", "column" from gs_index_advise('select c_id,c_d_id from tb_ai_01 where c_id >= 18 and c_id < 35 and c_w_id = 20;');

返回结果为：

     table      |  column  
----------------+----------
    tb_ai_01    | c_w_id,c_id
(1 row)

6、创建索引后，再次执行索引推荐语句。

CREATE INDEX idx1 on tb_ai_01(c_w_id,c_id);
select "table", "column" from gs_index_advise('select c_id,c_d_id from tb_ai_01 where c_id >= 18 and c_id < 35 and c_w_id = 20;');

返回结果如下，创建索引之后不显示推荐索引：

     table      |  column  
----------------+----------
    tb_ai_01    | 
(1 row)

示例2：分区表索引推荐。

1、创建分区表并插入数据。

create table tb_ai_par_01(id int, val1 int, val2 text)
partition by range(id) (
partition tb_par_range_p1 values less than (200),
partition tb_par_range_p2 values less than (400),
partition tb_par_range_p3 values less than (600),
partition tb_par_range_p4 values less than (800),
partition tb_par_range_p5 values less than (1000),
partition tb_par_range_p6 values less than (1200),
partition tb_par_range_p7 values less than (1400),
partition tb_par_range_p8 values less than (1600),
partition tb_par_range_p9 values less than (MAXVALUE)
)enable row movement;

insert into tb_ai_par_01 values(generate_series(1, 200000), generate_series(1, 200000), generate_series(1, 200000));

2、分析分区表。

analyze tb_ai_par_01;

3、生成推荐索引。

select "table", "column", "indextype" from gs_index_advise('select id,val1 from tb_ai_par_01 where val1 = 2000;'); 

返回结果为：

    table       |  column  |  indextype
----------------+----------+------------
  tb_ai_par_01  |   val1   |    global
(1 row)

虚拟索引

功能描述

虚拟索引功能将模拟真实索引的建立，避免真实索引创建所需的时间和空间开销，用户基于虚拟索引，可通过优化器评估该索引对指定查询语句的代价影响，该功能支持用户在数据库中直接进行操作。

虚拟索引功能功能涉及的系统函数接口如下表所示：

虚拟索引功能功能涉及的GUC参数如下：

注意事项

• 执行EXPLAIN ANALYZE不会涉及虚拟索引功能。
• 创建的虚拟索引是数据库实例级别的，各个会话（session）之间可共享设置，关闭会话后虚拟索引仍可存在，但是重启数据库后将被清空。
• 本功能暂不支持视图、物化视图、列存表。

使用示例

1、创建普通表并插入数据。

create table tb_ai_02 (c_w_id integer not null,c_d_id integer not null,c_id integer not null,c_discount decimal(4,4),c_credit char(2),c_last varchar(16),c_first varchar(16),c_credit_lim decimal(12,2),c_balance decimal(12,2),c_ytd_payment decimal(12,2),c_payment_cnt integer,c_delivery_cnt integer,c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_middle char(2),c_data varchar(500));

insert into tb_ai_02 values(generate_series(1, 10),generate_series(1, 1000,1),generate_series(1, 10000000));
analyze tb_ai_02;

2、使用函数hypopg_create_index创建虚拟索引。

select * from hypopg_create_index('create index on tb_ai_02(c_w_id)');

返回结果为：

 indexrelid |             indexname              
------------+------------------------------------
      17107 | <17107>btree_tb_ai_02_c_w_id
(1 row)

3、开启GUC参数enable_hypo_index。

参数控制数据库的优化器进行EXPLAIN时是否考虑创建的虚拟索引。通过对特定的查询语句执行explain，用户可根据优化器给出的执行计划评估该索引是否能够提升该查询语句的执行效率。

开启开启GUC参数前，执行EXPLAIN 查询语句。

explain SELECT c_d_id from tb_ai_02 where c_w_id > 10 and c_w_id < 20;

返回结果为：

                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on on tb_al_02(cost=0.00..230000.00 rows=l width=4)
   Filter: ((c_w_id > 10) AND (c w id < 20))
(2 rows)

开启GUC参数enable_hypo_index。

set enable_hypo_index = on;

4、再次执行EXPLAIN +查询语句。

explain SELECT c_d_id from tb_ai_02 where c_w_id > 10 and c_w_id < 20;

返回结果为：

                           QUERY PLAN                                                   
-----------------------------------------------------------------------------------------
 Index Scan using <17107>btree_tb_ai_02__c_w_id on tb_ai_02  (cost=0.00..8.27 rows=1 width=4)
   Index Cond: ((c_w_id > 10) AND (c w id < 20))
(2 rows)

通过对比两个执行计划可以观察到，该索引预计会降低指定查询语句的执行代价，用户可考虑创建对应的真实索引。

5、使用使用函数hypopg_display_index展示所有创建过的虚拟索引。

select * from hypopg_display_index();

返回结果为：

         indexname              | indexrelid |     table      |  column  
--------------------------------+------------+----------------+----------
<17107>btree_tb_ai_02__c_w_id   |      17107 |    tb_ai_02    | (c_w_id)
 (1 rows)

6、使用函数hypopg_estimate_size估计虚拟索引创建所需的空间大小（单位：字节）。

select * from hypopg_estimate_size(17107);

返回结果为：

 hypopg_estimate_size 
----------------------
            261529600
(1 row)

7、删除虚拟索引。

（1）使用函数hypopg_drop_index删除指定oid的虚拟索引。

select * from hypopg_drop_index(17107);

（2）使用函数hypopg_reset_index一次性清除所有创建的虚拟索引。

select * from hypopg_reset_index();

WORKLOAD级别索引推荐

功能描述

WORKLOAD级别索引推荐功能将包含有多条DML语句的workload作为输入，最终生成一批可对整体workload的执行表现进行优化的索引。同时，本功能提供从日志或系统表中抽取业务数据SQL流水的功能。对于workload级别的索引推荐，用户可通过运行数据库外的脚本使用此功能。

注意事项

当前执行用户下安装有vsql工具，该工具路径已被加入到PATH环境变量中。

业务数据抽取

日志中的SQL采集

执行根据日志抽取SQL语句的功能，命令如下：

gs_dbmind component extract_log [l LOG_DIRECTORY] [f OUTPUT_FILE] [p LOG_LINE_PREFIX] [-d DATABASE] [-U USERNAME][--start_time] [--sql_amount] [--statement] [--json] [--max_reserved_period] [--max_template_num]

参数说明

• LOG_DIRECTORY：pg_log的存放目录。
• OUTPUT_PATH：输出SQL流水文件的保存路径，即抽取出的业务数据存放的文件路径。
• LOG_LINE_PREFIX：指定每条日志信息的前缀格式。
• DATABASE：（可选）数据库名称，不指定默认所有数据库。
• USERNAME：（可选）用户名称，不指定默认所有用户。
• start_time：（可选）日志收集的开始时间，不指定默认所有文件。
• sql_amount：（可选）收集SQL数量的最大值，不指定默认收集所有SQL。
• statement：（可选）表示收集pg_log日志中statement标识开头的SQL，不指定默认不收集。
• json：（可选）指定收集日志的文件存储格式为SQL归一化后的json，不指定默认格式每条SQL占一行。
• max_reserved_period：（可选）指定json模式下，增量收集日志中保留的模板的最大的更新时长，不指定默认都保留，单位/天。
• max_template_num：（可选）指定json模式下保留的最大模板数量，不指定默认都保留。

使用示例

1、设置相关GUC参数log_min_duration_statement = 0和log_statement= 'all'。

修改postgresql.conf文件

log_min_duration_statement = 0
log_statement= 'all'

2、重启数据库。

vb_ctl restart

3、执行根据日志抽取SQL语句的功能。

gs_dbmind component extract_log data/vastbase/pg_log sql_log.txt '%m %c %d %a %x %n %e %u' -d vastbase -U db_fxj --start_time '2023-02-10 00:00:00' --statement

4、将步骤1中设置的GUC参数还原为设置前的值。

业务数据抽取完毕建议将上述GUC参数复原，否则容易导致日志文件膨胀。

系统表中的SQL采集

执行系统表抽取SQL语句功能，命令如下：

echo PASSWORD | gs_dbmind component fetch_statement [DB_PORT] [DATABASE] [OUTPUT] [--db-host DB_HOST] [-U DB_USER] [--schema SCHEMA]
[--statement-type {asp,slow,history,activity}] [--start-time STAET_TIME] [--end-time END_TIME] [--verify] [--driver] 

参数说明

• DB_PORT：连接数据库的端口号。
• DATABASE：连接数据库的名字。
• OUTPUT：包含SQL流水的输出文件。
• DB_HOST：（可选）连接数据库的主机名。
• DB_USER：（可选）连接数据库的用户名，用户需具有sysadmin或monitor admin权限。
• SCHEMA：模式名称，仅在statement-type为history时使用，默认为public。
• statement-type：SQL流水类型，分为asp，slow，history，activity四种:
  • asp：从gs_asp中提取SQL流水，需保证开启GUC参数enable_asp。
  • slow：指定抽取当前活跃的慢SQL。
  • history：指定抽取历史慢SQL。
  • activity：指定抽取当前活跃SQL。
• START_TIME：起始时间，仅在statement-type为asp时使用，且为必选。
• END_TIME：结束时间，仅在statement-type为asp时使用，且为必选。
• verify：是否校验SQL合法性。
• driver：是否使用python驱动器连接数据库，默认vsql连接。
• show-detail：（可选）是否显示当前推荐索引集合的详细优化信息。
• show-benefits：（可选）是否显示索引收益。

使用示例

运行本功能，命令如下：

echo PASSWORD | gs_dbmind component index_advisor [p DB_PORT] [d DATABASE] [f FILE] [--h DB_HOST] [-U DB_USER] [--schema SCHEMA]
[--max_index_num MAX_INDEX_NUM][--max_index_storage MAX_INDEX_STORAGE] [--multi_iter_mode] [--max-n-distinct MAX_N_DISTINCT]
[--min-improved-rate MIN_IMPROVED_RATE] [--max-candidate-columns MAX_CANDIDATE_COLUMNS] [--max-index-columns MAX_INDEX_COLUMNS] 
[--min-reltuples MIN_RELTUPLES] [--multi_node]  [--json] [--driver] [--show_detail] [--show-benifits]

其中的输入参数分别为：

• DB_PORT：连接数据库的端口号。
• DATABASE：连接数据库的名字。
• FILE：包含workload语句的文件路径。
• DB_HOST：（可选）连接数据库的主机号。
• DB_USER：（可选）连接数据库的用户名。
• SCHEMA：模式名称。
• MAX_INDEX_NUM：（可选）最大的索引推荐数目。
• MAX_INDEX_STORAGE：（可选）最大的索引集合空间大小。
• MAX_N_DISTINCT：distinct值个数的倒数，默认为0.01。
• MIN_IMPROVED_RATE：最小提升比例，默认为0.1。
• MAX_CANDIDATE_COLUMNS：（可选）索引最大的候选列个数。
• MAX_INDEX_COLUMNS：索引最大列数，默认为4。
• MIN_RELTUPLES：最小记录数，默认为10000。
• multi_node：（可选）指定当前是否为分布式数据库实例。
• multi_iter_mode：（可选）算法模式，可通过是否设置该参数来切换算法。
• json：（可选）指定workload语句的文件路径格式为SQL归一化后的json，默认格式每条SQL占一行。
• driver：（可选）指定是否使用python驱动器连接数据库，默认gsql连接。
• show-detail：（可选）是否显示当前推荐索引集合的详细优化信息。
• show-benefits：（可选）是否显示索引收益。

1、准备好包含有多条DML语句的文件作为输入的workload，文件中每条语句占据一行。用户可从数据库的离线日志中获得历史的业务语句。

创建1.sql文件，内如如下所示：

create table tb_ai_03 (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(16,4),
c_credit char(20),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(20),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(20),
c_data varchar(500)
);

insert into tb_ai_03 values(generate_series(1, 100),generate_series(1, 1000,1),generate_series(1, 10000000),generate_series(1, 100),generate_series(1, 1000,1),generate_series(1, 10000000),generate_series(1, 100),generate_series(1, 1000,1),generate_series(1, 10000000));
analyze tb_ai_03;

SELECT c_d_id from tb_ai_03 where c_w_id > 10 and c_w_id < 20;
select c_id,c_d_id from tb_ai_03 where c_id >= 18 and c_id < 35 and c_w_id = 20;
SELECT c_d_id from tb_ai_03 where c_id > 10 and c_id < 20;
select c_id,c_d_id from tb_ai_03 where c_id >= 18 and c_id < 35 and c_d_id = 20;
SELECT c_d_id from tb_ai_03 where c_d_id > 10 and c_d_id < 20;
select c_id,c_d_id from tb_ai_03 where c_d_id >= 18 and c_d_id < 35 and c_w_id = 20;
SELECT c_d_id from tb_ai_03 where c_discount > 10 and c_discount < 20;
select c_id,c_d_id from tb_ai_03 where c_discount >= 18 and c_discount < 35 and c_w_id = 20;

2、执行含多条语句的sql。

gs_dbmind component index_advisor 5632 vastbase 1.sql --schema public --max_index_num 10 --multi_iter_mode

执行该命令需输入数据库用户密码。

返回结果为：