PostgreSQL DBA(46) - PG Operator classes and families

先前章節已簡單提到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)

參考資料
Indexes in PostgreSQL — 2