先前章节已简单提到pg_am,以hash为例介绍了hash index不能支持大于等于,小于等于等相关操作,我们了解到需要信息来确认访问方法接受哪些数据类型和哪些操作符.
[pg12@localhost ~]$ psql -d testdb
psql (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::regtype
testdb-# from pg_opclass opc, pg_opfamily opf, pg_am am
testdb-# where opc.opcfamily = opf.oid
testdb-# and opf.opfmethod = am.oid
testdb-# 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::regtype
testdb-# from pg_opclass opc, pg_am am
testdb-# where opc.opcmethod = am.oid
testdb-# 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::regoperator
testdb-# from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
testdb-# where opc.opcname = 'int8_ops'
testdb-# and opf.oid = opc.opcfamily
testdb-# and am.oid = opf.opfmethod
testdb-# and amop.amopfamily = opc.opcfamily
testdb-# and am.amname = 'hash'
testdb-# and amop.amoplefttype = opc.opcintype;
amopopr
--------------------
=(bigint,bigint)
=(bigint,smallint)
=(bigint,integer)
(3 rows)
