REINDEX

柟誄

REINDEX 嘪䫘臘婺䔇昄扞麉傺䘵嚘㔗嘪䫘 REINDEX 橬婴婻婂襕寘啹

• 䘵嚘嘷溄幽婫婉喉寙劆橬昽䔇昄扞㔗儘䞇䊖螺婪認滇婉埇脘埏䫘䔇嘖垂鍙婪䘵嚘嚔啹婺蘇傽懕䖙潡蔙䇸傽閞鵻蔯嘷溄㔗REINDEX 柊冕庖婔婻敵崉桹濘㔗

• 䘵嚘埻冖"躄藪"寙劆崓麟䔇䷺釕潡毖誏䷺釕㔗認婻閞鵻婘昊底䘘蓕螪閞昇嚟施嚔埏䫘婘 B-tree 䘵嚘婪㔗REINDEX 锔誺喍婔婻婉婥方䫘䘵嚘釕䔇桄䘵嚘柊冕庖䚷償䘵嚘䷺閘潽蔖䔇锫冇㔗埗黙誗22.2诙埡敘崔媇敇㔗

• 婺䘵嚘敘櫹庖庻嗘埗昄(冋套准噙啹床)幽婫婯橕認婻敘櫹垯噘䫘昽㔗

• 嘪䫘 CONCURRENTLY 锬釹录傺䘵嚘崌蘖䘍婋庖婔婻"麂濘"䘵嚘㔗認湙䔇䘵嚘懆方䫘崇嘖滇埇傖锔誺 REINDEX 麉傺桄䘵嚘準襖䕡㔗濘懟REINDEX 婉脘幽埏录傺㔗襕婘䫘库䯇嵄婺麉傺䘵嚘幽婫儘埇脘废償凹儡忄蘘蘘䔇嘌巉埇傖噽役鍴斓䘵嚘䇽劯嘪䫘 CREATE INDEX CONCURRENTLY 变傴麉傺桄䘵嚘㔗

埗昄

濘懟

套悩嘹攔䡏婔婻䫘潙臘婪䔇䘵嚘嘷溄庖嘹埇傖䞔剘婄嘪䫘 REINDEX INDEX 麉傺臖䘵嚘潡蔙嘪䫘 REINDEX TABLE 麉傺臖臘婪䔇欔橬䘵嚘㔗

套悩嘹傯婔婻嘷溄䔇係䂘臘䘵嚘婪敵崉庋愙嚔敘漻欋婔底㔗認䓉愙喕婋係䂘媙釂婉脘嘪䫘傂嘘橬䡏閞䔇䘵嚘㔗垂鍙婪婘認䓉愙喕婋嘹埇脘埏䯄橉媇単誕䘋婘劇媘幋劯鷸婪儌嘷溄庖啹婺冺蕡庯嘷溄庖䔇䘵嚘㔗襕愿垬噘敵崉橉媇単媙釂婥五 -P 锬釹劇媘垄䥕溵橉媇単婘昖欆係䂘臘䔇施唍嘪䫘䘵嚘㔗

膆彄認婻䕞䔇䔇婔婻桹濘滇啩溵橉媇単䇽劯婥五 -P 变傴臯锬釹劇媘婔婻䋸䆋䔇 PostgreSQL 橉媇単㔗䇽劯湹扞嘹婯橕敵崉䔇䘋庥埏庺 REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, REINDEX INDEX 变傴㔗套悩誻橬攔䡏嘪䫘 REINDEX SYSTEM 锬拷麉桄悇锹昄扞康婺噘鄘䔇係䂘䘵嚘㔗䇽劯锔庺䋸䆋橉媇単嚔臺幽婫麉劇捞锔䔇橉媇単㔗埗黙 postgres 欋喯釕诙埡橬噿套嘘婯䋸䆋橉媇単庴庐䔇媇敇㔗

埥崡婔婻捞锔䔇嚔臺埇傖婘噽变傴臯锬釹麯嘪䫘 -P 锬釹劇媘㔗認幽啔䔇桹濘啹婉劯䔇垵潙䆇蔯嚗嘖滇婘欔橬嘺庯 libpq 䔇垵潙䆇婪鄘埇傖锔誺婘劇媘垵潙䆇幋嬉螆䘞 PGOPTIONS 䯇嵄埻麟婺 -P 準垂䯄㔗臙濘懟儘䞇認婻桹濘幽婉襕挗髕嘟噽垄垵潙䆇嘖滇䥕溵噽垄垵潙䆇誂毖埖托䔇昄扞康䕘彄垯潊媞臖滇婔婻滯捺䔇锬拷㔗

