33.14. 欷匘䘵嚘毖埼

33.14.1. 䘵嚘桹濘启淉嘩严䌂

pg_am 臘婺懟婻䘵嚘桹濘(喙鄘䓄嘩螪閞桹濘)鄘寙劆婔溇螄嘘㔗凹臘䔇捞锔螪閞桹濘櫇毕喙傺庯 PostgreSQL 嘖欔橬螪閞桹濘婘 pg_am 麯鄘橬柟誄㔗埇傖锔誺垔幬襕挗䔇毖埼誺䘋幽婘 pg_am 麯录傺婔婻桄臯䔇媂濘嵂媹婔婻䘵嚘螪閞桹濘婉誺認底底誩誩轙庺庖橸䆹䔇喙垹(埗黙䆹49)㔗

婔婻䘵嚘桹濘䔇誺䘋幽婉䕘毖䘖長傂嘘臖䘵嚘桹濘儖襕淉嘩䔇昄扞䌂傋䔇媇敇㔗蔯滇淉嘩严䌂臘滯䘵嚘桹濘婘淉嘩䬹垔昄扞䌂傋䔇施唍驔襕嘪䫘䔇淉嘩镖劽㔗淉嘩严䌂䔇劉䓄䔇䫌準滇啹婺垄傸弄滯滇婔䓉䘵嚘埇傖嘪䫘䔇 WHERE 床埖䔇淉嘩严镖(幘儌滇埇傖蘸寡潊婔婻䘵嚘欆柟溇傽)㔗婔婻淉嘩严䌂幘埇傖弄滯婔底䘵嚘桹濘驔襕䔇喙鄘淉嘩䔇櫇毕誺䘋嘖滇垄傸幽婉䕘毖启埇傖婯䘵嚘婔蕙嘪䫘䔇 WHERE 床埖淉嘩严䕩噿㔗

埇傖婺劯婔婻昄扞䌂傋启䘵嚘桹濘垔幬崔婻淉嘩严䌂㔗認幽啔䔇䂷悩滇埇傖婺婔䓉昄扞䌂傋垔幬崔喖䘵嚘臺幬㔗懫套婔婻 B-tree 䘵嚘襕挗婺垄淉嘩䔇懟䓉昄扞䌂傋垔幬婔婻毐废釺废㔗凹庯婔婻崉昄昄扞䌂傋蔯蘔橬婔婻锔誺崉昄䂺凹唚凹昄扞毐废䔇 B-tree 淉嘩严䌂埇脘嚔橬䫘誻橬婔婻滇䫘垂鄘毐废京京㔗锔婩噽婺婔婻淉嘩严䌂嚔赆螴婺橔婩䫘䔇幽婫赆湺螄婺臖昄扞䌂傋启䘵嚘桹濘䔇䚺䩕淉嘩严䌂㔗

劯湙䔇淉嘩严䌂劉庖埇傖䫘庯崔䓉婉劯䔇䘵嚘桹濘(懫套 B-tree 启 Hash 螪閞桹濘鄘橬埆 int4_ops 䔇淉嘩严䌂)嘖滇懟婻認湙䔇臘鄘滇婔婻䋸䆋䔇垂嘷媙釂彖彆垔幬㔗

33.14.2. 䘵嚘桹濘亡䘖

启婔䓉淉嘩严䌂䕩噿蕫䔇淉嘩严滇锔誺"亡䘖埙"湺臖䔇亡䘖埙䫘庯湺臖懟䓉淉嘩严婘垄䔇淉嘩严䌂䯇嵄麯䔇臺幬㔗懫套B-tree 凹髞庖橬婖湚䔇毐废襕挗償庯彄崓庯啹溴償"償庯"启"崓庯潡京庯"認湙䔇淉嘩严鄘滇 B-tree 欔懘噘轼䔇㔗啹婺 PostgreSQL 噕螩䫘潙垔幬淉嘩严PostgreSQL 方濘備锔誺昖䩋淉嘩严䔇劉庖(懫套 < 潡 >=)儌滯䍘垄誕臯䔇懫膄滇傔幽㔗垂鍙婪䘵嚘桹濘垔幬庖婔喖"亡䘖"垄埇傖䩋嘩婔轸攓䔇淉嘩严㔗懟䓉淉嘩严䌂滆䴺凹庯䬹垔昄扞䌂傋蔯蘔滇巻䓉垂鍙淉嘩严凹庫懟䓉亡䘖傖埪蓼麪䘵嚘䔇臺幬㔗

