一、并行查询概述

并行查询是PostgreSQL的一项重要特性，它允许将一个查询分解为多个子任务，由多个CPU核心并行执行，从而提高查询速度。

1.1 并行查询的基本概念

• 并行度（Degree of Parallelism）：执行查询的并行工作进程数量
• 领导者进程（Leader Process）：负责协调并行工作进程的进程
• 工作进程（Worker Process）：实际执行查询子任务的进程
• Gather节点：在查询计划中负责收集和合并并行工作进程结果的节点
• Gather Merge节点：在查询计划中负责收集和合并有序结果的节点

1.2 并行查询的优势

1. 提高查询速度：利用多个CPU核心并行执行查询
2. 充分利用硬件资源：提高CPU利用率
3. 减少响应时间：特别是对于大型表和复杂查询
4. 提高系统吞吐量：允许同时处理更多查询

1.3 并行查询的适用场景

并行查询适用于以下场景：

• 大型表扫描：对大型表进行全表扫描或范围扫描
• 复杂聚合查询：包含SUM、COUNT、AVG等聚合函数的查询
• 连接查询：多个大型表的连接操作
• 排序操作：对大量数据进行排序
• 复杂计算：包含复杂表达式或函数的查询

二、并行查询的工作原理

2.1 并行查询的基本流程

1. 查询优化器决策：查询优化器决定是否使用并行查询
2. 并行计划生成：生成并行查询计划
3. 并行工作进程创建：领导者进程创建并行工作进程
4. 数据分片：将数据划分为多个片段，分配给不同的工作进程
5. 并行执行：工作进程并行执行子任务
6. 结果合并：领导者进程收集并合并工作进程的结果
7. 结果返回：将最终结果返回给客户端

2.2 并行查询的执行模式

PostgreSQL支持以下几种并行查询执行模式：

1. 并行扫描：

   • 并行顺序扫描（Parallel Seq Scan）
   • 并行索引扫描（Parallel Index Scan）
   • 并行位图扫描（Parallel Bitmap Heap Scan）

2. 并行连接：

   • 并行嵌套循环连接（Parallel Nested Loop Join）
   • 并行哈希连接（Parallel Hash Join）
   • 并行合并连接（Parallel Merge Join）

3. 并行聚合：

   • 部分聚合（Partial Aggregation）
   • 最终聚合（Final Aggregation）

4. 并行排序：

   • 并行排序（Parallel Sort）
   • 并行合并（Parallel Merge）

2.3 并行查询的限制

并行查询有一些限制，以下情况不支持并行查询：

• 游标查询：使用游标执行的查询
• CTE查询：包含公共表表达式的查询（PostgreSQL 18之前）
• 递归查询：递归CTE查询（PostgreSQL 18之前）
• FOR UPDATE/SHARE查询：包含FOR UPDATE或FOR SHARE子句的查询
• 临时表查询：查询临时表
• 外部表查询：查询外部表
• 某些内置函数：如current_date、current_time等不稳定函数

三、并行查询的配置参数

PostgreSQL提供了多个参数来控制并行查询的行为。

3.1 主要配置参数

参数名	描述	默认值	建议值
max_parallel_workers_per_gather	每个Gather节点的最大并行工作进程数	8	根据CPU核心数调整
max_parallel_workers	系统范围内的最大并行工作进程数	8	通常为CPU核心数
max_worker_processes	系统范围内的最大后台工作进程数	8	大于等于max_parallel_workers
parallel_leader_participation	领导者进程是否参与查询执行	on	on
min_parallel_table_scan_size	表扫描使用并行查询的最小表大小	8MB	根据表大小调整
min_parallel_index_scan_size	索引扫描使用并行查询的最小索引大小	512kB	根据索引大小调整
force_parallel_mode	强制使用并行查询的模式	off	off
enable_parallel_append	启用并行追加	on	on
enable_parallel_hash	启用并行哈希连接	on	on

3.2 参数配置示例

-- 调整并行查询参数
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET max_worker_processes = 16;
ALTER SYSTEM SET min_parallel_table_scan_size = '16MB';

-- 重启PostgreSQL使配置生效
-- sudo systemctl restart postgresql-18

四、并行查询计划分析

4.1 查看并行查询计划

使用EXPLAIN ANALYZE命令可以查看并行查询计划：

-- 启用并行查询
SET max_parallel_workers_per_gather = 4;

-- 查看并行查询计划
EXPLAIN ANALYZE 
SELECT department, SUM(salary) 
FROM employees 
GROUP BY department;

4.2 并行查询计划示例

