PostgreSQL中并行查询代价估算方法__在PostgresSQL9.6中引入了并行查询，从那之后这一功能不断地得到扩展。在PostgreSQL11和PostgreSQL12中，数据库引擎增加了更多的功能。然而，仍然有一些与并行查询相关的问题，这些问题经常出现在培训过程中，因此有必要澄清说明一下。: 世界上功能最强大的开源数据库...

作者：Hans-Jürgen Schönig

译者: 陈雁飞崔鹏

作者简介

Hans-Jürgen Schönig：从20世纪90年代开始使用PostgreSQL，担任CYBERTEC公司的CEO和技术主管（www.cybertec-postgresql.com）， CYBERTEC是该领域的市场领导者之一，自2000年以来已为全球无数客户提供服务。

译者简介

陈雁飞：开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作

崔鹏：任职于海能达通信股份有限公司，致力于PostgreSQL数据库在专网通信领域的应用与推广

在PostgresSQL9.6中引入了并行查询，从那之后这一功能不断地得到扩展。在PostgreSQL11和PostgreSQL12中，数据库引擎增加了更多的功能。然而，仍然有一些与并行查询相关的问题，这些问题经常出现在培训过程中，因此有必要澄清说明一下。

顺序扫描的代价估计

为了说明并行查询的工作原理，创建一个简单的表，仅仅有两列，如下：

test=# CREATE TABLE t_test AS
&nbsp;&nbsp;SELECT id AS many, id % 2 AS few
&nbsp;&nbsp;FROM generate_series(1, 10000000) AS id;
SELECT 10000000
&nbsp;
test=# ANALYZE;
ANALYZE

列many包含有1000万条不同数据，列few包含有两个不同的值。但是，出于演示用例的考虑，所有合理的大表都可以使用。

我们用来分析PostgreSQL优化器如何工作的查询语句也非常简单，如下：

test=# SET max_parallel_workers_per_gather TO 0;
SET
test=# explain SELECT count(*) FROM t_test;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY PLAN
------------------------------------------------------------------------
&nbsp;Aggregate (cost=169248.60..169248.61 rows=1 width=8)
&nbsp;&nbsp;&nbsp;-> Seq Scan on t_test (cost=0.00..144248.48 rows=10000048 width=0)
&nbsp;(2 rows)

默认配置将自动使PostgreSQL进行并行顺序扫描，为了更加方便阅读，我们希望先不使用并行查询。

通过设置max_parallel_workers_per_gather为0可以关闭并行查询。如在执行计划中所看到的，顺序扫描的估算成本为144248，而整个语句估算成本是169248。那么PostgreSQL是如何计算得到这个数字呢？让我们先看下如下详细内容：

test=# SELECT pg_relation_size('t_test') AS size,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pg_relation_size('t_test') / 8192 AS blocks;
&nbsp;&nbsp;size&nbsp;&nbsp;&nbsp;&nbsp; | blocks 
-----------+--------
&nbsp;362479616 | 44248
(1 row)

表t_test大概占用350MB空间并包含有44248个物理块。每个块将被顺序地读和处理。必须对这些块中所有行统计完才能得到最终结果。优化器使用下面的公式计算代价：

test=# SELECT current_setting('seq_page_cost')::numeric * 44248
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+ current_setting('cpu_tuple_cost')::numeric * 10000000
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+ current_setting('cpu_operator_cost')::numeric * 10000000;
&nbsp;
&nbsp;&nbsp;?column?
-------------
&nbsp;169248.0000
(1 row)

可以看到，这里使用了两个参数：优化器使用seq_page_cost表示顺序读取一个块的代价。更重要的是，必须考虑到一个事实，即所有这些行在最终统计之前还必须通过CPU（cpu_tuple_cost）。使用cpu_operator_cost是因为计数基本上和为每一行调用“+1”相同。因此，在执行计划中得到顺序扫描的总代价为169248。

