PostgreSQL 10 索引 - 自定义运算符

到目前为止，我们的目标是找出要索引的列，然后盲目地建索引。我们假设数据以某种固定的常数做排序。事实上，这可能不正确。确实，数字总能有相同的顺序，但是其他类型的数据并不总能有预定义的固定的排序顺序。
比如下面两条记录

1118 09 08 78
2345 01 05 77

这两行的顺序可能是什么？他们的语义不明确。其实前四位是校验和，第一行的后三个数字表示1978年8月9日。所以，第二行更小。问题是PostgreSQL不知道这两条记录的实际含义。如果该列是文本，PostgreSQL会以文本格式排序。如果该列是数字，就以数字的方式排序。
幸好PostgreSQL提供了运算符类别：

postgres=# \h CREATE OPERATOR CLASS
命令：       CREATE OPERATOR CLASS
描述：       建立新的运算子类别
语法：
CREATE OPERATOR CLASS 名称 [ DEFAULT ] FOR TYPE 数据_类型
  USING 访问索引的方法 [ FAMILY 操作符群的名称 ] AS
  {  OPERATOR 访问索引所用方法的编号 操作符名称 [ ( 操作数类型, 操作数类型 ) ] [ FOR SEARCH | FOR ORDER BY sort_family_name(排序家族名) ]
   | FUNCTION 访问索引所使用函数的编号 [ ( 操作数类型 [ , 操作数类型 ] ) ] 函数名称 ( 参数类型 [, ...] )
   | STORAGE 存储类型
  } [, ... ]

运算符类别可以告诉索引如何表现。让我们看看标准运算符<、<=、=、>=和>。标准运算符类型支持标准数据类型和标准运算。
可以自定义运算符来提供你需要的逻辑。这些自定义运算符可以形成一个运算符类别，然后传给索引。

增加新的运算符

我们先建表和测试数据：

CREATE TABLE t_sva (sva text);
INSERT INTO t_sva VALUES ('1118090878');
INSERT INTO t_sva VALUES ('2345010477');

PostgreSQL的CREATE OPERATOR命令是这样的：

postgres=# \h CREATE OPERATOR
命令：       CREATE OPERATOR
描述：       建立新的运算子
语法：
CREATE OPERATOR 名称 (
    {FUNCTION|PROCEDURE} = 函数名称
    [, LEFTARG = 操作符左边操作数的类型 ] [, RIGHTARG = 操作符右边操作数的类型 ]
    [, COMMUTATOR = 交换操作符 ] [, NEGATOR = 取负操作符 ]
    [, RESTRICT = 限制选择性估算函数 ] [, JOIN = 连接选择性估算函数 ]
    [, HASHES ] [, MERGES ]
)

基本上，概念是这样的：operator调用函数，这需要一个或者两个参数-运算符的左参数和右参数。既然运算符和函数差不多。那就要实现由运算符隐藏的函数逻辑。为了实现上面的排序，我写了这个函数：

CREATE OR REPLACE FUNCTION normalize_si(text) RETURNS text AS $$
    BEGIN
    RETURN substring($1, 9, 2) ||
            substring($1, 7, 2) ||
            substring($1, 5, 2) ||
            substring($1, 1, 4);
    END; $$
LANGUAGE 'plpgsql' IMMUTABLE;

调用这个函数，会返回下面的结果：

postgres=# SELECT normalize_si('1118090878');
 normalize_si 
--------------
 7808091118
(1 行记录)

我们所做的，只是交换了一些数字。现在，可以像普通字符串那样排序了。下一步，函数被直接用来做比较。

CREATE OR REPLACE FUNCTION si_lt(text, text) RETURNS boolean AS $$
    BEGIN
        RETURN normalize_si($1) < normalize_si($2);
    END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- lower equals
CREATE OR REPLACE FUNCTION si_le(text, text) RETURNS boolean AS $$
    BEGIN
        RETURN normalize_si($1) <= normalize_si($2);
    END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- greater equal
CREATE OR REPLACE FUNCTION si_ge(text, text) RETURNS boolean AS $$
    BEGIN
        RETURN normalize_si($1) >= normalize_si($2);
    END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
-- greater
CREATE OR REPLACE FUNCTION si_gt(text, text) RETURNS boolean AS $$
    BEGIN
        RETURN normalize_si($1) > normalize_si($2);
    END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

这里有两件重要的事情：

• 函数不能用SQL写。它只能在过程语言或者编译好的语言内工作。这是因为SQL函数可以是内联的（inline），有时候会削弱整体尝试
• 应该使用这样的命名约定-小于函数应该叫_lt，小于等于函数应该叫_le等

这样，定义好了这四个函数。第五个等于函数是不必要的。

现在可以定义运算符了：

-- define operators
CREATE OPERATOR <# (PROCEDURE=si_lt, LEFTARG=text, RIGHTARG=text);
CREATE OPERATOR <=# (PROCEDURE=si_le, LEFTARG=text, RIGHTARG=text);
CREATE OPERATOR >=# (PROCEDURE=si_ge, LEFTARG=text, RIGHTARG=text);
CREATE OPERATOR ># (PROCEDURE=si_gt, LEFTARG=text, RIGHTARG=text);

运算符需要一个名字、被调用的过程、左参数和右参数的类型。当运算符被调用的时候，左参数是第一个参数，右参数是第二个参数。
依赖于使用的索引的类型，需要一些支持函数。对于标准的b-tree，只需要一个支持函数，用于内部加速：

CREATE OR REPLACE FUNCTION si_same(text, text) RETURNS int AS $$
    BEGIN
        IF normalize_si($1) < normalize_si($2)
        THEN
            RETURN -1;
        ELSIF normalize_si($1) > normalize_si($2)
        THEN
            RETURN +1;
        ELSE
            RETURN 0;
        END IF;
    END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

函数si_same判断两个参数，分别返回-1、0或者1。这个_same函数是工作的主力，一定要优化。

增加运算符类别

CREATE OPERATOR CLASS sva_special_ops
FOR TYPE text USING btree
AS
    OPERATOR 1 <# ,
    OPERATOR 2 <=# ,
    OPERATOR 3 = ,
    OPERATOR 4 >=# ,
    OPERATOR 5 ># ,
    FUNCTION 1 si_same(text, text);

该运算符类别有一个名字，并且明确地使用b-tree。创建索引的时候，可以使用该运算符类别：

CREATE INDEX idx_special ON t_sva (sva sva_special_ops);

这样，sva列被索引，使用的是sva_special_ops运算符类别。如果不明确地使用sva_special_ops，PostgreSQL就使用默认运算符类别。

测试自定义运算符类别

我们的例子，测试市局只有两行。因此，PostgreSQL不使用索引（表太小，使用索引的开销太大）。
为了不添加数据还能继续测试，你可以告诉优化器，顺序扫描成本太高。

SET enable_seqscan TO off;

这样，索引就能工作了：

postgres=# explain SELECT * FROM t_sva WHERE sva = '0000112273';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Only Scan using idx_special on t_sva  (cost=0.13..8.14 rows=1 width=32)
   Index Cond: (sva = '0000112273'::text)
(2 行记录)