11.2. 䘵嚘䌂傋

PostgreSQL 柊冕庖喘庹䓉䘵嚘䌂傋B-tree, Hash, GiST, GIN 㔗懟䓉䘵嚘䌂傋鄘懫膄锗劽昊底䬹垔䔇昖臵䌂傋啹婺垄傸䫘庖婉劯䔇䞖濘㔗䚺䩕施CREATE INDEX 变傴儖录傺婔婻 B-tree 䘵嚘垄锗劽崓崔昄愙喕㔗

B-tree 锗劽崇䊖闼底脘崘毬釺废庻嗘䔇昄扞幋婪䔇京庯启评啘昖臵㔗䬹彆滇婘婔婻傺䆋庖䘵嚘䔇庖枕潬埪彄嘪䫘

<

<=

=

>=

>

淉嘩严幋婔誕臯懫膄䔇施唍PostgreSQL 䔇昖臵蓇彐単鄘嚔蔄荏嘪䫘 B-tree 䘵嚘㔗京昽庯認底淉嘩严䂇劽䔇悇锹懫套 BETWEEN 启 IN 幘埇傖䫘抩䘵 B-tree 䘵嚘垂䯄㔗嘖滇襕濘懟IS NULL 婉劯庯 = 幽婫滇婉脘傺䆋䘵嚘䔇㔗

備嘷昇嚟滇婔婻婩麟幽婫體垔婘庖严婾嚔崘䔇施唍嚻寡単欉嚔檪 B-tree 䘵嚘䫘庯昇嚟对陉淉嘩严 LIKE 启 ~ 懫套col LIKE 'foo%' 潡 col ~ '^foo' 嘖滇 col LIKE '%bar' 儌婉臯㔗劯施套悩嘹䔇橉媇単橻嘪䫘 C 寺嘘螆䘞闼幽嘹驔襕䫘婔婻䬹枪䔇淉嘩严䌂录傺䘵嚘準櫇毕昇嚟对陉昖臵婪䔇䘵嚘㔗埗黙誗11.8㔗誻橬埇脘儖 B-tree 䘵嚘䫘庯 ILIKE 启 ~* 嘖滇備嘷昇嚟傖麂庖應庖严(婉埖崓償喍嘌巉䔇庖严)嚔崘欉埇傖㔗

Hash 䘵嚘埻脘崇䊖䞔剘䔇京庯懫膄㔗嘷婔婻䘵嚘庖䔇彖潬埪彄嘪䫘 = 淉嘩严誕臯懫膄䔇施唍昖臵蓇彐単嚔蔄荏嘪䫘 Hash 䘵嚘㔗婋麵䔇变傴䫘庯录傺 Hash 䘵嚘

CREATE INDEX name ON table USING hash (column);

GiST 䘵嚘婉滇剘䋸婔䓉䘵嚘䌂傋蔯滇婔䓉悽悇埇傖婘認䓉悽悇婪垂䯄冽崔婉劯䔇䘵嚘亡䘖㔗啹溴埇傖嘪䫘 GiST 䘵嚘䔇䬹垔淉嘩严䌂傋醻庥冺蕡庯䘵嚘亡䘖(淉嘩严䌂)㔗嘩婺䴺冋PostgreSQL 䔇湺庖埏婄婺寙劆䫘庯庯䂘庹嘘昄扞䌂傋䔇 GiST 淉嘩严䌂垄櫇毕

<<

&<

&>

>>

<<|

&<|

|&>

|>>

@>

<@

~=

&&

淉嘩严䔇䘵嚘昖臵㔗認底淉嘩严䔇劆幬埗蓕誗9.10㔗螩崔噽垄 GiST 淉嘩严䌂嘉庯 contrib 婺潡蔙滇剘䋸䔇釹䕞敘崔媇敇埗蓕䆹50㔗

GIN 䘵嚘滇埉蘸䘵嚘垄埇傖崇䊖寙劆崔婻髞䔇唚(懫套昄䂇)㔗婯 GiST 䌂嚚GIN 櫇毕䫘潙垔幬䔇䘵嚘亡䘖埇傖嘪䫘 GIN 䘵嚘䔇䬹垔淉嘩严䌂傋湹扞䘵嚘亡䘖䔇婉劯蔯婉劯㔗嘩婺䴺冋PostgreSQL 䔇湺庖埏婄婺寙劆䫘庯婔䂘昄䂇䔇 GIN 淉嘩严䌂垄櫇毕

<@

@>

=

&&

淉嘩严䔇䘵嚘昖臵㔗認底淉嘩严䔇劆幬埗蓕誗9.14㔗螩崔噽垄 GIN 淉嘩严䌂嘉庯 contrib, tsearch2, intarray 昇庖㔗敘崔媇敇埗蓕䆹51㔗