PostgreSQL教程FG091-PG并行查询：配置与适用场景优化

本文档风哥主要介绍PostgreSQL的并行查询配置和适用场景优化，包括并行查询的概念、架构、配置方法和调优技巧。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL并行查询的概念

PostgreSQL并行查询是指将单个查询任务分解为多个子任务，由多个进程同时执行，以提高查询性能的技术。并行查询可以充分利用多核CPU资源，加速复杂查询的执行，特别是对于大型表的扫描、聚合和连接操作。更多视频教程www.fgedu.net.cn

PostgreSQL并行查询的主要特点：

利用多核CPU资源：充分利用服务器的多核CPU
加速复杂查询：特别是大型表的扫描、聚合和连接操作
自动并行化：优化器自动决定是否使用并行查询
可配置性：通过参数控制并行查询的行为

1.2 PostgreSQL并行查询架构

PostgreSQL并行查询的架构包括：

# PostgreSQL并行查询架构
– 领导者进程（Leader Process）：负责协调并行查询的执行
– 工作者进程（Worker Process）：执行并行任务的进程
– 并行执行计划：包含并行操作的执行计划
– 并行度（Parallel Degree）：并行工作者进程的数量

# 并行查询执行流程
1. 领导者进程解析查询语句
2. 优化器生成并行执行计划
3. 领导者进程启动多个工作者进程
4. 工作者进程执行并行任务
5. 领导者进程收集和处理工作者进程的结果
6. 领导者进程返回最终结果给客户端

# 并行操作类型
– 并行顺序扫描（Parallel Seq Scan）
– 并行索引扫描（Parallel Index Scan）
– 并行索引只扫描（Parallel Index Only Scan）
– 并行位图扫描（Parallel Bitmap Heap Scan）
– 并行聚合（Parallel Aggregate）
– 并行连接（Parallel Join）
– 并行排序（Parallel Sort）

1.3 PostgreSQL并行查询的优势

PostgreSQL并行查询的优势包括：

提高查询性能：充分利用多核CPU资源，加速查询执行
处理大型数据集：更好地处理大型表和复杂查询
降低响应时间：减少查询的响应时间，提高用户体验
资源利用率高：充分利用服务器资源，提高系统整体性能
自动并行化：优化器自动决定是否使用并行查询，无需手动干预

Part02-生产环境规划与建议

2.1 PostgreSQL并行查询规划

PostgreSQL并行查询规划要点：

# 并行查询规划步骤
1. 分析系统硬件：了解服务器的CPU核心数和内存大小
2. 分析工作负载：了解查询类型和数据量
3. 确定并行度：根据硬件和工作负载确定合适的并行度
4. 配置并行查询参数：设置合适的并行查询参数
5. 测试并行查询效果：在测试环境中测试并行查询效果
6. 监控和调整：监控并行查询的执行情况，根据需要调整参数

# 并行度规划
– CPU核心数：并行度不应超过CPU核心数
– 内存大小：并行度越高，内存使用越大
– 查询类型：复杂查询适合更高的并行度
– 数据量：大型表适合更高的并行度

# 并行查询适用场景
– 大型表的全表扫描
– 复杂的聚合查询
– 大型表的连接操作
– 数据仓库查询
– 分析型查询

# 并行查询不适用场景
– 小型表的查询
– 简单查询
– 高并发场景
– 内存不足的场景

2.2 并行查询配置建议

PostgreSQL并行查询配置建议：

# 并行查询配置参数
– max_worker_processes：最大工作者进程数，建议设置为CPU核心数
– max_parallel_workers：最大并行工作者进程数，建议设置为CPU核心数的一半
– max_parallel_workers_per_gather：每个Gather节点的最大并行工作者数，建议设置为4-8
– parallel_leader_participation：领导者进程是否参与工作，建议设置为on
– min_parallel_table_scan_size：表扫描的最小大小，建议设置为8MB
– min_parallel_index_scan_size：索引扫描的最小大小，建议设置为5MB
– force_parallel_mode：强制并行模式，建议设置为off

# 内存参数配置
– work_mem：每个工作者进程的工作内存，建议根据并行度调整
– maintenance_work_mem：维护操作的内存，建议适当调整

# 其他配置参数
– effective_cache_size：有效缓存大小，建议设置为总内存的50-75%
– random_page_cost：随机页面访问成本，根据存储类型调整
– sequential_page_cost：顺序页面访问成本，根据存储类型调整

# 配置建议
– 对于4核心CPU：max_worker_processes=4, max_parallel_workers=2, max_parallel_workers_per_gather=2
– 对于8核心CPU：max_worker_processes=8, max_parallel_workers=4, max_parallel_workers_per_gather=4
– 对于16核心CPU：max_worker_processes=16, max_parallel_workers=8, max_parallel_workers_per_gather=8