Finalize GroupAggregate  (cost=1000.55..1000.67 rows=2 width=12) (actual time=0.123..0.125 rows=2 loops=1)
  Group Key: department
  ->  Gather Merge  (cost=1000.55..1000.65 rows=4 width=12) (actual time=0.120..0.122 rows=4 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=0.55..0.63 rows=2 width=12) (actual time=0.050..0.051 rows=2 loops=2)
              Group Key: department
              ->  Parallel Seq Scan on employees  (cost=0.00..0.50 rows=50 width=10) (actual time=0.015..0.020 rows=50 loops=2)
Planning Time: 0.156 ms
Execution Time: 0.182 ms

4.3 并行查询计划中的关键节点

1. Gather/Gather Merge：收集和合并工作进程的结果
2. Partial Aggregate：部分聚合，在工作进程中执行
3. Finalize Aggregate：最终聚合，在领导者进程中执行
4. Parallel Seq Scan：并行顺序扫描
5. Parallel Index Scan：并行索引扫描
6. Parallel Hash Join：并行哈希连接

4.4 并行查询计划的解读

• Workers Planned：计划使用的并行工作进程数
• Workers Launched：实际启动的并行工作进程数
• Partial：表示在工作进程中执行的操作
• Finalize：表示在领导者进程中执行的最终操作
• Gather：表示收集工作进程结果的操作

五、PostgreSQL vs SQL Server 2019+ vs MySQL 8.0+：并行查询优化对比

在本节中，我们将PostgreSQL 18的并行查询优化与SQL Server 2019+和MySQL 8.0+的类似功能进行对比，分析它们在并行查询实现、配置和性能方面的差异。

5.1 并行查询支持对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
并行查询类型	并行SELECT/UPDATE/DELETE/MERGE/递归查询/CTE	并行SELECT/INSERT/UPDATE/DELETE/MERGE	并行SELECT（有限支持）
并行扫描	支持并行顺序扫描、索引扫描、位图扫描	支持多种扫描类型的并行执行	支持并行全表扫描和索引扫描
并行连接	支持并行嵌套循环、哈希连接、合并连接	支持多种连接类型的并行执行	支持有限的并行连接
并行聚合	支持部分聚合和最终聚合	支持并行聚合	支持有限的并行聚合
并行排序	支持并行排序和合并	支持并行排序	无原生支持
并行分区操作	支持并行分区扫描和维护	支持并行分区操作	支持有限的并行分区操作

5.2 并行查询配置对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
并行度控制	动态并行度调整，支持领导者进程参与	自适应并行度，支持MAXDOP配置	基于成本的并行度，最高8个线程
并行度配置参数	max_parallel_workers_per_gather, max_parallel_workers	MAXDOP, Cost Threshold for Parallelism	max_parallel_workers_per_gather
并行触发阈值	基于表大小和索引大小	基于查询成本	基于表大小和查询成本
领导者进程参与	支持，可配置parallel_leader_participation	支持，领导者进程参与执行	不支持
并行查询开关	多个参数控制不同类型的并行操作	全局和查询级别的开关	有限的开关控制

5.3 并行查询性能对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
性能提升	显著，特别是对于大型表和复杂查询	显著，企业级性能优化	有限，主要适合简单查询
资源利用率	良好，能够充分利用CPU核心	优秀，精细的资源管理	一般，资源利用效率不高
并行度自适应	支持动态并行度调整	支持自适应并行查询	有限的自适应能力
查询计划优化	改进的并行计划选择	成熟的并行计划优化	简单的并行计划生成
大规模数据处理	优秀，适合TB级数据	优秀，适合PB级数据	良好，适合GB级数据

5.4 并行查询限制对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
游标查询	不支持并行	支持并行游标	不支持并行
CTE查询	PostgreSQL 18支持并行CTE	支持并行CTE	不支持并行CTE
递归查询	PostgreSQL 18支持并行递归查询	支持并行递归查询	不支持并行递归查询
临时表查询	不支持并行	支持并行临时表查询	不支持并行
外部表查询	不支持并行	支持并行外部表查询	不支持并行
不稳定函数	不支持包含不稳定函数的查询	支持，有相应的处理机制	不支持

5.5 并行查询适用场景对比

场景	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
大型表扫描	非常适合	非常适合	适合
复杂聚合查询	非常适合	非常适合	适合
多表连接查询	适合	非常适合	有限支持
排序操作	适合	非常适合	不支持
复杂计算	适合	非常适合	有限支持
OLAP查询	适合	非常适合	有限支持
OLTP查询	适合简单OLTP查询	支持混合工作负载	适合高并发OLTP

六、并行查询的优化技巧

6.1 调整并行度

