开发者学堂课程【PostgreSQL快速入门： PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（二）】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/91
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（二）
内容介绍：
四、EXPLAIN 成本计算
五、EXPLAIN 行数评估
六、EXPLAIN 成本计算-索引扫描
四、EXPLAIN 成本计算
1、跟 explain 成本相关的有表或视图，列了 pg_stata 视图和 pg_class 表，pg_class 表里面有两个跟成本计算相关的，分别是是 relpages 和 reluples。relpages 表示这个表占用多少页面，reluples 表示总共有多少条记录。
举个例子看一下，select count（*）from u，u表没有记录。
看一下 t1 表，t1 表有记录，先搜集它的信息。
新建一个表，create table test1〈id int〉，insert into test1 select generate_series〈1，00000〉，往里面插入十万条记录，analyze test1，这样统计信息就收集了。
select * from pg_class where oid=｀test1｀：： regclass，
reltuplss 有十万条记录，有443个页面（relpages），总共占了443个页面和十万条记录，这可以拿来做成本计算。
2、假如做一个全表扫描，成本很容易计算出来，一个是 reltuples，就是 cpu 的 tuples cost，是100000×CPU tuples cost 的因子，relpages 是443，443乘以连续扫描每个页面的成本因子。select count（*）from test1
这里也是 cost，其实来自于 cpu tuples cost 和 relpages cost。show seq_page_cost 代价是1，cpu_tuple_cost 代价是0.01，所以它的成本等于是 seqcost乘以443 个数据块，再加上十万条记录乘以0.01，这就是最终的代价1443，这是全表扫描的如何计算成本。如果是对于很复杂的扫描，比如说走索引或带 复杂的 where 条件，就要用到 pg_status 统计信息，并且还要用到下面的代价因子。
seq_page_cost --全表扫描的单个数据块的代价因子
random_page_cost --索引扫描的单个数据块的代价因子 cpu_tuple_cost –处理每条记录的 CPU 开销代价因子
cpu_index_tuple_cost -- 索引扫描时每个索引条目的CPU开销代价因子
cpu_operator_cost--操作符或函数的开销代价因子
假如有一个 where 条件，where id＜100，小于号就是一个 operate，它是额外带来的 CPU 的开销。除了全表扫描之外，还有额外的开销，就是小于100的开销。
select * from pg_operate where oprname=‘＜’ 看小于号对应的是哪种函数，小于号有很多操作符，有很多类型，这里是 int 型。select oid from pg_type where typname =‘integer’，select oid from pg_type where typname =‘int4’，int4 的oid 是23。重新查一下 operate 表，operate 左右都是23，
select * from pg_operate where oprname=‘＜’ and oprleft=23 and oprright＝23，对应的是 int4lt 函数，oprcode 对应的是 int4lt 函数，select * from pg_proc where proname=‘int4lt’，这个函数的成本（procost）是1。每一条记录上面都会额外带来1乘以cpu_operate_cost 的开销，cpu_operate_cost是0.025，它的成本变成了 1*443+100000*0.01+0.025*1*100000，这样就得到1693了，也就是下图看到的1693。
3、知道简单的公式之后，看一下复杂的。pg_stats 是用于复杂的查询里，会用到下面的统计信息，
这些统计信息非常全面，schemaname 表示 pg_stats 统计信息的对象放在哪个 schema 下；tablename 统计信息对象是哪个表名；attname 统计信息对应的是哪个列，列指的是对应到的统计信息。比如说 test1 表上有一个 id 键，select * from pg_stats where tablename=‘test1’ and attname=‘id’，这样就输出了一条它的统计信息。
对着上图分解一下意思。
inherited 指的是这条统计信息是否包含子表的列，如果 pg_stats 表有子表的话，创建了另外一个表叫 test2，Inherited test1，有一个子表。
null_frac 指的是这个 id 有百分之多少的行是空值，0%表示所有的都是非空的。
avg_width 指的是平均列的长度，它是一个 int 类型，有4个字节。n_distinct 指的是百分之多少的值是唯一值，有多少行就有多少唯一值，这里插入的值是1到10万，全都是唯一值。
most_common_vals 指最频繁出现的是哪些值，这个字段存进去的都是完全唯一，所以不可能有 most_common_vals，也不可能有 most_common_freqs。test1 表上面的 id 字段的每一行都是不一样的，所以没有哪些值是比较频繁的，频度也是空。
4、hisrogram_bounds 代表的是这个字段上面做了一些 bucket，它的数据分布在 bucket 里面每一个数据分布的百分比一样，bucket 有多大跟一个参数有关，bucket 的大小和
default_statistics_target 有关，如果这个表定义了一个新的 statistics target，它的柱状图可以改变。现在有100个 bucket，就是有100个这样的间隔，1到1012是一个间隔，1012到2091又是一个间隔，相当于有100个篮子，每个篮子里面从1到1012之间的值有多少条记录跟1012到2091之间有多少条记录是一样的，同时跟99012到99997也是一样的。
correlation 指的是数据的存储顺序和字段值的排序是否一样？这里存进去的值跟 ID 的值确实一样，物理上的分布的值和 id 分布是一样的。
id 等于一的放在第一条记录，等于二放在第二条记录。correlation 等于一表示它完全相近，如果反着来，它的顺序会完全相反。如果把它 truncate 掉，它的记录反着来，insert into test1 select generate_series 〈100000，1，-1〉，现在反着来，看一下表的统计信息，analyze test1，可以看到它的顺序是完全反着的。
跟存储数据是反着的。
most_common_elems 和 most_common_elem_freqs如果存储的是数组类型，就会有 most_common_elem_freqs 这个字段，这个字段对应的数组全部打散之后就会有 most_common_elems，对应的百分比是多少。
elem_count_histogram 也是跟数组有关系。
5、全表扫描
Digoal=#explain seloct * from f;
QUERY PLAN
Scq Scan on f(cost-0.00.12999.00 rows=640000 width-49)
(1row)
Cost是怎么得来的?全表扫描的成本计算只需要用到pg_class. digoal=# select relpages,reltuples from pg_class where relname='f';
relpages | relruples 57
6599| 640000
(1 row)
全表扫描成本怎样计算已经讲过了，是连续扫描的代价因子乘以有多少个数据块，再加上 cpu_tuple_cost 乘以 CPU的行数。
digoal=# show seq_page_cost;
seq_page_cost
1
（row)
digoal=# show cpu_tuple_cost;
cpu_tuple_cost
0.01
(1 row)
COST值:
digoal=# select 6599*1+640000*0.01;
?column?
12999.00
(1 row)
五、EXPLAIN 行数评估
1、从柱状图评估行数的例子，就是评估出来的行数是怎样得来的？举个例子，explain select * from test1 where id<1000，
1012行怎样来的？
EXPLAIN SELECT*FROM tenk1 WHEREunique1<1000;
QUERYPLAN
Bitmap Heap Scan on tenk1(cost=24.06.394.64 rows=1007 width=244)
Recheck Cond:(unique1<1000)
->Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond:(uniquel1<1000)
在8.3以及以前版本default_statistics_target默认是10,也就是10个bucket
SELECT histogram_bounds FROM pg_stats
WHERE tablename=’tenk1’AND attname=’unique1';
histogram_bounds
{10,993,1997,3050,4040,5036,5957,7057,8029,9016,9995}
上面例子是1007行，我们举的例子是1012行。总的行数是十万行，select * from pg_class where oid=‘test1’：：regclass，来自于下图
它有100个 bucket，小于1000，第一个 bucket 已经完全拥有了，第二个bucket 只占了其中的一部分。
2、这个例子的行选择性如下
selectivity=(1+(1000-bucket[2].min)/(bucket[2].max-bucket[2].min))/num_buckets
=(1+(1000-993)(1997-993))/10
=0.100697
最终得到的行数是:
rows=rel_cardinality* selectivity
=10000*0.100697
=1007 (rounding off)
这里rel_cardinality=pg_class.reltuples.
1表示一个 bucket，1000-bucket[2].min 改成 2041 减去 min，除以第二个 bucket 总共的范围段，select （1+(1000-987.0）/（2041-987.0）），这个bucket 就算出来了，再除以总共的 bucket（100个），结果是0.01.
这个结果再乘以有多少个记录，有十万条记录，结果是1012。
就是下面得出的行数1012，这是一种评估算法。
3、从MCV(most common valucs)评估行数的例子
EXPLAIN SELECT* FROM tenkI WHERE stringul='CRAAAA';
QUERY PLAN
Scq Scan on tenk1(cost=0.00483.00 rows=30 width=244) Filter:(stringul='CRAAAA'::name)
SELECT null frac,n distinct,mostcommon_vals，most_common
_freqs FROM pg_stats
WHERE tablename='tenk1' AND attname=stringul';
Null_frac | 0
n_distinct | 676
most_common_vals |(EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA)
most common freqs | {0.00333333,0.003,0.003,0.003,0,003,
0.003,0.003,0.003,0.003,0.003)
还有一种评估算法是从 most common valucs 里面评估，假如这个值正好落在 most common valucs，insert into test1 select 1 from
generate_series<1,10000>，插入十万条1到这个表中，analyze test1，1里面有十万条，如果改成 id=1，有多少数据能够评估出来？
可以看到 most_common_vals 就有了，most_common_freqs 的占比是50%多,然后乘以行数。select * from pg_class where oid=’test1’::regclass，现在的行数变成了二十万。
200000*0.502167 得到的就是行数就是评估出来的行数。
4、从 MCV(most common values) 和 distinct 值个数评估行数的例子
EXPLAIN SELECT*FROM tenk WHERE stringul='xxx';
QUERY PLAN
Scq Scan on tenk1(cost=0.00483.00 rows=15 width=244) Filter:(stringul='xxx'::name)
1减去所有MCV的占比，再乘以distinct值的个数减去MCV的个数 selectivity=(1-sum(mvf))(num_distinct-num_mcv)
=(1-(0.00333333+0.003+0.003+0.003+0.003+0.003+
0.003+0.003+0.003+0.003)/(676-10)
=0.0014559
rows=10000*0.0014559
= 15 (rounding off)
条件改一下，不是 where id=1 了，而是 where id=2，explain select * from test1 where id=2，
4行是用 (1-sum(mvf))(num_distinct-num_mcv) 公式算出来的。除了 most_common_vals 的占比之外的记录的占比，select <1-0.502167> 是剩余记录的占比，除以 num_distinct，num_distinct 是一个负数。
如果它大于零是 number of distinct values in the column；如果小于零是一个占比，占了12.96%。如果是负数选择性相当于是distinct 值的比列，distinct 值相当于乘以20万，因为有20万条记录。然后减去 most_common_vals 的个数，id=1 的只有一个，1减去 most_common_vals 的频率（0.501867），再除以 25919，就得到了等于单个值的选择列。
select 200000*0.00019218835603225433080，得到3.8，约等于4。
5、
还有例子是小于一个值的，同时还有 most common valuse，这种选择算法要用到 most common valuse 以及其他的一些东西。histogram_bounds是一个柱状图，在柱状图里的占比相当于要刨去 most common valuse，也就是下面这些 bucket 总共的占比相当于1减去 most common valuse 占比的总和，
这些 bucket 只占了49.82%，在计算的时候要减去那些值，最后得到的选择性。假如现在的值大于一万，
rows 行就相当于要刨去0.501867选择性，从大于一万的数总共有多少个 bucket。为了方便计算大于97866，大于97866相当于在里面占有两个 bucket，select （1-0.501867）*2/100，得到它的选择性如图
得到这个选择性之后再乘以20万，就得到了1993这个行数（四舍五入）。
6、多个列查询条件的选择性相乘评估例子
EXPLAIN SELECT*FROM tenk1 WHERE unique1<1000 AND stringul='xxx';
QUERY PLAN
Bitmap Hcap Scan on tenk1(cost=23.80..396.91 rows=1 width=244)
Recheck Cond:(unique1<1000)
Filter:(stringul='xxx'::name)
->Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond:(unique1<1000)
多列的选择性相乘得到最终的选择性
selectivity=selectivity(unique1<1000)* selectivity(stringul='xxx')
=0100697*0.0014559
=0.0001466
rows =10000*0.0001466
=1 (rounding off)
对于有多种条件的，又有等于又有小于，它的评估是 unique1<1000 字段的选择性乘以 stringul='xxx' 字段的选择性。
六、EXPLAIN 成本计算-索引扫描
1、索引扫描时,和全表扫描不同，扫描 PAGE 的开销是pages*random_page_cost。
另外,索引扫描一般都涉及操作符,例如大于、小于,等于。所以在计算索引扫描成本的时候，和计算全表扫描会更加复杂，这些操作符对应的函数的 COST 乘以cpu_operator_cost 就得到这个操作符的代价因子，乘以实际操作的行数就得到 CPU 操作符开销，Rows Explain 中可能无输出。
2、索引的CPU开销则是实际扫描的索引条目数乘以cpu index_tuplecost，Rows Explain中无输出。最后的一个开销是实际返回或丢给上层的TUPLE带来的 CPU开销，cpu_tuple_cost*实际扫描的行数Rows Explain中有输出。
