【数据库】
数据库 - 执行计划
1. 执行计划命令
explain
2. 命令参数
-
analyze：选项为TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE
-
buffers：buffers必须跟analyze一起使用，只有真实执行SQL才能获取缓冲区信息
-
costs：表示每一个计划节点的估计启动和总代价，以及估计的行数和每行的宽度
-
Verbose ：显示关于计划的额外信息 ，例如：节点输出的列（Output）
-
Format（text，xml，json，yaml）（格式化输出）
eg : EXPLAIN ( ANALYSE TRUE, BUFFERS TRUE, VERBOSE true,COSTS TRUE, FORMAT text )
3. 输出含义
| 输出参数 | 含义 |
|---|---|
| Cost | 这个计划节点的预计的启动开销和总开销 |
| Rows | 这个计划节点的预计输出行数 |
| Width | 这个计划节点返回行的预计平均宽度(以字节计算) |
| actual time | 这个计划节点的实际启动时间和总运行时间 |
| Loops | 这个计划节点的实际重启次数 |
| Filter | 这个扫描节点的过滤条件 |
| Index Cond | 这个索引扫描节点的索引匹配条件 |
| Rows Removed by Filter | 这个扫描节点通过过滤条件过滤掉的行数 |
| Seq Scan | 扫描表 |
| Index Scan | 索引扫描 |
| Bitmap Index Scan | 利用Bitmap 结构扫描 |
| Bitmap Heap Scan | 把BitmapIndex Scan 返回的Bitmap 结构转换为元组结构 |
| Subquery Scan | 子查询 |
| Cte Scan | 用于扫描WITH 字句的结果集 |
| WorkTable Scan | 用于扫描Recursive Union 的中间数据 |
| Function Scan | 函数扫描 |
| Nested Loop | 循环结合 |
| Merge Join | 合并结合 |
| Hash Join | 哈希结合 |
| Sort | 排序，ORDER BY操作 |
| Hash | 哈希运算 |
| Unique | DISTINCT，UNION操作 |
| Aggregate | count, sum,avg集约函数 |
4. 扫描
- 顺序扫描：也称为全表扫描，从头到尾扫描数据表，然后取出符合条件的数据
- 索引扫描：在索引中找出数据位置，然后再将数据取出
- 位图扫描：走索引，进行索引扫描，并且将符合条件的行的指针取出，存放在内存的位图里，扫描结束后，再对位图中的指针去读取数据块中的数据
5. join
-
嵌套循环（
Nested Loops）或循环联接 ,时间复杂度O(m*n)（补充：索引可以提高nested loop 的效率,O(M**log(N))）（1）A表提取一条记录，取B表查找相应的匹配记录，如果有，就把该条记录的信息推到等待返回的结果集中
（2）再去从A表中提取第二条记录，取B表中找匹配记录，如果有，就把记录信息返回结果集
（3）以此类推，直到A表中的数据全部被处理完成，将结果集返回，完成嵌套循环连接的操作
-
合并连接（
merge join）（1）A表先进行排序，形成临时表C
（2）B表排序，形成临时表D
（3）C与D进行合并操作，返回结果集
-
Hash连接（
hash join）（1）B表在内存建立一个散列表C
（2）从A表读取第一条记录，到C中查询匹配记录，若有，推到结果集中
（3）以此类推，直到A中没有记录，返回结果集
6. 影响执行计划的参数
# - Planner Method Configuration -#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_seqscan = on
#enable_sort = on
#enable_incremental_sort = on
#enable_tidscan = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_parallel_hash = on
#enable_partition_pruning = on
7. 执行计划原理
[参考](/)
（1）PG对各表的统计信息：pg_stats、pg_class、pg_statistic
（2）成本因子：PostgreSQL是基于代价模型来选择最优的执行计划的，而成本因子则是计算代价模型的最重要参数。（代价=CPU代价+IO代价+数据传输[如网络]代价）
postgresql.conf.sample中可设置
# - Planner Cost Constants -#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above#jit_above_cost = 100000 # perform JIT compilation if available# and query more expensive than this;# -1 disables
#jit_inline_above_cost = 500000 # inline small functions if query is# more expensive than this; -1 disables
#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if# query is more expensive than this;# -1 disables#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB
（3）PostgreSQL的查询优化器会选择代价最小的来执行
-> SQL输入
-> 解析SQL，获取解析后的语法树
-> 分析、重写语法树，获取查询树
-> 根据重写、分析后的查询树计算各路径代价，从而选择一条成本最优的执行树
-> 根据执行树进行执行
-> 获取结果并返回
8. 直方图
描述表中每列的数据分布
直方图信息存储在pg_statistic表中，可通过其视图pg_stats来查看
select tablename,attname,histogram_bounds from pg_statswhere tablename = '' and attname = '';