B-tree 䘵嚘垔幬庖庫䓉亡䘖㔗婘臘33-2婺滆䴺㔗

Hash 䘵嚘埻臘䴺毬嘉䔇䕩京啹溴垄傸埻垔幬庖婔婻亡䘖婘臘33-3麯滆䴺㔗

GiST 䘵嚘䫔躿敘媹䕕昂垄傸湹橸儌澇橬啺垔䔇亡䘖镖㔗垂鍙婪滇懟婻䬹垔 GiST 淉嘩严䌂䔇"婔躘攓"櫇毕誺䘋蓼麪亡䘖埙滇傔幽湙床㔗嘩婺䴺冋橬庹婻喙䘞䔇 GiST 䘵嚘淉嘩严䌂䘵嚘庯䂘庹嘘凹茇柊冕臘33-4婺欔䴺䔇"R-tree"亡䘖㔗噽婺䔇啕婻滇婴䂘敋臘(麉培㔕䕩劯㔕寙劆㔕寙劆庯)㔕啕婻埻蔄荏 x 床湺㔕啕婻凹 y 床湺誕臯劯湙敋臘㔗

GIN 䘵嚘婘䕕昂攓桹麵婯 GiST 䘵嚘䌂嚚垄傸鄘澇橬婔婻啺垔䔇亡䘖镖蔯滇䫌懟婻淉嘩严䌂䔇櫇毕誺䘋湹扞淉嘩严䌂䔇垔幬準蓼麪亡䘖埙㔗嘩婺䴺冋臘33-5滆䴺庖喙䘞䔇昄䂇淉嘩严䌂嘪䫘䔇亡䘖埙㔗

臙濘懟欔橬亡䘖淉嘩严鄘誫啂婄儫唚㔗垂鍙婪欔橬垔幬婺䘵嚘桹濘亡䘖䔇淉嘩严鄘媙釂誫啂 boolean 䌂傋啹婺垄傸媙釂庺䯄婘婔婻 WHERE 床埖䔇釽北認湙欉脘赆婔婻䘵嚘嘪䫘㔗

釺冪柊婔婋pg_am 麯䔇 amorderstrategy 庖枕只臬臖䘵嚘桹濘滇劥櫇毕毐废䔇欆柟㔗镽懟叿五垄婉櫇毕套悩垄櫇毕闼幽 amorderstrategy 儌滇凹庫臖毐废淉嘩严䔇亡䘖埙㔗懫套B-tree 䔇 amorderstrategy = 1 滇垄䔇"償庯"䔇亡䘖埙㔗

33.14.3. 䘵嚘桹濘櫇毕誺䘋

橬施唍亡䘖䔇媇敇誻婉轿傖螷係䂘喿垔套嘘嘪䫘昊婻䘵嚘㔗啹溴䘵嚘桹濘驔襕鍇媹䔇婔底誺䘋準媺臕溼婩噖嘩㔗冋套B-tree 䘵嚘桹濘媙釂脘崘懫膄婴婻髞庖傖喿垔噽婺婔婻滇崓庯㔕京庯㔕誻滇償庯埥崡婔婻㔗䌂嚚䔇誻橬 Hash 䘵嚘桹濘媙釂脘崘婘髞唚婪螇䞖昼彖唚㔗認底淉嘩启 SQL 变傴溇傽麯嘪䫘䔇淉嘩严幽婉凹庫垄傸婘喙鄘赆䘵嚘桹濘䔇䞇䊖誺䘋脄䫘㔗

