認底陉䘞埗昄柊冕庖嘌巉昖臵嚻寡単锬拷昖臵蓇彐䔇寘哋桹濘㔗套悩嚻寡単婺䬹垔䔇昖臵锬拷䔇䚺䩕蓇彐幽婉滇橔嚻闼幽潏傸儌埇傖锔誺嘪䫘認底陉䘞埗昄嚺彽嚻寡単锬拷婔婻敘喘䔇蓇彐準婘施蓼喿認婻閞鵻㔗婉誺愩幙婄噿閺認底螆䘞庹幯傯婉滇婻喘婂懟㔗敘喘䔇櫹塇嚻寡単锬拷蓇彐䔇桹濘寙拸脄誗蓇彐単嚔體婩麟㔕敘鵏乕誊臯 ANALYZE㔕嵂崓陉䘞埗昄 default_statistics_target 䔇唚㔕嘪䫘 ALTER TABLE SET STATISTICS 婺昊婻庖枕嵂媹櫽镖䔇䂘螇媇敇㔗
欷嚔潡蔙噿閺蓇彐単凹嘉商欆柟蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹 Hash 蕔镖蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹 Hash 誂毖蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹䘵嚘欆柟蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹肉劽誂毖蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹啯喖冻䯇誂毖蓇彐䌂傋䔇嘪䫘㔗潏傸婉埇脘垯噘潽鍴啯喖冻䯇誂毖嘖滇檪認婻埻麟噿閺儌嚔螷蓇彐単婘庻婘噽垄桹濘䔇施唍嚻噽锬拷噽垄桹濘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹釺废欆柟蓇彐䌂傋䔇嘪䫘㔗潏傸婉埇脘垯噘潽鍴釺废欆柟嘖滇檪認婻埻麟噿閺嚔螷蓇彐単婘庻婘噽垄桹濘䔇施唍嚻噽锬拷噽垄桹濘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単嘪䫘滯䇞䔇毐废準黴㔗潏傸婉埇脘垯噘潽鍴滯䇞䔇毐废嘖滇檪認婻埻麟噿閺埇傖螷蓇彐単婘庻婘噽垄桹濘䔇施唍嚻噽锬拷噽垄桹濘㔗䚺䩕滇 on
欷嚔潡蔙噿閺蓇彐単凹 TID 欆柟蓇彐䌂傋䔇嘪䫘㔗䚺䩕滇 on
橸誗婺柟誄䔇嚔體埇傖毬䙓傂懟湺庖庥麟㔗潏傸埻噿媄噽䕩凹唚啹溴傖䕩劯䔇係昄䚷櫆垄傸儖婉嚔凹蓇彐単库䫘傂嘘嘌巉㔗嚹䂘婪垄傸傖檷埡釺废釕䔇嚔體嘩婺嘺庖剘嘉㔗幘儌滇臘儖 seq_page_cost 螆婺 1.0 劯施噽垄嚔體埗昄凹䙓垄準螆䘞㔗嘷䇽嘹幘埇傖嘪䫘噽垄嘺庖懫套傖懆䓐螇䔇垂鍙欓臯施閘㔗
㔊濘懟㔏俘俘䔇滇䯄婘誻澇橬垔幬冖冽劽䊖䔇桹濘準彴桺婋麵庺䯄䔇"嚔體"埻麟斟䔇䊖愿昄唚㔗垄傸橔喘毬䙓昊婻䬹垔垬輙䔇广庺昖臵嚔體準臇麟㔗認懟叿五備備湹扞冽儏麟䔇臘黯䂷悩準媞櫹垄傸滇冽剌鍷䔇㔗
螆䘞蓇彐単螇䞖婔渇釺废伕䕻釕麵檷埡䔇嚔體㔗邻螴唚滇 1.0 㔗
螆䘞蓇彐単螇䞖婔渇麂釺废伕䕻釕麵檷埡䔇嚔體㔗邻螴唚滇 4.0 㔗(䕩凹庯 seq_page_cost)废儏認婻唚儖凚躘敘唆劏庯嘪䫘䘵嚘欆柟蔯嵂媹認婻唚儖凚躘敘唆劏庯嘪䫘釺废欆柟㔗埇傖锔誺劯施嵂媹潡废儏認婴婻唚準脄昘伕䕻 I/O 䕩凹庯 CPU 䔇嚔體(婘婋麵䔇埗昄婺柟誄)㔗
㔊柊䴺㔏荘䇽噕螩嘹儖 random_page_cost 螆䘞䔇懫 seq_page_cost 償嘖滇䬷䊖婪䔇垂鍙愙喕幽婉埖溴嘌巉㔗䇽蔯嘷欔橬昄扞康鄘嘉庯喙庻婺施婴蔙螆䘞婺䕩京滇麂婩劽䊖䔇啹婺婘溴愙喕婋幌废檷埡幽婉懫釺废檷埡嚔體敘崓㔗劯湙婘䚷喾䯺冽醻䔇昄扞康婪嘹庫嘷䕩凹庯 CPU 嚔體劯施鍉嘯認婴婻唚啹婺诙埡喙庻婺䔇釕懫锔婩愙喕婋䔇嚔體償螩崔㔗
螆䘞蓇彐単螇䞖婘婔渇昖臵婺崇䊖婔婻昄扞臯䔇嚔體㔗䚺䩕滇 0.01 㔗
螆䘞蓇彐単螇䞖婘婔渇䘵嚘欆柟婺崇䊖懟溇䘵嚘臯䔇嚔體㔗䚺䩕滇 0.005 㔗
螆䘞蓇彐単螇䞖婘婔渇昖臵婺欓臯婔婻淉嘩严潡庘昄䔇嚔體㔗䚺䩕滇 0.0025 㔗
婺蓇彐単螆䘞婘婔渇䘵嚘欆柟婺埇䫘䔇伕䕻䚷喾寺䔇橬昽崓償㔗認婻埗昄嚔婘螇䞖婔婻䘵嚘䔇鵇螇嚔體唚䔇施唍媹傖蔄荏㔗敘醻䔇昄唚嚔凚躘敘埇脘嘪䫘䘵嚘欆柟敘嘯䔇昄唚嚔凚躘敘橬埇脘锬拷釺废欆柟㔗婘螆䘞認婻埗昄䔇施唍嘹誻庫臖蔄荏 PostgreSQL 䔇昄扞桺傽嚔嘪䫘䔇噌庆䚷喾寺启喙湩䔇伕䕻䚷喾寺㔗埥崡誻襕蔄荏鵇螇嚔嘪䫘婉劯䘵嚘䔇幽埏昖臵昄䕞啹婺垄傸媙釂噌庆埇䫘䔇喙庻䷺閘㔗認婻埗昄凹 PostgreSQL 彖陉䔇噌庆喙庻崓償澇橬嘌巉垄幘婉嚔嘪䫘喙湩伕䕻䚷喾垄埻䫘庯嚄䞖㔗昄唚滇䫘伕䕻釕準螇䞖䔇锔婩懟婻釕麵滇 8192 庖誗㔗䚺䩕滇 16384(128MB)㔗
噕螩潡䥕溵嘺啹昖臵嚻寡認滇婔䓉臘商婉锔誺䷙婆抩䘵準垂䯄昖臵蓇彐䔇䞖濘㔗䚺䩕滇噕螩㔗geqo_threshold 埻麟柊冕庖婔䓉婺䬹垔䌂彆䔇昖臵噿閺 GEQO 䔇敘來䂖桹濘㔗
埻橬嘷潬埪䔇 FROM 噿係昄麟躿儏橬認幽崔婻䔇施唍欉嘪䫘嘺啹昖臵嚻寡㔗凹庯昄麟償庯溴唚䔇昖臵幘螩嘪䫘彴垔攓䔇䷙婆抩䘵敘橬昽㔗嘖滇凹庯橬螩崔臘䔇昖臵蓇彐単啔彴桺襕誌冽崔施閘㔗䚺䩕滇 12 㔗臙濘懟婔婻 FULL OUTER JOIN 悇锹埻䞖婔婻 FROM 釹㔗
毓彽 GEQO 麯蓇彐施閘启昖臵蓇彐䔇橬昽攓幋閘䔇广臇㔗認婻埻麟媙釂滇婔婻评啘傯 1 彄 10 䔇昘昄㔗䚺䩕唚滇 5 㔗崓䔇昄唚嵂媹誌婘誕臯昖臵蓇彐婪麵䔇施閘嘖滇幘冽埇脘嚔柊醻锬婺敘橬昽䔇昖臵蓇彐䔇庹䯺㔗
geqo_effort 垂鍙婪幽澇橬䕘毖幾傔幽庋愙埻滇䫘庯螇䞖噽垄闼底嘌巉 GEQO 臯婺埻麟䔇䚺䩕唚(婘婋麵柟誄)㔗套悩嘹懪懟嘹埇傖欋噖螆䘞噽垄埗昄㔗
毓彽 GEQO 嘪䫘䔇挹崓償㔗挹崓償滇嘺啹噘嘷婺䔇婻嘷昄麟㔗垄媙釂躿儏滇 2 幽婫橬䫘䔇昄唚锔婩婘 100 启 1000 幋閘㔗套悩檪垄螆䘞婺镽(䚺䩕)闼幽儌嚔嘺庯 geqo_effort 启昖臵婺臘䔇昄麟锬埡婔婻劽锗䔇唚㔗
毓彽 GEQO 嘪䫘䔇床傼昄䕞㔗床傼䔇懟攺滇䞖濘䔇誺傼渇昄㔗垄媙釂躿儏滇 1 橬䫘䔇唚评啘启挹崓償䕩劯㔗套悩螆䘞婺镽(䚺䩕)闼幽儖嘺庯 geqo_pool_size 锬埡劽锗䔇唚㔗
毓彽GEQO嘪䫘䔇锬拷攓啟喘㔗锬拷攓啟喘滇婘婔婻䓉䆴婺䔇锬拷攓寋媕㔗昄唚埇傖滇 1.5 彄 2.0 幋閘䚺䩕滇 2.0 㔗
婺澇橬䫘 ALTER TABLE SET STATISTICS 螆䘞庖枕䕩噿䕞湺䔇臘婺噽垄庖枕螆䘞䚺䩕䂘螇䕞湺㔗敘崓䔇昄唚嵂媹庖 ANALYZE 欔驔襕䔇施閘嘖滇埇脘嚔櫹塇蓇彐単䔇嚄螇蘘麟㔗䚺䩕唚滇 10 㔗橬噿 PostgreSQL 䔇昖臵蓇彐単嘪䫘䔇䂘螇䔇敘崔媇敇臙埗蔄誗13.2㔗
欷嚔潡蔙噿閺昖臵蓇彐単嘪䫘臘亥溘鍊彽臘螪閞䔇䬹攓㔗䚺䩕滇 off
套悩認婻埗昄滇 on 闼幽蓇彐単䫘昖臵溇傽启 CHECK 亥溘誕臯懫膄幽婫婘昖臵溇傽启亥溘喾仕䔇愙喕婋媘䘖凹臘䔇欆柟㔗懫套
CREATE TABLE parent(key integer, ...); CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent); CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent); ... SELECT * FROM parent WHERE key = 2400;
婘欷嚔亥溘毐鍴䔇施唍認婻 SELECT 儖垯噘婉嚔欆柟 child1000 㔗認湙埇傖婘嘪䫘䂓欪彽嘩彖寺臘䔇施唍柊醻攓脘㔗
䕞嬉constraint_exclusion 䚺䩕赆噿閺啹婺套悩昖臵蓇彐赆䚷喾庖闼幽儌嚔橬婉溼䇞䂷悩䔇鼯鍷套悩婔婻臘亥溘櫹埻潡蔙役鍴庖闼幽嬉麵䫘潊䔇昖臵螇彐䯄婘儌埇脘滇髍䔇庖蔯埽澇橬喙䘞䔇橺彽嚺彽麉桄蓇彐㔗埥崡婔婻檪垄噿閺䔇寘啹滇亥溘演昖䕩凹準臘嚔體誻滇懫膄崓䔇婘冽崔䯇嵄婋垄幽婉脘誗亥嚔體㔗埻滇婘嘹垂鍙婪嘪䫘庖傯螆螇婪儌彷䫘認婻䬹攓䔇臘彖寺䔇婺劽婋潏傸欉毘艊欷嚔垄㔗
埗蔄誗5.9诙埡橬噿嘪䫘亥溘毐鍴启彖寺䔇敘崔媇敇㔗
套悩䫘潊䔇 FROM 彖臘婉轙誺認婻鍊彽䔇釹昄蓇彐単儖檪床昖臵肉劽彄婪北昖臵㔗償䔇昄唚鍉嘯蓇彐䔇施閘嘖滇埇脘嚔䫘潊噞底䔇昖臵螇彐㔗䚺䩕滇 8 㔗锔婩檪垄鍊彽婘償庯 geqo_threshold 䔇昄唚滇懫膄滯捺䔇㔗敘崔媇敇臙昖䩋誗13.3㔗
套悩冖庺䔇彖臘婉轙誺認婻昄䕞䔇釹闼幽蓇彐単儖檪鍴 FULL JOIN 幋崡䔇 JOIN 悇锹檹广彄 FROM 彖臘釹婺㔗償䔇昄唚鍉嘯蓇彐䔇施閘嘖滇埇脘嚔䫘潊噞底䔇昖臵螇彐㔗
䚺䩕施認婻唚启 from_collapse_limit 䕩劯認湙锗劽崓崔昄婺劽㔗檪垄螆䘞婺 1 彍镪噉傂嘘 JOIN 䔇肉劽認湙儌儖滯䇞嘪䫘臺埖婺䔇誂毖釺废㔗昖臵嚻寡単幽婉滇攂脘锬埡橔嚻䔇誂毖釺废醻亓䫘潙埇傖锬拷攗施檪認婻埻麟螆䘞婺 1 䇽劯滯䇞婄弄滯傡傸驔襕䔇誂毖釺废㔗敘崔媇敇埗蓕誗13.3㔗