一、环境准备
1、环境信息
192.168.184.191 pg-01
2、环境准备
安装好postgresql14.10版本数据库，可参考：
CentOS7.5【脚本自动编译】安装PostgreSQL14.10
3、创建测试数据
（1）创建测试表1
-- 创建测试表
CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
random_int INTEGER,
random_text TEXT
);
-- 插入随机数据
DO $$
DECLARE
i INTEGER;
random_text_length INTEGER;
random_text_value TEXT;
BEGIN
FOR i IN 1..2000000 LOOP
-- 生成随机长度的文本
random_text_length := 50 + (random() * 150)::INTEGER;
random_text_value := substr(md5(random()::TEXT), 1, random_text_length);
-- 插入随机数据
INSERT INTO test_table (random_int, random_text)
VALUES ((random() * 1000000)::INTEGER, random_text_value);
END LOOP;
END $$;
-- 检查表大小,反复执行上述插入数据，达到自己想要的数据大小
SELECT pg_size_pretty(pg_total_relation_size('test_table')) AS table_size;
-- 收集统计信息
ANALYZE test_table;
（2）创建测试表2
-- 创建第二个测试表
CREATE TABLE test_table2 (
id SERIAL PRIMARY KEY,
test_table_id INTEGER,
random_value NUMERIC
);
-- 插入随机数据到第二个表
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..2000000 LOOP
INSERT INTO test_table2 (test_table_id, random_value)
VALUES ((random() * 2000000)::INTEGER, random() * 1000);
END LOOP;
END $$;
-- 检查表大小,反复执行上述插入数据，达到自己想要的数据大小
SELECT pg_size_pretty(pg_total_relation_size('test_table2')) AS table_size;
-- 收集统计信息
ANALYZE test_table2;
-- 执行复杂查询并查看执行计划
EXPLAIN (ANALYZE, VERBOSE)
SELECT t1.random_int, AVG(t2.random_value) AS avg_value
FROM test_table t1
JOIN test_table2 t2 ON t1.id = t2.test_table_id
GROUP BY t1.random_int
ORDER BY avg_value DESC
LIMIT 10;
（3）并行未开启执行计划如下
-- 执行复杂查询并查看执行计划
EXPLAIN (ANALYZE, VERBOSE)
SELECT t1.random_int, AVG(t2.random_value) AS avg_value
FROM test_table t1
JOIN test_table2 t2 ON t1.id = t2.test_table_id
GROUP BY t1.random_int
ORDER BY avg_value DESC
LIMIT 10;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=307446.92..307446.95 rows=10 width=36) (actual time=3397.521..3397.525 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
-> Sort (cost=307446.92..309129.12 rows=672879 width=36) (actual time=3397.519..3397.522 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
Sort Key: (avg(t2.random_value)) DESC
Sort Method: top-N heapsort Memory: 26kB
-> HashAggregate (cost=264964.01..292906.25 rows=672879 width=36) (actual time=2438.736..3302.394 rows=717509 loops=1)
Output: t1.random_int, avg(t2.random_value)
Group Key: t1.random_int
Planned Partitions: 64 Batches: 321 Memory Usage: 4281kB Disk Usage: 126016kB
-> Hash Join (cost=71505.00..136839.01 rows=2000000 width=15) (actual time=496.254..1871.620 rows=1999999 loops=1)
Output: t1.random_int, t2.random_value
Inner Unique: true
Hash Cond: (t2.test_table_id = t1.id)
-> Seq Scan on public.test_table2 t2 (cost=0.00..32739.00 rows=2000000 width=15) (actual time=0.049..219.616 rows=2000000 loops=1)
Output: t2.random_value, t2.test_table_id
-> Hash (cost=38692.00..38692.00 rows=2000000 width=8) (actual time=495.516..495.517 rows=2000000 loops=1)
Output: t1.random_int, t1.id
Buckets: 131072 Batches: 32 Memory Usage: 3477kB
-> Seq Scan on public.test_table t1 (cost=0.00..38692.00 rows=2000000 width=8) (actual time=0.006..222.160 rows=2000000 loops=1)
Output: t1.random_int, t1.id
Query Identifier: 4332992826514698635
Planning Time: 0.398 ms
Execution Time: 3411.009 ms
(24 rows)
二、并行查询开启条件
1、并行查询的适用条件
并行查询的适用条件
并行查询在PostgreSQL中是一项可以显著提高查询性能的功能，但其使用受到多种因素的限制。以下是一些关键的配置和条件，它们决定了并行查询是否可以被应用：
（1）max_parallel_workers_per_gather
max_parallel_workers_per_gather必须设置为大于零的值。
这意味着至少有一个并行工作者可以被用于并行查询计划的执行。
（2）系统不能处于单用户模式
系统不能处于单用户模式。在单用户模式下，整个数据库系统作为单一进程运行，因此无法启动背景工作者进程。
2、并行查询不适用情况
即使并行查询计划理论上可以生成，但如果出现以下情况之一，查询优化器将不会生成并行计划：
（1）查询涉及数据写入或行级锁
如果查询包含数据修改操作（无论是顶级操作还是公共表表达式（CTE）内的操作），则不会为该查询生成并行计划。
例外情况是创建新表并填充数据的命令，这些命令可以使用并行计划：
CREATE TABLE … AS
SELECT INTO
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
（2）查询可能在执行过程中被挂起
如果系统认为查询的执行可能会被部分或增量的中断，那么不会生成并行计划。
例如，使用DECLARE CURSOR创建的游标永远不会使用并行计划。
同样地，形式如FOR x IN query LOOP … END LOOP的PL/pgSQL循环也不会使用并行计划，因为并行查询系统无法确保循环代码在并行查询活跃时安全执行。
（3）查询使用了标记为PARALLEL UNSAFE的函数
大多数系统定义的函数是PARALLEL SAFE的，但用户定义的函数默认被标记为PARALLEL UNSAFE。
（4）查询在另一个已经并行的查询内部运行
例如，如果一个并行查询调用的函数自身发出SQL查询，那么该查询将不会使用并行计划。
这是一个当前实现的限制，而且可能不希望移除这一限制，以免单个查询使用过多的进程。
3、执行时的限制
即使为特定查询生成了并行查询计划，在执行时也可能因以下情况之一而无法并行执行：
（1）背景工作者不足
如果由于max_worker_processes的限制，无法获取到足够的背景工作者。
（2）并行工作者数量超出限制
如果由于max_parallel_workers的限制，无法获取到足够的并行工作者。
（3）客户端发送带有非零获取计数的Execute消息
这通常发生在不依赖libpq的客户端中。如果这种情况频繁发生，可以考虑在可能发生串行执行的会话中将max_parallel_workers_per_gather设置为零，以避免生成在串行执行时可能次优的查询计划。
三、并行查询配置
1、修改postgresql.conf配置
cd /data/postgresql/pgdata
vim postgresql.conf
修改以下参数
max_parallel_workers_per_gather = 4;
max_parallel_workers = 16;
max_worker_processes = 16;
parallel_setup_cost = 1000;
parallel_tuple_cost = 0.1;
min_parallel_table_scan_size = 8MB;
min_parallel_index_scan_size = 512KB;
jit = on;
2、重启数据库
su - mydba
pg_ctl restart
pg_ctl status
3、再次执行查询
-- 执行复杂查询并查看执行计划
EXPLAIN (ANALYZE, VERBOSE)
SELECT t1.random_int, AVG(t2.random_value) AS avg_value
FROM test_table t1
JOIN test_table2 t2 ON t1.id = t2.test_table_id
GROUP BY t1.random_int
ORDER BY avg_value DESC
LIMIT 10;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=376576.04..376576.07 rows=10 width=36) (actual time=3425.935..3454.169 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
-> Sort (cost=376576.04..378527.81 rows=780708 width=36) (actual time=3425.934..3454.167 rows=10 loops=1)
Output: t1.random_int, (avg(t2.random_value))
Sort Key: (avg(t2.random_value)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=232414.56..359705.22 rows=780708 width=36) (actual time=1536.253..3322.661 rows=822508 loops=1)
Output: t1.random_int, avg(t2.random_value)
Group Key: t1.random_int
-> Gather Merge (cost=232414.56..326525.13 rows=3122832 width=36) (actual time=1536.246..2242.094 rows=1513177 loops=1)
Output: t1.random_int, (PARTIAL avg(t2.random_value))
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=232314.50..249573.30 rows=780708 width=36) (actual time=1460.530..1877.445 rows=302635 loops=5)
Output: t1.random_int, PARTIAL avg(t2.random_value)
Group Key: t1.random_int
Worker 0: actual time=1452.893..1829.912 rows=269927 loops=1
Worker 1: actual time=1447.372..1949.316 rows=345027 loops=1
Worker 2: actual time=1420.501..1809.073 rows=265385 loops=1
Worker 3: actual time=1445.882..1859.911 rows=285168 loops=1
-> Sort (cost=232314.50..234814.48 rows=999994 width=15) (actual time=1460.510..1574.450 rows=800000 loops=5)
Output: t1.random_int, t2.random_value
Sort Key: t1.random_int
Sort Method: external merge Disk: 24008kB
Worker 0: actual time=1452.868..1557.530 rows=675843 loops=1
Sort Method: external merge Disk: 16712kB
Worker 1: actual time=1447.354..1594.915 rows=929370 loops=1
Sort Method: external merge Disk: 22976kB
Worker 2: actual time=1420.478..1529.668 rows=683334 loops=1
Sort Method: external merge Disk: 16896kB
Worker 3: actual time=1445.860..1563.544 rows=740113 loops=1
Sort Method: external merge Disk: 18304kB
-> Parallel Hash Join (cost=63790.87..115566.80 rows=999994 width=15) (actual time=661.549..1104.918 rows=800000 loops=5)
Output: t1.random_int, t2.random_value
Inner Unique: true
Hash Cond: (t2.test_table_id = t1.id)
Worker 0: actual time=675.613..1100.772 rows=675843 loops=1
Worker 1: actual time=654.632..1117.668 rows=929370 loops=1
Worker 2: actual time=675.644..1097.117 rows=683334 loops=1
Worker 3: actual time=637.896..1088.461 rows=740113 loops=1
-> Parallel Seq Scan on public.test_table2 t2 (cost=0.00..35477.94 rows=999994 width=15) (actual time=0.025..135.608 rows=800000 loops=5)
Output: t2.random_value, t2.test_table_id
Worker 0: actual time=0.035..155.472 rows=828960 loops=1
Worker 1: actual time=0.030..132.829 rows=1002916 loops=1
Worker 2: actual time=0.030..118.347 rows=884021 loops=1
Worker 3: actual time=0.022..153.351 rows=538824 loops=1
-> Parallel Hash (cost=47383.94..47383.94 rows=999994 width=8) (actual time=316.469..316.470 rows=800000 loops=5)
Output: t1.random_int, t1.id
Buckets: 262144 Batches: 32 Memory Usage: 7008kB
Worker 0: actual time=317.067..317.068 rows=878596 loops=1
Worker 1: actual time=322.550..322.550 rows=688224 loops=1
Worker 2: actual time=297.070..297.071 rows=656017 loops=1
Worker 3: actual time=326.658..326.659 rows=1000450 loops=1
-> Parallel Seq Scan on public.test_table t1 (cost=0.00..47383.94 rows=999994 width=8) (actual time=0.016..120.297 rows=800000 loops=5)
Output: t1.random_int, t1.id
Worker 0: actual time=0.013..132.002 rows=878596 loops=1
Worker 1: actual time=0.019..95.735 rows=688224 loops=1
Worker 2: actual time=0.022..98.217 rows=656017 loops=1
Worker 3: actual time=0.015..138.631 rows=1000450 loops=1
Query Identifier: 1846774944933342204
Planning Time: 0.183 ms
Execution Time: 3454.233 ms
(62 rows)
扫一扫
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
