PostgreSQL如何確定某個opclass支援哪些操作符(支援索引)，JOIN方法，排序
標籤
PostgreSQL , 索引介面 , 操作符 , am , family , opclass , operator , function , order by
背景
PostgreSQL有很多的索引介面，同時索引介面還支援使用者擴充套件，當擴充套件索引時，需要提供opclass。
那麼每一個擴充套件的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) ;
小結
從本例來看，使用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
相關文章
- PostgreSQL 13支援增量排序(Incremental Sorting)SQL排序REM
- 如何檢視 SAP Fiori Elements List Report Table 都支援哪些設定
- 獲取母字串中某個子字串的某個確定的index值字串Index
- mysql 支援索引字元超過 767MySql索引字元
- 如何確定你的Linux發行版中有沒有某個軟體包Linux
- Spring Boot 中如何支援非同步方法Spring Boot非同步
- 陣列有哪些方法支援響應式更新的？底層原理如何實現?陣列
- Python中dict支援多個key的方法Python
- iis支援.apk檔案下載的設定方法APK
- JVM 還支援哪些語言JVM
- hadoop需要哪些技術支援Hadoop
- SAP ABAP OData 服務如何支援 $orderby (排序)操作試讀版排序
- 怎麼確定postgresql是否安裝SQL
- iPadOS14支援哪些裝置？iPadOS14支援機型及升級方法iPad
- 如何找到某個鍵值在索引中存在於哪個BLOCK中索引BloC
- PostgreSQL：模式匹配操作符SQL模式
- Oracle & MySQL & PostgreSQL資料庫恢復支援OracleMySql資料庫
- 圖解MySQL索引(三)—如何正確使用索引？圖解MySql索引
- Python支援哪些資料型別Python資料型別
- redis 支援哪些資料型別Redis資料型別
- 用程式碼判斷當前系統是否支援某個版本的feature
- keycloak~正確讓api介面支援跨域API跨域
- onethink如何支援MSSQL?SQL
- 如何唯一確定一個 Java 類？Java
- citusdata支援的一些postgresql的外掛SQL
- webpack 支援的模組方法Web
- OkHttp設定支援Etag快取HTTP快取
- PostgreSQL/GreenPlum Merge Inner Join解密SQL解密
- Fuchsia最新訊息，確認支援Android應用Android
- 支援雲列印的印表機有哪些？
- Flink 支援的重啟策略有哪些
- Dubbo 支援哪些序列化協議？協議
- 看看Qt 6.0 支援哪些模組吧QT
- 逆向工程通過某個欄位排序排序
- 按照NSArray內部的某個物件排序物件排序
- 方法的通用格式 定義方法要做到兩個明確
- python如何支援併發？Python
- redis cluster如何支援pipelineRedis