执行计划解决-part4
我将涵盖通常出现在 explain 输出中的其余操作。
Unique
从名称来看，这个操作的作用似乎很明确——它用于去除重复的数据。
例如，当你执行以下操作时，这种情况可能会发生：
select distinct field from table;
但在较新的数据库版本中，这个查询通常会使用 HashAggregate 来完成。
openGauss=# select version();
version
--------------------------------------------------------------------------------------------------------------------------------
----------------------
(openGauss 3.0.5 build 5564ce8e) compiled at 2023-08-16 09:17:31 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g+
+ (GCC) 7.3.0, 64-bit
(1 row)
openGauss=# explain analyze select distinct relname from pg_class ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
HashAggregate (cost=65.59..75.63 rows=1004 width=64) (actual time=2.968..3.171 rows=1005 loops=1)
Group By Key: relname
-> Seq Scan on pg_class (cost=0.00..63.07 rows=1007 width=64) (actual time=0.111..2.561 rows=1008 loops=1)
Total runtime: 3.380 ms
(4 rows)
Unique 操作的问题在于，它要求数据必须是已排序的。这并不是因为它需要某种特定的顺序，而是为了确保所有具有相同值的行能够“排列在一起”，从而方便去重处理——只要相邻行不重复，就能保证全局唯一。
这使得它在能够使用的情况下非常高效，因为它几乎不消耗任何内存。它只是检查当前行的值是否与前一行的值相同，如果相同，就丢弃当前行。仅此而已。
因此，我们可以通过预先排序数据来强制使用它：
$ explain select distinct relkind from (select relkind from pg_class order by relkind) as x;
QUERY PLAN
-----------------------------------------------------------------------
Unique (cost=22.88..27.26 rows=4 width=1)
-> Sort (cost=22.88..23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1)
(4 rows)
Append
这个执行计划会依次运行多个子操作，并将所有子操作返回的行合并为一个结果集返回。
这是 UNION/UNION ALL 查询使用的：
$ explain select oid from pg_class union all select oid from pg_proc union all select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..104.43 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(4 rows)
在这里你可以看到 append 对三个表运行了三次扫描，并将所有行一起返回。
请注意我使用了 UNION ALL。如果我使用 UNION，我们会得到：
$ explain select oid from pg_class union select oid from pg_proc union select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=141.22..170.65 rows=2943 width=4)
-> Append (cost=0.00..133.86 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(5 rows)
这是因为 UNION 会移除重复行——在这个案例中，这是通过 HashAggregate操作完成的。
Result
这通常发生在非常简单的测试查询中。当你的查询选择某个常量值（或多个值）时，会使用这个操作。
$ explain select 1, 2;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 row)
除了测试查询之外，这种执行计划有时也会出现在实现“插入数据，但如果会引发重复则不插入”这类逻辑的查询中：
$ explain insert into t (i) select 1 where not exists (select * from t where i = 1);
QUERY PLAN
---------------------------------------------------------------------
Insert on t (cost=3.33..3.35 rows=1 width=4)
-> Result (cost=3.33..3.34 rows=1 width=0)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on t t_1 (cost=0.00..40.00 rows=12 width=0)
Filter: (i = 1)
(6 rows)
Values Scan 值扫描
就像上面的Result一样，值扫描用于返回查询中输入的简单数据，但这次——它可以基于 VALUES()功能返回整个记录集。
如果你不知道的话，你可以使用 VALUES语法，不借助任何表格，选择多行多列，就像这里：
$ select * from ( values (1, 'hubert'), (2, 'depesz'), (3, 'lubaczewski') ) as t (a,b);
a | b
---+-------------
1 | hubert
2 | depesz
3 | lubaczewski
(3 rows)
这样的查询计划看起来像：
QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
(1 row)
它也最常用于 INSERT 语句，但它还有其他用途，比如自定义排序(How to order by some random – query defined – values? – select * from depesz;)。
GroupAggregate 分组聚合
这与之前描述的 HashAggregate 类似。
不同之处在于，为了使 GroupAggregate 工作，必须使用 GROUP BY 子句中使用的列（无论多少列）对数据进行排序。
和 Unique 一样，GroupAggregate 使用的内存非常少，但强制要求对数据进行排序。
示例：
$ explain select relkind, count(*) from (select relkind from pg_class order by relkind) x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------
GroupAggregate (cost=22.88..28.03 rows=4 width=1)
-> Sort (cost=22.88..23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1)
(4 rows)
HashSetOp
此操作由 INTERSECT/EXCEPT 操作（可选的ALL修饰符）使用。
它通过为一对子查询运行 Append 的子操作，然后根据结果以及可选的 ALL 修饰符，判断哪些行应该被返回。我尚未深入研究其源代码，因此无法确切说明其实现机制，但根据其名称和行为来看，这似乎是一种基于计数器的简单解决方案。
在这里我们可以看到，与 UNION 不同，这些操作基于两个数据源进行工作：
$ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4)
-> Append (cost=0.15..163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4)
(6 rows)
但如果有三个数据源，我们就会得到一个更复杂的树形结构：
$ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y intersect all select * from (Select oid from pg_database order by oid) as w;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=1.03..172.53 rows=2 width=4)
-> Append (cost=1.03..171.79 rows=294 width=4)
-> Subquery Scan on "*SELECT* 3" (cost=1.03..1.07 rows=2 width=4)
-> Sort (cost=1.03..1.03 rows=2 width=4)
Sort Key: pg_database.oid
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
-> Result (cost=0.15..170.72 rows=292 width=4)
-> HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4)
-> Append (cost=0.15..163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4)
(13 rows)
CTE Scan
这类似于之前提到的物化操作。它运行查询的一部分，并将输出结果存储起来，以便查询的其他部分（或多个部分）可以使用。
示例：
$ explain analyze with x as (select relname, relkind from pg_class) select relkind, count(*), (select count(*) from x) from x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
HashAggregate (cost=24.80..26.80 rows=200 width=1) (actual time=0.466..0.468 rows=6 loops=1)
CTE x
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65) (actual time=0.009..0.127 rows=295 loops=1)
InitPlan 2 (returns $1)
-> Aggregate (cost=6.57..6.58 rows=1 width=0) (actual time=0.085..0.085 rows=1 loops=1)
-> CTE Scan on x x_1 (cost=0.00..5.84 rows=292 width=0) (actual time=0.000..0.055 rows=295 loops=1)
-> CTE Scan on x (cost=0.00..5.84 rows=292 width=1) (actual time=0.012..0.277 rows=295 loops=1)
Total runtime: 0.524 ms
(8 rows)
请注意，pg_class 表仅被扫描了一次（第6行），但其结果被存储在“x”中，随后被扫描了两次——一次在聚合操作内部（第9行），另一次在 HashAggregate 中（第10行）。
它与 Materialize 有何不同？要完整回答这个问题，可能需要深入源码，但在我看来，主要区别在于：CTE 是由用户定义的，而 Materialize 是数据库在认为有必要时自动选择使用的一种辅助操作。
非常重要的一点是，CTE 会严格按照其定义的方式执行。因此，它们可以用来规避优化器在某些情况下可能做出的不太理想的优化决策。
InitPlan
当你的查询中存在某个部分必须（或可以）在其他操作之前计算，并且该部分不依赖于查询中其他任何内容时，就会出现这种执行计划。
$ explain select * from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.11..24.76 rows=73 width=203)
Filter: (relkind = $0)
InitPlan 1 (returns $0)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.11..13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(7 rows)
在这种情况下，必须先执行子查询中的imit/sort/seq-scan操作，然后才能开始对 pg_class 的常规顺序扫描——因为 数据库需要将每一行的 relkind值与子查询返回的值进行比较。
另一方面，如果我写:
$ explain select *, (select length('depesz')) from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.01..10.93 rows=292 width=203)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
数据库正确地识别出该子查询列不依赖于 pg_class 表中的任何数据，因此它可以只执行一次，而无需为每一行重复执行长度计算。
当然你可以有多个 init 计划，就像这里一样：
$ explain select *, (select length('depesz')) from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.12..24.77 rows=73 width=203)
Filter: (relkind = $1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
InitPlan 2 (returns $1)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.11..13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(9 rows)
不过，有一点非常重要——单个查询中 InitPlan 的编号是全局的，而不是按操作独立编号的。
SubPlan
SubPlans 与 NestedLoop 有些相似。这种方式下，它们可以被多次调用。
SubPlan 被调用以计算子查询中的数据，而实际上这些数据依赖于当前行。
例如：
$ explain analyze select c.relname, c.relkind, (Select count(*) from pg_Class x where c.relkind = x.relkind) from pg_Class c;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on pg_class c (cost=0.00..3468.93 rows=292 width=65) (actual time=0.135..26.717 rows=295 loops=1)
SubPlan 1
-> Aggregate (cost=11.83..11.84 rows=1 width=0) (actual time=0.090..0.090 rows=1 loops=295)
-> Seq Scan on pg_class x (cost=0.00..11.65 rows=73 width=0) (actual time=0.010..0.081 rows=93 loops=295)
Filter: (c.relkind = relkind)
Rows Removed by Filter: 202
Total runtime: 26.783 ms
(7 rows)
对于 pg_class as c 扫描返回的每一行，数据库都必须执行一次子计划（SubPlan），该子计划会检查 pg_class 表中与当前处理行的 relkind 列值相同的行有多少条。
请注意，Seq Scan on pg_class x 这一行中的 loops=295，与前面 Seq Scan on pg_class c 节点中的 rows=295 相对应。这表示该扫描操作被执行了 295 次（每次外层循环一行），总共处理了 295 行数据。
