PgSQL的操作符、操作符类及操作符族

PgSQL的操作符、操作符类及操作符族

操作符、操作符类和操作符族是索引功能的重要组成部分。定义一个索引的时候，可以为索引中的每一列指定一个操作符类：

CREATE INDEX idxname ON table (id1 opclass [sort options][,...]);

如果不指定则使用默认的操作符类。如果使用自定义类型创建索引，并且针对该数据类型不创建操作符类的话，该索引就无法使用。

操作符类如何让一个索引生效呢？

1、操作符

针对每种数据类型，有比较操作符>、>=、<、<=、=等；算数操作符：加减乘除等；逻辑操作符：AND、OR、NOT等。可以从pg_operator系统表查看操作符的元数据信息。

2、操作符类

对于一个具体的数据类型，如果要能够正常使用索引，除了需要有对应的比较操作符外，还需要操作符类等信息。每个索引都需要对每种数据类型定义一个操作符类，指定在索引中使用的操作符的集合。比如一个int4类型的操作符>、>=、<、<=、=等集合（btree的操作符类必须提供这五种比较操作符）。

在pg_opclass系统表存储操作符类的元数据信息:

一个操作符类定义了用于某个索引访问方法的一种特定数据类型的一组操作：>、>=等，可以用在一个索引的WHERE clause操作符的集合，所以叫做操作符类。

可以为一个数据类型的同一个索引定义多个操作符类，最常用的一个被标记为默认操作符类，作为整个类型在一个索引访问方法下的默认选择。当然也可以创建索引时显式指定使用哪个。

索引访问方法的strategy和support function的关系是：操作符类中的操作符对应表访问方法的具体策略，操作符的函数对应表访问方法的support function。比如btree索引有5个策略：

Btree访问方法定义了3类支持函数：

1）比较两个key，并且返回小于0的整数、0、大于0的整数，分别表示第一个key<第2个key、等于和大于第2个key

2）返回C-callable sort support functions的地址（可选）

3）将测试值与基准值加/减偏移量后的值进行比较，并根据比较结果返回false或者true

以int8类型进行举例：

3、操作符族

上面的例子中，不仅有int8_ops opclass，还有integer_ops opfamily。虽然在一个特定的索引列中肯定会有一种数据类型，但是索引列和其他不同数据类型的值比较也比较常见；当几种数据类型比较相似的时候，比如int8、int2、int4等，为了处理这类常见，PgSQL使用了opfamily概念。

操作符族将操作符类组织在一起，使他们可以相互比较；同一个操作符族内的操作符类对应的数据类型可以之间比较。在pg_opfamily存储操作符族的元数据信息。

与操作符类不同：

1）操作符类仅针对一种数据类型，而操作符族包含多个操作符类包括多种类型。

2）操作符类的成员是一个整体，只能整体创建和删除，不能通过ALTER操作添加和删除；但是操作符族可以动态修改成员

3）一个操作符类必须属于一个操作符族，创建操作符类的时候如果没有指定则会默认创建一个同名的操作符族

4）创建索引时指定使用的操作符类，判断是否可以走索引时使用操作符所在的操作符类

可以给integer_ops添加混合数据类型的比较，比如：

ALTER OPERATOR FAMILY integer_ops USING btree ADD
  -- cross-type comparisons int8 vs int2
  OPERATOR 1 < (int8, int2) ,
  OPERATOR 2 <= (int8, int2) ,
  OPERATOR 3 = (int8, int2) ,
  OPERATOR 4 >= (int8, int2) ,
  OPERATOR 5 > (int8, int2) ,
  FUNCTION 1 btint82cmp(int8, int2) ,

  -- cross-type comparisons int8 vs int4
  OPERATOR 1 < (int8, int4) ,
  OPERATOR 2 <= (int8, int4) ,
  OPERATOR 3 = (int8, int4) ,
  OPERATOR 4 >= (int8, int4) ,
  OPERATOR 5 > (int8, int4) ,
  FUNCTION 1 btint84cmp(int8, int4) ,

  -- cross-type comparisons int4 vs int2
  OPERATOR 1 < (int4, int2) ,
  OPERATOR 2 <= (int4, int2) ,
  OPERATOR 3 = (int4, int2) ,
  OPERATOR 4 >= (int4, int2) ,
  OPERATOR 5 > (int4, int2) ,
  FUNCTION 1 btint42cmp(int4, int2) ,

  -- cross-type comparisons int4 vs int8
  OPERATOR 1 < (int4, int8) ,
  OPERATOR 2 <= (int4, int8) ,
  OPERATOR 3 = (int4, int8) ,
  OPERATOR 4 >= (int4, int8) ,
  OPERATOR 5 > (int4, int8) ,
  FUNCTION 1 btint48cmp(int4, int8) ,

  -- cross-type comparisons int2 vs int8
  OPERATOR 1 < (int2, int8) ,
  OPERATOR 2 <= (int2, int8) ,
  OPERATOR 3 = (int2, int8) ,
  OPERATOR 4 >= (int2, int8) ,
  OPERATOR 5 > (int2, int8) ,
  FUNCTION 1 btint28cmp(int2, int8) ,

  -- cross-type comparisons int2 vs int4
  OPERATOR 1 < (int2, int4) ,
  OPERATOR 2 <= (int2, int4) ,
  OPERATOR 3 = (int2, int4) ,
  OPERATOR 4 >= (int2, int4) ,
  OPERATOR 5 > (int2, int4) ,
  FUNCTION 1 btint24cmp(int2, int4) ;

Int数据类型的op family下可以为int2、int4、int8之间添加比较操作符及支持函数，从而供索引使用：

上述涉及的几个系统表之间的关系：

4、索引

主要由函数match_clause_to_index来判断一个qual条件是否可以用上索引。

如上图所示，是否能用索引主要判断操作符等的左右列是否和索引中的列匹配：

1）以操作符为例，首先需要检查操作符的左或者右参数

2）左/右参数是Var，里面标记的列和索引列匹配

3）索引和操作符针对的是同一个范围表

4）索引的排序规则和操作符的排序规则一样

5）根据操作符OID和opfamily可以从pg_amop系统表中检索到值，也就是操作符在索引am的opfamily中

参考

https://www.postgresql.org/docs/11/btree-support-funcs.html