儌償亡䘖婔湙淉嘩严䌂弄滯婘婔垔䔇昄扞䌂傋启臺幬蓼麪䔇溇傽婋巻婻䬹垔庘昄凹庫認底蓐謾婺䔇巻婔婻㔗䘵嚘桹濘弄滯垄驔襕䔇庘昄镖蔯淉嘩严䌂锔誺䂍垄傸蕋庽"櫇毕庘昄䚡埙"準湺臖襕溼䇞嘪䫘䔇庘昄㔗

B-tree 驔襕婔婻臘33-6麯滆䴺䔇櫇毕庘昄㔗

䌂嚚䔇滇 Hash 䘵嚘幘驔襕婔婻櫇毕庘昄婘臘33-7麯滆䴺㔗

GiST 䘵嚘驔襕婄䓉櫇毕庘昄婘臘33-8麯滆䴺㔗

GIN 䘵嚘驔襕啕䓉櫇毕庘昄婘臘33-9麯滆䴺㔗

启亡䘖淉嘩严婉劯櫇毕庘昄誫啂䬹垔䘵嚘桹濘鵇橘䔇昄扞䌂傋懫套婘 B-tree 䔇愙喕婋誫啂婔婻橬严埙昘昄㔗

33.14.4. 冋床

斵䇽噾䂟庖蓼庖認底楗媕闼幽䯄婘儌準䩋婔婻录傺桄淉嘩严䌂䔇冋床㔗嘹埇傖婘 src/tutorial/complex.c 启 src/tutorial/complex.sql 婺欆彄認麯螾誄䔇冋床㔗淉嘩严䌂儕輙庖闼底傖䂺凹唚釺废凹崉昄毐废䔇淉嘩严認湙儌埇傖锬拷 complex_abs_ops 認婻劉庖㔗饡噽驔襕婔婻淉嘩严镖劽㔗䫘庯垔幬淉嘩严䔇誺䘋噾䂟婘誗33.12螘螺誺庖㔗凹認婻䫘庯 B-tree 䔇淉嘩严䌂驔襕䔇淉嘩严滇

• 䂺凹唚 償庯 (亡䘖 1)
• 䂺凹唚 償庯京庯 (亡䘖 2)
• 䂺凹唚 京庯 (亡䘖 3)
• 䂺凹唚 崓庯京庯 (亡䘖 4)
• 䂺凹唚 崓庯 (亡䘖 5)

垔幬婔䂇䕩噿䔇懫膄淉嘩严橔婉垹滷庺髍䔇桹濘滇饡噽喍庺 B-tree 懫膄櫇毕庘昄䇽劯喉喍庺噽垄寙輙庖櫇毕庘昄䔇剘臯庘昄㔗認儌废儏庖昊底愙喕婋凚躘婉婔躘䂷悩䔇橺嚔㔗湹扞認婻毺嚘饡噽喍庺

#define Mag(c)  ((c)->x*(c)->x + (c)->y*(c)->y)

static int
complex_abs_cmp_internal(Complex *a, Complex *b)
{
    double      amag = Mag(a),
                bmag = Mag(b);

    if (amag < bmag)
        return -1;
    if (amag > bmag)
        return 1;
    return 0;
}

䯄婘償庯庘昄䩋蕙準償認湙

PG_FUNCTION_INFO_V1(complex_abs_lt);

Datum
complex_abs_lt(PG_FUNCTION_ARGS)
{
    Complex    *a = (Complex *) PG_GETARG_POINTER(0);
    Complex    *b = (Complex *) PG_GETARG_POINTER(1);

    PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
}

噽垄啕婻庘昄䔇婉劯幋崇備婘垄傸套嘘儖喙鄘庘昄䔇䂷悩婯镽懫膄㔗

婋婔準嘺庯 SQL 庘昄弄滯庘昄启淉嘩严

CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
    AS 'filename', 'complex_abs_lt'
    LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR < (
   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
   commutator = > , negator = >= ,
   restrict = scalarltsel, join = scalarltjoinsel
);

