PostgreSQL 并行查询：解锁查询效率的并行魔法

欢迎体验这篇关于 PostgreSQL **并行查询（Parallel Query）**的教学指南！如果你正在使用 PostgreSQL 管理数据库，并且希望加速复杂查询的执行速度，并行查询是一个强大的功能。这篇文章将以通俗易懂的方式，带你从零开始掌握并行查询的工作原理、配置方法、适用场景，以及如何通过优化让数据库查询效率飞跃。无论你是数据库新手还是希望深入调优的开发者，这篇指南都将为你提供实用的知识和实践灵感。

1. 什么是 PostgreSQL 并行查询？

并行查询是 PostgreSQL 自 9.6 版本引入的一项功能，允许查询在多个 CPU 核心上并行执行，从而显著提高大型数据集的处理速度。它通过将查询任务分配给多个工作进程（Worker Processes），并行处理数据扫描、聚合或连接等操作，最终合并结果返回给客户端。

1.1 核心概念

并行执行：查询的某些部分（如表扫描、聚合）被拆分为子任务，由多个工作进程并行处理。
领导进程（Leader Process）：负责协调工作进程、收集结果并与客户端通信。
工作进程（Worker Process）：执行分配的任务，数量由配置和查询复杂度决定。
并行安全（Parallel Safety）：并非所有查询都能并行执行，查询必须是“并行安全的”（如避免写操作）。

1.2 并行查询的优势

加速大数据处理：对于扫描大表或复杂聚合的查询，并行查询可利用多核 CPU 缩短执行时间。
资源高效利用：充分利用服务器的计算能力，适合多核、高内存环境。
透明性：无需修改查询，PostgreSQL 自动决定是否使用并行执行。

在开始之前，确保你：

已安装 PostgreSQL（推荐最新稳定版本，如 17.x）。
能通过 psql 或 GUI 工具（如 pgAdmin）访问数据库。
服务器有多个 CPU 核心以支持并行执行。
了解基本的 SQL 查询和执行计划（如 EXPLAIN）。

接下来，我们将从并行查询的工作原理开始，逐步探索其效率提升的奥秘。

2. 并行查询的工作原理

理解并行查询的底层机制是优化其使用的关键。以下是其核心流程：

2.1 查询规划

当你执行一个查询时，PostgreSQL 的查询优化器会评估是否适合并行执行。优化器考虑以下因素：

查询类型：支持并行的操作包括表扫描（Sequential Scan）、索引扫描（Index Scan）、聚合（如 SUM、COUNT）、连接（Nested Loop、Hash Join 等）。
表大小：大表（行数或数据量较多）更可能触发并行执行。
并行成本：优化器估算并行执行的成本（CPU、I/O、通信开销）与单线程执行的比较。
配置参数：如 max_parallel_workers_per_gather 和 parallel_setup_cost。

如果并行执行的成本低于单线程，优化器会生成并行执行计划。

2.2 并行执行流程

领导进程启动：客户端的查询由领导进程处理，它负责解析查询和协调工作。
分配工作进程：领导进程根据配置（如 max_parallel_workers_per_gather）和表大小，启动若干工作进程。
任务分片：查询任务（如扫描表的分片、计算聚合）分配给工作进程，每个进程处理一部分数据。
数据交换：工作进程通过共享内存或临时文件交换中间结果。
结果合并：领导进程收集工作进程的结果，完成最终计算（如排序或分组）并返回给客户端。

2.3 并行安全的限制

并非所有查询都能并行执行。以下情况会禁用并行查询：

写操作：INSERT、UPDATE、DELETE 等修改数据的查询。
事务性操作：如 CREATE TABLE、LOCK TABLE。
非安全函数：自定义函数或涉及全局状态的函数可能不安全。
复杂子查询：某些嵌套查询或 CTE（公用表表达式）可能限制并行。

检查并行计划：使用 EXPLAIN 查看是否启用并行：

1

EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01';

输出样例：

Finalize Aggregate
  ->  Gather
        Workers Planned: 2
        ->  Parallel Seq Scan on orders
              Filter: (order_date >= '2025-01-01')

Gather：表示并行执行，合并工作进程结果。
Workers Planned: 2：计划使用 2 个工作进程。
Parallel Seq Scan：并行顺序扫描。

3. 配置并行查询

要启用并优化并行查询，需要调整 PostgreSQL 的配置参数。以下是关键参数及其作用：

3.1 全局配置

编辑 postgresql.conf 文件，设置以下参数：