2.3 并行查询适用场景

并行查询适用场景：

学习交流加群风哥微信: itpux-com

数据仓库查询：大型表的聚合和分析查询
ETL操作：数据提取、转换和加载操作
报表生成：复杂的报表查询
大型表扫描：需要全表扫描的查询
复杂连接：多个大型表的连接操作
聚合查询：包含GROUP BY、COUNT、SUM等聚合函数的查询

风哥提示：并行查询可以显著提高复杂查询的性能，但需要根据系统硬件和工作负载合理配置，避免过度并行导致系统资源竞争。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 并行查询配置

3.1.1 调整并行查询参数

# 查看当前并行查询参数
$ sudo -u pgsql psql -c “SHOW max_worker_processes;”

max_worker_processes
———————-
8
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers;”

max_parallel_workers
———————-
4
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers_per_gather;”

max_parallel_workers_per_gather
———————————-
2
(1 row)

# 调整并行查询参数
$ sudo vi /postgresql/data/postgresql.conf

# 修改并行查询参数
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 4
parallel_leader_participation = on
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 5MB
force_parallel_mode = off

# 保存并退出

# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql

# 验证修改结果
$ sudo -u pgsql psql -c “SHOW max_worker_processes;”

max_worker_processes
———————-
8
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers;”

max_parallel_workers
———————-
4
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers_per_gather;”

max_parallel_workers_per_gather
———————————-
4
(1 row)

3.1.2 调整内存参数

# 调整内存参数
$ sudo vi /postgresql/data/postgresql.conf

# 修改内存参数
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 验证修改结果
$ sudo -u pgsql psql -c “SHOW work_mem;”

work_mem
———-
16MB
(1 row)

$ sudo -u pgsql psql -c “SHOW maintenance_work_mem;”

maintenance_work_mem
———————-
512MB
(1 row)

$ sudo -u pgsql psql -c “SHOW effective_cache_size;”

effective_cache_size
———————-
12GB
(1 row)

3.2 并行查询监控

3.2.1 查看并行查询执行计划

# 查看并行查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT department, COUNT(*) FROM fgedu_employees GROUP BY department;”

QUERY PLAN
——————————————————————————————————————————————–
Finalize GroupAggregate (cost=1000.58..1000.70 rows=5 width=38) (actual time=0.123..0.125 rows=5 loops=1)
Group Key: department
-> Gather (cost=1000.58..1000.65 rows=10 width=38) (actual time=0.115..0.119 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=0.58..0.60 rows=5 width=38) (actual time=0.045..0.046 rows=3 loops=3)
Group Key: department
-> Parallel Seq Scan on fgedu_employees (cost=0.00..0.50 rows=3333 width=14) (actual time=0.008..0.015 rows=3333 loops=3)
Planning Time: 0.123 ms
Execution Time: 0.156 ms

# 查看非并行查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE id = 1;”

QUERY PLAN
——————————————————————————————————————————
Index Scan using fgedu_employees_pkey on fgedu_employees (cost=0.29..8.31 rows=1 width=38) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (id = 1)
Planning Time: 0.056 ms
Execution Time: 0.025 ms

3.2.2 监控并行查询进程

# 查看并行查询进程
$ ps aux | grep pgsql | grep worker

pgsql 12345 0.0 0.1 123456 7890 ? Ss 10:00 0:00 pgsql: worker process: parallel worker for PID 67890
pgsql 12346 0.0 0.1 123456 7890 ? Ss 10:00 0:00 pgsql: worker process: parallel worker for PID 67890

# 查看系统进程
$ top

# 查看PostgreSQL活动进程
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_activity WHERE state = ‘active’;”

3.2.3 监控并行查询性能

# 监控并行查询性能
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_progress_parallel_vacuum;”

# 查看系统资源使用情况
$ vmstat 1

procs ———–memory———- —swap– —–io—- -system– ——cpu—–
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1024000 12345 678900 0 0 0 0 123 456 5 2 93 0 0

# 查看磁盘IO使用情况
$ iostat -x 1

# 查看CPU使用情况
$ mpstat -P ALL 1

3.3 并行查询调优

3.3.1 调整并行度

# 调整并行度
$ sudo vi /postgresql/data/postgresql.conf

# 修改并行度参数
max_parallel_workers_per_gather = 8

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 测试并行查询性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT department, AVG(salary) FROM fgedu_employees GROUP BY department;”

# 调整min_parallel_table_scan_size参数
$ sudo vi /postgresql/data/postgresql.conf

# 修改min_parallel_table_scan_size参数
min_parallel_table_scan_size = 16MB

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.3.2 优化查询语句

# 优化查询语句

# 原始查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees e JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000;”

# 优化查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, e.department, s.amount FROM fgedu_employees e JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000;”

