PG从小工到专家学习笔记-PostgreSQL 执行计划简要说明
1. 执行计划解释
1.1 EXPLAIN 命令
1.1.1. 简单执行计划
testdb=# explain select * from t1;
Seq Scan on t1 (cost=0.00..18.80 rows=880 width=62)
- Seq Scan on t1 顺序扫描表t1，即全表扫描
- cost=0.00…18.80 ：由’…’ 分割为两部分，0.00 为返回第一行需要的cost，18.80 为返回所有数据的cost
- rows=880：返回880行
- width=62：每行平均宽度为62字节
- cost消耗说明
- 顺序扫描一个数据块，cost定义为1
- 随机扫描一个数据块，cost定义为4
- 处理一个数据行的CPU cost 为0.01
- 处理一个索引行的CPU cost 为0.005
- 每个操作符的CPU代价 cost 为0.0025
1.1.2. 执行计划展示
explain (format [json|xml|YAML|]) [analyze|(analyze true)] [(cost false)] [(analyze true,buffer true)]
- format : 可以指定执行计划输出格式
- analyze：可以得到更精确的执行计划，实际启动时间、执行时间、实际扫描行数
- cost false：只看执行路径，不看消耗
- analyze true,buffer true：可以得到实际代价和缓冲区命中情况
1.1.3. 全表扫描
执行计划使用Seq Scan表示
在PG中也称为顺序扫描，把表的所有数据块从头到尾读一遍，然后从数据块中找到符合条件的数据块。
1.1.4. 索引扫描
执行计划使用Index Scan表示
在索引中找到需要的数据行的物理位置，然后再到表的数据块中把对应数据读出来的过程，
1.1.5. 位图扫描
位图扫描也是索引扫描的一种方式，先扫描索引把符合条件的行或块在内存中构建一个位图，然后根据位图到表的数据文件中把对应的数据读出来；
如果走两个索引，可以把两个索引的位图进行and或者or计算合并成一个位图，再到表的数据文件中把对应的数据读出来。
执行计划标识：
- Bitmap Index Scan：在索引中查找符合条件的行，在内存建立位图再到表中扫描
- Bitmap Heap Scan：在内存建立位图再到表中扫描
- Recheck Cond：多版本，当从索引中找到行从表中读出来，还需要检查下条件
- BitmapOr/BitmapAnd：位图合并
1.1.6. 条件过滤
执行计划标识：Filter
where条件上加的过滤条件，当扫描数据行时，会找出满足过滤条件的行
1.1.7. Nestloop Join
嵌套循环连接(Nestloop Join)是两个表做连接时最朴素的连接方式；
在嵌套循环中内表（被驱动表 inner table）被外表（即驱动表outer table）驱动，外表的每一行都需要在内表中检索找到与它匹配的行，要把返回子集较小的表作为外表（驱动表）并且在内表（被驱动表）相关字段创建索引
1.1.8. Hash Join
优化器使用两张表中较小的表，并利用连接字段在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行
适用于较小的表完全放到内存中，总成本是访问两张表的成本之和；如果表太大，不能完全写入内存，会将表分割为不同分区，把不能写入内存部分写入磁盘临时段，要求有较大的临时段提高性能
1.1.9. Merge Join
通常情况下Hash Join 比Merge Join性能好，如果源数据由索引或者结果已经被排序，在执行Merge Join时就不需要再排序，这种情况下Merge Join性能就会更好一些
2. 与执行计划相关的配置项
2.1. ENABLE*参数
一般不建议修改该类型参数影响优化器的执行计划
| 参数名称 | 类型 | 说明 |
|---|---|---|
| enable_seqscan | boolean | 是否选择关闭全表顺序扫描，但并不是完全禁止全表顺序扫描，关闭后会让优化器在有其他方法时优先选用其他其他方法 |
| enable_indexscan | boolean | 是否选择索引扫描 |
| enable_ bitmapscan | boolean | 是否选择位图扫描 |
| enable_tidscan | boolean | 是否选择位图扫描 |
| enable_nestloop | boolean | 多表连接是否使用嵌套循环；off，如果有其他选择就不走嵌套循环 |
| enable_hashjoin | boolean | 多表关联，是否选择hash join |
| enable_mergejoin | boolean | 多表关联，是否选择merge join |
| enable_hashagg | boolean | 是否使用hash聚合 |
| enable_sort | boolean | 是否使用明确排序；off，如果有其他选择，优化器优先选择其他路径 |
2.2. COST基准值参数
执行计划在选择最优路径时，不同路径的cost值只有相对意义，同时对所有的cost进行同比例扩张或者缩放不会对不同路径的选择产生任何影响
默认情况下，以顺序扫描一个数据块的开销作为基准单位，seq_page_cost：1，其他开销的基准参数对照seq_page_cost来配置。
| 参数名称 | 类型 | 说明 |
|---|---|---|
| seq_page_cost | float | 一次顺序访问一个数据块的开销，默认：1.0 |
| random_page_cost | float | 随机访问一个数据块的开销，默认：4.0 |
| cpu_tuple_cost | float | 计算处理一条数据行的开销，默认：0.01 |
| cpu_index_tuple_cost | float | 计算处理一条索引行的开销，默认：0.005 |
| cpu_operator_cost | float | 执行一个操作符或者函数的开销，默认：0.0025 |
| effective_cache_size | float | 一次索引扫描中可用磁盘缓冲区的有效大小，越大越趋向于使用索引扫描，越小趋向于使用全表顺序扫描；不影响PG分配的共享内存，只用作执行计划的COST评估；数值用数据页计算，通常每个页面8KB大小，16384个数据块大小即128MB |
例如：random_page_cost 调大，优化器趋向于使用索引扫描而不是全表顺序扫描
2.3. 基因查询优化参数
基因查询优化（GEQO）使用探索式搜索来执行查询规划的算法，可以降低负载查询的规划时间；GEQO的检索是随机的，所以生成的执行计划可能存在不确定性；
| 参数名称 | 类型 | 说明 |
|---|---|---|
| geqo | boolean | 使用基因查询优化开关，默认开启； |
| geqo_threshold | integer | 涉及的from关系达到配置数量时，才使用基因查询优化；默认：12，一个full outer join 只能算一个from项 |
| geqo_effort | integer | 控制GEQO时间规划和查询规划有效性之间的平衡，默认：5 （1-10的整数） |
| geqo_pool_size | integer | GEQO使用的池大小， |
| geqo_generations | integer | 控制GEQO子代数目，即迭代次数 |
| geqo_selection_bias | integer | 控制GEQO选择性倾向，默认：2.0 |
| geqo_seed | integer | 控制GEQO使用随机数产生器的初始值，用以选择随机路径 |
2.4. 其他执行计划匹配项
| 参数名称 | 类型 | 说明 |
|---|---|---|
| default_statistics_target | integer | 默认：100，max：10000 ANALYZE在pg_statistic中存储的信息量（特别是每个列的most_common_vals中的最大项数和histogram_bounds数组）可以用ALTER TABLE SET STATISTICS命令为每一列设置， 或者通过设置配置变量default_statistics_target进行全局设置。 目前的默认限制是 100 个项。提升该限制可能会让规划器做出更准确的估计（特别是对那些有不规则数据分布的列）， 其代价是在pg_statistic中消耗了更多空间，并且需要略微多一些的时间来计算估计数值。 相比之下，比较低的限制可能更适合那些数据分布比较简单的列 |
| constraint_exclusion | enum | 控制查询规划器对表约束的使用，以优化查询。 constraint_exclusion的允许值是on（对所有表检查约束）、off（从不检查约束）和partition（只对继承的子表和UNION ALL子查询检查约束）。 partition是默认设置。它通常与传统的继承树一起使用来提高性能。简单查询上并且不会产生任何好处。 如果没有用传统继承树分区的表时，最好是完全关闭它。 |
| cursor_tuple_fraction | float | 设置规划器对将被检索的一个游标的行的比例的估计。默认值是 0.1。更小的值使得规划器偏向为游标使用“快速开始”计划，它将很快地检索前几行但是可能需要很长时间来获取所有行。更大的值倾向于使总的估计时间更短。最大设置为 1.0，游标将和普通查询完全一样地被规划，只考虑总估计时间并且不考虑前几行会被多快地返回 |
| from_collapse_limit | integer | 如果生成的FROM列表不超过配置值，规划器将把子查询融合到上层查询。较小的值可以减少规划时间，但是可能会生成较差的查询计划。默认值是 8 |
| join_collapse_limit | integer | 如果得出的列表中不超过配置值，那么规划器将把显式JOIN（除了FULL JOIN）结构重写到 FROM项列表中。较小的值可减少规划时间，但是可能会生成差些的查询计划；默认情况下，这个变量被设置成和from_collapse_limit相同， 这样适合大多数使用。把它设置为 1 可避免任何显式JOIN的重排序。因此查询中指定的显式连接顺序就是关系被连接的实际顺序。因为查询规划器并不是总能 选取最优的连接顺序，高级用户可以选择暂时把这个变量设置为 1，然后显式地指定他们想要的连接顺序。将这个值设置为geqo_threshold或更大，可能触发使用 GEQO 规划器，从而产生非最优计划 |
3. 统计信息的收集
PgStat 子进程：PG专门的统计信息收集器进程；
统计信息作用：
- 查询优化代价评估
- 为数据库活动的监控及性能分析提供帮助
统计信息保存：
- 表和索引的行数、块数等统计信息保存在系统表pg_class，
- 其他的统计信息主要收集在pg_statistic中
3.1. 统计信息收集器配置项
| 参数名称 | 类型 | 说明 |
|---|---|---|
| track_counts | boolean | 收否收集表和索引上统计信息，默认开启； |
| track_functions | enum | 是否收集函数调用次数和时间统计信息：‘none’：不收集，‘pl’：只收集过程语言函数，‘all’：所有函数；默认‘none’ |
| track_activities | boolean | 是否允许跟踪每个session正在执行的sql命令的信息和命令开始时间，pg_stat_activity表，默认开启 |
| track_activity_query_size | integer | pg_stat_activity试图query字段最多显示多少字节，默认：1024 |
| track_io_timing | boolean | 是否允许统计IO调用时间，默认关闭 |
| update_process_title | boolean | 当后台进程执行命令时，是否更新title信息；linux默认开启，使用ps命令看到后台服务进程是否正在执行命令 |
| stats_temp_directory | string | 设置存储临时数据路径，可以是相对路径也可以绝对路径；默认：pg_stat_tmp |
3.2. 手工收集统计信息
使用analyze 收集统计信息，存储到pg_statistic表
autovacuum 守护进程默认开启，可以自动收集统计信息，可以手动调整autovacuum执行频率
关于autovacuum相关参数配置：http://postgres.cn/docs/12/runtime-config-autovacuum.html
- 语法：
ANALYZE [VERBOSE] [table [column [,……]]]
- VERBOSE：显示处理进度，以及表的一些统计信息
- table：要分析的表名，不指明则对整个数据库的表作分析
- column：要分析特定字段的名称，不指明则分析所有字段
命令示范：
-- 分析t1的id2 列
ANALYZE t1(id2);
-- 分析t1的id2,id1 列
ANALYZE t1(id2,id1);
analyze 命令会在表上增加一个读锁，对于大表会抽样进行收集统计信息；
-- 收集某个列统计target值
alter table t1 alter column id2 set statistic 200;
-- 手工指定列收集统计信息的时候有多少唯一值
alter table t1 alter column id2 set (n_distinct=2000);
-- 对有继承关系的父表，可以指定n_distinct_inherited，可以同步到子表
alter table t1 alter column id2 set (n_distinct_inherited=2000);
