PostgreSQL9.6的新特性并行查询

时间:2022-07-11 12:05:14


PostgreSQL20169月发布了9.6版本,在该版本中新增了并行计算功能,目前PG支持的并行查询主要是顺序扫描(Sequencial Scans),并且支持部分链接查询(join)和聚合(aggregation)。

并行查询涉及的参数

max_worker_processes:决定了整个数据库集群允许启动多少个work process(注意如果有standbystandby的参数必须大于等于主库的参数值)。设置为0,表示不允许并行。

max_parallel_workers_per_gather: 最多会有多少个后台进程来一起完成当前查询,推荐值为1-4。这些workers主要来自max_worker_processes(进程池的大小)。在OLTP业务中,因为每个worker都会消耗同等的work_mem等资源,可能会产生比较严重的争抢。

min_parallel_relation_size: 启用并行查询的最小数据表的大小,作为是否启用并行计算的条件之一,如果小于它,不启用并行计算。并不是所有小于它的表一定不会启用并行。

parallel_setup_cost表示启动woker process的启动成本,因为启动worker进程需要建立共享内存等操作,属于附带的额外成本。其值越小,数据库越有可能使用并行查询。

parallel_tuple_costwoker进程处理完后的tuple要传输给上层node,即进程间查询结果的交换成本,即后台进程间传输一个元组的代价。其值越小,数据库越有可能使用并行。

force_parallel_mode: 主要用于测试,on/true表示强制使用并行查询。

parallel_workers:设置表级并行度,可在建表时设置,也可后期设置

PostgreSQL优化器计算并行度及如何决定使用并行