毺垔溼䇞䔇庴扵単启"麂"淉嘩严傖埪弄滯鍊彽启誂毖锬拷攓庘昄鄘滇麂婩麉襕䔇劥彍嚻寡単儖方濘橬昽婄彷䫘䘵嚘㔗臙濘懟償庯㔕京庯㔕崓庯婬䓉愙喕婋庫臖嘪䫘婉劯䔇锬拷攓庘昄㔗

噽垄庹婻唚冖濘懟䔇閞鵻

• 埻橬埇傖橬婔婻噾变劉淉嘩严(=)檪 complex 䌂傋啔婺噽婴婻淉嘩昄㔗認䓉愙喕婋澇橬噽垄䫘庯 complex 䔇 = 淉嘩严嘖滇套悩襕彽嘩婔婻垂䫘䔇昄扞䌂傋埇脘驔襕 = 啔婺䫘庯崉昄䔇捞锔京庯淉嘩䔇淉嘩严㔗認䓉愙喕婋埇脘驔襕嘪䫘婔底噽垄淉嘩严劉䓄準变劉 complex_abs_eq 㔗

• 儘䞇 PostgreSQL 埇傖崇䊖 SQL 劉庖䕩劯䔇庘昄埻襕垄傸䔇膷噖昄扞䌂傋婉劯蔯 C 埻脘崇䊖婔婻噙橬䂍垔劉䓄䔇噘匔誺䘋㔗啹溴婉脘檪 C 庘昄变劉婺償 abs_eq 認湙䞔剘䔇劉庖㔗锔婩婘 C 庘昄劉麯麵寙劆昄扞䌂傋劉䓄滇婔婻喘幹愇認湙儌婉嚔启䫘庯噽垄昄扞䌂傋䔇庘昄喾仕㔗

• 埇傖彽嘩劉婺 abs_eq 䔇 SQL 庘昄冺麹 PostgreSQL 锔誺膷噖昄扞䌂傋䔇婉劯準寺彖傂嘘噽垄劯劉 SQL 庘昄㔗婺庖傴冋床䞔剘啔䔇庘昄婘 C 北渇启 SQL 北渇鄘橬䕩劯䔇劉䓄㔗

婋婔準滇濘喯 B-tree 驔襕䔇懫膄"櫇毕誺䘋"㔗垂䯄認婻冋床䔇 C 傼乕婘寙劆淉嘩严誺䘋䔇劯婔婻桺傽婺婋麵滇垔幬庘昄䔇桹濘

CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS 'filename'
    LANGUAGE C IMMUTABLE STRICT;

斵䇽噾䂟橬庖驔襕䔇淉嘩严启櫇毕誺䘋儌埇傖橔劯录傺認婻淉嘩严䌂庖

CREATE OPERATOR CLASS complex_abs_ops
    DEFAULT FOR TYPE complex USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       complex_abs_cmp(complex, complex);

認湙儌垯潊庖䯄婘埇傖婘婔婻 complex 彖婪录傺启嘪䫘 B-tree 䘵嚘庖㔗

埇傖檪淉嘩严螄嘘喍冖敘喖嘍婔底償

        OPERATOR        1       < (complex, complex) ,

嘖滇套悩臖淉嘩严毖埖䔇昄扞䌂傋滇垔幬䔇淉嘩严䌂崇䊖䔇婩薪闼儌澇媙襕認幽啔㔗

婪麵䔇冋床啺螆嘹愿檪認婻桄淉嘩严䌂嘩婺 complex 昄扞䌂傋䔇䚺䩕 B-tree 淉嘩严䌂㔗套悩嘹婉愿認幽啔埻襕寂毬噿髞庖 DEFAULT 剿埇㔗

33.14.5. 庴埬昄扞䌂傋䔇淉嘩严䌂

彄䕞嬉婺溵潏傸鄘锊劆䔇啺垔婔婻淉嘩严䌂埻脘崇䊖婔䓉昄扞䌂傋㔗荘䇽懟婻䘵嚘庖枕鄘埻脘滇剘䋸婔䓉昄扞䌂傋嘖滇嘪䫘䘵嚘淉嘩严準懫膄婔婻噾䘵嚘庖枕启婔婻婉劯䌂傋䔇唚婩婩冽橬䫘崇㔗䕞嬉B-tree 启 GiST 䘵嚘桹濘櫇毕認湙啔㔗

