????#0. ??????.
??????PostgreSQL?е???????????????????м?????????????????????β???????????????????м?????????????????????м???????????????IO????????????????????????????????????????м?????????????????????????????????????????????????????д???????????????棺??1?????????????2??IO????????У????????????漰??CPU??????????IO??????????????????????????????????????????Щ???????????????????
???????????????????????????????????д???棬?????????????????????????????????IO??????????????磺????????? SELECT * FROM tenk1 WHERE stringu1 = ‘xxx’; ????????????????м?????????????
????#1. ??????
????(1) ?????? pg_statistic ?? pg_stats
????????pg_statistic???汣???????????????????????????磺???????е?Page?????tuples???????? ?????????????????????е??????pg_statistic??????????????????pg_stat??????????????????????????????planner????????????????????л????????????????????????裬?????????????analyze?????? relpages?? reltuples?? ??relpages?????仯????????analyze???????????????relpage??reltuples?????????????????????
????????selectivity ????where????е??????(??????????????????棬???????????????)????????? pg_operator???????в??????????selectivity??????pg_operator?е?oprrest?????????????????????? ?????ú?????pg_statistic????????????????????????????(historgram)??
???????磺
????SELECT histogram_bounds FROM pg_stats
????WHERE tablename='tenk1' AND attname='unique1';
????histogram_bounds
????------------------------------------------------------
????{0??993??1997??3050??4040??5036??5957??7057??8029??9016??9995}
???????????
????PgSQL
????SELECT * FROM tenk1 WHERE unique1 < 1000;
????????1000??????????棬[993 1997]????????????????????????????????????????????? < 1000???????????
????????????????????
????selectivity = (1 + (1000 – bucket[2].min)/(bucket[2].max – bucket[2].min))/num_buckets
????= (1 + (1000 – 993)/(1997 – 993))/10
????= 0.100697
????????????? ???????
????[Num(No. (X-1)) + (N- X.min)/(X.max-X.min)]/ NumOfBucket
???????????????????????????? rows = rel_cardinality * selectivity ?? ???? rel_cardinality?????е???????????????????ж????????? ????????????????????????????Ч??????????????????????????ó?????????й??????pg_stats?У?????????????????????????? most_common_freqs?? most_common_vals????
???????磺
????“{1??100??10}”;”
?????????freqs
????{0.97588??0.0126532??0.0106762}”
???????磺????SELECT * FROM tenk1 WHERE stringu1 = ‘xxx’; ?????? ????????? xxx ???? most common value????????????????????most common freqs????????selectivity??????????????mcv?б?????????????selectivity?????????????mcv?б????????? ????????????μ????????????????????mcv?б??е????????????????????е???????????????????????????????
????selectivity = (1 – sum(mvf))/(num_distinct – num_mcv)
???????з???￡??????е?most common freqs???? ??????????1????????????????????????????????????￡???????????mvc????￡???most common freqs???? ??1- sum (msf)?????????????????????????????????????????????????
???????????? SELECT * FROM tenk1 WHERE unique1 < 1000; ????????????????mcv?б??е????????????????????????????????в???mcv?б??е???????????У????????????????mcv?б??????????????δ??????Щ??mcv?б????????????Щ???????????????????￡????????????????????mcv?б??е???????????????????????????????е?mcf?????
???????磺????????? SELECT * FROM tenk1 WHERE stringu1 < ‘IAAAAA’;
?????????????????????￡?????????pg_stats???в????????
????SELECT histogram_bounds FROM pg_stats WHERE tablename='tenk1' AND attname='stringu1';
????histogram_bounds
????--------------------------------------------------------------------------------
????{AAAAAA??CQAAAA??FRAAAA??IBAAAA??KRAAAA??NFAAAA??PSAAAA??SGAAAA??VAAAAA??XLAAAA??ZZAAAA}
???????????????д????????????????????????????????????????
????????????????
????SELECT * FROM tenk1 t1?? tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
???????????????? t1.unique1 < 50????????????????????????????????????????
?????????src/backend/optimizer/util/plancat.c?м??????????С?????κε?where?????????????????????????src/backend/optimizer/path/clausesel.c??????????????????????????src/backend/utils/adt/selfuncs.c?е????????ɡ?
??????????????????????????????????????????????????????Щ?????????????????????????????Щ???????????????Щ????????????????????????????????????????Щ??????????????????????????????????????????е??????????????????????????????????????????????????????????????????У???Щ????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????2??????????????????????????????·??????????????????????????“???”??????