1、确定整个系统能开多少worker进程(max_worker_processes

2、计算并行计算的成本,优化器根据CBO原则选择是否开启并行(parallel_setup_costparallel_tuple_cost)。

3、强制开启并行(force_parallel_mode)。

4、根据表级parallel_workers参数决定每个查询的并行度取最小值(parallel_workers, max_parallel_workers_per_gather)

5、当表没有设置parallel_workers参数,并且表的大小大于min_parallel_relation_size时,由算法决定每个查询的并行度。

并行顺序扫描测试

什么是顺序操作

顺序操作(同oracle中的全表扫描),意味着数据库会按顺序读取整张表,逐行确认是否符合查询条件。一般来说,当你关注给定查询语句的执行时间时,需要关注顺序操作。由以上可知,对于一个单表查询来说,顺序操作的时间复杂度为O(n)。对于时间敏感的查询,走索引是更好的选择,索引(默认的二叉树索引)有更好的时间复杂度O(log(n))。但使用索引是有代价的:在进行插入和更新操作时,需要花费额外的时间更新索引,并占用额外的内存和磁盘空间。因此,在一些情况下不使用索引,走顺序操作可能是更好的选择。以上这些需要根据实际情况取舍。

首先创建一个people表,只有id(主键)age:

postgres=# CREATETABLE people (id int PRIMARY KEY NOT NULL, age int NOT NULL);

CREATE TABLE

postgres=# \d people

   Table "public.people"

Column |Type   | Modifiers

-------+---------+-----------

id     | integer | not null

age    | integer | not null

Indexes:

   "people_pkey" PRIMARY KEY, btree (id)

插入一些数据。一千万行应该足以看到并行计算的用处。表中每个人的年龄取0~100的随机数。

postgres=# INSERTINTO people SELECT id, (random()*100)::integer AS age FROM generate_series(1,10000000) AS id;

INSERT 0 10000000

现在尝试获取所有年龄为6岁的人,预计获取约百分之一的行。

postgres=# EXPLAINANALYZE SELECT * FROM people WHERE age =6;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------

 Seq Scan on people  (cost=0.00..169247.71 rows=104000 width=8) (actual time=0.052..1572.701 rows=100310 loops=1)

   Filter: (age = 6)

   Rows Removed by Filter: 9899690

 Planning time: 0.061 ms

 Execution time: 1579.476 ms

(5 rows)

上面查询花了1579.476 ms。并行查询默认是禁用的。现在启用并行查询,允许PostgreSQL最多使用两个并行,然后再次运行该查询。

postgres=# SET max_parallel_workers_per_gather = 2;

SET

postgres=# EXPLAINANALYZE SELECT * FROM people WHERE age =6;

 QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------

 Gather(cost=1000.00..107731.21 rows=104000 width=8) (actual time=0.431..892.823 rows=100310 loops=1)

   Workers Planned: 2

   Workers Launched: 2

   ->Parallel Seq Scan on people(cost=0.00..96331.21 rows=43333 width=8) (actual time=0.109..862.562 rows=33437 loops=3)

         Filter: (age = 6)

         Rows Removed by Filter: 3299897

 Planning time: 0.133 ms

 Execution time: 906.548 ms

(8 rows)

使用并行查询后,同样语句查询事件缩减到906.548 ms,还不到原来时间的一半。启用并行查询收集数据并将“收集”的数据进行聚合会带来额外的开销。每增加一个并行,开销也随之增大。有时更多的并行并不能改善查询性能。但为了验证并行的性能,你需要在数据库服务器上进行试验,因为服务器拥有更多的CPU核心。

不是所有的查询都会使用并行。例如尝试获取年龄低于50的数据(这将返回一半数据)

postgres=# EXPLAINANALYZE SELECT * FROM people WHERE age <50;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------

 Seq Scan on people  (cost=0.00..169247.71 rows=4955739 width=8) (actual time=0.079..1957.076 rows=4949330 loops=1)

   Filter: (age < 50)

   Rows Removed by Filter: 5050670

 Planning time: 0.097 ms

 Execution time: 2233.848 ms

(5 rows)

上面的查询返回表中的绝大多数数据,没有使用并行,为什么会这样呢? 当查询只返回表的一小部分时,并行计算进程启动、运行(匹配查询条件)及合并结果集的开销小于串行计算的开销。当返回表中大部分数据时,并行计算的开销可能会高于其所带来的好处。

如果要强制使用并行,可以强制设置并行计算的开销为0,如下所示:

postgres=# SET parallel_tuple_cost TO 0;

SET

postgres=# EXPLAINANALYZE SELECT * FROM people WHERE age <50;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------

 Gather(cost=1000.00..97331.21 rows=4955739 width=8) (actual time=0.424..3147.678 rows=4949330 loops=1)

   Workers Planned: 2

   Workers Launched: 2

   ->Parallel Seq Scan on people(cost=0.00..96331.21 rows=2064891 width=8) (actual time=0.082..1325.310 rows=1649777 loops=3)

         Filter: (age < 50)

         Rows Removed by Filter: 1683557

 Planning time: 0.104 ms

 Execution time: 3454.690 ms

(8 rows)

从上面结果中可以看到,强制并行后,查询语句执行时间由2233.848 ms增加到3454.690 ms,说明并行计算的开销是真实存在的。

聚合函数的并行计算测试

测试之前,现重置一下现有环境

postgres=# SET parallel_tuple_cost TO DEFAULT;

SET

postgres=# SET max_parallel_workers_per_gather TO 0;

SET

下面语句在未开启并行时,计算所有人的平均年龄

postgres=# EXPLAINANALYZE SELECT avg(age) FROM people;

                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=169247.72..169247.73 rows=1 width=32) (actual time=2751.862..2751.862 rows=1 loops=1)

   ->Seq Scan on people  (cost=0.00..144247.77 rows=9999977 width=4) (actual time=0.054..1250.670 rows=10000000 loops=1)

 Planning time: 0.054 ms

 Execution time: 2751.905 ms

(4 rows)

开启并行后,再次计算平均年龄

postgres=# SET max_parallel_workers_per_gather TO 2;

SET

postgres=# EXPLAINANALYZE SELECT avg(age) FROM people;

