案例二：虚拟索引联合Hint，预测调优效果

案例二：虚拟索引联合Hint，预测调优效果

Hint可以手动要求数据库优化器使用某种方式生成执行计划，因此，对于某些数据库优化器难以生成最优执行计划的场景，可以手动指定执行计划。例如对某张表中的数据进行扫描操作（Scan），可以采用tablescan, indexscan, indexonlyscan, 其分别对应了表扫描、索引扫描、覆盖索引扫描。对于后两种扫描形式，必须要求先在数据库表上存在索引才可以操作。而虚拟索引则可以实现在不创建索引的情况下，测试某个索引扫描的效果。

1. 创建一张表t1，并生成一定量数据，供后续测试。

   create table t1 (id int, name text);
   insert into t1 select generate_series(0, 100000), 'test';
   analyze t1;
   

2. 测试当前优化器默认的范围检索执行计划，并获取其总代价；由于没有创建索引，该SQL语句使用的是全表扫描（SeqScan）。

   explain select * from t1 where id > 1;
   

3. 在t1表的id列上新建虚拟索引。

   -- 开启参数，以便后续执行explain时能够采用虚拟索引
   set enable_hypo_index = on; 
   -- 创建session级别虚拟索引，该session退出后，这个虚拟索引信息也会被自动清理掉
   select hypopg_create_index('create index on t1(id)','session');  
   

4. 通过explain语句，查看该SQL语句是否能够采用该索引；由于该列的distinct值很大，且涉及回表，优化器默认不会采用该索引，该语句执行计划与步骤2无变化，仍是全表扫描（SeqScan）。

   explain select * from t1 where id > 1;
   

5. 通过hint操作，手动要求走索引扫描，查看能否成功；由于指定了hint, 且存在该索引（尽管是虚拟的），仍然可以通过explain看到优化器使用了索引扫描 IndexScan。

   -- 其中<57762>btree_t1_id是自动生成的虚拟索引名，实际操作中以创建虚拟索引时的返回值为准
   explain select /*+ indexscan(t1 "<57762>btree_t1_id") */ * from t1 where id > 1;
   

 说明：

• 执行EXPLAIN ANALYZE不会涉及虚拟索引功能。
• 创建的虚拟索引是数据库实例级别的，各个会话（session）之间可共享设置，关闭会话后虚拟索引仍可存在，但是重启数据库后将被清空。
• 本功能暂不支持视图、物化视图、列存表。
• 开启虚拟索引功能并执行EXPLAIN语句时，可以生成创建虚拟索引之后的执行计划；同时，indexscan/indexonlyscan hint支持虚拟索引。
• 会话级别虚拟索引在各个会话间的设置互不影响，关闭会话后将被清空。
• 与真实索引不同，虚拟索引的相关操作不可回滚。
• 虚拟索引相关函数，不支持dblink远程调用。