并行顺序扫描估计

在Cybertec数据库培训期间，有很多人对PostgreSQL的顺序扫描估算方式感到困惑并对这个有很多疑问。首先看下面的执行计划，看看会发生什么：

test=# SET max_parallel_workers_per_gather TO default;
SET
test=# explain SELECT count(*) FROM t_test;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY PLAN
------------------------------------------------------------------------------------
&nbsp;Finalize Aggregate (cost=97331.80..97331.81 rows=1 width=8)
&nbsp;-> Gather (cost=97331.58..97331.79 rows=2 width=8)
&nbsp;&nbsp;&nbsp;&nbsp;Workers Planned: 2
&nbsp;&nbsp;&nbsp;&nbsp;-> Partial Aggregate (cost=96331.58..96331.59 rows=1 width=8)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-> Parallel Seq Scan on t_test (cost=0.00..85914.87 rows=4166687 width=0)
(5 rows)

如看到的那样，PostgreSQL决定使用2个CPU。但是PostgreSQL是如何考虑到这一点的呢？“rows=4166687”

答案就在下面的公式中：

10000048.0 / (2 + (1 – 0.3 * 2)) = 4166686.66 rows

PostgreSQL期望表中的行数为10000048（由前一次ANALYZE分析决定）。接下来的事情就是PostgreSQL试图决定一个核能做多少工作。但是上面这个公式的具体含义又是什么呢？