QUERY PLAN    

---------------------------------------------------------------------------------------------------------------------------

 Finalize Aggregate  (cost=97331.43..97331.44 rows=1 width=32) (actual time=1616.346..1616.346 rows=1 loops=1)

   ->Gather  (cost=97331.21..97331.42 rows=2 width=32) (actual time=1616.143..1616.316 rows=3 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         ->  Partial Aggregate  (cost=96331.21..96331.22 rows=1 width=32) (actual time=1610.785..1610.785 rows=1 loops=3)

               ->  Parallel Seq Scan on people  (cost=0.00..85914.57 rows=4166657 width=4) (actual time=0.067..957.355 rows=3333333 loops=3)

 Planning time: 0.248 ms

 Execution time: 1619.181 ms

(8 rows)

从上面两次查询中可以看到,并行计算将查询时间由2751.905 ms降低到了1619.181ms

join并行测试

创建测试环境。创建一个1000万行的pets表。

postgres=# CREATETABLE pets (owner_id int NOT NULL, species character(3) NOTNULL);

postgres=# CREATEINDEX pets_owner_id ON pets (owner_id);

postgres=# INSERTINTO pets SELECT (random()*10000000)::integer AS owner_id, ('{cat,dog}'::text[])[ceil(random()*2)] as species FROM generate_series(1,10000000);

不启用并行计算,执行join语句

postgres=# SET max_parallel_workers_per_gather TO 0;

SET

postgres=# EXPLAINANALYZE SELECT * FROM pets JOIN people ON pets.owner_id = people.id WHERE pets.species = 'cat' AND people.age = 18;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=171025.88..310311.99 rows=407 width=28) (actual time=1627.973..5963.378 rows=49943 loops=1)

   Hash Cond: (pets.owner_id = people.id)

   ->Seq Scan on pets  (cost=0.00..138275.00 rows=37611 width=20) (actual time=0.050..2784.238 rows=4997112 loops=1)

         Filter: (species = 'cat'::bpchar)

         Rows Removed by Filter: 5002888

   ->Hash  (cost=169247.71..169247.71 rows=108333 width=8) (actual time=1626.987..1626.987 rows=100094 loops=1)

         Buckets: 131072  Batches: 2  Memory Usage: 2974kB

         ->  Seq Scan on people  (cost=0.00..169247.71 rows=108333 width=8) (actual time=0.045..1596.765 rows=100094 loops=1)

               Filter: (age = 18)

               Rows Removed by Filter: 9899906

 Planning time: 0.466 ms

 Execution time: 5967.223 ms

(12 rows)

以上查询花费这几乎是5967.223 ms,下面启用并行计算

postgres=# SET max_parallel_workers_per_gather TO 2;

SET

postgres=# EXPLAINANALYZE SELECT * FROM pets JOIN people ON pets.owner_id = people.id WHERE pets.species = 'cat' AND people.age = 18;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Gather(cost=1000.43..244061.39 rows=53871 width=16) (actual time=0.304..1295.285 rows=49943 loops=1)

   Workers Planned: 2

   Workers Launched: 2

   ->Nested Loop  (cost=0.43..237674.29 rows=22446 width=16) (actual time=0.347..1274.578 rows=16648 loops=3)

         ->  Parallel Seq Scan on people  (cost=0.00..96331.21 rows=45139 width=8) (actual time=0.147..882.415 rows=33365 loops=3)

               Filter: (age = 18)

               Rows Removed by Filter: 3299969

         ->  Index Scan using pets_owner_id on pets  (cost=0.43..3.12 rows=1 width=8) (actual time=0.010..0.011 rows=0 loops=100094)

               Index Cond: (owner_id = people.id)

               Filter: (species = 'cat'::bpchar)

               Rows Removed by Filter: 1

 Planning time: 0.274 ms

 Execution time: 1306.590 ms

(13 rows)

由以上可知,查询语句的执行时间从5967.223 ms降低到1306.590 ms