PostgreSQL SQL优化 索引操作符

1 背景知识

1、创建索引时，可以为字段指定一个操作符类型。
2、操作符类型指明索引扫描需要使用的操作符。
3、例如，一个 int4 类型上的 Btree 索引会使用 int4_ops 类，这个操作符类包括用于 int4 类型值的比较函数。
4、通常情况下默认操作符类通常就足够了。

1.1 操作符类型

1、对于一种数据类型，可能存在多个操作符类型。
2、例如，对一种复数数据类型。
（1）按照绝对值排序。
（2）按照实数部分排序。
（3）通过定义两个操作符类来实现，并且在创建一个索引时选择合适的类。

3、操作符类会决定基本的排序顺序。

2 索引支持的操作符

2.1 查询当前数据库支持的所有操作符

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opf.opfname AS opfamily_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default,
       amop.amopopr::regoperator AS opfamily_operator
    FROM pg_am am, pg_opclass opc,pg_amop amop,pg_opfamily opf
    WHERE opc.opcmethod = am.oid AND
          opc.opcfamily = opf.oid AND amop.amopfamily = opf.oid
    ORDER BY index_method, opclass_name,opfamily_operator;

2.2 数据字典详解

pg_am
pg_opclass
pg_amop
pg_opfamily

3 Btree 索引支持的操作符

1、显示默认不启用的操作符。

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opf.opfname AS opfamily_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default,
       amop.amopopr::regoperator AS opfamily_operator
    FROM pg_am am, pg_opclass opc,pg_amop amop,pg_opfamily opf
    WHERE opc.opcmethod = am.oid 
    AND opc.opcfamily = opf.oid 
    AND amop.amopfamily = opf.oid 
    AND am.amname = 'btree'
    AND opc.opcdefault='f'
    ORDER BY index_method, opclass_name,opfamily_operator;
//屏幕输出：

4 Btree 索引常用的操作符

4.1 以上操作符和默认操作符类的区别

严格按照C 排序字符集规则进行排序，而不是根据本地排序字符集（Collect）排序规则。

4.2 适合的场景

1、适合数据库没有使用标准 C 本地字符集的场景。
2、适合使用在涉及模式匹配表达式（LIKE 或POSIX正则表达式）的查询中。
3、示例，创建索引一个 varchar 列。

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

4.3 不适合的场景

1、涉及到 <、<=、> 或 >= 比较的查询时，应使用默认操作符类的索引。否则不会执行索引扫描。
2、可以在同一个列上创建多个使用不同操作符类的索引。
3、数据库使用标准 C 本地字符集的场景。

但是普通的等值比较可以使用这些操作符类。

5 索引的操作符提高性能

请参考 LIKE无法使用索引。