CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
CREATE INDEX 婘毺垔䔇臘婪录傺婔婻劉婺 index_name 䔇䘵嚘㔗䘵嚘婂襕䫘準柊醻昄扞康攓脘㔗嘖滇套悩婉敄嘷䔇嘪䫘儖凚躘攓脘䔇婋鍉㔗
䘵嚘䔇髞庖庖枕滇傖庖枕劉䔇桹嚟弄滯䔇潡蔙滇埇锬䔇喍婘婔婻婖拸嚓麯麵䔇臘膆嚟㔗套悩䘵嚘桹嚟櫇毕崔庖枕䘵嚘闼幽幘埇傖弄滯崔婻庖枕㔗
䘵嚘庖枕埇傖滇婔婻嘪䫘婔婻潡崔婻庖枕唚誕臯螇䞖䔇臘膆嚟㔗認婻䬹攓埇䫘庯诙埡凹嘺橸昄扞䔇昊䓉埻嘵䔇媆锘螪閞㔗懫套婔婻婘 upper(col) 婪䔇庘昄䘵嚘儖噕螩 WHERE upper(col) = 'JIM' 床埖嘪䫘䘵嚘㔗
PostgreSQL 婺傯䘵嚘柊冕 B-tree, hash, GiST, GIN 䘵嚘桹濘㔗䫘潙幘埇傖垔幬垄傸躻噌䔇䘵嚘桹濘嘖認婻噖嘩䕩嘷崉溗㔗
套悩庺䯄庖 WHERE 床埖彍录傺婔婻鄘彖䘵嚘㔗鄘彖䘵嚘滇婔婻埻寙劆臘䔇婔鄘彖螄嘘䔇䘵嚘锔婩滇臖臘婺懫噽垄鄘彖昄扞敘橬䫘䔇鄘彖㔗懫套套悩嘹橬婔婻臘麯麵寙劆噾螄蘥启橻螄蘥䔇垔剘橻螄蘥䔇垔剘埻剹臘䔇婔償鄘彖蔯婫認鄘彖滇橔婩䫘䔇鄘彖闼幽嘹儌埇傖锔誺埻婘橻螄蘥鄘彖录傺婔婻䘵嚘準櫹塇攓脘㔗埥崡婔婻埇脘䔇䫘锫滇嘪䫘婥橬 UNIQUE 䔇 WHERE 嚺彽婔婻臘䔇昊婻床镖䔇嫇婔攓㔗埗黙誗11.7诙埡敘崔媇敇㔗
WHERE 床埖麯䔇臘膆嚟埻脘嚘䫘婋北臘䔇庖枕垄埇傖嘪䫘欔橬庖枕蔯婉備備滇赆䘵嚘䔇庖枕㔗䕞嬉床昖臵启蕔镖臘膆嚟幘婉脘庺䯄婘 WHERE 床埖麯㔗
䘵嚘垔幬麯䔇欔橬庘昄启淉嘩严鄘媙釂滇"immutable"(婉埻䔇)幘儌滇臘垄傸䔇䂷悩媙釂埻脘冺蕡庯垄傸䔇膷噖埗昄蔯婉脘冺蕡傂嘘崡鄘䔇嘌巉(懫套埥崡婔婻臘䔇喙垹潡蔙嘷嬉施閘)㔗認婻鍊彽埇傖䇞媺臖䘵嚘䔇臯婺滇垔幬謇喘䔇㔗襕婘婔婻䘵嚘婪潡 WHERE 婺嘪䫘䫘潙垔幬庘昄臙檪垄湺螄婺 immutable 庘昄㔗
傴係䂘婘录傺䘵嚘施(套悩昄扞噾䂟庻婘)启懟渇時媹昄扞施演敋臘婺滇劥橬麉崉唚㔗套悩某噖潡敘桄䔇唚嚔凚躘麉崉䔇螄嘘施儖䫘潊婔婻髍臇㔗
嘪䫘臖锬釹劯PostgreSQL 儖婘录傺䘵嚘䔇誺䘋婺婉婘臘婪毕橬傂嘘黾溵某噖㔕敘櫹㔕役鍴䔇喍噖髕劥彍儖毕橬喍噖髕䕘彄录傺垯潊欉麪櫆㔗嘪䫘認婻锬釹施橬庹婻濘懟䗹埗蓕幽臯录傺䘵嚘㔗
襕录傺䔇䘵嚘劉婉脘寙劆昇嚟劉㔗䘵嚘攂滇婘劯婔婻昇嚟婺嘩婺佽臘录傺䔇㔗
襕䘵嚘䔇臘劉(埇脘橬昇嚟媞閄)
襕嘪䫘䔇䘵嚘桹濘䔇劉庖㔗埇锬䔇劉庖滇 btree(䚺䩕), hash, gist, gin
臘䔇彖/庖枕劉
婔婻嘺庯臖臘䔇婔婻潡崔婻庖枕䔇臘膆嚟㔗認婻臘膆嚟锔婩媙釂婥五婖拸嚓寙啘喍庺套臺濘婺滆䴺闼湙㔗婉誺套悩臘膆嚟橬庘昄脄䫘䔇嘵嚟闼幽婖拸嚓埇傖䩕䘖㔗
婔婻噿蕫䔇淉嘩严䌂㔗埗黙婋桺诙埡䂖誗㔗
䘵嚘桹濘䬹垔䔇庻嗘埗昄䔇劉庖㔗埗黙婋桺诙埡䂖誗㔗
录傺䘵嚘䔇臘䷺閘㔗套悩澇橬弄滯彍嘪䫘 default_tablespace 臘䷺閘套悩 default_tablespace 滇䷺庖严婾彍嘪䫘昄扞康䔇䚺䩕臘䷺閘㔗
婺婔婻鄘彖䘵嚘垔幬亥溘臘膆嚟
WITH 床埖埇傖婺䘵嚘毺垔庻嗘埗昄㔗懟婻䘵嚘桹濘鄘埇傖橬垄躻噌䔇婔喖庻嗘埗昄㔗喙䘞䔇䘵嚘桹濘鄘鄘毖埖婋麵認婻剘䋸䔇埗昄
婔婻䘵嚘䔇准噙啹床(fillfactor)滇婔婻䍆彖懫垄臘䴺录傺䘵嚘施懟婻䘵嚘釕䔇昄扞准噙䯺㔗凹庯 B-trees 準臘懟叿五婘录傺䘵嚘施埽床釕儖毬䙓溴䍆彖懫准噙昄扞婘埿冓(橔崓䔇髞唚)欷匘䘵嚘施劯湙幘毬䙓溴䍆彖懫准噙昄扞㔗套悩劯準昊婻釕赆垯噘准悇闼幽臖釕儖赆彖嬾傯蔯凚躘䘵嚘攓脘锔寡㔗B-trees 邻螴䔇准噙啹床滇 90 嘖滇橬昽䔇埡唚评啘滇 10 彄 100 㔗凹庯麍攕䔇婉嚔埏䫘櫹埻䔇臘橔嘿唚 100 埇傖螷䘵嚘䔇䬷䊖嘷䓇橔償嘖滇凹庯婉桺嵂阪䔇臘膄償䔇准噙啹床敘劽锗啹婺認儖儘埇脘废儏凹釕䔇彖嬾㔗噽垄䘵嚘桹濘凹准噙啹床䔇䊖蓼婯溴䌂嚚嘖滇噽邻螴唚劇婉䕩劯㔗(臏蔙濘套悩橬溇傽变橘攓䔇麉傺䘵嚘闼幽傺螞嘪䫘膄崓䔇准噙啹床傖废儏䘵嚘䔇䬷䊖嘷䓇)
录傺䘵嚘䔇誺䘋嚔凹昄扞康䔇婩蓇淉嘩攓脘橬婉彷嘌巉㔗锔婩婘录傺䘵嚘䔇施唍 PostgreSQL 嚔髕垔臘傖黾溵喍噖䇽劯凹臘啔婔渇垯昘欆柟傖垯潊䘵嚘䔇录傺㔗婘溴誺䘋婺噽傡庋媇傉䇽埇傖臂埡臘嘖滇某噖㔕敘桄㔕役鍴儖赆婔䕘黂凂彄䘵嚘录傺垯懘㔗認湙啔凹庯崇庯昂虄䪽攕䔇昄扞康埇脘嚔库䫘婖麉䔇攓脘嘌巉㔗啹婺昊底冽崓䔇臘埇脘驔襕昄婻償施䔇施閘準傺䆋䘵嚘蔯婫剿嘪凹庯䫘库婺溼婘嘪䫘䔇膄償䔇臘認䓉黂凂锔婩幘滇婉埇毖埖䔇㔗
PostgreSQL 噕螩婘 CREATE INDEX 录傺䘵嚘施嘪䫘 CONCURRENTLY 锬釹毺垔婉髕垔臘㔗認湙 PostgreSQL 儌媙釂凹臘欆柟婴渇幽婫媙釂京写欔橬溼婘欓臯䔇庋媇垯潊㔗認䓉桹濘嵂媹庖攂嘷噖嘩麟幽婫埇脘驔襕麂婩阪䔇施閘欉脘垯潊䘵嚘䔇录傺㔗䇽蔯䫌庯認䓉桹濘噕螩婘录傺䘵嚘䔇劯施誕臯婩蓇淉嘩啹溴認䓉桹濘锗劽庯婘誊臯誺䘋婺時媹桄䘵嚘㔗嘷䇽录傺䘵嚘欔驔襕䔇鵺崡 CPU 启 I/O 嚔體傉䇽嚔凹噽垄淉嘩橬婉彷嘌巉㔗
套悩婘凹臘誕臯丸庯渇欆柟䔇施唍庺䯄庖閞鵻懫套婘嫇婔䘵嚘庖枕婪庺䯄庖麉崉唚CREATE INDEX 变傴儖嚔崌蘖幽镖䘍婋婔婻"麂濘"䔇䘵嚘㔗認婻"麂濘"䘵嚘儖赆媘䘖啹婺垄埇脘滇婉垯昘䔇嘷䇽垄幘婉嚔凚躘敘桄㔕某噖㔕役鍴施䔇鵺崡嚔體㔗婘認䓉愙喕婋毘艊役鍴臖䘵嚘䇽劯儺臘麉桄欓臯 CREATE INDEX CONCURRENTLY 变傴埥婔䓉埇脘䔇桹濘滇嘪䫘 REINDEX 麉傺䘵嚘㔗䫌庯 REINDEX 婉櫇毕幽臯录傺䘵嚘啹溴埇脘婉滇婔婻喘桹濘㔗
凹庯幽臯录傺嫇婔䘵嚘誻橬婔婻躥只婘嚔哋凹臘誕臯丸庯渇欆柟䔇施唍噾䂟婘噽傡庋媇婪嚺彽誕臯嫇婔亥溘庖㔗認懟叿五婘䘵嚘录傺垯懘幋嬉套悩橬噽垄誺埉嫇婔亥溘䔇臯婺闼幽儖嚔檖髍䫔躿婘䘵嚘橔䂽录傺崌蘖䔇愙喕婋幘滇套溴㔗劯湙套悩婘丸庯渇欆柟䔇誺䘋婺埏䫘髍臇䫘潊䔇"麂濘"䘵嚘傉儖婘锟劯嚺彽欓臯嫇婔亥溘䔇演昖㔗
幽臯录傺臘膆嚟䘵嚘启鄘彖䘵嚘幘滇埇傖䔇㔗婘臘膆嚟挗唚誺䘋婺埏䫘䔇髍臇劯湙幘嚔婘嫇婔亥溘䘵嚘婪凚躘䌂嚚嬉麵柟誄誺䔇臯婺㔗
婘录傺捞锔䘵嚘䔇劯施誻噕螩婘劯婔嚹臘婪录傺噽傡捞锔䘵嚘嘖滇婘婔嚹臘婪埻脘誕臯婔婻幽臯䘵嚘䔇录傺㔗婘溴婴䓉愙喕婋鄘婉噕螩劯施凹臘欔婘䔇昇嚟誕臯媞櫹㔗埥婔婻噞嚗滇 CREATE INDEX 埇傖櫆婘婔婻庋媇庖婺欓臯嘖 CREATE INDEX CONCURRENTLY 婉埇傖㔗
埗黙䆹11诙埡橬噿嘘施嘪䫘䘵嚘㔕嘘施婉嘪䫘䘵嚘傖埪䘵嚘婘巻䓉愙喕婋滇橬䫘䔇媇敇㔗
䕞嬉埻橬 B-tree 启 GiST 䘵嚘桹濘櫇毕崔庖枕䘵嚘㔗䚺䩕施橔崔埇傖弄滯 32 婻髞庖(埇傖婘䚡臏 PostgreSQL 施媞櫹)㔗䕞嬉埻橬 B-tree 櫇毕嫇婔䘵嚘㔗
埇傖婺䘵嚘䔇懟婻彖/庖枕弄滯婔婻淉嘩严䌂湺臖儖襕赆臖䘵嚘䫘庯臖彖/庖枕䔇淉嘩严㔗冋套婔婻啕庖誗昘昄䔇 B-tree 䘵嚘儖嘪䫘 int4_ops 臘認婻淉嘩严䌂寙拸啕庖誗昘昄䔇懫膄庘昄㔗垂鍙婪臖嘘䔇昄扞䌂傋䔇䚺䩕淉嘩严䌂婔轸儌轿崘庖㔗昊底昄扞䌂傋橬淉嘩严䌂䔇寘啹滇垄傸埇脘橬崔婻橬懟幬䔇釺废㔗冋套崉昄䌂傋埇脘傖䂺凹唚潡蔙垂鄘毐废㔗埇傖锔誺婺臖昄扞䌂傋垔幬婴婻淉嘩严䌂䇽劯婘傺䆋䘵嚘䔇施唍锬拷劽锗䔇臘準垂䯄㔗橬噿淉嘩严䌂敘崔䔇媇敇婘誗11.8启誗33.14麯㔗
嘪䫘 DROP INDEX 役鍴婔婻䘵嚘㔗
䚺䩕施䘵嚘婉嚔䫘庯 IS NULL 床埖㔗認䓉婺劽婋嘪䫘䘵嚘䔇橔喘桹濘滇䫘 IS NULL 脷臉录傺婔婻鄘彖䘵嚘㔗
斷噽䔇 PostgreSQL 䬽橸誻橬婔婻 R-tree 䘵嚘桹濘㔗啹婺垄幽婉懫 GiST 桹濘嚻䓔啹溴䯄婘噾䂟赆役鍴庖㔗套悩毺垔庖 USING rtree 䔇臺CREATE INDEX 儖檪垄嘷嘩 USING gist 䩋写幽儖蔕䔇䘵嚘蘸寡婺 GiST 䘵嚘㔗
婘臘 films 婪䔇 title 庖枕婪录傺婔婻 B-tree 䘵嚘
CREATE UNIQUE INDEX title_idx ON films (title);
婘臘膆嚟 lower(title) 婪录傺婔婻䘵嚘傖噕螩醻昽䔇崓償喍方噿抩䘵
CREATE INDEX lower_title_idx ON films ((lower(title)));
嘪䫘麂邻螴䔇准噙啹床录傺䘵嚘
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
婘臘 films 䔇 code 庖枕婪录傺婔婻䘵嚘幽婫螷䘵嚘嘉庯臘䷺閘 indexspace 喙
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
婘婉髕垔臘䔇愙喕婋录傺䘵嚘
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);