作者
digoal
日期
2018-05-30
标签
PostgreSQL , 索引接口 , 操作符 , am , family , opclass , operator , function , order by
背景
PostgreSQL有很多的索引接口，同时索引接口还支持用户扩展，当扩展索引时，需要提供opclass。
例如，创建索引的语法如下
CREATE INDEX name ON table (column opclass [sort options] [, ...]);
其中sort options是COLLATE, ASC/DESC and/or NULLS FIRST/NULLS LAST
同一个数据类型可能有多个ops，例如text,varchar,bpchar类型，除了默认的ops，还有text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops。这几个OPS是挨个字符的比较，所以支持=,LIKE和规则表达式查询方式，但是不支持<, <=, >, or >=。
The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard “C” locale. As an example, you might index a varchar column like this:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes. (Ordinary equality comparisons can use these operator classes, however.) It is possible to create multiple indexes on the same column with different operator classes. If you do use the C locale, you do not need the xxx_pattern_ops operator classes, because an index with the default operator class is usable for pattern-matching queries in the C locale.
每一个扩展的opclass，支持什么操作符，支持什么JOIN方法，支持排序吗，是默认的OPCLASS吗？
以btree_gin扩展包为例，它扩展了标准类型的GIN索引接口。那么扩展后支持哪些呢？
create extension btree_gin;
如何查询某个扩展的opclass支持哪些索引方法
比如int4_ops是扩展的opclass。
```
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
关联pg_opclass.opcmethod, pg_am.oid
(opckeytype表示索引里面存的KEY是什么类型，0表示与opcintype一致)
postgres=# select oid,* from pg_am;
oid | amname | amhandler | amtype
------+--------+-------------+--------
403 | btree | bthandler | i
405 | hash | hashhandler | i
783 | gist | gisthandler | i
2742 | gin | ginhandler | i
4000 | spgist | spghandler | i
3580 | brin | brinhandler | i
(6 rows)
```
int4_ops分别支持btree, hash, gin索引方法。
如何查询某个扩展的opclass支持哪些类型
```
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
关联pg_opclass.opcintype, pg_type.oid
postgres=# select oid,typname from pg_type where oid in (23);
oid | typname
-----+---------
23 | int4
(1 row)
```
如何查询某个扩展的opclass支持哪些操作符
```
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
关联pg_opclass.opcfamily, pg_amop.amopfamily
postgres=# select * from pg_amop where amopfamily=45744;
amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose | amopopr | amopmethod | amopsortfamily
------------+--------------+---------------+--------------+-------------+---------+------------+----------------
45744 | 23 | 23 | 1 | s | 97 | 2742 | 0
45744 | 23 | 23 | 2 | s | 523 | 2742 | 0
45744 | 23 | 23 | 3 | s | 96 | 2742 | 0
45744 | 23 | 23 | 4 | s | 525 | 2742 | 0
45744 | 23 | 23 | 5 | s | 521 | 2742 | 0
(5 rows)
关联pg_operator.oid, pg_amop.amopopr
postgres=# select oprname from pg_operator where oid in (select amopopr from pg_amop where amopfamily=45744);
oprname
<=
=
<
=
(5 rows)
```
如何查询某个扩展的opclass是否为默认OPCLASS
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
opcdefault=t表示这个opcname是opcintype这个类型在opcmethod这个索引接口中的默认opclass.
如何查询某个扩展的opclass支持哪些JOIN方法
实际上JOIN方法与操作符有关，而操作符是否支持索引与索引接口实现有关。
所以这个问题可以定义为一个索引接口支持的操作符，支持哪些JOIN方法。
```
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
关联pg_opclass.opcfamily, pg_amop.amopfamily
postgres=# select * from pg_amop where amopfamily=45744;
amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose | amopopr | amopmethod | amopsortfamily
------------+--------------+---------------+--------------+-------------+---------+------------+----------------
45744 | 23 | 23 | 1 | s | 97 | 2742 | 0
45744 | 23 | 23 | 2 | s | 523 | 2742 | 0
45744 | 23 | 23 | 3 | s | 96 | 2742 | 0
45744 | 23 | 23 | 4 | s | 525 | 2742 | 0
45744 | 23 | 23 | 5 | s | 521 | 2742 | 0
(5 rows)
关联pg_operator.oid, pg_amop.amopopr
postgres=# select * from pg_operator where oid in (select amopopr from pg_amop where amopfamily=45744);
oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------+-------------+-----------------
<= | 11 | 10 | b | f | f | 23 | 23 | 16 | 525 | 521 | int4le | scalarltsel | scalarltjoinsel
| 11 | 10 | b | f | f | 23 | 23 | 16 | 97 | 523 | int4gt | scalargtsel | scalargtjoinsel= | 11 | 10 | b | t | t | 23 | 23 | 16 | 96 | 518 | int4eq | eqsel | eqjoinsel
< | 11 | 10 | b | f | f | 23 | 23 | 16 | 521 | 525 | int4lt | scalarltsel | scalarltjoinsel
= | 11 | 10 | b | f | f | 23 | 23 | 16 | 523 | 97 | int4ge | scalargtsel | scalargtjoinsel
(5 rows)
```
oprcanmerge是否支持MERGE JOIN。
oprcanhash是否支持HASH JOIN。
如何查询某个扩展的opclass是否支持排序
```
postgres=# select * from pg_opclass where opcname='int4_ops';
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+----------+--------------+----------+-----------+-----------+------------+------------
403 | int4_ops | 11 | 10 | 1976 | 23 | t | 0
405 | int4_ops | 11 | 10 | 1977 | 23 | t | 0
2742 | int4_ops | 2200 | 10 | 45744 | 23 | t | 0
(3 rows)
关联pg_opclass.opcfamily, pg_amop.amopfamily
postgres=# select * from pg_amop where amopfamily=45744;
amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose | amopopr | amopmethod | amopsortfamily
------------+--------------+---------------+--------------+-------------+---------+------------+----------------
45744 | 23 | 23 | 1 | s | 97 | 2742 | 0
45744 | 23 | 23 | 2 | s | 523 | 2742 | 0
45744 | 23 | 23 | 3 | s | 96 | 2742 | 0
45744 | 23 | 23 | 4 | s | 525 | 2742 | 0
45744 | 23 | 23 | 5 | s | 521 | 2742 | 0
(5 rows)
amopsortfamily
The B-tree operator family this entry sorts according to, if an ordering operator; zero if a search operator.
0 表示这是个搜索的操作符
其他表示这个是排序操作符
```
例如这个是knn排序的操作符
```
postgres=# select * from pg_amop where amopsortfamily<>0;
amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose | amopopr | amopmethod | amopsortfamily
------------+--------------+---------------+--------------+-------------+---------+------------+----------------
1029 | 600 | 600 | 15 | o | 517 | 783 | 1970
2594 | 604 | 600 | 15 | o | 3289 | 783 | 1970
2595 | 718 | 600 | 15 | o | 3291 | 783 | 1970
(3 rows)
postgres=# select * from pg_opclass where opcfamily=1029;
opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype
-----------+-----------+--------------+----------+-----------+-----------+------------+------------
783 | point_ops | 11 | 10 | 1029 | 600 | t | 603
(1 row)
postgres=# select * from pg_operator where oid=517;
oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+----------------+---------+---------
<-> | 11 | 10 | b | f | f | 600 | 600 | 701 | 517 | 0 | point_distance | - | -
(1 row)
postgres=# select * from pg_operator where oid=3289;
oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+------------+---------+---------
<-> | 11 | 10 | b | f | f | 604 | 600 | 701 | 3276 | 0 | dist_polyp | - | -
(1 row)
postgres=# select * from pg_operator where oid=3291;
oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+-------------+---------+---------
<-> | 11 | 10 | b | f | f | 718 | 600 | 701 | 1522 | 0 | dist_cpoint | - | -
(1 row)
```
如果创建了postgis插件，则可以看到有更多的支持距离排序的操作符，还有btree_gist，rum，pg_trgm等插件都支持距离排序。
```
postgres=# create extension postgis;
CREATE EXTENSION
postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create extension rum;
CREATE EXTENSION
postgres=# create extension pg_trgm;
CREATE EXTENSION
postgres=# select oprleft::regtype,oprright::regtype,oprresult::regtype,oprname,oprkind,oprcode from pg_operator where oid in (select amopopr from pg_amop where amopsortfamily<>0);
oprleft | oprright | oprresult | oprname | oprkind | oprcode
-----------------------------+-----------------------------+------------------+---------+---------+------------------------------------
point | point | double precision | <-> | b | point_distance
circle | point | double precision | <-> | b | dist_cpoint
polygon | point | double precision | <-> | b | dist_polyp
geometry | geometry | double precision | <-> | b | geometry_distance_centroid
geometry | geometry | double precision | <#> | b | geometry_distance_box
geometry | geometry | double precision | <<->> | b | geometry_distance_centroid_nd
geometry | geometry | double precision | |=| | b | geometry_distance_cpa
geography | geography | double precision | <-> | b | geography_distance_knn
interval | interval | interval | <-> | b | interval_dist
oid | oid | oid | <-> | b | oid_dist
time without time zone | time without time zone | interval | <-> | b | time_dist
timestamp without time zone | timestamp without time zone | interval | <-> | b | ts_dist
money | money | money | <-> | b | cash_dist
date | date | integer | <-> | b | date_dist
real | real | real | <-> | b | float4_dist
double precision | double precision | double precision | <-> | b | float8_dist
smallint | smallint | smallint | <-> | b | int2_dist
integer | integer | integer | <-> | b | int4_dist
bigint | bigint | bigint | <-> | b | int8_dist
timestamp with time zone | timestamp with time zone | interval | <-> | b | tstz_dist
tsvector | tsquery | real | <=> | b | public.rum_ts_distance
timestamp without time zone | timestamp without time zone | double precision | <=> | b | rum_timestamp_distance
timestamp without time zone | timestamp without time zone | double precision | <=| | b | rum_timestamp_left_distance
timestamp without time zone | timestamp without time zone | double precision | |=> | b | rum_timestamp_right_distance
timestamp with time zone | timestamp with time zone | double precision | <=> | b | rum_timestamptz_distance
timestamp with time zone | timestamp with time zone | double precision | <=| | b | rum_timestamptz_left_distance
timestamp with time zone | timestamp with time zone | double precision | |=> | b | rum_timestamptz_right_distance
smallint | smallint | double precision | <=> | b | rum_int2_distance
smallint | smallint | double precision | <=| | b | rum_int2_left_distance
smallint | smallint | double precision | |=> | b | rum_int2_right_distance
integer | integer | double precision | <=> | b | rum_int4_distance
integer | integer | double precision | <=| | b | rum_int4_left_distance
integer | integer | double precision | |=> | b | rum_int4_right_distance
bigint | bigint | double precision | <=> | b | rum_int8_distance
bigint | bigint | double precision | <=| | b | rum_int8_left_distance
bigint | bigint | double precision | |=> | b | rum_int8_right_distance
real | real | double precision | <=> | b | rum_float4_distance
real | real | double precision | <=| | b | rum_float4_left_distance
real | real | double precision | |=> | b | rum_float4_right_distance
double precision | double precision | double precision | <=> | b | rum_float8_distance
double precision | double precision | double precision | <=| | b | rum_float8_left_distance
double precision | double precision | double precision | |=> | b | rum_float8_right_distance
money | money | double precision | <=> | b | rum_money_distance
money | money | double precision | <=| | b | rum_money_left_distance
money | money | double precision | |=> | b | rum_money_right_distance
oid | oid | double precision | <=> | b | rum_oid_distance
oid | oid | double precision | <=| | b | rum_oid_left_distance
oid | oid | double precision | |=> | b | rum_oid_right_distance
anyarray | anyarray | double precision | <=> | b | rum_anyarray_distance
text | text | real | <-> | b | similarity_dist
text | text | real | <->> | b | word_similarity_dist_commutator_op
(51 rows)
```
代码层面理解
https://www.postgresql.org/docs/devel/static/xindex.html
例子
```
CREATE OPERATOR FAMILY integer_ops USING btree;
CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS
-- standard int8 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint8cmp(int8, int8) ,
FUNCTION 2 btint8sortsupport(internal) ,
FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ;
CREATE OPERATOR CLASS int4_ops
DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS
-- standard int4 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint4cmp(int4, int4) ,
FUNCTION 2 btint4sortsupport(internal) ,
FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ;
CREATE OPERATOR CLASS int2_ops
DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS
-- standard int2 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint2cmp(int2, int2) ,
FUNCTION 2 btint2sortsupport(internal) ,
FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ;
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) ,
-- cross-type in_range functions
FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) ,
FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) ,
FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) ,
FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;
```
查询所有的默认ops，opclass是op family的子集
SELECT am.amname AS index_method,
opc.opcname AS opclass_name,
opc.opcintype::regtype AS indexed_type,
opc.opcdefault AS is_default
FROM pg_am am, pg_opclass opc
WHERE opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;
index_method | opclass_name | opfamily_name | indexed_type | is_default
--------------+------------------------+-----------------------+-----------------------------+------------
brin | bit_minmax_ops | bit_minmax_ops | bit | t
brin | box_inclusion_ops | box_inclusion_ops | box | t
brin | bpchar_minmax_ops | bpchar_minmax_ops | character | t
brin | bytea_minmax_ops | bytea_minmax_ops | bytea | t
brin | char_minmax_ops | char_minmax_ops | "char" | t
brin | date_minmax_ops | datetime_minmax_ops | date | t
brin | float4_minmax_ops | float_minmax_ops | real | t
brin | float8_minmax_ops | float_minmax_ops | double precision | t
brin | inet_inclusion_ops | network_inclusion_ops | inet | t
brin | inet_minmax_ops | network_minmax_ops | inet | f
brin | int2_minmax_ops | integer_minmax_ops | smallint | t
brin | int4_minmax_ops | integer_minmax_ops | integer | t
brin | int8_minmax_ops | integer_minmax_ops | bigint | t
brin | interval_minmax_ops | interval_minmax_ops | interval | t
brin | macaddr8_minmax_ops | macaddr8_minmax_ops | macaddr8 | t
brin | macaddr_minmax_ops | macaddr_minmax_ops | macaddr | t
brin | name_minmax_ops | name_minmax_ops | name | t
brin | numeric_minmax_ops | numeric_minmax_ops | numeric | t
brin | oid_minmax_ops | oid_minmax_ops | oid | t
brin | pg_lsn_minmax_ops | pg_lsn_minmax_ops | pg_lsn | t
brin | range_inclusion_ops | range_inclusion_ops | anyrange | t
brin | text_minmax_ops | text_minmax_ops | text | t
brin | tid_minmax_ops | tid_minmax_ops | tid | t
brin | time_minmax_ops | time_minmax_ops | time without time zone | t
brin | timestamp_minmax_ops | datetime_minmax_ops | timestamp without time zone | t
brin | timestamptz_minmax_ops | datetime_minmax_ops | timestamp with time zone | t
brin | timetz_minmax_ops | timetz_minmax_ops | time with time zone | t
brin | uuid_minmax_ops | uuid_minmax_ops | uuid | t
brin | varbit_minmax_ops | varbit_minmax_ops | bit varying | t
btree | array_ops | array_ops | anyarray | t
btree | bit_ops | bit_ops | bit | t
btree | bool_ops | bool_ops | boolean | t
btree | bpchar_ops | bpchar_ops | character | t
btree | bpchar_pattern_ops | bpchar_pattern_ops | character | f
btree | bytea_ops | bytea_ops | bytea | t
btree | char_ops | char_ops | "char" | t
btree | cidr_ops | network_ops | inet | f
btree | date_ops | datetime_ops | date | t
btree | enum_ops | enum_ops | anyenum | t
btree | float4_ops | float_ops | real | t
btree | float8_ops | float_ops | double precision | t
btree | inet_ops | network_ops | inet | t
btree | int2_ops | integer_ops | smallint | t
btree | int4_ops | integer_ops | integer | t
btree | int8_ops | integer_ops | bigint | t
btree | interval_ops | interval_ops | interval | t
btree | jsonb_ops | jsonb_ops | jsonb | t
btree | macaddr8_ops | macaddr8_ops | macaddr8 | t
btree | macaddr_ops | macaddr_ops | macaddr | t
btree | money_ops | money_ops | money | t
btree | name_ops | text_ops | name | t
btree | numeric_ops | numeric_ops | numeric | t
btree | oid_ops | oid_ops | oid | t
btree | oidvector_ops | oidvector_ops | oidvector | t
btree | pg_lsn_ops | pg_lsn_ops | pg_lsn | t
btree | range_ops | range_ops | anyrange | t
btree | record_image_ops | record_image_ops | record | f
btree | record_ops | record_ops | record | t
btree | text_ops | text_ops | text | t
btree | text_pattern_ops | text_pattern_ops | text | f
btree | tid_ops | tid_ops | tid | t
btree | time_ops | time_ops | time without time zone | t
btree | timestamp_ops | datetime_ops | timestamp without time zone | t
btree | timestamptz_ops | datetime_ops | timestamp with time zone | t
btree | timetz_ops | timetz_ops | time with time zone | t
btree | tsquery_ops | tsquery_ops | tsquery | t
btree | tsvector_ops | tsvector_ops | tsvector | t
btree | uuid_ops | uuid_ops | uuid | t
btree | varbit_ops | varbit_ops | bit varying | t
btree | varchar_ops | text_ops | text | f
btree | varchar_pattern_ops | text_pattern_ops | text | f
gin | array_ops | array_ops | anyarray | t
gin | jsonb_ops | jsonb_ops | jsonb | t
gin | jsonb_path_ops | jsonb_path_ops | jsonb | f
gin | tsvector_ops | tsvector_ops | tsvector | t
gist | box_ops | box_ops | box | t
gist | circle_ops | circle_ops | circle | t
gist | inet_ops | network_ops | inet | f
gist | point_ops | point_ops | point | t
gist | poly_ops | poly_ops | polygon | t
gist | range_ops | range_ops | anyrange | t
gist | tsquery_ops | tsquery_ops | tsquery | t
gist | tsvector_ops | tsvector_ops | tsvector | t
hash | aclitem_ops | aclitem_ops | aclitem | t
hash | array_ops | array_ops | anyarray | t
hash | bool_ops | bool_ops | boolean | t
hash | bpchar_ops | bpchar_ops | character | t
hash | bpchar_pattern_ops | bpchar_pattern_ops | character | f
hash | bytea_ops | bytea_ops | bytea | t
hash | char_ops | char_ops | "char" | t
hash | cid_ops | cid_ops | cid | t
hash | cidr_ops | network_ops | inet | f
hash | date_ops | date_ops | date | t
hash | enum_ops | enum_ops | anyenum | t
hash | float4_ops | float_ops | real | t
hash | float8_ops | float_ops | double precision | t
hash | inet_ops | network_ops | inet | t
hash | int2_ops | integer_ops | smallint | t
hash | int4_ops | integer_ops | integer | t
hash | int8_ops | integer_ops | bigint | t
hash | interval_ops | interval_ops | interval | t
hash | jsonb_ops | jsonb_ops | jsonb | t
hash | macaddr8_ops | macaddr8_ops | macaddr8 | t
hash | macaddr_ops | macaddr_ops | macaddr | t
hash | name_ops | text_ops | name | t
hash | numeric_ops | numeric_ops | numeric | t
hash | oid_ops | oid_ops | oid | t
hash | oidvector_ops | oidvector_ops | oidvector | t
hash | pg_lsn_ops | pg_lsn_ops | pg_lsn | t
hash | range_ops | range_ops | anyrange | t
hash | text_ops | text_ops | text | t
hash | text_pattern_ops | text_pattern_ops | text | f
hash | tid_ops | tid_ops | tid | t
hash | time_ops | time_ops | time without time zone | t
hash | timestamp_ops | timestamp_ops | timestamp without time zone | t
hash | timestamptz_ops | timestamptz_ops | timestamp with time zone | t
hash | timetz_ops | timetz_ops | time with time zone | t
hash | uuid_ops | uuid_ops | uuid | t
hash | varchar_ops | text_ops | text | f
hash | varchar_pattern_ops | text_pattern_ops | text | f
hash | xid_ops | xid_ops | xid | t
spgist | box_ops | box_ops | box | t
spgist | inet_ops | network_ops | inet | t
spgist | kd_point_ops | kd_point_ops | point | f
spgist | poly_ops | poly_ops | polygon | t
spgist | quad_point_ops | quad_point_ops | point | t
spgist | range_ops | range_ops | anyrange | t
spgist | text_ops | text_ops | text | t
(128 rows)
查询opclass属于哪个op family
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
FROM pg_am am, pg_opclass opc, pg_opfamily opf
WHERE opc.opcmethod = am.oid AND
opc.opcfamily = opf.oid
ORDER BY index_method, opclass_name;
index_method | opclass_name | opfamily_name | indexed_type | is_default
--------------+------------------------+-----------------------+-----------------------------+------------
brin | bit_minmax_ops | bit_minmax_ops | bit | t
brin | box_inclusion_ops | box_inclusion_ops | box | t
brin | bpchar_minmax_ops | bpchar_minmax_ops | character | t
brin | bytea_minmax_ops | bytea_minmax_ops | bytea | t
brin | char_minmax_ops | char_minmax_ops | "char" | t
brin | date_minmax_ops | datetime_minmax_ops | date | t
brin | float4_minmax_ops | float_minmax_ops | real | t
brin | float8_minmax_ops | float_minmax_ops | double precision | t
brin | inet_inclusion_ops | network_inclusion_ops | inet | t
brin | inet_minmax_ops | network_minmax_ops | inet | f
brin | int2_minmax_ops | integer_minmax_ops | smallint | t
brin | int4_minmax_ops | integer_minmax_ops | integer | t
brin | int8_minmax_ops | integer_minmax_ops | bigint | t
brin | interval_minmax_ops | interval_minmax_ops | interval | t
brin | macaddr8_minmax_ops | macaddr8_minmax_ops | macaddr8 | t
brin | macaddr_minmax_ops | macaddr_minmax_ops | macaddr | t
brin | name_minmax_ops | name_minmax_ops | name | t
brin | numeric_minmax_ops | numeric_minmax_ops | numeric | t
brin | oid_minmax_ops | oid_minmax_ops | oid | t
brin | pg_lsn_minmax_ops | pg_lsn_minmax_ops | pg_lsn | t
brin | range_inclusion_ops | range_inclusion_ops | anyrange | t
brin | text_minmax_ops | text_minmax_ops | text | t
brin | tid_minmax_ops | tid_minmax_ops | tid | t
brin | time_minmax_ops | time_minmax_ops | time without time zone | t
brin | timestamp_minmax_ops | datetime_minmax_ops | timestamp without time zone | t
brin | timestamptz_minmax_ops | datetime_minmax_ops | timestamp with time zone | t
brin | timetz_minmax_ops | timetz_minmax_ops | time with time zone | t
brin | uuid_minmax_ops | uuid_minmax_ops | uuid | t
brin | varbit_minmax_ops | varbit_minmax_ops | bit varying | t
btree | array_ops | array_ops | anyarray | t
btree | bit_ops | bit_ops | bit | t
btree | bool_ops | bool_ops | boolean | t
btree | bpchar_ops | bpchar_ops | character | t
btree | bpchar_pattern_ops | bpchar_pattern_ops | character | f
btree | bytea_ops | bytea_ops | bytea | t
btree | char_ops | char_ops | "char" | t
btree | cidr_ops | network_ops | inet | f
btree | date_ops | datetime_ops | date | t
btree | enum_ops | enum_ops | anyenum | t
btree | float4_ops | float_ops | real | t
btree | float8_ops | float_ops | double precision | t
btree | inet_ops | network_ops | inet | t
btree | int2_ops | integer_ops | smallint | t
btree | int4_ops | integer_ops | integer | t
btree | int8_ops | integer_ops | bigint | t
btree | interval_ops | interval_ops | interval | t
btree | jsonb_ops | jsonb_ops | jsonb | t
btree | macaddr8_ops | macaddr8_ops | macaddr8 | t
btree | macaddr_ops | macaddr_ops | macaddr | t
btree | money_ops | money_ops | money | t
btree | name_ops | text_ops | name | t
btree | numeric_ops | numeric_ops | numeric | t
btree | oid_ops | oid_ops | oid | t
btree | oidvector_ops | oidvector_ops | oidvector | t
btree | pg_lsn_ops | pg_lsn_ops | pg_lsn | t
btree | range_ops | range_ops | anyrange | t
btree | record_image_ops | record_image_ops | record | f
btree | record_ops | record_ops | record | t
btree | text_ops | text_ops | text | t
btree | text_pattern_ops | text_pattern_ops | text | f
btree | tid_ops | tid_ops | tid | t
btree | time_ops | time_ops | time without time zone | t
btree | timestamp_ops | datetime_ops | timestamp without time zone | t
btree | timestamptz_ops | datetime_ops | timestamp with time zone | t
btree | timetz_ops | timetz_ops | time with time zone | t
btree | tsquery_ops | tsquery_ops | tsquery | t
btree | tsvector_ops | tsvector_ops | tsvector | t
btree | uuid_ops | uuid_ops | uuid | t
btree | varbit_ops | varbit_ops | bit varying | t
btree | varchar_ops | text_ops | text | f
btree | varchar_pattern_ops | text_pattern_ops | text | f
gin | array_ops | array_ops | anyarray | t
gin | jsonb_ops | jsonb_ops | jsonb | t
gin | jsonb_path_ops | jsonb_path_ops | jsonb | f
gin | tsvector_ops | tsvector_ops | tsvector | t
gist | box_ops | box_ops | box | t
gist | circle_ops | circle_ops | circle | t
gist | inet_ops | network_ops | inet | f
gist | point_ops | point_ops | point | t
gist | poly_ops | poly_ops | polygon | t
gist | range_ops | range_ops | anyrange | t
gist | tsquery_ops | tsquery_ops | tsquery | t
gist | tsvector_ops | tsvector_ops | tsvector | t
hash | aclitem_ops | aclitem_ops | aclitem | t
hash | array_ops | array_ops | anyarray | t
hash | bool_ops | bool_ops | boolean | t
hash | bpchar_ops | bpchar_ops | character | t
hash | bpchar_pattern_ops | bpchar_pattern_ops | character | f
hash | bytea_ops | bytea_ops | bytea | t
hash | char_ops | char_ops | "char" | t
hash | cid_ops | cid_ops | cid | t
hash | cidr_ops | network_ops | inet | f
hash | date_ops | date_ops | date | t
hash | enum_ops | enum_ops | anyenum | t
hash | float4_ops | float_ops | real | t
hash | float8_ops | float_ops | double precision | t
hash | inet_ops | network_ops | inet | t
hash | int2_ops | integer_ops | smallint | t
hash | int4_ops | integer_ops | integer | t
hash | int8_ops | integer_ops | bigint | t
hash | interval_ops | interval_ops | interval | t
hash | jsonb_ops | jsonb_ops | jsonb | t
hash | macaddr8_ops | macaddr8_ops | macaddr8 | t
hash | macaddr_ops | macaddr_ops | macaddr | t
hash | name_ops | text_ops | name | t
hash | numeric_ops | numeric_ops | numeric | t
hash | oid_ops | oid_ops | oid | t
hash | oidvector_ops | oidvector_ops | oidvector | t
hash | pg_lsn_ops | pg_lsn_ops | pg_lsn | t
hash | range_ops | range_ops | anyrange | t
hash | text_ops | text_ops | text | t
hash | text_pattern_ops | text_pattern_ops | text | f
hash | tid_ops | tid_ops | tid | t
hash | time_ops | time_ops | time without time zone | t
hash | timestamp_ops | timestamp_ops | timestamp without time zone | t
hash | timestamptz_ops | timestamptz_ops | timestamp with time zone | t
hash | timetz_ops | timetz_ops | time with time zone | t
hash | uuid_ops | uuid_ops | uuid | t
hash | varchar_ops | text_ops | text | f
hash | varchar_pattern_ops | text_pattern_ops | text | f
hash | xid_ops | xid_ops | xid | t
spgist | box_ops | box_ops | box | t
spgist | inet_ops | network_ops | inet | t
spgist | kd_point_ops | kd_point_ops | point | f
spgist | poly_ops | poly_ops | polygon | t
spgist | quad_point_ops | quad_point_ops | point | t
spgist | range_ops | range_ops | anyrange | t
spgist | text_ops | text_ops | text | t
(128 rows)
查询所有定义的op family以及这些op family里面包含了哪些operator
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
opc.opcname AS opclass_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_opclass opc, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid AND
opc.opcmethod = am.oid AND
opc.opcfamily = opf.oid
ORDER BY index_method, opfamily_name, opfamily_operator;
index_method | opfamily_name | opclass_name | opfamily_operator
--------------+-----------------------+------------------------+-------------------------------------------------------------
brin | bit_minmax_ops | bit_minmax_ops | =(bit,bit)
brin | bit_minmax_ops | bit_minmax_ops | <(bit,bit)
brin | bit_minmax_ops | bit_minmax_ops | >(bit,bit)
brin | bit_minmax_ops | bit_minmax_ops | <=(bit,bit)
brin | bit_minmax_ops | bit_minmax_ops | >=(bit,bit)
brin | box_inclusion_ops | box_inclusion_ops | @>(box,point)
brin | box_inclusion_ops | box_inclusion_ops | <<(box,box)
brin | box_inclusion_ops | box_inclusion_ops | &<(box,box)
brin | box_inclusion_ops | box_inclusion_ops | &>(box,box)
brin | box_inclusion_ops | box_inclusion_ops | >>(box,box)
brin | box_inclusion_ops | box_inclusion_ops | <@(box,box)
brin | box_inclusion_ops | box_inclusion_ops | @>(box,box)
brin | box_inclusion_ops | box_inclusion_ops | ~=(box,box)
brin | box_inclusion_ops | box_inclusion_ops | &&(box,box)
brin | box_inclusion_ops | box_inclusion_ops | <<|(box,box)
brin | box_inclusion_ops | box_inclusion_ops | &<|(box,box)
brin | box_inclusion_ops | box_inclusion_ops | |&>(box,box)
brin | box_inclusion_ops | box_inclusion_ops | |>>(box,box)
brin | bpchar_minmax_ops | bpchar_minmax_ops | =(character,character)
brin | bpchar_minmax_ops | bpchar_minmax_ops | <(character,character)
brin | bpchar_minmax_ops | bpchar_minmax_ops | <=(character,character)
brin | bpchar_minmax_ops | bpchar_minmax_ops | >(character,character)
brin | bpchar_minmax_ops | bpchar_minmax_ops | >=(character,character)
brin | bytea_minmax_ops | bytea_minmax_ops | =(bytea,bytea)
brin | bytea_minmax_ops | bytea_minmax_ops | <(bytea,bytea)
brin | bytea_minmax_ops | bytea_minmax_ops | <=(bytea,bytea)
brin | bytea_minmax_ops | bytea_minmax_ops | >(bytea,bytea)
brin | bytea_minmax_ops | bytea_minmax_ops | >=(bytea,bytea)
brin | char_minmax_ops | char_minmax_ops | =("char","char")
brin | char_minmax_ops | char_minmax_ops | <("char","char")
brin | char_minmax_ops | char_minmax_ops | <=("char","char")
brin | char_minmax_ops | char_minmax_ops | >("char","char")
brin | char_minmax_ops | char_minmax_ops | >=("char","char")
.............
spgist | poly_ops | poly_ops | <<(polygon,polygon)
spgist | poly_ops | poly_ops | &<(polygon,polygon)
spgist | poly_ops | poly_ops | &>(polygon,polygon)
spgist | poly_ops | poly_ops | >>(polygon,polygon)
spgist | poly_ops | poly_ops | <@(polygon,polygon)
spgist | poly_ops | poly_ops | @>(polygon,polygon)
spgist | poly_ops | poly_ops | ~=(polygon,polygon)
spgist | poly_ops | poly_ops | &&(polygon,polygon)
spgist | poly_ops | poly_ops | <<|(polygon,polygon)
spgist | poly_ops | poly_ops | &<|(polygon,polygon)
spgist | poly_ops | poly_ops | |&>(polygon,polygon)
spgist | poly_ops | poly_ops | |>>(polygon,polygon)
spgist | poly_ops | poly_ops | <->(polygon,point)
spgist | quad_point_ops | quad_point_ops | >^(point,point)
spgist | quad_point_ops | quad_point_ops | <<(point,point)
spgist | quad_point_ops | quad_point_ops | >>(point,point)
spgist | quad_point_ops | quad_point_ops | <^(point,point)
spgist | quad_point_ops | quad_point_ops | ~=(point,point)
spgist | quad_point_ops | quad_point_ops | <@(point,box)
spgist | quad_point_ops | quad_point_ops | <->(point,point)
spgist | range_ops | range_ops | =(anyrange,anyrange)
spgist | range_ops | range_ops | &&(anyrange,anyrange)
spgist | range_ops | range_ops | @>(anyrange,anyelement)
spgist | range_ops | range_ops | @>(anyrange,anyrange)
spgist | range_ops | range_ops | <@(anyrange,anyrange)
spgist | range_ops | range_ops | <<(anyrange,anyrange)
spgist | range_ops | range_ops | >>(anyrange,anyrange)
spgist | range_ops | range_ops | &<(anyrange,anyrange)
spgist | range_ops | range_ops | &>(anyrange,anyrange)
spgist | range_ops | range_ops | -|-(anyrange,anyrange)
spgist | text_ops | text_ops | =(text,text)
spgist | text_ops | text_ops | <(text,text)
spgist | text_ops | text_ops | <=(text,text)
spgist | text_ops | text_ops | >(text,text)
spgist | text_ops | text_ops | >=(text,text)
spgist | text_ops | text_ops | ~<~(text,text)
spgist | text_ops | text_ops | ~<=~(text,text)
spgist | text_ops | text_ops | ~>=~(text,text)
spgist | text_ops | text_ops | ~>~(text,text)
spgist | text_ops | text_ops | ^@(text,text)
(1197 rows)
小结
从本例来看，使用btree_gin接口，可以实现标准类型在GIN索引中的范围扫描。
参考
https://www.postgresql.org/docs/10/static/catalogs.html
```
51.3. pg_am
51.4. pg_amop
51.5. pg_amproc
51.33. pg_opclass
51.34. pg_operator
51.35. pg_opfamily
```
https://www.postgresql.org/docs/12/indexes-opclass.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
