PostgreSQL（知识片）：查询/计算Selectivity（可选性）

一、视图pg_ststs查询可选性

1、当可选性较小时，可以用视图pg_ststs来查询

 表的每一列的MVC（most Common Value）作为一对most_common_vals和most_common_freqs的列存储在pg_ststs视图中。

       （1）most_common_vals：最常见的值。是统计MCVs列表的列；

       （2）most_common_freqs：最常见值的频率。是统计MCV频率的列。

2、举个例子：使用视图pg_ststs来查询Selectivity

       有一个表“countries”，记录了六大洲的信息：Africa、Europe、Asia、“North America”、Oceanic、“South America”，以及每个洲包含的所有国家。

       此时如果使用SQL：SELECT * FROM countries WHERE continent='Asia';那么这个亚洲城市的可选性是多少呢？

计算结果内容如下：

上图第三列就是可选性，即：亚洲名下所有国家的个数占总国家个数的比例。总结：

       与“亚洲”对应的最常见频率值为0.227979。因此，在该估计中使用0.227979作为选择性；

       对于列值可选项很高的情况，就不能使用MCV，则使用目标列的直方图界限值来估计成本。

此时，可以使用视图pg_ststs可以直接对可选性进行查询：

\x
SELECT most_common_vals,most_common_freqs 
FROM pg_stats 
WHERE tablename='countries' 
AND attname='continent';

二、直方图histogram_bounds查询可选性

直方图histogram_bounds是一个值列表，用于将列的值分成大致相等的总体组。

当可选性很高的时候，可以使用此方法来查询。

1、Buckets and histogram_bounds

桶和直方图边界

如上图所示，默认情况下，直方图界限会划分为100个桶, bucket从0开始编号：

bucket_0~ bucket_99，0~99就是桶id。每个桶（bucket）存储大约相同数量的元组。

每个桶都有其最小值和最大值（下方对于的数即为其最小值）。直方图界限的值也是相应存储桶的界限。例如，直方图上界的第0个值是1,这意味着它是存储在bucket_0中的元组的最小值；第1个值是100，这是存储在bucket_1中的元组的最小值，依此类推。

2、PG中直方图的计算公式为

Selectivity={ bucket_id + (目标数值-hb[n])/( hb[n+1]-hb[n])} / 桶个数

#bucket_id：where子句中，限定的数值，所在的桶id

#目标数值：where子句中，限定的数值

#hb[n]：where子句中，限定的数值，所在的桶的最小界限值

#hb[n+1]：where子句中，限定的数值，所在桶的下一个桶的最小界限值

#桶个数：一共有多少个桶

SELECT histogram_bounds
FROM pg_stats
WHERE tablename='tbl'
AND attname='data';

3、举个栗子：使用直方图histogram_bounds来查询Selectivity

假设存在图内所示的数据：

使用WHERE data<240来计算选择性（Selectivity）时，公式如下：

bucket_id=2（240在hb（2）里）

目标数值=240

hb[n]=200  （hb(2)桶内的最小值）

hb[n+1]=300   （hb(3)桶内的最小值）

Selectivity={ 2+ (240-200) / (300-200) } / 100 = 0.024