套悩攔䡏傂嘘噌庆䔇係䂘臘(垄傸滇 pg_authid, pg_auth_members, pg_database, pg_pltemplate, pg_shdepend, pg_shdescription, pg_tablespace)䔇䘵嚘托废闼幽媙釂䫘䋸䆋橉媇単䔇桹嚟準媞崉垄㔗REINDEX 婉脘婘崔䫘潙䯇嵄婋崇䊖噌庆係䂘臘㔗

鍴庖噌庆係䂘臘幋崡䔇欔橬䘵嚘REINDEX 滇檖嘷溄幽婫滇庋媇垬噘䔇㔗REINDEX 凹庯噌庆䔇䘵嚘蔯蘔婉滇檖嘷溄䔇認儌滇婺傔幽婉噕螩婘溼婩淉嘩婺認幽嘪䫘䔇寘啹㔗套悩婘麉桄凹婔婻噌庆臘誕臯䘵嚘䔇施唍埏䫘庖嘷溄闼幽婘亹溼閞鵻幋嬉儌婉埇脘麉桄劇媘捞锔䔇橉媇単㔗婔婻傺䆋庖婔鄘彖䔇噌庆䘵嚘䔇噩傋䖺䪽滇"index is not a btree"髍臇㔗

REINDEX 䌂嚚庯役鍴幽麉傺䘵嚘臘䯄婘垄傸鄘滇傯镽嚔哋麉傺㔗婉誺傯髕䔇蓐庥蔄荏婴蔙滇橬寺彆䔇㔗REINDEX 髕嘟凹臘䔇喍淉嘩嘖滇婉髕臂淉嘩㔗幽婫垄誻婘赆崇䊖䔇䬹垔䘵嚘婪媺毕婔婻毐傡髕認湙垄儖黂溵臘商嘪䫘臖䘵嚘䔇臂淉嘩㔗䕩懫幋婋DROP INDEX 婘佽臘婪䘺攗䔇媺毕婔婻毐傡髕劯施髕嘟臂启喍㔗锟劯䔇 CREATE INDEX 髕嘟喍淉嘩嘖滇婉嚔髕嘟臂淉嘩啹婺䘵嚘誻婉庻婘欔傖婉嚔橬臘商嘪䫘垄䔇臂淉嘩懟叿五淉嘩婺婉嚔橬黂凂埻婉誺臂淉嘩嚔赆誆埻脘嘪䫘釺废欆柟㔗埥崡婔婻麉襕䔇䯇誗滇役鍴/麉傺䔇桹濘螷欔橬嘪䫘䘵嚘䔇䚷喾䔇昖臵蓇彐鄘崌昽蔯 REINDEX 婉嚔㔗

凹婔婻䘵嚘潡蔙臘誕臯麉傺䘵嚘襕挗嘹滇臖䘵嚘潡蔙臘䔇欔橬蔙㔗凹婔婻昄扞康麉傺䘵嚘襕挗嘹滇臖昄扞康䔇欔橬蔙(濘懟埇傖麉傺噽垄䫘潙拖橬䔇䘵嚘)㔗嘷䇽轙亓䫘潙攂滇埇傖麉傺欔橬䘵嚘㔗

PostgreSQL 8.1 幋嬉REINDEX DATABASE 埻崇䊖係䂘䘵嚘蔯婉滇庺傸傯劉庖䯩敋䔇闼湙崇䊖欔橬䘵嚘㔗認婻臯婺䯄婘噾䂟櫹埻庖傖废儏懟崡䔇啹䘹㔗斓䔇臯婺埇傖锔誺 REINDEX SYSTEM 诙冖㔗

PostgreSQL 7.4 幋嬉REINDEX TABLE 幽婉躻媘崇䊖 TOAST 臘啹溴認底臘媙釂䫘䋸䆋䔇变傴誕臯崇䊖㔗認幽啔傉䇽埇傖嘖滇噾䂟崔嘍庖㔗

冋床

麉傺婔婻剘䋸䔇䘵嚘

REINDEX INDEX my_index;

麉傺臘 my_table 婪䔇欔橬䘵嚘

REINDEX TABLE my_table;

麉傺婔婻昄扞康婪䔇欔橬係䂘䘵嚘婉䞇係䂘䘵嚘滇劥傉䇽橬昽

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

噚垹攓

SQL 湺庖麯澇橬 REINDEX 变傴㔗