13.2. 蓇彐単嘪䫘䔇䂘螇媇敇

儌償潏傸婘婪婔誗麯匘䴺䔇闼湙昖臵蓇彐単驔襕嚄螇婔婻昖臵演䘵䔇臯昄認湙欉脘锬拷溼䇞䔇昖臵蓇彐㔗橸誗儌係䂘䫘庯認底嚄螇䔇䂘螇誕臯婔底柟誄㔗

䂘螇䔇婔婻鄘彖儌滇懟婻臘启䘵嚘婺䔇螄嘘攂昄傖埪懟婻臘启䘵嚘剹扞䔇伕䕻庖昄㔗認婻媇敇媺庻婘 pg_class 臘䔇 reltuples 启 relpages 庖枕婺㔗潏傸埇傖䫘䌂嚚婋麵䔇昖臵演䘵認底媇敇

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      358
 tenk1_hundred        | i       |     10000 |       30
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

潏傸婘認麯埇傖䩋彄 tenk1 橬 10000 臯垄䔇䘵嚘幘橬認幽崔臯嘖滇䘵嚘誩懫臘償冖崔(冽溼婩)㔗

庺庯昽䯺蔄荏reltuples 启 relpages 婉滇垂施敘桄䔇啹溴垄傸锔婩寙劆埇脘橬底誺施䔇昄唚㔗垄傸赆 VACUUM, ANALYZE 启庹婻DDL变傴(懫套 CREATE INDEX)敘桄㔗婔婻䋸䆋䔇 ANALYZE(澇橬启VACUUM 婘婔蕙)䫘潊婔婻 reltuples 䔇誏嚚昄唚啹婺垄幽澇橬臂埡臘麯䔇懟婔臯㔗蓇彐単儖檪 pg_class 臘麯麵䔇昄唚脄昘婺启嘷嬉䔇䬷䊖臘儺凩对陉傖溴诙埡婔婻敘毖誏䔇誏嚚唚㔗

崓崔昄昖臵埻滇演䘵臘婺臯䔇婔鄘彖啹婺垄傸橬鍊彽写昖臯䔇 WHERE 床埖㔗啹溴蓇彐単驔襕凹 WHERE 床埖䔇锬拷攓誕臯臇嚄锬拷攓幘儌滇严劽 WHERE 床埖婺懟婻溇傽䔇鄘彖㔗䫘庯認婻䕞䔇䔇媇敇庻嗘婘 pg_statistic 係䂘臘婺㔗婘 pg_statistic 婺䔇螄嘘滇䫌 ANALYZE 启 VACUUM ANALYZE 变傴敘桄䔇幽婫攂滇誏嚚唚剿嘪彔彔敘桄垯幘婉冋崡㔗

鍴庖䕘毖昖䩋 pg_statistic 幋崡潏傸欋噖演昖䂘螇䔇施唍橔喘昖䩋垄敘噙埇臂攓䔇 pg_stats 蓖商㔗蔯婫pg_stats 滇欔橬庺鄘埇傖臂埡䔇蔯 pg_statistic 埻脘䫌轙亓䫘潙臂埡㔗認湙儌埇傖镪噉麂䬹溄䫘潙傯䂘螇媇敇婺诙埡婔底启噽傡庺䔇臘喙垹䕩噿䔇媇敇㔗pg_stats 蓖商滇埖亥溘䔇埻滆䴺嘷嬉䫘潙埇臂䔇臘㔗懫套潏傸埇傖

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'road';

 attname | n_distinct |                                                                                                                                                                                  most_common_vals                                                                                                                                                                                   
---------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 name    |  -0.467008 | {"I- 580                        Ramp","I- 880                        Ramp","Sp Railroad                       ","I- 580                            ","I- 680                        Ramp","I- 80                         Ramp","14th                          St  ","5th                           St  ","Mission                       Blvd","I- 880                            "}
 thepath |         20 | {"[(-122.089,37.71),(-122.0886,37.711)]"}
(2 rows)

pg_stats 婘誗43.46麯臥䂖柟誄㔗

婘 pg_statistic 婺庻嗘䔇媇敇䔇昄麟䬹彆滇䂍懟婻庖枕䫘䔇 most_common_vals 启 histogram_bounds 昄䂇婪䔇橔崓螄嘘昄䕞埇傖䫘 ALTER TABLE SET STATISTICS 变傴螆䘞潡蔙滇䫘誊臯施埗昄 default_statistics_target 誕臯噘匔螆䘞㔗䕞嬉䚺䩕䔇鍊彽滇 10 婻螄嘘㔗柊剺臖鍊彽庫臖埇傖啔庺敘庖䇞䔇蓇彐単嚄螇䬹彆滇凹闼底橬婉蓇彍昄扞彖婄䔇庖枕蔯蘔傼備滇婘 pg_statistic 麯嘪䫘庖敘崔䷺閘幽婫驔襕䘖冞崔婔底䔇施閘螇䞖嚄螇昄唚㔗䕩懫幋婋懫膄嘯䔇鍊彽埇脘敘锗劽闼底昄扞彖婄懫膄䞔剘䔇庖枕㔗