根据系统的CPU核心数和查询复杂度调整并行度：

-- 为复杂查询设置较高的并行度
SET max_parallel_workers_per_gather = 8;

-- 为简单查询设置较低的并行度或禁用并行
SET max_parallel_workers_per_gather = 1;
-- 或禁用并行
SET max_parallel_workers_per_gather = 0;

6.2 优化表设计

1. 合理设置表的存储参数：

   -- 设置合适的填充因子
   ALTER TABLE employees SET (fillfactor = 80);
   

2. 使用合适的索引：

   • 对于经常进行范围查询的列，创建B-tree索引
   • 对于经常进行聚合查询的列，考虑创建部分索引

3. 分区表优化：

   • 对大型表进行分区
   • 启用并行分区扫描

   -- 启用并行分区扫描
   SET enable_parallel_partition_scan = on;
   

6.3 优化查询语句

1. 避免不必要的列：只查询需要的列

   -- 好的做法：只查询需要的列
   SELECT id, name FROM employees;
   
   -- 不好的做法：查询所有列
   SELECT * FROM employees;
   

2. 优化聚合查询：

   • 使用合适的聚合函数
   • 考虑使用部分聚合

   -- 优化聚合查询
   SELECT department, SUM(salary) FROM employees GROUP BY department;
   

3. 优化连接查询：

   • 选择合适的连接顺序
   • 使用合适的连接类型

   -- 优化连接查询
   SELECT e.name, d.department_name 
   FROM employees e 
   JOIN departments d ON e.department_id = d.id;
   

4. 避免复杂表达式：

   • 将复杂表达式移到WHERE子句中
   • 考虑使用生成列

   -- 使用生成列
   ALTER TABLE employees ADD COLUMN full_name GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;
   

6.4 优化系统资源

1. 调整内存配置：

   -- 增加共享缓冲区
   ALTER SYSTEM SET shared_buffers = '4GB';
   
   -- 增加工作内存
   ALTER SYSTEM SET work_mem = '64MB';
   
   -- 增加维护工作内存
   ALTER SYSTEM SET maintenance_work_mem = '1GB';
   

2. 调整存储配置：

   • 使用高性能存储设备（如SSD）
   • 调整RAID级别
   • 优化文件系统参数

3. 调整CPU配置：

   • 启用CPU超线程
   • 调整CPU频率
   • 考虑使用NUMA架构

七、并行查询的监控

7.1 查看并行查询状态

-- 查看当前并行查询
SELECT 
    pid, 
    query, 
    state, 
    backend_type,
    query_start
FROM 
    pg_stat_activity
WHERE 
    backend_type LIKE '%Parallel%' OR 
    query LIKE '%Parallel%';

-- 查看并行工作进程状态
SELECT 
    pid, 
    backend_type, 
    query
FROM 
    pg_stat_activity
WHERE 
    backend_type = 'parallel worker';

7.2 查看并行查询统计信息

-- 查看并行查询统计信息
SELECT * FROM pg_stat_progress_parallel;

-- 查看系统级并行查询统计
SELECT 
    name, 
    setting, 
    unit, 
    short_desc
FROM 
    pg_settings
WHERE 
    name LIKE '%parallel%';

7.3 监控并行查询性能

使用EXPLAIN ANALYZE监控并行查询性能：

-- 监控并行查询性能
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT department, SUM(salary) 
FROM employees 
GROUP BY department;

7.4 识别并行查询瓶颈

1. CPU瓶颈：

   • 监控CPU使用率
   • 查看是否有工作进程CPU使用率达到100%

2. I/O瓶颈：

   • 监控磁盘I/O使用率
   • 查看是否有大量的磁盘读取

3. 内存瓶颈：

   • 监控内存使用率
   • 查看是否有内存不足的情况

4. 网络瓶颈：

   • 监控网络流量
   • 查看是否有大量的数据传输

八、并行查询的最佳实践

8.1 配置最佳实践

1. 根据CPU核心数调整并行度：

   • 对于N核CPU，max_parallel_workers_per_gather建议设置为N/2到N-1
   • max_parallel_workers建议设置为N

2. 合理设置并行查询的触发阈值：

   • 根据表大小调整min_parallel_table_scan_size
   • 根据索引大小调整min_parallel_index_scan_size

3. 启用并行leader参与：

   • 设置parallel_leader_participation = on
   • 充分利用领导者进程的CPU资源

4. 启用并行哈希连接：

   • 设置enable_parallel_hash = on
   • 提高连接查询的并行性能

8.2 查询优化最佳实践

1. 为大型表启用并行查询：

   -- 为特定表启用并行查询
   ALTER TABLE large_table SET (parallel_workers = 4);
   