B-trees 襕挗懟婻淉嘩严䔇噥淉嘩昄媙釂滇噾䘵嚘昄扞䌂傋嘖埿淉嘩昄埇傖滇婉劯䔇䌂傋㔗劯施媙釂庻婘婔婻对陉䔇櫇毕庘昄㔗冋套喙䘞䔇 bigint(int8) 䌂傋䔇淉嘩严䌂噕螩婯 int4 启 int2 誕臯庴埬懫膄㔗噽垔幬埇傖認湙喍

CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree AS
  -- 湺庖 int8 懫膄
  OPERATOR 1 < ,
  OPERATOR 2 <= ,
  OPERATOR 3 = ,
  OPERATOR 4 >= ,
  OPERATOR 5 > ,
  FUNCTION 1 btint8cmp(int8, int8) ,

  -- 婯 int2 (smallint) 庴埬懫膄
  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) ,

  -- 婯 int4 (integer) 庴埬懫膄
  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) ;

驔襕濘懟䔇滇認麯䔇垔幬"麉蘘"庖淉嘩严亡䘖启櫇毕庘昄埙㔗認備凹 B-tree 淉嘩严䌂滇噕螩䔇埻襕懟婻昄庖䔇垂冋鄘橬婉劯䔇埿淉嘩昄䌂傋㔗麂庴埬䌂傋垂冋儖滇臖淉嘩严䌂䔇邻螴潡婂淉嘩严㔗

GiST 䘵嚘婉噕螩麉蘘亡䘖潡櫇毕庘昄埙嘖锔誺婺懟婻驔襕赆櫇毕䔇淉嘩严滯䇞毺垔亡䘖埙傉䇽脘崘傯櫇毕崔䓉埿淉嘩昄昄扞䌂傋婺诙䕪㔗consistent 櫇毕庘昄媙釂嘺庯亡䘖埙䇞垔噙嘷驔襕啔傔幽幽媙釂庖崺毖埖敄嘷昄扞䌂傋䔇懫膄唚㔗

33.14.6. 淉嘩严䌂䔇係䂘䕩噿攓

鍴庖滇劥埇傖䫘庯䘵嚘崡PostgreSQL 誻橬崔䓉锫冇嘪䫘淉嘩严䌂準毘桺淉嘩严攓蘘㔗啹溴剿嘪幽婉欷䞖婺嘹躻垔幬䔇昄扞䌂傋䘵嚘婘傂嘘庖枕婪傺䆋䘵嚘嘹埇脘誻滇婯橕录傺淉嘩严䌂㔗

䬹彆滇臩套 ORDER BY 启 DISTINCT 幋䌂驔襕凹唚誕臯懫膄启毐废䔇 SQL 䬹攓㔗襕婘躻垔幬䔇昄扞䌂傋婪垂䯄認底䬹攓PostgreSQL 儖嚔婺臖䌂傋昖欆邻螴䔇 B-tree 淉嘩严䌂㔗臖淉嘩严䌂婺䔇"equals"潊叻婺 GROUP BY 启 DISTINCT 垔幬庖䕩京䔇楗媕劯施淉嘩严䌂䔇毐废釺废垔幬庖邻螴䔇 ORDER BY 毐废㔗

䫘潙躻垔幬䌂傋昄䂇䔇懫膄劯湙釕冺蕡庯邻螴 B-tree 淉嘩严䌂䔇臺懟㔗

套悩凹庯昊婻昄扞䌂傋婉庻婘邻螴 B-tree 淉嘩严䌂闼幽係䂘儖嚔躻媘凂欆邻螴䔇 Hash 淉嘩严䌂㔗嘖啹婺 Hash 淉嘩严䌂備備柊冕䕩京懫膄欔傖婘垂處婺垄備脘䫘庯昄䂇䔇䕩京攓敋臘㔗

