本篇主要分析PG的统计信息。
ORACLE统计信息迁移到PG（不接触用户数据），应用场景比较特殊，不再赘述。
准备
代码语言：javascript复制drop table mapping;
create table mapping(id0 int, id1 int, id2 int, info varchar(32));
insert into mapping values(1, 23, 413, 'b');
insert into mapping values(2, 23, 325, 'a');
insert into mapping values(3, 23, 652, 'c');
insert into mapping values(4, 17, 584, 'b');
insert into mapping values(5, 43, 325, 'd');
insert into mapping values(6, 39, 149, 'e');
insert into mapping values(7, 93, 999, 'ffff');
insert into mapping values(8, 24, 999, 'gggg');
insert into mapping values(9, 24, NULL, 'hhh');
insert into mapping values(0, 19, NULL, 'zzz');
-- 便于直观看到统计信息，先查一下pg_stats这个视图
postgres=# select * from pg_stats where tablename='mapping';
-[ RECORD 1 ]---------- ----------------------------
schemaname | public
tablename | mapping
attname | id0
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {0,1,2,3,4,5,6,7,8,9}
correlation | 0.454545
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 2 ]---------- ----------------------------
schemaname | public
tablename | mapping
attname | id1
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.7
most_common_vals | {23,24}
most_common_freqs | {0.3,0.2}
histogram_bounds | {17,19,39,43,93}
correlation | 0.260606
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 3 ]---------- ----------------------------
schemaname | public
tablename | mapping
attname | id2
inherited | f
null_frac | 0.2
avg_width | 4
n_distinct | -0.6
most_common_vals | {325,999}
most_common_freqs | {0.2,0.2}
histogram_bounds | {149,413,584,652}
correlation | 0.428571
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 4 ]---------- ----------------------------
schemaname | public
tablename | mapping
attname | info
inherited | f
null_frac | 0
avg_width | 3
n_distinct | -0.9
most_common_vals | {b}
most_common_freqs | {0.2}
histogram_bounds | {a,c,d,e,ffff,gggg,hhh,zzz}
correlation | 0.975758
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
