PostgreSQL 快速返回表上某列的唯一值(枚举值) - pg_stats.most_common_vals

标签

PostgreSQL , 统计信息 , 唯一值 , 枚举值

背景

PostgreSQL的列统计信息中包含一项高频词，同时包含一项唯一值个数。

pg_stats.n_distinct  
  
pg_stats.most_common_vals  

同时PostgreSQL允许用户自定义统计信息柱状图BUCKET的个数。

alter table     ALTER [ COLUMN ] column_name SET STATISTICS integer  
  
  

默认柱状图BUCKET个数为100

postgres=# show default_statistics_target ;  
 default_statistics_target   
---------------------------  
 100  
(1 row)  

当唯一值的个数小于柱状图的BUCKET个数时，从高频词(pg_stats.most_common_vals)得到的实际上就是该列的唯一值的内容。

例子

使用一个函数，加上以上判断逻辑，用来直接获取某个表，某个列的唯一值的内容。

create or replace function get_distinct_value(  
  name,   -- schema  
  name,   -- table  
  name    -- column  
) returns text as $$  
declare  
  v int;  
  res text;  
begin  
  select   
    case attstattarget   
      when -1 then current_setting('default_statistics_target')::int   -- 默认bucket  
      else attstattarget   -- 自定义bucket  
    end into v   
  from pg_attribute where attrelid=($1||'.'||$2)::regclass and attname=$3 ;  
    
  select (most_common_vals)::text into res -- 高频词  
    from pg_stats where not inherited and schemaname = $1 and tablename = $2 and attname = $3 and n_distinct >=1 and n_distinct <= v;  
  return res;  
end;  
$$ language plpgsql strict;  

1、创建测试表，写入测试数据

postgres=# create table tbl(id int,c1 int,c2 int);  
CREATE TABLE  
postgres=# insert into tbl select id, random()*100, random()*200 from generate_series(1,10000000) t(id);  
INSERT 0 10000000  

2、分析表

postgres=# analyze tbl;  
ANALYZE  

3、通过以上函数，获取c1列的唯一值内容。

postgres=# select * from get_distinct_value('public','tbl','c1') ;  
 get_distinct_value   
--------------------  
   
(1 row)  

返回空，因为bucket个数小于n_distinct，所以得到的唯一值实际上是不准确的。因此返回了空，与逻辑相符。

4、修改C1，C2列的BUCKET为300，大于实际的唯一值个数

postgres=# alter table tbl alter COLUMN c1 set statistics 300;  
ALTER TABLE  
postgres=# alter table tbl alter COLUMN c2 set statistics 300;  
ALTER TABLE  
  
重新分析表  
postgres=# analyze tbl;  
ANALYZE  

5、通过以上函数，获取c1, c2列的唯一值内容。 得到了正确的结果

postgres=# select * from get_distinct_value('public','tbl','c1') ;  
                                                                                                                                           get_distinct_value                                                                                  
                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------  
 {56,83,61,67,37,8,53,66,17,44,29,54,9,15,85,49,80,96,58,22,47,68,62,30,77,34,64,23,39,11,5,24,42,7,3,32,57,73,88,40,93,13,91,86,51,89,81,43,71,35,45,48,84,16,87,50,27,99,4,25,38,14,41,72,78,95,74,76,18,94,28,90,2,12,79,21,70,36,52,75,31  
,33,55,60,92,6,63,69,98,46,97,19,59,26,20,10,65,1,82,0,100}  
(1 row)  
  
postgres=# select * from get_distinct_value('public','tbl','c2') ;  
                                                                                                                                                                                                                                               
                                                                                                      get_distinct_value                                                                                                                       
                                                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 {75,132,83,91,82,69,4,74,195,35,157,124,178,13,61,134,70,153,31,190,3,150,84,135,140,50,114,152,193,119,147,116,143,199,11,45,68,94,183,5,93,51,130,32,98,46,118,47,52,79,146,6,39,101,154,162,191,66,148,165,187,21,67,72,103,54,73,122,106  
,127,131,137,168,169,179,96,115,177,186,64,121,14,29,37,30,78,17,80,117,120,133,144,159,7,26,38,113,172,141,158,89,155,164,15,63,125,184,108,12,189,56,139,20,59,43,104,126,48,65,163,166,19,28,77,180,194,95,99,198,176,16,33,40,110,160,197  
,49,100,167,2,8,18,55,156,188,36,53,88,90,142,185,27,42,85,1,109,25,62,57,107,123,76,112,86,10,71,128,149,175,24,34,92,173,181,60,97,136,151,41,58,111,161,192,9,81,87,105,182,23,129,138,145,171,44,174,196,22,170,102,200,0}  
(1 row)  
  
  
postgres=# select * from unnest(get_distinct_value('public','tbl','c2')::int[]) ;  
 unnest   
--------  
     75  
    132  
     83  
     91  
     82  
     69  
      4  
     74  
... ...  
  
(201 rows)  

小结

1、由于统计信息并不是实时更新的，所以使用本文提到的方法，并不一定能得到实时的准确结果。但是可以在执行前ANALYZE一下，那么得到的结果就非常准确。

2、当时有本文提到的方法返回的结果为NULL时，再使用select x from tbl group by x;来返回唯一值。

统计信息中有很多有趣的内容，比如还可以用来

1、评估高频词，例如我们的表里面存储了APP的下周记录，我们通过高频词可以评估出热门的APP。

2、通过explain还可以用来评估SQL的返回记录数。用于分页评估。

《妙用explain Plan Rows快速估算行 - 分页数估算》

参考

《PostgreSQL 11 preview - 表达式索引柱状图buckets\STATISTICS\default_statistics_target可设置》

《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》