係䂘䂍 amcostestimate 庘昄婔婻 WHERE 床埖䔇彖臘認婻 WHERE 床埖彖臘滇係䂘螴婺埇傖赆䘵嚘嘪䫘䔇婩薪㔗垄媙釂誫啂螪閞臖䘵嚘䔇嚔體嚄螇唚傖埪 WHERE 床埖䔇锬拷攓(幘儌滇臘婘䘵嚘欆柟橘閘演䘵䔇儖赆誫啂䔇昄扞臯婘佽臘婺欔剹扞䔇懫冋)㔗凹庯䞔剘䔇婺劽庹幯嚔體嚄螇単䔇欔橬噖嘩鄘埇傖锔誺脄䫘嚻寡単麯麵䔇湺庖誺䘋垯潊橬 amcostestimate 認婻庘昄䔇䕞䔇滇噕螩䘵嚘螪閞桹濘柊冕启䘵嚘䌂傋䕩噿䔇䘖臖認湙幘螩埇傖櫹誕湺庖䔇嚔體嚄螇㔗
懟婻 amcostestimate 庘昄鄘橬婋麵認湙䔇了劉
void
amcostestimate (PlannerInfo *root,
IndexOptInfo *index,
List *indexQuals,
RelOptInfo *outer_rel,
Cost *indexStartupCost,
Cost *indexTotalCost,
Selectivity *indexSelectivity,
double *indexCorrelation);
崘啕婻埗昄滇膷噖
蓇彐単䔇橬噿溼婘赆崇䊖䔇昖臵䔇媇敇㔗
婘蔄荏嘪䫘䔇䘵嚘㔗
䘵嚘溇傽床埖䔇彖臘(锊劆滇 AND 䔇)套悩滇 NIL 彖臘(䷺彖臘)儌臘䴺澇橬埇䫘䔇溇傽㔗臙濘懟認婻彖臘寙劆臘膆嚟湏蔯婉滇 ScanKey(欆柟髞庖)㔗
套悩臖䘵嚘埇脘襕䫘庯誂毖喙鄘欆柟闼幽認婻儖滇蓇彐単噿庯誂毖䔇崡冓媇敇劥彍婺 NULL 㔗嘷婉婺 NULL 施婔底 qual 庖埖儖嚔誂毖嘪䫘婥橬認婻 rel 䔇庖埖蔯婉滇䞔剘䔇亥溘庖埖㔗劯湙嚔體臇嚄庫嘷蔄荏彄䘵嚘欆柟儖嚔婺 rel 䔇懟婔臯欓臯婔渇㔗
劯麵啕婻埗昄滇嚹锐嚘䫘䔇膷庺
螆䘞婺䘵嚘劇媘崇䊖䔇嚔體
螆䘞婺䘵嚘崇䊖䔇攂嚔體
螆䘞婺䘵嚘䔇锬拷傋
螆䘞婺䘵嚘欆柟釺废启婋北䔇臘䔇釺废幋閘䔇䕩噿橬昽攓
臙濘懟嚔體嚄螇庘昄媙釂䫘 C 喍蔯婉脘䫘 SQL 潡蔙傂嘘埇䫘䔇庻嗘誺䘋臺蘔啹婺垄傸媙釂螪閞蓇彐単/嚻寡単䔇喙鄘昄扞䂷悇㔗
䘵嚘螪閞嚔體庫臖傖 src/backend/optimizer/path/costsize.c 嘪䫘䔇剘嘉誕臯螇䞖婔婻釺废伕䕻庖檷埡嚔體滇 1.0 婔婻麂釺废檷埡嚔體滇 seq_page_cost 蔯崇䊖婔婻䘵嚘臯䔇嚔體锔婩庫臖滇 random_page_cost 蔯崇䊖婔婻䘵嚘臯䔇嚔體锔婩庫臖滇 cpu_index_tuple_cost 㔗埥崡婘傂嘘䘵嚘崇䊖橘閘脄䫘䔇懫膄淉嘩严鄘庫臖嵂媹婔婻昄麟婺 cpu_operator_cost 唉昄䔇嚔體(䬹彆滇螇䞖䘵嚘溇傽 indexQuals t躻噌䔇施唍)㔗
螪閞嚔體庫臖寙拸欔橬婯欆柟䘵嚘橸躆䕩噿䔇伕䕻启 CPU 嚔體嘖滇婉寙拸演䘵潡蔙崇䊖䘵嚘湺臖庺準䔇佽臘䔇臯䔇嚔體㔗
"劇媘嚔體"滇攂欆柟嚔體婺䔇認湙婔鄘彖婘嚔哋檷埡丸婔臯幋嬉媙釂誌毬䔇嚔體㔗凹庯崓崔昄䘵嚘認婻埇傖滇镽嘖滇闼底劇媘嚔體冽崓䔇䘵嚘䌂傋埇脘婉脘檪垄螆䘞婺镽㔗
indexSelectivity 庫臖螆䘞潊婘䘵嚘欆柟橘閘佽臘婺䔇臯赆锬庺庺準䔇鄘彖䔇䍆彖懫㔗婘䘵嚘懫膄溆昼䔇愙喕婋認婻唚锔婩懫垂鍙锔誺䂍庺䔇昖臵溇傽幋臯欔剹䔇䍆彖懫襕醻㔗
indexCorrelation 庫臖螆䘞潊䘵嚘釺废启臘釺废幋閘䔇䕩噿攓(评啘婘 -1.0 彄 1.0 幋閘)㔗認婻昄唚䫘庯脄昘傯佽臘婺檷埡臯䔇嚔體嚄螇㔗
婘誂毖愙喕婋誫啂䔇昄唚庫嘷婘懟婔渇䘵嚘欆柟幋閘广庺㔗
嚔體嚄螇
婔婻噩傋䔇嚔體嚄螇単嚔償婋麵認湙誕臯崇䊖
嘺庯䂍庺䔇昖臵溇傽嚄螇幽誫啂佽臘婺儖赆螪閞䔇臯䔇䍆彖懫㔗套悩䚺幟䘵嚘䌂傋䕩噿冖䘖臖闼幽嘪䫘湺庖䔇嚻寡単庘昄 clauselist_selectivity()
*indexSelectivity = clauselist_selectivity(root, indexQuals,
index->rel->relid, JOIN_INNER);嚄螇婘欆柟誺䘋婺儖赆螪閞䔇䘵嚘臯昄㔗凹庯螩崔䘵嚘䌂傋認婻京庯 indexSelectivity 幻傖䘵嚘婺䔇臯昄嘖滇埇脘敘崔㔗臙濘懟釕麵婺䔇䘵嚘儺凩启臯昄埇傖傯 IndexOptInfo 䂷悇婺诙冖㔗
嚄螇婘欆柟婺儖演䘵䔇䘵嚘釕麵昄麟㔗認婻埇脘儌滇 indexSelectivity 幻傖䘵嚘䔇攂釕麵昄㔗
螇䞖䘵嚘螪閞嚔體㔗婔婻锔䫘䔇嚄螇単埇傖認幽幾
/*
* Our generic assumption is that the index pages will be read
* sequentially, so they cost seq_page_cost each, not random_page_cost.
* Also, we charge for evaluation of the indexquals at each index row.
* All the costs are assumed to be paid incrementally during the scan.
*/
cost_qual_eval(&index_qual_cost, indexQuals);
*indexStartupCost = index_qual_cost.startup;
*indexTotalCost = seq_page_cost * numIndexPages +
(cpu_index_tuple_cost + index_qual_cost.per_tuple) * numIndexTuples;
婉誺婪麵澇橬蔄荏婘誂毖愙喕婋䔇崔渇䘵嚘欆柟婺彖橘(amortization)嚔體㔗
嚄螇䘵嚘䔇䕩噿攓㔗凹庯䞔剘䔇婘剘婻庖枕婪䔇橬废䘵嚘認婻唚埇傖傯 pg_statistic 婺演䘵㔗套悩䕩噿攓滇橻䘖闼幽媺垽䔇嚄螇滇镽(澇橬䕩噿攓)㔗
嚔體嚄螇単庘昄䔇冋床埇傖婘 src/backend/utils/adt/selfuncs.c 麯麵欆彄㔗