浅析PostgreSQL并行查询代价估算
并行查询特性最早是在PostgreSQL 9.6中引入。自那时起，社区一直在扩展该功能。在PostgreSQL 11和PostgreSQL 12中，社区开发人员向数据库引擎添加了更多的相关功能。本文就PostgreSQL查询优化器对并行执行代价估算的工作原理进行阐述，不足之处希望大家多多指正。
串行顺序扫描执行代价估算
为了方便解释，用一个简单的例子来说明问题。首先通过代码片段1创建一个表包含两列。
1. //代码片段1 2. mydb=# CREATE TABLE people (id int PRIMARY KEY NOT NULL, age int NOT NULL); 3. CREATE TABLE
为了演示并行查询带来的益处，利用代码片段2往表people插入一千万条数据，其中age字段是0-100之间的随机数。
1. //代码片段2 2. mydb=# INSERT INTO people 3. SELECT id, (random()*100)::integer AS age 4. FROM generate_series(1,10000000) AS id; 5. INSERT 0 10000000
默认情况下PostgreSQL是打开并行执行的。我们首先关闭并行执行开关，检查串行查询执行的耗时。通过将配置参数max_parallel_workers_per_gather设置为0可以关闭并行执行特性。从查询执行计划可以看到，顺序扫描的执行代价为144.248ms（Pg的执行计划显示的时间由两部分组成，‘..’前面的称之为start_up_cost,后面部分叫做run_cost）。总的查询执行代价为169.248ms。Pg如何计算得到这些数字的？
1. //代码片段3 2. mydb=# SET max_parallel_workers_per_gather TO 0; 3. SET 4. mydb=# explain analyze select count(*) from people; 5. QUERY PLAN 6. -------------------------------------------------------------------------------- 7. Aggregate (cost=169248.60..169248.61 rows=1 width=8) (actual time=1375.237..1375.237 rows=1 loops=1) 8. -> Seq Scan on people (cost=0.00..144248.48 rows=10000048 width=0) (actual time=0.036..837.964 rows=10000000 loops=1) 9. (4 rows)
Pg查询执行时间total_cost = start_up_cost + run_cost,具体来说pg的运行时的代价由以下的式子计算得到：
total_cost = cpu_run_cost + disk_run_cost
= (cpu_tuple_cost + cpu_operator_cost) × Ntuple + seq_page_cost×Npage (1)
其中cpu_tuple_cost是CPU传输该记录的代价；cpu_operator_cost指函数运算或其他操作符的开销。有了上述计算规则以后，可以通过Pg的元数据表pg_class查询表people相关统计信息,见代码片段4。可以得到Npage=44248，Ntuple = 10000048.Pg源代码的cost.h头文件定义了公式(1)相关的计算代价，见代码片段5：
1. //代码片段4 2. mydb=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'people'; 3. relpages | reltuples 4. ----------+--------------- 5. 44248 | 1.0000048e+07 6. (1 row)
计算代价如下：
1. //代码片段5 2. /* defaults for costsize.c's Cost parameters */ 3. /* NB: cost-estimation code should use the variables, not these constants! */ 4. /* If you change these, update backend/utils/misc/postgresql.sample.conf */ 5. #define DEFAULT_SEQ_PAGE_COST 1.0 6. #define DEFAULT_RANDOM_PAGE_COST 4.0 7. #define DEFAULT_CPU_TUPLE_COST 0.01 8. #define DEFAULT_CPU_INDEX_TUPLE_COST 0.005 9. #define DEFAULT_CPU_OPERATOR_COST 0.0025 10. #define DEFAULT_PARALLEL_TUPLE_COST 0.1 11. #define DEFAULT_PARALLEL_SETUP_COST 1000.0
将代码片段5的计算代价，放入公式(1)，我们可以得到顺序扫描的代价：
total_cost = (cpu_tuple_cost)×Ntuple + seq_page_cost×Npage
= 0.01×1.0000048e+07 + 1×44248
= 144248.48
这就是代码片段3第8行的顺序执行代价。而总的执行代价，需要对每条元组进行count函数操作，因此需要加上cpu_operator_cost,因此聚集函数count的查询执行start_up_cost为：
Start_up_cost = cpu_run_cost + disk_run_cost
= (cpu_tuple_cost + cpu_operator_cost)×Ntuple + seq_page_cost×Npage (1)
= (0.01 + 0.0025) ×1.0000048e+07 + 1×44248
= 169248.6 （代码片段3第7行所示）
最后，系统只需要对这条元组传输即可，因此总的查询执行代价为：
total_cost = (cpu_tuple_cost)×Ntuple + start_up_cost
= 0.01 + 169248.6
= 169248.61（代码片段3第7行所示）
并行顺序扫描执行代价估算
现在我们将并行执行的特性打开令max_parallel_workers_per_gather=2，相应的执行计划如下：
1. //代码片段6 2. mydb=# set max_parallel_workers_per_gather = 2; 3. SET 4. mydb=# explain analyze select count(*) from people; 5. QUERY PLAN 6. --------------------------------------------------------------------------------------------------------------- 7. Finalize Aggregate (cost=97331.80..97331.81 rows=1 width=8) (actual time=524.896..524.896 rows=1 loops=1) 8. -> Gather (cost=97331.58..97331.79 rows=2 width=8) (actual time=524.828..526.162 rows=3 loops=1) 9. Workers Planned: 2 10. Workers Launched: 2 11. -> Partial Aggregate (cost=96331.58..96331.59 rows=1 width=8) (actual time=522.605..522.606 rows=1 loops=3) 12. -> Parallel Seq Scan on people (cost=0.00..85914.87 rows=4166687 width=0) (actual time=0.033..317.765 rows=3333333 loops=3) 13. Planning Time: 0.307 ms 14. Execution Time: 526.209 ms 15. (8 rows)
为了解释并行查询执行代价，我们首先看看不同进程（为什么用进程是历史原因）之间工作量的分配。当并发进程只有两个的时候，主进程需要将大部分时间花费在执行工作，当并行进程的增加，主进程逐渐将工作转向聚合部分结果。Pg的进程工作量分配正是建立在这个基础之上。具体的分配算法见代码片段7第22行-27行。并行执行的框架如图1所示。
1. //代码片段7
2. static double
3. get_parallel_divisor(Path *path)
4. {
5. double parallel_divisor = path->parallel_workers;
6.
7. /*
8. * Early experience with parallel query suggests that when there is only
9. * one worker, the leader often makes a very substantial contribution to
10. * executing the parallel portion of the plan, but as more workers are
11. * added, it does less and less, because it's busy reading tuples from the
12. * workers and doing whatever non-parallel post-processing is needed. By
13. * the time we reach 4 workers, the leader no longer makes a meaningful
14. * contribution. Thus, for now, estimate that the leader spends 30% of
15. * its time servicing each worker, and the remainder executing the
16. * parallel plan.
17. */
18. if (parallel_leader_participation)
19. {
20. double leader_contribution;
21.
22. leader_contribution = 1.0 - (0.3 * path->parallel_workers);
23. if (leader_contribution > 0)
24. parallel_divisor += leader_contribution;
25. }
26.
27. return parallel_divisor;
28.
图1.并行查询框架
因此当并行执行的worker为2的时候，每个工作进程所分配得到的工作量为：10000048.0 / (2 + (1 – 0.3 * 2)) = 4166686.66 rows.这就是代码片段6第13行得到每个worker处理的元组(rows)数目。这里需要注意的是虽然是并行扫描但是IO代价仍然是按照全表扫描的代价计算。这主要是因为每个并发进程在同一份数据上按照block-by-block的方式顺序推进全局next指针，这等价于全表扫描。
总结
PostgreSQL的口号是开源数据库中最强大的关系型数据库系统。其实业界也一直将其称为开源数据库的oracle。此言不虚，因为很多企业客户在去“O”问题上选择用PostgreSQL替代Oracle。PostgreSQL不仅在语法上高度兼容Oracle而且在性能上也向Oracle靠拢，例如并行查询特性就是PostgreSQL引以为豪的特性之一。PostgreSQL实现了并行scan，merge-join，hash-join，以及partition-join等。这篇文章对PostgreSQL的并行查询进行了初步的分析，探讨了并行工作进程之间的工作量分配，查询执行代价的计算，以及并行查询的总体框架。后续我们计划对PostgreSQL的并行join算法进行深入探讨以更好地了解PostgreSQL的并行查询功能.
- 点赞
- 收藏
- 关注作者
评论（0）