# 创建索引
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_employee_id ON fgedu_fgfgfgfgsales(employee_id);”

# 重新分析表
$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE fgedu_employees;”
$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE fgedu_fgfgfgfgsales;”

# 测试优化后的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, e.department, s.amount FROM fgedu_employees e JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000;”

Part04-生产案例与实战讲解

4.1 并行查询配置案例

4.1.1 案例描述

场景：一个拥有8核心CPU的服务器，运行PostgreSQL数据库，需要配置并行查询以提高复杂查询的性能。

from oracle:www.itpux.com

4.1.2 实施方案

# 1. 分析系统硬件

$ lscpu

Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 8
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) CPU E5-2680 v4 @ 2.40GHz
Stepping: 2
CPU MHz: 2400.000
BogoMIPS: 4800.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 35840K
NUMA node0 CPU(s): 0-7

# 2. 配置并行查询参数

$ sudo vi /postgresql/data/postgresql.conf

# 调整内存参数
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

# 保存并退出

# 3. 重新启动PostgreSQL服务

$ sudo systemctl restart postgresql

# 4. 验证配置

$ sudo -u pgsql psql -c “SHOW max_worker_processes;”

max_worker_processes
———————-
8
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers;”

max_parallel_workers
———————-
4
(1 row)

$ sudo -u pgsql psql -c “SHOW max_parallel_workers_per_gather;”

max_parallel_workers_per_gather
———————————-
4
(1 row)

# 5. 测试并行查询

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT department, COUNT(*), AVG(salary) FROM fgedu_employees GROUP BY department;”

QUERY PLAN
——————————————————————————————————————————————–
Finalize GroupAggregate (cost=1000.58..1000.75 rows=5 width=46) (actual time=0.135..0.137 rows=5 loops=1)
Group Key: department
-> Gather (cost=1000.58..1000.67 rows=12 width=46) (actual time=0.128..0.131 rows=12 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=0.58..0.60 rows=3 width=46) (actual time=0.035..0.036 rows=3 loops=5)
Group Key: department
-> Parallel Seq Scan on fgedu_employees (cost=0.00..0.50 rows=2000 width=18) (actual time=0.006..0.012 rows=2000 loops=5)
Planning Time: 0.145 ms
Execution Time: 0.168 ms

4.2 并行查询优化案例

4.2.1 案例描述

场景：一个复杂的查询执行缓慢，需要通过并行查询优化来提高性能。

4.2.2 实施方案

# 1. 分析慢查询

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT e.name, d.name AS department_name, s.amount FROM fgedu_employees e JOIN fgedu_departments d ON e.department = d.name JOIN fgedu_fgfgfgfgsales s ON e.id = s.employee_id WHERE e.salary > 8000 AND s.amount > 10000;”

# 2. 检查并行查询配置

$ sudo -u pgsql psql -c “SHOW max_parallel_workers_per_gather;”

max_parallel_workers_per_gather
———————————-
2
(1 row)

# 3. 调整并行查询参数

$ sudo vi /postgresql/data/postgresql.conf

# 修改并行查询参数
max_parallel_workers_per_gather = 4
min_parallel_table_scan_size = 4MB

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 4. 创建必要的索引

$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_employee_id_amount ON fgedu_fgfgfgfgsales(employee_id, amount);”

# 5. 重新分析表

$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE fgedu_employees;”
$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE fgedu_departments;”
$ sudo -u pgsql psql -d fgedu_production -c “ANALYZE fgedu_fgfgfgfgsales;”

# 6. 测试优化后的查询

# 7. 监控查询性能

4.3 并行查询性能测试案例

4.3.1 案例描述

场景：需要测试不同并行度对查询性能的影响，以确定最佳并行度配置。

4.3.2 实施方案

# 1. 创建测试数据

$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_large_table (id serial PRIMARY KEY, name varchar(50), value numeric(10,2), created_at timestamp);”

$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_large_table (name, value, created_at) SELECT ‘Test ‘ || i, random() * 10000, NOW() – (i || ‘ days’)::interval FROM generate_series(1, 1000000) i;”

# 2. 测试不同并行度的性能

# 测试并行度为2
$ sudo vi /postgresql/data/postgresql.conf
max_parallel_workers_per_gather = 2
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT value, COUNT(*) FROM fgedu_large_table GROUP BY value ORDER BY value;”

# 测试并行度为4
$ sudo vi /postgresql/data/postgresql.conf
max_parallel_workers_per_gather = 4
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT value, COUNT(*) FROM fgedu_large_table GROUP BY value ORDER BY value;”

# 测试并行度为8
$ sudo vi /postgresql/data/postgresql.conf
max_parallel_workers_per_gather = 8
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT value, COUNT(*) FROM fgedu_large_table GROUP BY value ORDER BY value;”

