PG 执行计划
执行计划解释
|
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] SETTINGS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] SUMMARY [ boolean ] FORMAT { TEXT | XML | JSON | YAML } |
- ANALYZE：真正执行SQL，默认:F
- VERBOSE：显示计划的附加消息，包括：计划树每个节点的输出的各列、触发器名字（如有），默认：F
- COSTS:每个计划节点的启动和总成本，以及估计行数和每行宽度。默认:T
- SETTINGS：包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值与内置默认值不同。此参数默认为FALSE
- BUFFERS：显示关于缓冲区的使用消息，需要与analyze一同使用；显示的缓冲区信息包括：共享块、本地块和临时块读和写的块数；共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。
代价参数
|
seq_page_cost (floating point) 设置规划器计算一次顺序磁盘页面抓取的开销。默认值是1.0。 通过设置同名的表空间参数（ALTER TABLESPACE），这个值可以重写为一个特定的表空间。 random_page_cost (floating point) 设置规划器对一次非顺序获取磁盘页面的代价估计。默认值是 4.0，可以设置表空间级别。减少这个值（相对于seq_page_cost）将导致系统更倾向于索引扫描；提高它将让索引扫 描看起来相对更昂贵。你可以一起提高或降低两个值来改变磁盘 I/O 代价相对于 CPU 代价的重要性。在一个高度缓存化的数据库中，你应该相对于 CPU 参数降低这两个值，因为获取一个已经在 RAM 中的页面的代价要远小于通常情况下的代价。 cpu_tuple_cost (floating point) 设置规划器对一次查询中处理每一行的代价估计。默认值是 0.01 cpu_index_tuple_cost (floating point) 设置规划器对一次索引扫描中处理每一个索引项的代价估计。默认值是 0.005。 cpu_operator_cost (floating point) 设置规划器对于一次查询中处理每个操作符或函数的代价估计。默认值是 0.0025。 parallel_setup_cost (floating point) 设置规划器对启动并行工作者进程的代价估计。默认是 1000。 parallel_tuple_cost (floating point) 设置规划器对于从一个并行工作者进程传递一个元组给另一个进程的代价估计。默认是 0.1。 min_parallel_table_scan_size (integer) 为必须扫描的表数据量设置一个最小值，扫描的表数据量超过这一个值才会考虑使用并行扫描。如果指定值时没有单位，则以块为单位，即BLCKSZ字节，通常为8kB。默认值是8兆字节（8MB）。 min_parallel_index_scan_size (integer) 为必须扫描的索引数据量设置一个最小值，扫描的索引数据量超过这一个值时才会考虑使用并行扫描。 注意并行索引扫描通常并不会触及整个索引，它是规划器认为该扫描会实际用到的相关页面的数量。 如果指定值时没有单位，则以块为单位，即BLCKSZ字节， 通常为8kB。默认值是512千字节（512kB）。 effective_cache_size (integer) 设置规划器对一个单一查询可用的有效磁盘缓冲区尺寸的假设。 这个参数会被考虑在使用一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。 在设置这个参数时，你还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区，尽管有些数据可能在两个地方都存在。 另外，还要考虑预计在不同表上的并发查询数目，因为它们必须共享可用的空间。 这个参数对PostgreSQL分配的共享内存尺寸没有影响，它也不会保留内核磁盘缓冲，它只用于估计的目的。系统也不会假设在查询之间数据会保留在磁盘缓冲中。 如果指定值时没有单位，则以块为单位，即BLCKSZ字节，通常为8kB。 默认值是 4吉字节（4GB）。 jit_above_cost (floating point) 设置激活JIT编译的查询代价，如果查询代价超过这个值就会激活JIT编译。执行JIT会消耗一些规划时间，但是能够加速查询执行。将这个值设置为-1会禁用JIT编译。默认值是100000。 jit_inline_above_cost (floating point) 设置JIT编译尝试内联函数和操作符的查询代价阈值，如果查询代价超过这个值，JIT编译就会尝试内联。内联会增加规划时间，但是可以提高执行速度。将这个参数设置成小于jit_above_cost是没有意义的。将这个参数设置为-1会禁用内联。默认值是500000。 jit_optimize_above_cost (floating point) 设置JIT编译应用优化的查询代价阈值，如果查询代价超过这个值，JIT编译就会应用开销较大的优化。这类优化会增加规划时间，但是更能够改进执行速度。将这个参数设置成小于jit_above_cost是没有意义的，并且将它设置成大于jit_inline_above_cost也未必有益。将这个参数设置为-1会禁用开销较大的优化。默认值是500000。 |
GEQO(遗传查询优化)
GEQO是一个使用探索式搜索来执行查询规划的算法。它可以降低负载查询的规划时间。 同时，GEQO的检索是随机的，因此它的规划可能会不可确定。遗传查询规划器（GEQO）是一种使用启发式搜索来进行查询规划的算法。它可以降低对于复杂查询（连接很多表的查询）的规划时间，但是代价是它产生的计划有时候要差于使用穷举搜索算法找到的计划。
PostgreSQL中GEQO实现的特点有：
• 一种稳态 GA（遗传算法）（在种群中替换适应度最差的个体，而不是整代替换）的使用允许对改进的
查询计划快速收敛。这对在合理时间内处理查询是最重要的；
• 边重组杂交的使用特别适合于通过GA为TSP的解决方案保持低丢边率；
• 遗传操作符变异被废弃，这样不需要修补机制来产生合法的TSP旅行。
GEQO模块的一部分是从 D. Whitley 的遗传算法中改编而来。
GEQO模块允许PostgreSQL查询优化器支持通过非穷举搜索高效地处理大量连接的查询。
遗传算法会丢弃最不适应的候选。然后通过组合更适合的候选的基因来产生新的候选 — 即使从已知代价低的连接序列随机选择片段来创建用于考虑的新序列。这个处理将被重复，直到已经考虑的连接序列的数量达到一个预设值。然后在搜索中任何时候找到的最好的一个将被用来产生最终的计划。
|
geqo (boolean) 允许或禁止遗传查询优化。默认是启用。 geqo_threshold (integer) 只有当涉及的FROM项数量至少有这么多个的时候，才使用遗传查询优化（注意一个FULLOUTER JOIN只被计为一个FROM项）。默认值是 12。对于更简单的查询，通常会使用普通的穷举搜索规划器 geqo_effort (integer) 控制 GEQO 里规划时间和查询规划的有效性之间的平衡。这个变量必须是 一个范围从 1到 10 的整数。缺省值是 5，更大的值会增加花在查询规划上的时间，但是同时也增加了选择一个高效查询计划的可能性。 geqo_pool_size (integer) 控制 GEQO 使用的池尺寸，它就是遗传种群中的个体数目。它必须至少为 2，且有用的 值通常在 100 到 1000 之间。如果它被设置为零（默认设置）则会基于geqo_effort和 查询中表的数量选择一个合适的值。 geqo_generations (integer) 控制 GEQO 使用的子代数目。子代的意思是算法的迭代次数。它必须至少是1 ，有用的值范围和池大小相同。如果设置为零(缺省)，那么将基于 geqo_pool_size选取合适的值。 geqo_selection_bias (floating point) 控制 GEQO 使用的选择偏好。选择偏好是种群中的选择压力。值可以是 1.5 到 2.0 之间，后者是默认值。 geqo_seed (floating point) 控制 GEQO 使用的随机数生成器的初始值，随机数生成器用于在连接顺序搜索空间中选择随机路径。该值可以从 0 （默认值）到 1。变化该值会改变被探索的连接路径集合，并且可能导致找到一个更好或更差的路径。 |
其它执行计划配置项
执行计划配置
|
enable_bitmapscan (boolean) 允许或禁止查询规划器使用位图扫描计划类型。默认值是on。 enable_gathermerge (boolean) 启用或者禁用查询规划器对收集归并计划类型的使用。默认值是on。 enable_hashagg (boolean) 允许或禁用查询规划器使用哈希聚集计划类型。默认值是on。 enable_hashjoin (boolean) 允许或禁止查询规划器使用哈希连接计划类型。默认值是on。 enable_indexscan (boolean) 允许或禁止查询规划器使用索引扫描计划类型。默认值是on。 enable_indexonlyscan (boolean) 允许或禁止查询规划器使用只用索引扫描计划类型（见第 11.9 节）。默认值是on。 enable_material (boolean) 允许或者禁止查询规划器使用物化。它不可能完全禁用物化，但是关闭这个变量将阻止 规划器插入物化节点，除非为了保证正确性。默认值是on。 enable_mergejoin (boolean) 允许或禁止查询规划器使用归并连接计划类型。默认值是on。 enable_nestloop (boolean) 允许或禁止查询规划器使用嵌套循环连接计划。它不可能完全禁止嵌套循环连接，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是on。 enable_parallel_append (boolean) 允许或禁止查询规划器使用并行追加计划类型。默认值是on。 enable_parallel_hash (boolean) 允许或禁止查询规划器对并行哈希使用哈希连接计划类型。如果哈希连接计划也没有启用，这个参数没有效果。默认值是on。 enable_partition_pruning (boolean) 允许或者禁止查询规划器从查询计划中消除一个分区表的分区。这也控制着规划器产生允许执行器在查询执行期间移除（忽略）分区的查询计划的能力。默认值是on。详情请参考第 5.11.4 节。 enable_partitionwise_join (boolean) 允许或者禁止查询规划器使用面向分区的连接，这使得分区表之间的连接以连接匹配的分区的方式来执行。面向分区的连接当前只适用于连接条件包括所有分区键的情况，连接条件必须是相同的数据类型并且子分区集合要完全匹配。由于面向分区的连接规划在规划期间会使用可观的CPU时间和内存，所以默认值为off。 enable_partitionwise_aggregate (boolean) 允许或者禁止查询规划器使用面向分区的分组或聚集，这使得在分区表上的分组或聚集可以在每个分区上分别执行。如果GROUP BY子句不包括分区键，只有部分聚集能够以基于每个分区的方式执行，并且finalization必须最后执行。由于面向分区的分组或聚集在规划期间会使用可观的CPU时间和内存，所以默认值为off。 enable_seqscan (boolean) 允许或禁止查询规划器使用顺序扫描计划类型。它不可能完全禁止顺序扫描，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是on。 enable_sort (boolean) 允许或禁止查询规划器使用显式排序步骤。它不可能完全禁止显式排序，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是on。 enable_tidscan (boolean) 允许或禁止查询规划器使用TID扫描计划类型。默认值是on。 |
执行计划节点类型
在PostgreSQL的执行计划中，是自上而下阅读的，通常执行计划会有相关的索引来表示不同的计划节点，其中计划节点类型分为四类：控制节点（Control Node），扫描节点（Scan Node），物化节点（Materialization Node），连接节点（Join Node）。
|
控制节点：append，组织多个字表或子查询的执行节点，主要用于union操作。 扫描节点：用于扫描表等对象以获取元组 Seq Scan（全表扫描）：把表的所有数据块从头到尾读一遍，筛选出符合条件的数据块； Index Scan（索引扫描）：为了加快查询速度，在索引中找到需要的数据行的物理位置，再到表数据块中把对应数据读出来，如B树，GiST，GIN，BRIN，HASH Bitmap Index/Heap Scan（位图索引/结果扫描）：把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图列表的数据文件把对应的数据读出来，先通过Bitmap Index Scan在索引中找到符合条件的行，在内存中建立位图，之后再到表中扫描Bitmap Heap Scan。 物化节点：能够缓存执行结果到缓存中，即第一次被执行时生成的结果元组缓存，等待上层节点使用，例如，sort节点能够获取下层节点返回的所有元组并根据指定的属性排序，并将排序结果缓存，每次上层节点取元组时就从缓存中按需读取。 Materialize：对下层节点返回的元组进行缓存（如连接表时） Sort：对下层返回的节点进行排序（如果内存超过iwork_mem参数指定大小，则节点工作空间切换到临时文件，性能急剧下降） Group：对下层排序元组进行分组操作 Agg：执行聚集函数（sum/max/min/avg） 条件过滤，一般在where后加上过滤条件，当扫描数据行时，会找出满足过滤条件的行，条件过滤在执行计划里面显示Filter，如果条件的列上面有索引，可能会走索引，不会走过滤。 连接节点：对应于关系代数中的连接操作，可以实现多种连接方式（条件连接/左连接/右连接/全连接/自然连接） Nestedloop Join（嵌套连接）： 内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大，要把返回子集较小的表作为外表，且内表的连接字段上要有索引。 执行过程为，确定一个驱动表（outer table），另一个表为inner table，驱动表中每一行与inner table中的相应记录关联； Hash Join（哈希连接）：优化器使用两个比较的表，并利用连接属性在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行； Merge Join（合并连接）：通常hash连接的性能要比merge连接好，但如果源数据上有索引，或结果已经被排过序，这时merge连接性能会优于hash连接； |
运算类型
|
运算类型 |
操作说明 |
是否有启动时间 |
|
Seq Scan |
顺序扫描表 |
无启动时间 |
|
Index Scan |
索引扫描 |
无启动时间 |
|
Bitmap Index Scan |
索引扫描 |
有启动时间 |
|
Bitmap Heap Scan |
索引扫描 |
有启动时间 |
|
Subquery Scan |
子查询 |
无启动时间 |
|
Tid Scan |
行号检索 |
无启动时间 |
|
Function Scan |
函数扫描 |
无启动时间 |
|
Nested Loop Join |
嵌套连接 |
无启动时间 |
|
Merge Join |
合并连接 |
有启动时间 |
|
Hash Join |
哈希连接 |
有启动时间 |
|
Sort |
排序(order by) |
有启动时间 |
|
Hash |
哈希运算 |
有启动时间 |
|
Result |
函数扫描，和具体的表无关 |
无启动时间 |
|
Unique |
distinct/union |
有启动时间 |
|
Limit |
limit/offset |
有启动时间 |
|
Aggregate |
count, sum,avg等聚集函数 |
有启动时间 |
|
Group |
group by |
有启动时间 |
|
Append |
union操作 |
无启动时间 |
|
Materialize |
子查询 |
有启动时间 |
|
SetOp |
intersect/except |
有启动时间 |