2. 避免过度并行：

   • 对于简单查询，禁用并行或使用较低的并行度
   • 避免同时执行过多的并行查询

3. 优化聚合查询：

   • 使用GROUP BY时，确保分组列上有索引
   • 考虑使用部分聚合

4. 优化连接查询：

   • 确保连接列上有索引
   • 选择合适的连接顺序
   • 使用合适的连接类型

8.3 监控和维护最佳实践

1. 定期监控并行查询性能：

   • 使用EXPLAIN ANALYZE定期分析查询性能
   • 监控系统资源使用情况

2. 定期更新统计信息：

   -- 更新统计信息
   ANALYZE employees;
   

3. 定期重建索引：

   -- 重建索引
   REINDEX TABLE employees;
   

4. 测试并行查询的效果：

   • 比较并行查询和串行查询的性能差异
   • 调整参数，找到最佳配置

8.4 故障排除最佳实践

1. 并行查询不启动：

   • 检查是否满足并行查询的条件
   • 检查并行查询参数设置
   • 查看查询计划，确认是否选择了并行计划

2. 并行查询性能不佳：

   • 检查是否存在资源瓶颈
   • 检查并行度是否合适
   • 优化查询语句

3. 并行查询导致系统负载过高：

   • 降低并行度
   • 限制并行查询的数量
   • 调整系统资源配置

九、实践项目：并行查询性能优化

9.1 项目目标

• 配置PostgreSQL并行查询
• 测试并行查询的性能
• 优化并行查询配置
• 监控并行查询状态
• 识别和解决并行查询瓶颈

9.2 项目步骤

1. 环境准备：

   • 安装PostgreSQL 18
   • 配置必要的参数
   • 创建测试数据库和表
   • 插入大量测试数据

2. 并行查询配置：

   • 调整并行查询相关参数
   • 启用并行查询
   • 配置表的并行工作进程数

3. 性能测试：

   • 执行各种类型的查询，包括全表扫描、聚合查询、连接查询等
   • 使用EXPLAIN ANALYZE分析查询性能
   • 比较并行查询和串行查询的性能差异

4. 并行查询优化：

   • 调整并行度
   • 优化表设计
   • 优化查询语句
   • 调整系统资源配置

5. 监控并行查询：

   • 监控并行查询的状态和性能
   • 识别并行查询瓶颈
   • 解决并行查询中的问题

9.3 项目验证

• 并行查询性能比串行查询提高至少50%
• 系统资源利用率合理
• 能够识别和解决并行查询瓶颈
• 并行查询配置优化合理

十、并行查询的未来发展

10.1 PostgreSQL 18的并行查询改进

PostgreSQL 18在并行查询方面进行了多项改进：

1. 支持更多类型的并行查询：

   • 并行UPDATE/DELETE
   • 并行MERGE
   • 并行递归查询
   • 并行CTE

2. 优化的并行执行机制：

   • 动态并行度调整
   • 更好的负载均衡
   • 减少同步开销

3. 改进的并行查询计划：

   • 更准确的并行度估计
   • 更好的并行计划选择

10.2 并行查询的发展趋势

1. 更智能的并行度调整：根据系统负载和查询复杂度自动调整并行度
2. 更广泛的并行查询支持：支持更多类型的查询和操作
3. 更好的资源管理：更细粒度的资源控制和隔离
4. 与分布式系统的集成：支持分布式并行查询
5. 更好的监控和诊断工具：提供更详细的并行查询监控信息

十一、总结

并行查询是PostgreSQL提高查询性能的重要特性，通过利用多个CPU核心并行执行查询，可以显著提高查询速度和系统吞吐量。

通过本章的学习，你应该已经掌握了：

1. 并行查询的基本概念和工作原理
2. 并行查询的配置参数和优化技巧
3. 并行查询计划的分析和解读
4. 并行查询的监控和维护
5. 并行查询的最佳实践和故障排除

在实际应用中，合理配置和优化并行查询，可以充分利用系统的硬件资源，提高查询性能，满足高并发、大数据量的查询需求。

十二、思考与练习

1. 什么是并行查询？并行查询有哪些优势？
2. 并行查询的基本工作原理是什么？
3. PostgreSQL支持哪些类型的并行查询？
4. 如何配置PostgreSQL的并行查询？
5. 如何分析并行查询计划？
6. 如何优化并行查询的性能？
7. 如何监控并行查询的状态和性能？
8. 并行查询的最佳实践有哪些？
9. 如何识别和解决并行查询瓶颈？
10. PostgreSQL 18在并行查询方面有哪些改进？