套悩昊婻昄扞䌂傋婉庻婘傂嘘䚺䩕淉嘩严䌂嘹儌嚔婘嘪䫘臖 SQL 䬹攓施冖彄婔婻䌂嚚"could not identify an ordering operator"䔇髍臇㔗

33.14.7. 淉嘩严䌂䔇䬹枪䬹攓

誻橬婴䓉淉嘩严䌂䔇䬹枪䬹攓澇橬螘螺婂襕滇啹婺垄傸凹庯䚺䩕䔇 B-tree 䘵嚘桹濘幽婉麂婩橬䫘㔗

锔婩檪婔婻淉嘩严弄滯婺婔婻淉嘩严䌂䔇潊叻懟叿五䘵嚘桹濘埇傖嘪䫘臖淉嘩严演䘵悇轿 WHERE 溇傽䔇臯镖劽㔗懫套

SELECT * FROM table WHERE integer_column < 4;

埇傖䫌婔婻傺䆋婘昘昄庖枕婪䔇 B-tree 䘵嚘來䇞婄悇轿㔗嘖滇橬施唍嚔橬認湙䔇䯄償䘵嚘滇䫘嘩对陉昄扞臯䔇幽婉來䇞䔇毺劏㔗懫套套悩婔婻 GiST 䘵嚘埻婺凹茇庻嗘变䘯䔇桹庖闼幽垄儌方濘來䇞婄悇轿婴婻麂桹嘵凹茇(懫套崔膹嘵)幋閘滇劥襖䕡䔇 WHERE 溇傽敋臘㔗嘖滇埇傖嘪䫘認婻䘵嚘欆庺闼底变䘯桹庖启䕞湺凹茇䔇变䘯桹庖麉劽䔇凹茇䇽劯埻婘䘵嚘欆彄䔇凹茇婪啔來䇞䔇麉劽敋臘㔗套悩認䓉愙嘵埇傖锔誺闼儌臘䘵嚘凹淉嘩严滇"溆昼䔇"幽婫婘 CREATE OPERATOR CLASS 变傴麯䂍 OPERATOR 床埖嵂媹 RECHECK 㔗套悩䘵嚘媺臕誫啂欔橬襕挗䔇臯媹婪婔底鍇媹䔇臯闼幽 RECHECK 儌劽濘認底鵺崡䔇臯儌埇傖锔誺欓臯橔彺䔇淉嘩严脄䫘潽鍴㔗

喉蔄荏埻婘䘵嚘婺庻嗘崉溗凹茇(懫套崔膹嘵)䔇变䘯桹庖䔇愙嘵㔗認䓉愙喕婋婘䘵嚘溇䕞麯庻嗘昘婻崔膹嘵澇橬崻崔䔇昄唚(幘埇傖埻庻嗘敘䞔剘䔇 box 䌂傋凹茇)㔗認䓉愙嘵䫌 CREATE OPERATOR CLASS 麯䔇 STORAGE 锬釹庻嗘㔗埇傖喍䌂嚚認湙䔇婩薪

CREATE OPERATOR CLASS polygon_ops
    DEFAULT FOR TYPE polygon USING gist AS
        ...
        STORAGE box;

䕞嬉埻橬 GiST 启 GIN 䘵嚘桹濘櫇毕婯庖枕昄扞䌂傋婉劯䔇 STORAGE 䌂傋㔗GiST compress 启 decompress 櫇毕誺䘋婘嘪䫘 STORAGE 䔇施唍媙釂崇䊖昄扞䌂傋蘸扵㔗凹庯 GIN 準臘STORAGE 䌂傋湺臖庖"髞"唚䔇䌂傋垄锔婩婯䘵嚘庖枕䔇䌂傋婉劯㔗懫套婔婻䫘庯昘昄昄䂇庖枕䔇淉嘩严䌂埇脘溼喘橬昘昄䌂傋䔇髞㔗GIN extractValue 启 extractQuery 櫇毕誺䘋蘘蘼傯噾䘵嚘䔇唚檘埡髞庖㔗