1 问题现象

在分析SQL执行计划时，千真万确地对查找列创建了索引，可是从SQL执行计划结果看没有使用上索引，我们称为索引失效。经过实践分析导致索引失效的原因比较多，如：谓词条件选择度，隐式转换，索引列执行计算、索引列使用函数等等。

2 技术背景

索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。

索引建立在数据库表中的某些列上。在索引列上使用表达式，并且表达式中使用函数计算，如果在此列使用函数表达式来创建索引，称之为函数索引。因此，在创建索引时，应该仔细考虑在哪些列上创建索引，还要分析一下索引列上有没有使用函数等等。

3 索引列上使用函数案例

此案例创建一张测试表同时在表上创建了普通索引与函数索引，再执行统计信息分析，然后用相同的SQL语句分析执行计划中的耗时。

3.1 建表语句和数据导入

gaussdb=# drop table if exists index_test1;
NOTICE:  table "index_test1" does not exist, skipping
DROP TABLE
gaussdb=# create table index_test1(c1 number,c2 number);
CREATE TABLE
gaussdb=# insert into index_test1 values(generate_series(1,100000),generate_series(1,100000));
INSERT 0 100000
gaussdb=# create index idx_test1 on index_test1(c1);
CREATE INDEX
gaussdb=# analyze index_test1;
ANALYZE

3.2 执行计划分析

gaussdb=# explain analyze select * from index_test1 where nvl(c1,10)=10;
 id |          operation          | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |     E-costs
----+-----------------------------+--------+--------+--------+-------------+---------+---------+-----------------
  1 | ->  Seq Scan on index_test1 | 28.327 |      1 |      1 | 64KB        |         |      12 | 0.000..1656.000
(1 row)
  Predicate Information (identified by plan id)
--------------------------------------------------
   1 --Seq Scan on index_test1
         Filter: (COALESCE(c1, 10::numeric) = 10)
         Rows Removed by Filter: 99999
(3 rows)
       ====== Query Summary =====
----------------------------------------
 Datanode executor start time: 0.044 ms
 Datanode executor run time: 28.358 ms
 Datanode executor end time: 0.012 ms
 Planner runtime: 0.419 ms
 Query Id: 1946399463954269948
 Total runtime: 28.431 ms
(6 rows)

gaussdb=# create index idx_test2 on index_test1(nvl(c1,10));
CREATE INDEX
gaussdb=# vacuum analyze index_test1;
VACUUM
gaussdb=# explain analyze select * from index_test1 where nvl(c1,10)=10;
 id |                   operation                   | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |   E-costs
----+-----------------------------------------------+--------+--------+--------+-------------+---------+---------+--------------
  1 | ->  Index Scan using idx_test2 on index_test1 | 0.135  |      1 |      1 | 79KB        |         |      12 | 0.000..8.268
(1 row)
    Predicate Information (identified by plan id)
------------------------------------------------------
   1 --Index Scan using idx_test2 on index_test1
         Index Cond: (COALESCE(c1, 10::numeric) = 10)
(2 rows)
       ====== Query Summary =====
----------------------------------------
 Datanode executor start time: 0.030 ms
 Datanode executor run time: 0.147 ms
 Datanode executor end time: 0.008 ms
 Planner runtime: 0.348 ms
 Query Id: 1946399463954272063
 Total runtime: 0.194 ms
(6 rows)
 ====== Query Others =====
---------------------------
 Bypass: Yes
(1 row)

4 处理结果

从3.2节的执行计划分析，在表index_test1上创建普通索引idx_test1执行SQL花费了28.431 ms，创建idx_test2函数索引执行SQL花费了0.194ms。

5 简单总结

在复杂的业务场景下尽量避免在索引列上执行函数的计算，如果避免不了的话那就创建函数索引。