max_parallel_workers_per_gather：
- 默认：2
- 建议：2-8（根据 CPU 核心数）
- 作用：每个查询的最大并行工作进程数。
max_parallel_workers：
- 默认：8
- 建议：等于或略低于 CPU 核心数
- 作用：系统总并行工作进程上限。
parallel_setup_cost：
- 默认：1000
- 建议：500-2000
- 作用：启动并行进程的成本，值越低越容易触发并行。
parallel_tuple_cost：
- 默认：0.1
- 建议：0.05-0.2
- 作用：并行传输每行数据的成本。
min_parallel_table_scan_size：
- 默认：8MB
- 建议：4MB-32MB
- 作用：触发并行扫描的最小表大小。
min_parallel_index_scan_size：
- 默认：512KB
- 建议：256KB-2MB
- 作用：触发并行索引扫描的最小索引大小。
force_parallel_mode：
- 默认：off
- 建议：off（测试时可设为 on）
- 作用：强制并行执行（用于调试）。

示例配置：

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 500
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB

重启数据库或重新加载配置：

1

SELECT pg_reload_conf();

3.2 会话级配置

临时调整并行参数：

1
2


SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 500;

3.3 检查配置

查看当前并行设置：

1
2


SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;

4. 并行查询的适用场景

并行查询在以下场景下能显著提高效率：

4.1 大表扫描

场景：查询需要扫描大量行（如全表扫描或过滤大量数据）。示例：

1

SELECT * FROM orders WHERE order_date >= '2025-01-01';

并行扫描将表分片，多个工作进程同时处理，减少扫描时间。

4.2 复杂聚合

场景：计算大表的聚合函数（如 SUM、COUNT、AVG）。示例：

1

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

并行进程分别计算子集的聚合，结果由领导进程合并。

4.3 大规模连接

场景：涉及大表的连接操作（如 Hash Join）。示例：

1
2
3
4


SELECT o.order_id, u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2025-01-01';

并行进程处理连接的子集，加速匹配。

4.4 排序和分组

场景：需要对大结果集排序或分组。示例：

1
2
3
4


SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
ORDER BY SUM(amount) DESC;

并行进程分别处理分组，领导进程合并排序。

4.5 不适用场景

小表查询：表数据量小，启动并行进程的开销可能高于收益。
写操作：并行查询不支持修改数据的操作。
高并发环境：过多并行进程可能导致 CPU 或 I/O 争用。

5. 优化并行查询

5.1 调整并行工作进程

根据服务器硬件优化 max_parallel_workers_per_gather：

4 核 CPU：设为 2-4。
16 核 CPU：设为 4-8。
检查 CPU 使用率，避免过载：

1

top

5.2 降低并行成本

减少 parallel_setup_cost 和 parallel_tuple_cost，鼓励优化器选择并行计划：

1
2


SET parallel_setup_cost = 500;
SET parallel_tuple_cost = 0.05;

5.3 优化表和索引

分区表：并行查询对分区表更有效，每个工作进程处理一个分区：

1
2
3
4
5
6
7
8
9


CREATE TABLE orders (
    order_id SERIAL,
    user_id INTEGER,
    order_date TIMESTAMP,
    amount NUMERIC
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

索引优化：为过滤条件创建索引，触发并行索引扫描：

1

CREATE INDEX idx_orders_order_date ON orders (order_date);

5.4 增加工作内存

并行查询需要更多内存，调整 work_mem：

1

SET work_mem = '16MB';

5.5 监控并行执行

使用 EXPLAIN ANALYZE 检查实际并行效果：

1

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01';

输出样例：

Finalize Aggregate  (cost=1000.00..1000.01 rows=1 width=8) (actual time=200.123..200.124 rows=1 loops=1)
  ->  Gather  (cost=1000.00..1000.01 rows=2 width=8) (actual time=200.100..200.110 rows=3 loops=1)
        Workers Launched: 2
        ->  Parallel Seq Scan on orders  (cost=0.00..900.00 rows=50000 width=8) (actual time=0.050..150.000 rows=40000 loops=3)
              Filter: (order_date >= '2025-01-01')
Planning Time: 0.200 ms
Execution Time: 200.150 ms

Workers Launched: 2：实际使用 2 个工作进程。
Execution Time：并行执行的总时间。

6. 实际案例分析

6.1 场景：加速大表聚合

背景：一个电子商务数据库的 orders 表有 1 亿行，查询订单总数耗时 10 秒：

1

SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01';

分析：

使用 EXPLAIN 检查计划，发现是单线程顺序扫描。
表大小 50GB，服务器有 8 核 CPU。

解决方案：

启用并行查询：

1
2


SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 500;

创建索引：

1

CREATE INDEX idx_orders_order_date ON orders (order_date);

运行查询并分析：

1

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01';

结果：

执行时间从 10 秒降到 3 秒。
计划显示 Parallel Seq Scan，使用了 4 个工作进程。

6.2 场景：优化复杂连接

背景：查询订单和用户信息，耗时 15 秒：

1
2
3
4


SELECT o.order_id, u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2025-01-01';

分析：

EXPLAIN 显示 Hash Join 未并行。
users 表较小，orders 表很大。