10、PostgresSQL并行查询

一、背景

随着SSD等磁盘技术的平民化，以及动辄上百GB内存的普及，I/O层面的性能问题得到了有效缓解。现代服务器除了磁盘和内存资源的增强，多CPU的配置也足够强大。数据库的Join、聚合等操作内存耗费比较大，很多时间花在了数据的交换和缓存上，CPU的利用率并不高，所以面向CPU的加速策略中，并发执行是一种常见的方法。通过把Join、聚合、排序等操作分解成多个操作实现并行，并行查询提升点是并行查询不是因为并行读取提升性能，而是数据分散到许多CPU核心上，CPU并行处理数据

二、版本支持情况

9.6  版本之前不支持并行查询

9.6+版本支持并行查询有限

10   版本暂不支持非btree索引类型的并行索引扫描

10+ 版本增强并行查询功能

三、并行查询原理机制

查询执行总是在“leader”进程中开始。一个leader执行所有非并行活动及其对并行处理的作用。执行相同查询的其他进程称为“worker”进程。并行执行使用动态后台工作器基础结构（在9.4中添加）。由于PostgreSQL的其他部分使用进程，而不是线程，因此创建三个工作进程的查询可能比传统的执行速度快4倍。

通信：
Workers使用消息队列(基于共享内存)与leader通信。每个进程有两个队列：一个是error队列；一个是tuples（元组）队列。

四、并行查询限制

• 如果所有CPu内核都已饱和，则不要启用并行执行。并行执行从其他查询中窃取CPU时间，并增加响应时间。
• 最重要的是，并行处理显著增加了具有高WORK-MEM值的内存使用量，因为每个hash连接或排序操作占用一个WORK-MEM内存量。
• 下一步，低延迟的OLTP查询在并行执行时不能再快了。特别是，当启用并行执行时，返回单行的查询可能耗时更长。
• 窗口函数和有序集聚合函数是非并行的。对Io绑定的工作负载没有好处。
• Pierian spring对于开发人员来说是一个TPC-H基准。检查是否有类似的查询以获得最佳并行执行。
• 并行执行只支持不带锁谓词的SELECT查询。
• 正确的索引可能是并行顺序表扫描的更好选择。不支持游标或挂起的查询。
• 没有并行排序算法。但是，使用排序的查询在某些方面仍然可以并行。将CTE（替换为...)替换为支持并行执行的子选择。
• 外部数据包装器当前不支持并行执行（但它们可以!)不支持完全外部联接。
• 设置最大行数的客户端禁用并行执行。
• 如果查询使用未标记为并行安全的函数，则它将是单线程的。可序列化事务隔离级别禁用并行执行。

五、参数

这些参数是针对整个实例，并非是一个session

max_parallel_workers_per_gather :是对workers数量的最小限制。，默认值为2,设置成0表示禁用并行查询
max_parallel_workers:查询执行器从受max_parallel_workers尺寸限制的池中获取workers 。
max_worker_processes:这个是workers的顶级限制后台进程的总数(此参数不允许修改，系统会自动根据实际的cpu个数（核数）来设置)。

这三个参数的配置值大小关系通常如下所示：
max_worker_processes > max_parallel_workers >max_parallel_workers_per_gather

表、索引大小影响分配worker数

min_parallel_index_scan_size：默认64M

min_parallel_table_scan_size：默认1024M

select name, setting from pg_settings where name like '%min_parallel%'

workers分配个数管理，他根据表或索引大小增加而增加，具体如规则

达到1024M表 => 1worker

达到3072M表 => 2worker

达到9216M表 => 3worker

可以单独为某个表设置worker数

(具体分配时候还是要看（max_parallel_workers_per_gather 、max_parallel_workers、max_worker_processes)这些参数

alter table tb_user set (parallel_workers=N)

动态修改系workers统参数

不重启服务器情况增加工作线程

alter system set max_parallel_workers_per_gather=3;

select * from pg_reload_conf();

检查成本参数

通过成本判断是否使用并行

“parallel_setup_cost”以避免短查询的并行执行。它模拟了内存设置、进程启动和初始通信所花费的时间。执行计划中累积成本超过该值就可能会使用并行，默认值1000。
“parallel_tuple_cost”: ' leader和 workers之间的沟通可能需要很长时间。时间与workers发送的元组数成正比。该参数模拟了通信成本。默认值0.1

六、可以使用并行的地方

聚合处理

嵌套循环

Hash Join

PostgreSQL 11及以前版本，每个workers进程都构建自己的哈希表。结果，4+workers进程无法提高绩效。
直到PostgresQL 12，新实现使用共享哈希表。每个工人都可以利用WORK-MEM来构建哈希表。

merger join

partition-wise join

如果连接表的分区键之间存在相等连接条件，那么两个类似分区表之间的连接可以分解为它们的匹配分区之间的连接。分区键之间的等连接意味着一个分区表的给定分区中给定行的所有连接伙伴必须在另一个分区表的相应分区中。因此，分区表之间的连接可以分解为匹配分区之间的连接。这种将分区表之间的连接分解为分区之间的连接的技术称为partition-wise join。

PostgresQL 12默认禁用分区连接功能。分区连接的规划成本很高。类似分区表的连接可以按匹配的分区进行。这允许postgres使用更小的哈希表。每个分区连接操作都可以并行执行。
set enable_partitionwise_join=t;
explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 1000o;

由于并行查询成本估计，改查询不会用到并行，可以改变成本估算设置:
tpch=# set parallel_setup_cost = 1;--默认值为1000
tpch=# set parallel_tuple_cost = 0.01;--默认值为0.1