PostgreSQL DBA(46) - PG Operator classes and families

先前章节已简单提到pg_am,以hash为例介绍了hash index不能支持大于等于,小于等于等相关操作,我们了解到需要信息来确认访问方法接受哪些数据类型和哪些操作符.

[pg12@localhost ~]$ psql -d testdbpsql (12beta1)Type "help" for help.testdb=# select * from pg_am;  oid  |    amname    |      amhandler       | amtype -------+--------------+----------------------+--------     2 | heap         | heap_tableam_handler | t   403 | btree        | bthandler            | i   405 | hash         | hashhandler          | i   783 | gist         | gisthandler          | i  2742 | gin          | ginhandler           | i  4000 | spgist       | spghandler           | i  3580 | brin         | brinhandler          | i 24597 | blackhole_am | blackhole_am_handler | t(8 rows)

本节在此基础上介绍Operator classes and families.

基础知识
PostgreSQL提供了 operator class 和 operator family 两个概念来说明访问方法接受哪些数据类型和哪些操作符. operator class 包含了索引操作特定数据类型的最小操作符集合, operator class 被归类为 operator family ,也就是或一个family可能包含多个 operator class .

testdb=# select * from pg_opfamily; oid  | opfmethod |        opfname        | opfnamespace | opfowner ------+-----------+-----------------------+--------------+----------  397 |       403 | array_ops             |           11 |       10  627 |       405 | array_ops             |           11 |       10  423 |       403 | bit_ops               |           11 |       10  424 |       403 | bool_ops              |           11 |       10  426 |       403 | bpchar_ops            |           11 |       10  427 |       405 | bpchar_ops            |           11 |       10  428 |       403 | bytea_ops             |           11 |       10...(107 rows)

上面是系统中存在的所有的opfamily,包括数组array_ops/位bit_ops/字符串bpchar_ops/整型integer_ops等数据类型等op family.比如integer_ops family包括了int8_ops, int4_ops, and int2_ops classes,分别对应bigint, integer, 和 smallint这几个类型.
pg_opfamily中的opfmethod字段与pg_am.oid关联,比如查询hash am的opfamily和opclass:

testdb=# select am.amname,opfname, opcname, opcintype::regtypetestdb-# from pg_opclass opc, pg_opfamily opf, pg_am amtestdb-# where opc.opcfamily = opf.oidtestdb-# and opf.opfmethod = am.oidtestdb-# and am.amname = 'hash'testdb-# order by opf.opfname; amname |      opfname       |       opcname       |          opcintype          --------+--------------------+---------------------+----------------------------- hash   | aclitem_ops        | aclitem_ops         | aclitem hash   | array_ops          | array_ops           | anyarray hash   | bool_ops           | bool_ops            | boolean hash   | bpchar_ops         | bpchar_ops          | character hash   | bpchar_pattern_ops | bpchar_pattern_ops  | character hash   | bytea_ops          | bytea_ops           | bytea hash   | char_ops           | char_ops            | "char" hash   | cid_ops            | cid_ops             | cid hash   | date_ops           | date_ops            | date hash   | enum_ops           | enum_ops            | anyenum hash   | float_ops          | float8_ops          | double precision hash   | float_ops          | float4_ops          | real hash   | integer_ops        | int2_ops            | smallint hash   | integer_ops        | int8_ops            | bigint hash   | integer_ops        | int4_ops            | integer hash   | interval_ops       | interval_ops        | interval hash   | jsonb_ops          | jsonb_ops           | jsonb hash   | macaddr8_ops       | macaddr8_ops        | macaddr8 hash   | macaddr_ops        | macaddr_ops         | macaddr hash   | network_ops        | cidr_ops            | inet hash   | network_ops        | inet_ops            | inet hash   | numeric_ops        | numeric_ops         | numeric hash   | oid_ops            | oid_ops             | oid hash   | oidvector_ops      | oidvector_ops       | oidvector hash   | pg_lsn_ops         | pg_lsn_ops          | pg_lsn hash   | range_ops          | range_ops           | anyrange hash   | text_ops           | text_ops            | text hash   | text_ops           | name_ops            | name hash   | text_ops           | varchar_ops         | text hash   | text_pattern_ops   | text_pattern_ops    | text hash   | text_pattern_ops   | varchar_pattern_ops | text hash   | tid_ops            | tid_ops             | tid hash   | time_ops           | time_ops            | time without time zone hash   | timestamp_ops      | timestamp_ops       | timestamp without time zone hash   | timestamptz_ops    | timestamptz_ops     | timestamp with time zone hash   | timetz_ops         | timetz_ops          | time with time zone hash   | uuid_ops           | uuid_ops            | uuid hash   | xid_ops            | xid_ops             | xid(38 rows)

可以看到,对于integer_ops opfamily,可以支持int2_ops/int4_ops/int8_ops这几类op clas,类型分别是smallint/integer/bigint.
op family可以包括额外的操作符用以比较不同类型的值,之所以归为同一个family是因为在使用index时,谓词可以适配不同的数据类型(如smallint/integer/bigint等).在大多数情况下,不需要知道op family和op class,只需要创建索引然后使用就好了.但,可以显式指定op lcass.

System catalog
下面是op family和op class的相关系统目录关系图:

通过上图,可找出相关的信息.
找出AM可处理的数据类型

testdb=# select am.amname,opcname, opcintype::regtypetestdb-# from pg_opclass opc, pg_am am testdb-# where opc.opcmethod = am.oidtestdb-# and am.amname = 'hash'testdb-# order by opcintype::regtype::text; amname |       opcname       |          opcintype          --------+---------------------+----------------------------- hash   | aclitem_ops         | aclitem hash   | array_ops           | anyarray hash   | enum_ops            | anyenum hash   | range_ops           | anyrange hash   | int8_ops            | bigint hash   | bool_ops            | boolean hash   | bytea_ops           | bytea hash   | char_ops            | "char" hash   | bpchar_pattern_ops  | character hash   | bpchar_ops          | character hash   | cid_ops             | cid hash   | date_ops            | date hash   | float8_ops          | double precision hash   | cidr_ops            | inet hash   | inet_ops            | inet hash   | int4_ops            | integer hash   | interval_ops        | interval hash   | jsonb_ops           | jsonb hash   | macaddr_ops         | macaddr hash   | macaddr8_ops        | macaddr8 hash   | name_ops            | name hash   | numeric_ops         | numeric hash   | oid_ops             | oid hash   | oidvector_ops       | oidvector hash   | pg_lsn_ops          | pg_lsn hash   | float4_ops          | real hash   | int2_ops            | smallint hash   | text_ops            | text hash   | varchar_ops         | text hash   | text_pattern_ops    | text hash   | varchar_pattern_ops | text hash   | tid_ops             | tid hash   | timestamp_ops       | timestamp without time zone hash   | timestamptz_ops     | timestamp with time zone hash   | time_ops            | time without time zone hash   | timetz_ops          | time with time zone hash   | uuid_ops            | uuid hash   | xid_ops             | xid(38 rows)

哪些op在op class中?(索引访问可用于该操作符的谓词)

testdb=# select amop.amopopr::regoperatortestdb-# from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amoptestdb-# where opc.opcname = 'int8_ops'testdb-# and opf.oid = opc.opcfamilytestdb-# and am.oid = opf.opfmethodtestdb-# and amop.amopfamily = opc.opcfamilytestdb-# and am.amname = 'hash'testdb-# and amop.amoplefttype = opc.opcintype;      amopopr       -------------------- =(bigint,bigint) =(bigint,smallint) =(bigint,integer)(3 rows)

参考资料
Indexes in PostgreSQL - 2