开发者学堂课程【PostgreSQL快速入门： PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（一）】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/91
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（一）
内容介绍：
一、PostgreSQL 的查询优化
二、EXPLAIN 语法
三、EXPLAIN 输出的含义
一、PostgreSQL 的查询优化
1、本节课讲解 Postgre 的查询优化，这章节涉及到explain SQL 分析工具的使用，理解 explain 的代价计算原理，explain 在输出的时候会有成本的输出，Postgre 优化器会根据不同的路径计算出来的成本，选择成本最优的执行计划。
会根据数据库的硬件环境校准代价因子，不同的硬件环境默认的 Postgre 的代价因子可能不合适。
2、默认的代价因子是 Planner Cost Constanta 等，
seq_page_cost =1.0 是全表扫描的单个扫描块的成本，它是一个成本基数，是1.0。random_page_cost =1.0 是随机扫描的，如果走索引扫描10个块，它的成本就是10乘以代价因子。cpu_tuple_cost =0.01指每取出一条记录，cpu 的开销是多少。
cpu_index_tuple_cost 走索引的话，索引扫描每一个 tuple 额外的开销是多少。cpu_operator_cost 如果有过滤，扫描十条记录只需要里面的某些条件，假如只需要一条，cpu_operator_cost 指的是过滤这十条记录带来的额外开销，operator 跟函数有关系。effective_cache_size = 128000MB 跟代价因子没有关系，是一个倾向性的东西。
比如他配置的是128G，就是告诉 Posrgre 整个系统有128G 的剩余内存，考虑查询这些数据块在 cache 里面，这样 random_page_cost 的成本会更低，就是离散扫描的成本会更低。
对于不同硬件，这些成本硬置需要调整。比如 CPU 很调暗，这些就要调低一些，或者使用的硬盘是强悍的硬盘，或者 SSD，那么它连续扫描和随机扫描的成本，可以设置成一样的。
3、还要讲的有 explain 输出的含义，比如组合行集，节点处理，合并连接，哈希连接等，并且可以结合 explain 的输出优化 SQL。
二、EXPLAIN 语法
1、explain 后面可以带 option，比如说analyze ，analyze 在 explain 的时候真正执行 statement，就是下面的SQL语句。
EXPLAIN [(option [.….])] statement
EXPLAIN[ANALYZE][VERBOSE] statement
以统计得到时间以及 buffer 的信息，如果要统计 buffer 和 timing，analyze 必须打开。analyze 打开一定要注意 analyze 是真正执行这条 SQL。
比如 explain analyze 一个 update 语句，会 update 一个数据，一般在调教好之后就不用在 analyze，直接 explain 就可以。
2、verbose 指的是输出详细信息；cost 是输出成本，默认打开；buffer 指的是输出查询消耗的 shared buffer 和local buffer 的信息，包括命中、未命中、脏、写； timing 指输出 explain statement 真实的执行时间；FORMAT { TEXT | XML | JSON | YAML } 是输出格式，格式可以是 text、xml、json 或者 ymal 格式。
三、EXPLAIN 输出的含义
1、例子讲解
digoal=# explain (analyzc, verbose, costs, buffers, timing) select count(*) from tbl_cost_align
QUERY PLAN
Aggregate(cost=220643.00.220643.01 rows= width=0)(actual time=4637.754.4637.754 rows= loops=)
Output: count(*)--这个节点的输出,聚合,输出第一行前的开销是220643.00.
--聚合的开销=220643.00-195393.00
Buffers: shared hit=4925read=89468-这个节点以及下级节点的BUFFER统计项
>Seq Scan on postgres.tbl_cost_align(cost=0.00.195393.00
rows=10100000 width=0)(actual timc=00183119.291 rows-10100000loops=1)--这个节点的路径(全表扫描)
--0.00表示输出第一行前的成本，如这里输出第一行前不需要排序为0.00.后面是这个节点真实的时间
Output: id,info,crt_time --这个节点输出的列
Buffers: shared hit=4925 read=89468 这个节点的shared buffer命中4925个page，从磁盘读取89468个page(如果 shared buffer够大,第二次执行的时候应该全部hit.)
Total runtime: 4637.805 ms总的执行时间
(7 rows)
（1）例子是 explain（analyze、verbose、costs、buffers、timing） 全部选上，输出是非常详细的，但是 analyze 会执行这条 SQL 语句，最终得到的 actual time 指的是真正执行这条 SQL 语句（select count(*) from tbl_cost_align）得到的时间。
explain 输出的时候是从最里层往上层节点看，在执行的时候是从最里层的节点开始。例子里最里层的节点是 Seq Scan，Seq Scan 是全表扫描的意思，cost=0.00.195393.00 是从代价因子计算出来的成本，actual timc=00183119.291 是真实的执行时间，rows=10100000是返回的行，这个表有一千零十万行，全表扫描消耗的支持时间总共是三秒钟。
0.018指的是在输出第一行的时候带来的开销，buffer 指的是 shared hit，指的是在 shared buffer 里面命中的，read 在外面读的，就是 shared buffer 以外的读。
（2）read 可能来自两个地方，一个是来自 OS cache，另一个是可能来自于真正的物理盘。因为 littles 超系统成有 cache，在读89468数据块的时候可能是在 OS cache 里面命中，也可能是在物理磁盘上读。但是对于 Postgre 来说，只要不在 Postgre 的 shared buffer 里面，就认为是从外面读的，不管是不是在 oscache，shared buffer 是在数据库的参数文件里的 shared buffer 配置。对于这个 SQL 语句，有4925个数据块在 shared buffer 里面命中，剩下的都是从外面读的。
（3）Output 表示 postgres.tbl_cost_align 节点的输出，输出了三个字段 id、info、crt_time，表示全表扫描的时候把这三个字段全部输出。
Aggregate 是一个聚合节点，聚合来自 count（*），全表扫描是扫描 tbl_cost_align 表 。聚合真正的输出时间是抛去全表扫描的时间加上聚合速度第一行之前带来的额外开销，真正聚合的时间是开销了4637毫秒减去3119毫秒，差不多1.5秒的样子，把这一千零十万条记录聚合成一条记录。
真正输出的话时间是一个很短的时间，因为只有一条记录存储 count，count 就一个数字，这个输出的时间很短，row 是真正输出的行有多少，actual time 后面也是真正输出的行有多少条。聚合的开销从 cost=220643.00 来看，这是从代价因子里面计算出来的 cost，这个 cost 减去下一个节点总的 cost，相当于聚合带来的开销。
2、组合行集例子
digoal=# explain (analyze, verbose, costs, buffers, timing) select 1 union select1; --union去重复，所以有sort节点
QUERY PLAN
Unique (cost=0.05..0.06 rows=2 width=0)(actual time=0.049..0.051 rows=1 loops=1)
Output:(1)
Buffers: shared hit=3
>Sort (cost=0.05..0.06 rows=2 width=0) (actual time=0.047..0.047 rows=2 loops=1)
Output:(1)
Sort Key:(1)
Sort Method:quicksort Memory: 25kB
Buffers: shared hit=3
>Append(cost=0.00..0.04 rows=2 width=0)(actual time=0.006..0.007rows=2 loops=1)
->Result(cost=0.00..0.01rows=1width=0)(actualtime=0.0030.003 rows=1 loops=1)
Output:l
->Result(cost=0.00..0.01rows=1 width=0)(actualtime=0.000.0.000 rows=1 loops=1)
Output: 1
Total runtime: 0.136 ms
(14 rows)
（1）有两个查询把结果union，或者是 unit oral，union 有一个去重，就是排序的过程。真正执行 analyze select 1 union select1，最底层的节点是“->”，->Result(cost=0.00..0.01rows=1
width=0)(actualtime=0.0030.003 rows=1 loops=1) 是select1 的第一个节点；->Result(cost=0.00..0.01rows=1 width=0)
(actualtime=0.000.0.000 rows=1 loops=1) 是select 1的第二个节点。这两个节点会做一个 append 操作，append 是合并，相当于 select 1 合并 select 1。然后到下面排序节点，
因为要去重，所以有个排序的过程。
（2）排序用到 quicksort Memory，就是在内存里面快速排序，消耗25KB。shared hit 是三个；sort key 是1，就是第一个字段做排序；output 也是1，得到排序之后，会有一个去重的操作（unique），最后输出的是一条记录，排序的时候总共有两条记录在排序（rows=2 loops=1）。Append 是一个组合行列合并的过程，也是从两个节点的一条记录合并成两条记录。
3、嵌套连接例子
（1）nested loop join: The right relation is scanned once for every row fo ound in the left relation.This strategyis easy to implement but can be very time consuming. (However, if the right relation can be scanned with an index scan, this can be a good strategy.It is possible to use values from the current row of the left relation as keys for the index scan of the right.)
连接有三种连接，分别是嵌套连接、哈希连接和合并连接，合并连接是marge join，哈希连接是哈希 join。嵌套连接叫 nested loop join，整个过程是左表扫描一次，右表根据左表每输出一条记录，跟右表做 join 的匹配，相当于是左表有十条记录，右表需要扫描十次，最终结果集再合并输出。这种表适合右表的关联列发生在唯一键值列或者主键列上的情况，在右表做 join 的时候，右表上有一个索引，如果没有索引右表每一次都是全表扫描，性能开销是非常大的。
（2）digoal=# explain (analyze, verbose, costs, buffers, timing) select f.* from f,p where f.p_id=p.id and f.p_id<10;
QUERY PLAN
Nested Loop(cost=0.57..22.29 rows-9 width=49)(actual time=0.011..0.042 rows=9 loops=1)
Output: f.id, f.p_id, f.info, f.crt_time
Buffers: shared hit=3l
-> Index Scan using idx_f_I on postgres.f(cost=0.29..2.45 rows=9 width=49) (actual time=0.005..0.010 rows=9 loops=1)--左表
Output: fid, f.p_id, f.info, f.crt_time
Index Cond: (f.p_id <10)
Buffers: shared hit=4
->Index Only Scan using idx_pkey on postgres.p(cost=0.29..2.19
rows= width=4)(actual time=0.002.0.003rows= loops=9)--右表
Output: p.id
Index Cond:(p.id=f.p_id)
Heap Fetches: 9
Buffers: shared hit=27
Total runtime: 0.072 ms
(13 rows)
假如有两个表，一个f表，一个p表，这两个表都有索引。f表的 pid 上有索引， p表 id 上有索引。右表是f表，它循环了9次，左表是f表。这个执行计划中右表是p表，因为p表上是PK，是一个唯一键值，左表输出9行，每一行右表都需要做一个条件（index cond），从左表输出9行，右表会循环九次，最终输出。Output 指的是输出的一些字段。shared hit，这里没有 read，表示所有的都是在 shared buffer 里面命中。
3、哈希连接例子
（1）hash join: the right relation is first scanned and loaded into a hash table,using its j oin attributes as hash keys. Next the left relation is scanned and the appropriate values of every row found are used as hash keys to locate the matching rows in the table.
哈希连接指的是右表扫描加载到内存 HASH 表，hash key 是一个 join 列。假如a表的id列和b表的id列做 join，如果右表是b表的话，那么这个哈希列就是b表的id列。然后左表扫描，并与内存中的 HASH 表进行关联，输出最终结果。
（2）digoal=# explain (analyze, verbose, costs, buffers, timing) selget f.* from f，p where f.p_id=p.id and f.p_id<10;
Hash Join (cost=2.56234.15 rows=9 width=49)(actual time=6.047.3.905 rows=9 loops=1)
Output: f.id, fp_id, f.info, f.crt_time
Hash Cond:(p.id=f.p_id)--HASH join key,fp_id.
Buffers: shared hit=98
->Seq Scan on postgres.p(cost=0.00.194.00rows=10000
width=4)(actual time=0.014.2.016 rows=10000 loops=1)-左表
Output: p.id,p.info,p.crt_time
Buffers: shared hit=94
-> Hash(cost=2.45..2.45 rows=9 width=49) (actual time=0.017..0.017 rows=9 loops= 1)
Output: f.id, f.p_id, f.info, f.crt_time
Buckets: 1024 Batches:1 Memory Usage:1kB--右表加载到内存，hash key是join key fp_id
Buffers: shared hit=4
->Index Scan using idx_f_1 on postgres.f(cost=0.29..2.45
rows=9width=49)(actual time=0.00..0.012 rows=9 loops=1)--右表
Output: f.id, fp_id, f.info, f.crt_time
Index Cond:(f.p_id<10)
Buffers: shared hit=4
Total runtime: 3.954 ms
(16 rows)
同样是这个查询（selget f.* from f，p where f.p_id=p.id and f.p_id<10;)，
但是把 nested local scan 禁掉，把 enable_nestloop 关掉，强制它走 hash join。走 hash join 能看到右表是扫描，然后加载到 hash 上。右表扫描然后加载到内存中，相当于走 idx_f_1 这个索引，把数据取出之后，做了 hash 的节点，相当于把取出的九条记录放到哈希内存表中，它的内存开销是1KB，buckets 是1024，batches 是1。同样也是命中四个块，indx scan 相当于是 hash root 下一个 root。左表是p表，左表根据哈希出来九条的记录依次做一个匹配，然后在输出，节点就是 hash join。HASH join key 是 f.p_id，
就是两个关联的右表的字段，右表是f，所以字段是 p_id,就是 hash p。hash cond，hash 条件是 p.id= f.p_id。
4、合并连接例子
（1）merge join: Fach relation is sorted on the join attributes before the join starts.Then the two relations are scanned in parallel,and matching rows are combined to form join rows.This kind of join is more attractive because each relation has to be scanned only once.The required sorting might be achieved either by an explicit sort step, or by scanning the relation in the proper order using an index on the join key.
这个连接要做两次排序，如果 join 的列有索引，可以不需要额外排序的过程；如果没有索引，则需要额外的排序过程做哈希 join。首先两个 join 的表根据 join key 进行排序，如果两个列都有索引，则没有这个排序的过程，然后根据 join key 排序顺序并行扫描两个表进行匹配输出最终结果。这种情况适合大表，并且索引列进行关联的情况，这两个大表只有 merge join 条件，其它条件都没有的情况下，走 merge join 带来的开销比较少。
（2）Digoal=# explain (analyze, verbose, costs, buffers, timing) select f.* from f.p where f.p_id=p.id and f.p_id<10;
QUERY PLAN
Merge Join(cost=0.57..301.85 rows=9 width=49)(actual time=0.030.0.049rows=9 loops=1)
Output: f.id,fp_id,f.info, f.crt_time
Merge Cond:(f.p_id=p.id)
Buffers: shared hit=8
->Index Scan using idx_f_1 on postgres.f(cost=0.29..2.45 rows=9 width=49)(actual time=0.005..0.012 rows=9 loops=1)
Output: f.id,f.p_id,f.info, f.crt_time
Index Cond:(f.p id<10)
Buffers: shared hit=4
->Index Only Scan using P_pkey on postgres.p (cost=0.29.274.29 rows=10000 width=4)(actual time=0.0170.022 rows=10 loops=1)
Output: p.id
Heap Fetches: 10
Buffers: shared hit=4
Total runtime:0.118 ms
(13 rows)
把 nestloop 和 hashjoin 都关掉，最后只允许 mergejoin。 还是这个查询语句（select f.* from f.p where f.p_id=p.id and f.p_id<10;
），走了两个索引，分别是 p_pkey 和 idx_f_1，都是两个键值，merge 条件是两个字段 f.p_id=p.id，这两个表的两个字段都有索引。mergejoin 成本是301，hashjoin 成本是234，nested loopjoin 成本是22.29，如果这三个join都允许的情况下，肯定是走嵌套循环。