# 3. 测试不同查询类型的并行性能

# 测试聚合查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT AVG(value), MAX(value), MIN(value) FROM fgedu_large_table;”

# 测试连接查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT l1.id, l1.name, l2.value FROM fgedu_large_table l1 JOIN fgedu_large_table l2 ON l1.id = l2.id WHERE l1.value > 5000;”

# 测试排序查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_large_table ORDER BY value DESC LIMIT 100;”

# 4. 分析测试结果

# 比较不同并行度的执行时间
# 分析并行度对不同查询类型的影响
# 确定最佳并行度配置

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议根据系统硬件和工作负载，合理配置并行查询参数。通过测试不同并行度的性能，确定最佳配置，以提高复杂查询的性能。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL并行查询最佳实践

PostgreSQL并行查询最佳实践：

根据硬件配置调优：根据服务器的CPU核心数和内存大小调整并行查询参数
根据工作负载调优：根据查询类型和数据量调整并行度
测试验证：在测试环境中测试不同并行度的性能，确定最佳配置
监控并行查询：监控并行查询的执行情况，及时发现和解决问题
合理设置并行度：并行度不应超过CPU核心数，避免过度并行导致资源竞争
优化查询语句：优化查询语句，提高并行查询的效果
创建必要的索引：创建合适的索引，提高查询性能
定期分析表：定期分析表，更新统计信息，确保优化器能够生成正确的并行执行计划

5.2 并行查询常见问题

并行查询常见问题及解决方案：

并行度设置过高：导致CPU和内存资源竞争，降低性能，解决方案：调整并行度参数
并行查询不启动：查询不符合并行条件，解决方案：调整min_parallel_table_scan_size参数
并行查询性能下降：资源竞争导致性能下降，解决方案：调整并行度和内存参数
内存使用过高：并行查询使用过多内存，解决方案：调整work_mem参数
查询计划不佳：优化器生成的并行执行计划不合理，解决方案：更新统计信息，优化查询语句
系统负载过高：并行查询导致系统负载过高，解决方案：限制并行度，避免在业务高峰期执行复杂查询
连接数过多：并行查询增加了连接数，解决方案：使用连接池，合理控制并发连接数
存储IO瓶颈：并行查询导致IO瓶颈，解决方案：使用更快的存储设备，优化存储配置

5.3 并行查询调优技巧

并行查询调优技巧：

并行度调优：

根据CPU核心数设置max_worker_processes
根据查询复杂度设置max_parallel_workers_per_gather
对于大型表，设置较小的min_parallel_table_scan_size
对于小型表，设置较大的min_parallel_table_scan_size，避免不必要的并行

内存调优：

根据并行度调整work_mem参数
确保有足够的内存支持并行查询
监控内存使用情况，避免内存不足

查询优化：

优化查询语句，减少不必要的列和行
创建合适的索引，提高查询性能
使用合适的连接方式，提高并行连接的效果
避免使用复杂的表达式和函数，影响并行度

系统调优：

使用高速存储设备，如SSD
调整操作系统参数，优化IO性能
监控系统资源使用情况，避免资源瓶颈
合理安排查询执行时间，避免业务高峰期

风哥提示：并行查询是提高PostgreSQL性能的重要技术，需要根据系统硬件和工作负载合理配置。通过测试不同并行度的性能，确定最佳配置，以充分利用服务器资源，提高查询性能。from PostgreSQL:www.itpux.com

持续改进：并行查询调优是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议定期测试并行查询性能，根据反馈持续调整配置参数，以保持系统的最佳性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG091-PG并行查询：配置与适用场景优化

Part01-基础概念与理论知识

1.1 PostgreSQL并行查询的概念

1.2 PostgreSQL并行查询架构

1.3 PostgreSQL并行查询的优势

Part02-生产环境规划与建议

2.1 PostgreSQL并行查询规划

2.2 并行查询配置建议

2.3 并行查询适用场景

Part03-生产环境项目实施方案

3.1 并行查询配置

3.1.1 调整并行查询参数

3.1.2 调整内存参数

3.2 并行查询监控

3.2.1 查看并行查询执行计划

3.2.2 监控并行查询进程

3.2.3 监控并行查询性能

3.3 并行查询调优

3.3.1 调整并行度

3.3.2 优化查询语句

Part04-生产案例与实战讲解

4.1 并行查询配置案例

4.1.1 案例描述

4.1.2 实施方案

4.2 并行查询优化案例

4.2.1 案例描述

4.2.2 实施方案

4.3 并行查询性能测试案例

4.3.1 案例描述

4.3.2 实施方案

Part05-风哥经验总结与分享

5.1 PostgreSQL并行查询最佳实践

5.2 并行查询常见问题

5.3 并行查询调优技巧

相关推荐

联系我们