并行查询

前言

如果说单线程查询是一位厨师在厨房里独自完成所有烹饪任务，那么并行查询就像是组建了一个烹饪团队，多位厨师（Worker进程）同时处理不同的食材和工作，最后由主厨（Leader进程）将各部分成果完美融合。PostgreSQL从9.6版本开始引入并行查询功能，充分利用现代多核处理器的计算能力，大幅提升了复杂查询的性能，特别是对大数据集的处理能力。

并行查询是如何工作的

PostgreSQL的并行查询机制主要由三个关键组件构成：

1. Leader进程：协调并行查询的主进程，负责将任务分配给Worker进程，并汇总结果
2. Gather节点：查询计划中的特殊节点，负责启动和管理Worker进程
3. Worker进程：执行并行查询的辅助进程，实际处理数据并返回部分结果给Leader

并行查询执行流程

-- 查看一个包含并行查询的执行计划
EXPLAIN SELECT COUNT(*) FROM large_table WHERE id > 1000;

在执行计划中，你会看到类似以下的输出：

Finalize Aggregate  (cost=12345.67..12345.68 rows=1 width=8)
  ->  Gather  (cost=12345.00..12345.67 rows=4 width=8)
        Workers Planned: 3
        ->  Partial Aggregate  (cost=12345.00..12345.01 rows=1 width=8)
              ->  Parallel Seq Scan on large_table  (cost=0.00..12345.00 rows=1000 width=0)
                    Filter: (id > 1000)

在这个执行计划中：

• Gather节点表示并行查询的协调点
• Workers Planned: 3表示计划使用3个Worker进程
• Partial Aggregate表示部分聚合操作，由Worker进程执行
• Parallel Seq Scan表示并行顺序扫描

并行查询的限制

并非所有查询都能并行执行，PostgreSQL对并行查询有以下限制：

1. 表限制：只有堆表(heap)支持并行查询，临时表、系统表和外部表不支持
2. 连接类型限制：只有内连接(INNER JOIN)、左外连接(LEFT JOIN)和右外连接(RIGHT JOIN)支持并行
3. 操作限制：并非所有操作都支持并行执行，如排序、分组等操作可能有特殊限制
4. 并行度限制：并行度受max_parallel_workers_per_gather参数限制

何时会用到并行查询

并行查询特别适合以下场景：

大表查询

-- 创建测试表
CREATE TABLE large_table AS
SELECT generate_series(1, 50000000) AS id, 
       'test' || (random()*100)::int AS name;

-- 分析表以收集统计信息
ANALYZE large_table;

-- 执行查询并查看执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM large_table WHERE id > 1000;

在这个例子中，PostgreSQL会使用并行查询来加速大表的扫描和聚合操作。

复杂连接操作

-- 创建两个测试表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入大量测试数据
INSERT INTO customers
SELECT generate_series(1, 1000000), 
       'Customer' || generate_series(1, 1000000),
       'customer' || generate_series(1, 1000000) || '@example.com';

INSERT INTO orders
SELECT generate_series(1, 10000000), 
       (random()*1000000)::int,
       current_date - (random()*365)::int,
       (random()*1000)::numeric(10,2);

-- 分析表
ANALYZE orders;
ANALYZE customers;

-- 执行连接查询并查看执行计划
EXPLAIN (ANALYZE, BUFFERS) 
SELECT c.name, COUNT(o.order_id), SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY SUM(o.amount) DESC
LIMIT 10;

在这个例子中，连接操作和聚合操作都可以并行执行，显著提高查询性能。

关闭并行查询对比

-- 设置max_parallel_workers_per_gather为0，禁用并行查询
SET max_parallel_workers_per_gather = 0;

-- 重新生成执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM large_table WHERE id > 1000;

对比两种执行计划，可以清楚地看到并行查询带来的性能提升。

影响并行查询的相关参数和说明

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

max_parallel_workers_per_gather

-- 设置每个Gather节点最多使用的Worker进程数
SET max_parallel_workers_per_gather = 4;

-- 查看当前值
SHOW max_parallel_workers_per_gather;

说明：控制并行查询中每个Gather节点可以使用的Worker进程数量，默认为2。设置为0表示禁用并行查询。

max_parallel_workers

-- 设置PostgreSQL可以使用的最大Worker进程数
SET max_parallel_workers = 8;

-- 查看当前值
SHOW max_parallel_workers;

说明：控制整个实例可以使用的最大Worker进程数量，默认为8。这个参数限制了所有并行查询可以使用的Worker总数。

max_parallel_maintenance_workers

-- 设置维护操作（如VACUUM、CREATE INDEX）可以使用的最大Worker进程数
SET max_parallel_maintenance_workers = 4;

-- 查看当前值
SHOW max_parallel_maintenance_workers;

说明：控制维护操作可以使用的最大Worker进程数量，默认为2。

parallel_setup_cost

-- 设置并行设置的代价
SET parallel_setup_cost = 1000;

-- 查看当前值
SHOW parallel_setup_cost;

说明：控制PostgreSQL选择并行查询的阈值，默认为1000。增加这个值会使PostgreSQL更倾向于使用并行查询。

parallel_tuple_cost

-- 设置处理每行的并行代价
SET parallel_tuple_cost = 0.1;

-- 查看当前值
SHOW parallel_tuple_cost;

说明：控制并行处理每行的代价估计，默认为0.1。减少这个值会使PostgreSQL更倾向于使用并行查询。

parallel_leader_participation

-- 设置Leader进程是否参与并行查询
SET parallel_leader_participation = on;

-- 查看当前值
SHOW parallel_leader_participation;

说明：控制Leader进程是否参与并行查询的数据处理，默认为on。设置为off时，Leader进程只负责协调和汇总结果。

并行查询的注意事项

资源竞争

并行查询虽然可以加速查询，但同时也会增加系统资源消耗，特别是CPU和内存。在高并发环境下，过多的并行查询可能导致资源竞争，反而降低整体性能：

-- 监控并行查询的资源使用
SELECT 
    pid, 
    query, 
    state,
    backend_start,
    query_start,
    state_change
FROM pg_stat_activity
WHERE query LIKE '%Parallel%' OR query LIKE '%Gather%';

并行度设置

并行度设置需要根据实际硬件配置和负载情况进行调整：

-- 根据CPU核心数设置并行度
-- 对于4核CPU，可以设置为3或4
SET max_parallel_workers_per_gather = 3;

-- 根据系统负载动态调整
-- 在高负载期间减少并行度
SET max_parallel_workers_per_gather = 1;

查询优化

并非所有查询都适合并行化，某些查询可能因并行化而性能下降：

-- 比较并行和串行执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM small_table;
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM small_table;
SET max_parallel_workers_per_gather = 2; -- 恢复默认值

对于小表查询，并行查询的开销可能超过收益。

版本兼容性

并行查询功能在不同PostgreSQL版本中有所变化：

-- 检查PostgreSQL版本
SELECT version();

• PostgreSQL 9.6：引入基本并行查询功能
• PostgreSQL 10：增强并行查询，支持更多操作类型
• PostgreSQL 11：进一步优化，支持并行排序和哈希连接
• PostgreSQL 12+：持续改进和扩展并行查询能力

监控和调优

定期监控并行查询的性能，进行针对性调优：

-- 监控并行查询统计信息
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
WHERE relname IN ('large_table', 'orders', 'customers');

-- 查看并行查询执行情况
SELECT query, calls, total_time, rows, 
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%Parallel%' 
   OR query LIKE '%Gather%'
ORDER BY total_time DESC;