PostgreSQL + ANALYZE + pg_statistic 表

Question

我对存储来自 ANALYZE 的数据的 pg_statistic 表有疑问。有人能告诉我这个表的列包含什么吗？我有 list here ，但我不知道(不明白)以下列中的内容:stanullfrac、stadistinct、stakindN、staopN , stanumbersN, stavaluesN.

Answer 1

对我来说似乎很简单。特别是如果您在定义中查看更加用户友好的 pg_stat View (\d+ pg_stats)

例如，说:

select * from pg_statistic where starelid = 23825 and staattnum=2;

starelid    | 23825      => table (oid)
staattnum   | 2          => column 2
stanullfrac | 0          =>  0% null valuess
stadistinct | -0.484307  => about 48% are distict, i.e., the average repetition is about 2
stakind1    | 1                   => code '1' : most common vals 
stavalues1  | {"John","Mary" ...  => values of most common valss
stanumbers1 | {0.0086,0.0064 ...  => percentage occurrence of common vals
stakind2    | 2                   => code '2' : histogram bounds 
stavalues2  | {-,"AAA", ...       => values (bounds) for histogram intervals
stanumbers2 | 
stakind3    | 3                   => code '3' => correlation
stavalues3  | 
stanumbers3 | {0.00826469}        => correlation coefficient

同样的事情使用 human_readable pg_stats View :

select * from pg_stats where tablename = 'persons' and attname = 'first_name';
schemaname        | public
tablename         | persons
attname           | first_name
inherited         | f
null_frac         | 0
avg_width         | 11
n_distinct        | -0.484307
most_common_vals  | {"John","Mary"...
most_common_freqs | {0.00867898,0.00640832...
histogram_bounds  | {-,"AAA"...
correlation       | 0.00826469