- 论坛徽章:
- 0
|
关于统计信息可以参考我以前写的类似文章,应该有很多篇都有介绍。
在谈n_distinct 和 n_distinct_inherited之前首先来看看继承表和父表的查询,这样就能看出n_distinct 和 n_distinct_inherited设置的区别了。
举例:
父表 tbl_user
继承表 tbl_user_p1
digoal=> truncate table tbl_user;
TRUNCATE TABLE
Time: 61.674 ms
digoal=> truncate table tbl_user_p1;
TRUNCATE TABLE
Time: 29.293 ms
digoal=> insert into tbl_user select generate_series(1,5),'zhou','digoal','sky-mobi',27;
INSERT 0 5
Time: 0.670 ms
digoal=> insert into tbl_user_p1 select generate_series(1,5),'zhou','digoal','sky-mobi',27;
INSERT 0 5
Time: 0.363 ms
digoal=> analyze tbl_user;
ANALYZE
Time: 0.537 ms
digoal=> analyze tbl_user_p1;
ANALYZE
Time: 0.244 ms
digoal=> select * from tbl_user where id=1;
-[ RECORD 1 ]-------
id | 1
firstname | zhou
lastname | digoal
corp | sky-mobi
age | 27
-[ RECORD 2 ]-------
id | 1
firstname | zhou
lastname | digoal
corp | sky-mobi
age | 27
Time: 0.508 ms
digoal=> select * from ONLY tbl_user where id=1;
-[ RECORD 1 ]-------
id | 1
firstname | zhou
lastname | digoal
corp | sky-mobi
age | 27
由于有加ONLY和不加的分别,不加的话查询父表相当于查询了父表和所有的子表。加ONLY表示仅仅查询父表。所以优化器需要的信息当然也是不一样的。因此 n_distinct 和 n_distinct_inherited 分表表示仅父表和父表以及所有子表的数据ANALYZE出来的值。
从pg_stats也可以看出来,带有子表的表,统计信息会比其他没有子表的统计信息记录多一倍,分别是 inherited字段=f表示仅父表的统计信息,=t表示父表以及所有子表的统计信息。
如下:
一个五个字段的表,正常情况下在pg_stats里面应该有5条记录,但是有继承表后,记录变为10条。
digoal=> select * from pg_stats where tablename ='tbl_user' order by attname;
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | hi
stogram_bounds | correlation
------------+-----------+-----------+-----------+-----------+-----------+------------+------------------+-----------------------+---
---------------+-------------
digoal | tbl_user | age | t | 0 | 4 | 1 | {27} | {1} |
| 1
digoal | tbl_user | age | f | 0 | 4 | -0.2 | {27} | {1} |
| 1
digoal | tbl_user | corp | t | 0 | 9 | 1 | {sky-mobi} | {1} |
| 1
digoal | tbl_user | corp | f | 0 | 9 | -0.2 | {sky-mobi} | {1} |
| 1
digoal | tbl_user | firstname | t | 0 | 5 | 10 | {zhou} | {1} |
| 1
digoal | tbl_user | firstname | f | 0 | 5 | 2 | {zhou} | {1} |
| 1
digoal | tbl_user | id | f | 0 | 4 | -1 | | | {1
,2,3,4,5} | 1
digoal | tbl_user | id | t | 0 | 4 | -0.5 | {1,2,3,4,5} | {0.2,0.2,0.2,0.2,0.2} |
| 0.636364
digoal | tbl_user | lastname | t | 0 | 7 | 1 | {digoal} | {1} |
| 1
digoal | tbl_user | lastname | f | 0 | 7 | -0.2 | {digoal} | {1} |
| 1
(10 rows)
n_distinct 这个值属于统计信息项中的一个指标,在pg_stats中可以查看到这个记录。
n_distinct的取值范围,最小 -1 表示unique , 最大没有限制。abs(负数) 表示 distinct/total(not null)的比例。
大于0表示该列有多少个唯一值 , 类似枚举类型中元素的个数。
修改方法:
alter table tbl_user alter column column_name set (n_distinct = ?, n_distinct_inherited =?);
重置方法:
alter table tbl_user alter column column_name reset (n_distinct, n_distinct_inherited); |
|