estimate = estimated_rows / (number_of_cores + (1 – leader_contribution * number_of_cores)

CENTER_PostgreSQL_Community

Leader进程通常需要花费大量精力给最终的结果。但是，假设leader花费大约30%的时间为工作进程提供服务。因此，随着核心数的增加，leader的贡献将降低。如果有4个或者更多的核工作，那么leader将不再对扫描有任何有意义的贡献——因此，PostgreSQL只会根据核心数来计算表的大小，而不是使用上面的公式。

其他并行操作

其他并行操作将使用类似的除数来估计这些操作所需的工作量。位图扫描等工作方式相同。

原文地址

https://www.cybertec-postgresql.com/en/how-postgresql-estimates-parallel-queries/

CENTER_PostgreSQL_Community

### **作者：Hans-Jürgen Schönig**   
### **译者: 陈雁飞 崔鹏**
### **作者简介**
#### Hans-Jürgen Schönig：从20世纪90年代开始使用PostgreSQL，担任CYBERTEC公司的CEO和技术主管（www.cybertec-postgresql.com）， CYBERTEC是该领域的市场领导者之一，自2000年以来已为全球无数客户提供服务。
### **译者简介**
#### 陈雁飞：开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作
#### 崔鹏：任职于海能达通信股份有限公司，致力于PostgreSQL数据库在专网通信领域的应用与推广

#### 在PostgresSQL9.6中引入了并行查询，从那之后这一功能不断地得到扩展。在PostgreSQL11和PostgreSQL12中，数据库引擎增加了更多的功能。然而，仍然有一些与并行查询相关的问题，这些问题经常出现在培训过程中，因此有必要澄清说明一下。
	
### **顺序扫描的代价估计**
#### 为了说明并行查询的工作原理，创建一个简单的表，仅仅有两列，如下：
```
test=# CREATE TABLE t_test AS
  SELECT id AS many, id % 2 AS few
  FROM generate_series(1, 10000000) AS id;
SELECT 10000000
 
test=# ANALYZE;
ANALYZE
```

#### 列many包含有1000万条不同数据，列few包含有两个不同的值。但是，出于演示用例的考虑，所有合理的大表都可以使用。
#### 我们用来分析PostgreSQL优化器如何工作的查询语句也非常简单，如下：
```
test=# SET max_parallel_workers_per_gather TO 0;
SET
test=# explain SELECT count(*) FROM t_test;
                                 QUERY PLAN
------------------------------------------------------------------------
 Aggregate (cost=169248.60..169248.61 rows=1 width=8)
   -> Seq Scan on t_test (cost=0.00..144248.48 rows=10000048 width=0)
 (2 rows)
```

#### 默认配置将自动使PostgreSQL进行并行顺序扫描，为了更加方便阅读，我们希望先不使用并行查询。

#### 通过设置max_parallel_workers_per_gather为0可以关闭并行查询。如在执行计划中所看到的，顺序扫描的估算成本为144248，而整个语句估算成本是169248。那么PostgreSQL是如何计算得到这个数字呢？让我们先看下如下详细内容：
```
test=# SELECT pg_relation_size('t_test') AS size,
              pg_relation_size('t_test') / 8192 AS blocks;
  size     | blocks 
-----------+--------
 362479616 | 44248
(1 row)
```

#### 表t_test大概占用350MB空间并包含有44248个物理块。每个块将被顺序地读和处理。必须对这些块中所有行统计完才能得到最终结果。优化器使用下面的公式计算代价：
```
test=# SELECT current_setting('seq_page_cost')::numeric * 44248
            + current_setting('cpu_tuple_cost')::numeric * 10000000
            + current_setting('cpu_operator_cost')::numeric * 10000000;
 
  ?column?
-------------
 169248.0000
(1 row)
```

#### 可以看到，这里使用了两个参数：优化器使用seq_page_cost表示顺序读取一个块的代价。更重要的是，必须考虑到一个事实，即所有这些行在最终统计之前还必须通过CPU（cpu_tuple_cost）。使用cpu_operator_cost是因为计数基本上和为每一行调用“+1”相同。因此，在执行计划中得到顺序扫描的总代价为169248。
### **并行顺序扫描估计**
#### 在Cybertec数据库培训期间，有很多人对PostgreSQL的顺序扫描估算方式感到困惑并对这个有很多疑问。首先看下面的执行计划，看看会发生什么：
```
test=# SET max_parallel_workers_per_gather TO default;
SET
test=# explain SELECT count(*) FROM t_test;
                                  QUERY PLAN
------------------------------------------------------------------------------------
 Finalize Aggregate (cost=97331.80..97331.81 rows=1 width=8)
 -> Gather (cost=97331.58..97331.79 rows=2 width=8)
    Workers Planned: 2
    -> Partial Aggregate (cost=96331.58..96331.59 rows=1 width=8)
       -> Parallel Seq Scan on t_test (cost=0.00..85914.87 rows=4166687 width=0)
(5 rows)
```

#### 如看到的那样，PostgreSQL决定使用2个CPU。但是PostgreSQL是如何考虑到这一点的呢？“rows=4166687”
#### 答案就在下面的公式中：
#### 10000048.0 / (2 + (1 – 0.3 * 2)) = 4166686.66 rows
#### PostgreSQL期望表中的行数为10000048（由前一次ANALYZE分析决定）。接下来的事情就是PostgreSQL试图决定一个核能做多少工作。但是上面这个公式的具体含义又是什么呢？
```
estimate = estimated_rows / (number_of_cores + (1 – leader_contribution * number_of_cores)
```
![CENTER_PostgreSQL_Community]( /images/news/2020/20200225_1537_图片34567777.png)
#### Leader进程通常需要花费大量精力给最终的结果。但是，假设leader花费大约30%的时间为工作进程提供服务。因此，随着核心数的增加，leader的贡献将降低。如果有4个或者更多的核工作，那么leader将不再对扫描有任何有意义的贡献——因此，PostgreSQL只会根据核心数来计算表的大小，而不是使用上面的公式。
### **其他并行操作**
#### 其他并行操作将使用类似的除数来估计这些操作所需的工作量。位图扫描等工作方式相同。
### **原文地址**
#### <https://www.cybertec-postgresql.com/en/how-postgresql-estimates-parallel-queries/>

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)