详解
如SQL执行计划概述节中所说,EXPLAIN会显示执行计划,但并不会实际执行SQL语句。EXPLAIN ANALYZE和EXPLAIN PERFORMANCE两者都会实际执行SQL语句并返回执行信息。在这一节将详细解释执行计划及执行信息。
执行计划
以如下SQL语句为例:
1
|
SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2; |
执行EXPLAIN的输出为:
执行计划层级解读(纵向):
- 第一层:Seq Scan on t2
表扫描算子,用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从buffer或者磁盘上读上来输送给上层节点参与计算。
- 第二层:Hash
- 第三层:Seq Scan on t1
表扫描算子,用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从buffer或者磁盘上读上来输送给上层节点参与hash join计算。
- 第四层:Hash Join
执行计划中的主要关键字说明:
- 表访问方式
- Seq Scan
- Index Scan
优化器决定使用两步的规划:最底层的规划节点访问一个索引,找出匹配索引条件的行的位置,然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多,但是因为并非所有表的页面都被访问了,这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是,上层规划节点在读取索引标识出来的行位置之前,会先将它们按照物理位置排序,这样可以最小化独立抓取的开销。
如果在WHERE里面使用的好几个字段上都有索引,那么优化器可能会使用索引的AND或OR的组合。但是这么做要求访问两个索引,因此与只使用一个索引,而把另外一个条件只当作过滤器相比,这个方法未必是更优。
根据索引排序机制的差异,索引扫描可以分为以下几类。
- Bitmap Heap Scan
- TID Scan
- Index Ctid Scan
- CTE Scan
- Foreign Scan
- Function Scan
- Sample Scan
- Subquery Scan
- Values Scan
- WorkTable Scan
- CStore Index Ctid Scan
- CStore Index Heap Scan
- 表连接方式
- Nested Loop
嵌套循环,适用于被连接的数据子集较小的查询。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(不能大于10000),要把返回子集较小的表作为外表,而且在内表的连接字段上建议要有索引。
- (Sonic) Hash Join
哈希连接,适用于数据量大的表的连接方式。优化器使用两个表中较小的表,利用连接键在内存中建立hash表,然后扫描较大的表并探测散列,找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同,不影响执行的结果集。
- Merge Join
归并连接,通常情况下执行性能差于哈希连接。如果源数据已经被排序过,在执行融合连接时,并不需要再排序,此时融合连接的性能优于哈希连接。
- Nested Loop
- 运算符
- sort
- filter
EXPLAIN输出显示WHERE子句当作一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件,并且只输出符合条件的行。预计的输出行数降低了,因为有WHERE子句。不过,扫描仍将必须访问所有 10000 行,因此开销没有降低;实际上它还增加了一些(确切的说,通过10000 * cpu_operator_cost)以反映检查WHERE条件的额外CPU时间。
- LIMIT
- Append
- Aggregate
- BitmapAnd
- BitmapOr
- Gather
- Group
- GroupAggregate
- Hash
- HashAggregate
- Merge Append
- Recursive Union
- SetOp
- Unique
-
一种用于 INTERSECT 或 EXCEPT 等集合操作的策略,它使用 Append 来避免预排序的输入。
- LockRows
- Materialize
- Result
- WindowAgg
- Merge
- StartWith Operator
- Index Cond
- Cstore Index And
- Cstore Index Or
- 其他关键字
执行信息
以如下SQL语句在pretty模式下的执行结果为例:
select sum(t2.c1) from t1,t2 where t1.c1=t2.c2 group by t1.c2;
执行EXPLAIN PERFORMANCE输出为:
explain performance select sum(t2.c1) from t1,t2 where t1.c1=t2.c2 group by t1.c2; id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | A-width | E-width | E-costs ----+------------------------------------+--------+--------+--------+------------+-------------+---------+---------+------------------ 1 | -> HashAggregate | 0.574 | 0 | 200 | | 29KB | | 8 | 396.113..398.113 2 | -> Hash Join (3,4) | 0.358 | 0 | 18915 | 200, 200 | 12KB | | 8 | 53.763..301.538 3 | -> Seq Scan on public.t1 | 0.037 | 1 | 1945 | | 22KB | | 8 | 0.000..29.450 4 | -> Hash | 0.038 | 0 | 1945 | | 264KB | | 8 | 29.450..29.450 5 | -> Seq Scan on public.t2 | 0.029 | 30 | 1945 | | 22KB | | 8 | 0.000..29.450 (5 rows) Predicate Information (identified by plan id) ----------------------------------------------- 2 --Hash Join (3,4) Hash Cond: (t1.c1 = t2.c2) (2 rows) Memory Information (identified by plan id) -------------------------------------------------- 1 --HashAggregate Peak Memory: 29KB, Estimate Memory: 64MB 2 --Hash Join (3,4) Peak Memory: 12KB, Estimate Memory: 64MB 3 --Seq Scan on public.t1 Peak Memory: 22KB, Estimate Memory: 64MB 4 --Hash Peak Memory: 264KB Buckets: 32768 Batches: 1 Memory Usage: 0kB 5 --Seq Scan on public.t2 Peak Memory: 22KB, Estimate Memory: 64MB (11 rows) Targetlist Information (identified by plan id) ------------------------------------------------ 1 --HashAggregate Output: sum(t2.c1), t1.c2 Group By Key: t1.c2 2 --Hash Join (3,4) Output: t1.c2, t2.c1 3 --Seq Scan on public.t1 Output: t1.c1, t1.c2, t1.c3 4 --Hash Output: t2.c1, t2.c2 5 --Seq Scan on public.t2 Output: t2.c1, t2.c2 (11 rows) Datanode Information (identified by plan id) ---------------------------------------------------------------------------------------------------------- 1 --HashAggregate (actual time=0.574..0.574 rows=0 loops=1) (Buffers: shared hit=2) (CPU: ex c/r=0, ex row=0, ex cyc=527797, inc cyc=8385141377087373) 2 --Hash Join (3,4) (actual time=0.358..0.358 rows=0 loops=1) (Buffers: shared hit=2) (CPU: ex c/r=-8385141375712241, ex row=1, ex cyc=-8385141375712241, inc cyc=8385141376559576) 3 --Seq Scan on public.t1 (actual time=0.037..0.037 rows=1 loops=1) (Buffers: shared hit=1) (CPU: ex c/r=8385141375728512, ex row=1, ex cyc=8385141375728512, inc cyc=8385141375728512) 4 --Hash (actual time=0.038..0.038 rows=0 loops=1) (Buffers: shared hit=1) (CPU: ex c/r=0, ex row=0, ex cyc=-251554241295571040, inc cyc=8385141376543305) 5 --Seq Scan on public.t2 (actual time=0.019..0.029 rows=30 loops=1) (Buffers: shared hit=1) (CPU: ex c/r=8664646089070478, ex row=30, ex cyc=259939382672114336, inc cyc=259939382672114336) (20 rows) ====== Query Summary ===== ---------------------------------------- Datanode executor start time: 0.180 ms Datanode executor run time: 0.590 ms Datanode executor end time: 0.051 ms Planner runtime: 0.366 ms Query Id: 844424930141239 Total runtime: 0.866 ms (6 rows)
上述示例中显示执行信息分为以下6个部分:
- 以表格的形式将计划显示出来,包含有11个字段,分别是:id、operation、A-time、A-rows、E-rows、E-distinct、Peak Memory、E-memory、A-width、E-width和E-costs。其中计划类字段(id、operation以及E开头字段)的含义与执行EXPLAIN时的含义一致,详见执行计划小节中的说明。A-time、A-rows、E-distinct、Peak Memory、A-width的含义说明如下:
- A-time:当前算子执行完成时间。
- A-rows:表示当前算子的实际输出元组数。
- E-distinct:表示hashjoin算子的distinct估计值。
- Peak Memory:此算子在执行时使用的内存峰值。
- A-width:表示当前算子每行元组的实际宽度,仅对于重内存使用算子会显示,包括:(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等,其中(Vec)HashJoin计算的宽度是其右子树算子的宽度,会显示在其右子树上。
- Predicate Information (identified by plan id):
- Memory Information (identified by plan id):
这一部分显示的是整个计划中会将内存的使用情况打印出来的算子的内存使用信息,主要是Hash、Sort算子,包括算子峰值内存(peak memory),控制内存(control memory),估算内存使用(operator memory),执行时实际宽度(width),内存使用自动扩展次数(auto spread num),是否提前下盘(early spilled),以及下盘信息,包括重复下盘次数(spill Time(s)),内外表下盘分区数(inner/outer partition spill num),下盘文件数(temp file num),下盘数据量及最小和最大分区的下盘数据量(written disk IO [min, max] )。
- Targetlist Information (identified by plan id)
- DataNode Information (identified by plan id):
- ====== Query Summary =====:
这一部分主要打印总的执行时间和网络流量,包括了初始化和结束阶段的最大最小执行时间,以及当前语句执行时系统可用内存、语句估算内存等信息。