PostgreSQL 教程: 并行查询优化

三月 16, 2024

摘要:在并行处理上面有一些限制,需要注意。在本教程中,您将学习如何在 PostgreSQL 中对查询进行并行优化。

工作进程数

后台工作进程的使用不限于并行查询执行:它们可以被逻辑复制机制使用,也可以由扩展创建使用。系统最多可以同时运行 max_worker_processes 个后台工作进程(默认为 8 个)。

其中,最多可以分配 max_parallel_workers 个工作进程(默认为 8 个)用于并行查询执行。

每个领导进程允许的工作进程数,由 max_parallel_workers_per_gather 参数设置(默认为 2 个)。

您可以根据以下几个因素,选择更改这些值:

  • 硬件配置:系统必须有空余的处理器核。
  • 表大小:并行查询对于较大的表是有用的。
  • 负载类型:大多数普遍的查询都能受益于并行执行。

这些因素通常对 OLAP 系统是匹配的,而对 OLTP 不是的。

规划器甚至不会考虑并行扫描,除非它希望读取至少 min_parallel_table_scan_size 的数据(默认为 8MB)。

以下是用于计算规划工作进程数的公式:

Number of worker processes

实质上,每当表大小增加三倍时,规划器就会再增加一个并行工作进程。下面是一个采用默认参数的示例表。

表,MB 工作者数
8 1
24 2
72 3
216 4
648 5
1944 6

可以使用表的存储参数 parallel_workers,来显式设置工作进程数。

不过,工作者的数量仍会受到 max_parallel_workers_per_gather 参数的限制。

我们来查询一个 20MB 的小表。这时只会规划和创建一个工作进程(参见 Workers PlannedWorkers Launched):

CREATE TABLE testpar (id integer, str text);

INSERT INTO testpar (id, str)
SELECT i, repeat(chr(65 + mod(i, 26)), 64) as str
FROM generate_series(1, 240000) AS s(i);

ANALYZE testpar;

SELECT pg_size_pretty(pg_table_size('testpar'));
 pg_size_pretty
----------------
 20 MB
(1 row)

EXPLAIN (analyze, costs off, timing off)
SELECT count(*) FROM testpar;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Parallel Seq Scan on testpar (actual rows=120000 loops=2)
 Planning time: 3.569 ms
 Execution time: 72.964 ms
(8 rows)

现在,我们来查询一个 98MB 的表。系统只会创建两个工作进程,以遵守 max_parallel_workers_per_gather 参数的限制。

INSERT INTO testpar (id, str)
SELECT i, repeat(chr(65 + mod(i, 26)), 64) as str
FROM generate_series(240001, 1200000) AS s(i);

ANALYZE testpar;

SELECT pg_size_pretty(pg_table_size('testpar'));
 pg_size_pretty
----------------
 98 MB
(1 row)

EXPLAIN (analyze, costs off, timing off)
SELECT count(*) FROM testpar;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1 loops=3)
               ->  Parallel Seq Scan on testpar (actual rows=400000 loops=3)
 Planning time: 0.149 ms
 Execution time: 108.843 ms
(8 rows)

如果提升限制,则会创建三个工作进程:

SET max_parallel_workers_per_gather = 4;

EXPLAIN (analyze, costs off, timing off)
SELECT count(*) FROM testpar;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=4 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         ->  Partial Aggregate (actual rows=1 loops=4)
               ->  Parallel Seq Scan on testpar (actual rows=300000 loops=4)
 Planning time: 0.122 ms
 Execution time: 113.670 ms
(8 rows)

在执行查询时,如果规划的工作进程数多于系统中可用的工作进程数,则只会创建可用数量的工作进程。

不能并行化的查询

并非每个查询都可以并行化。以下是不能并行化的查询类型:

  • 修改或锁定数据的查询(UPDATEDELETESELECT FOR UPDATE等)。

    在 PostgreSQL 11 中,当在命令CREATE TABLE ASSELECT INTOCREATE MATERIALIZED VIEW(在版本 14 及更高版本中,也包括REFRESH MATERIALIZED VIEW)中调用时,此类查询仍然可以并行执行。

    但是,即使在这些情况下,所有INSERT操作仍会按顺序执行。

  • 在执行期间可以暂停的任何查询。游标内的查询,包括在 PL/pgSQL FOR 循环中的查询。

  • 调用PARALLEL UNSAFE函数的查询。默认情况下,这包括所有用户定义的函数和一些标准函数。您可以从系统表中获取到并行不安全的函数的完整列表:

    SELECT * FROM pg_proc WHERE proparallel = 'u';
    
  • 从已并行化的查询中调用的函数内部调用的查询(以避免以递归方式创建新的后台工作进程)。

未来的 PostgreSQL 版本可能会删除其中一些限制。例如,版本 12 支持了在可序列化隔离级别并行化查询的能力。

查询不会并行运行的原因可能有多种:

  • 它本来就是不能并行的。
  • 您的配置阻止了生成并行计划(包括当一个表小于并行化阈值的情况)。
  • 并行计划比顺序计划估算的成本更高。

如果要强制一个查询采用并行执行(出于探索或其他目的),可以设置参数 force_parallel_mode。这会让规划器始终生成并行计划,除非查询严格地不能并行:

TRUNCATE testpar;

INSERT INTO testpar (id, str)
SELECT i, repeat(chr(65 + mod(i, 26)), 64) as str
FROM generate_series(1, 50000) AS s(i);

ANALYZE testpar;

SELECT pg_size_pretty(pg_table_size('testpar'));
 pg_size_pretty
----------------
 4200 kB
(1 row)

EXPLAIN SELECT * FROM testpar;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on testpar  (cost=0.00..1021.00 rows=50000 width=69)
(1 row)

SET force_parallel_mode = on;

EXPLAIN SELECT * FROM testpar;
                             QUERY PLAN
---------------------------------------------------------------------
 Gather  (cost=1000.00..7021.00 rows=50000 width=69)
   Workers Planned: 1
   Single Copy: true
   ->  Seq Scan on testpar  (cost=0.00..1021.00 rows=50000 width=69)
(4 rows)

并行受限的查询

一般来说,并行计划的好处主要取决于计划中有多少部分是并行运行的。然而,有些操作虽然在技术上不会妨碍并行化,但只能按顺序执行,并且只能由领导进程执行。换言之,这些操作不可能出现在计划的并行部分中,即 Gather 下方。

让我们开始设置一个小的测试用例:

TRUNCATE testpar;

INSERT INTO testpar (id, str)
SELECT i, repeat(chr(65 + mod(i, 26)), 64) as str
FROM generate_series(1, 240000) AS s(i);

ANALYZE testpar;

不可展开的子查询。包含不可展开子查询的操作的一个基本示例是,公共表表达式扫描(下面的CTE Scan节点):

EXPLAIN (costs off)
WITH t AS MATERIALIZED (
  SELECT * FROM testpar
)
SELECT count(*) FROM t;
         QUERY PLAN
-----------------------------
 Aggregate
   CTE t
     ->  Seq Scan on testpar
   ->  CTE Scan on t
(4 rows)

如果公共表表达式没有被物化(物化特性仅在 PostgreSQL 12 及更高版本中才支持),则不会有 CTE Scan 节点,也不会有问题。

表达式本身是可以并行处理的,只要它是一个更快的选项。

EXPLAIN (costs off)
WITH t AS MATERIALIZED (
  SELECT count(*) FROM testpar
)
SELECT * FROM t;
                       QUERY PLAN
--------------------------------------------------------
 CTE Scan on t
   CTE t
     ->  Finalize Aggregate
           ->  Gather
                 Workers Planned: 1
                 ->  Partial Aggregate
                       ->  Parallel Seq Scan on testpar
(7 rows)

另一个不可展开的子查询的示例是,带有 SubPlan 节点的查询。

EXPLAIN (costs off)
SELECT *
FROM flights f
WHERE f.scheduled_departure > ( -- SubPlan
  SELECT min(f2.scheduled_departure)
  FROM flights f2
  WHERE f2.aircraft_code = f.aircraft_code
);
                      QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights f
   Filter: (scheduled_departure > (SubPlan 1))
   SubPlan 1
     > Aggregate
         > Seq Scan on flights f2
            Filter: (aircraft_code = f.aircraft_code)
(6 rows)

前两行显示了主查询的计划:扫描flights表并筛选每一行。筛选条件包括了一个子查询,其计划紧跟主计划。SubPlan 节点会执行多次:在本例中,每扫描一行执行一次。

Seq Scan 父节点无法并行化,因为它需要 SubPlan 输出才能继续。

最后一个示例是,执行由 InitPlan 节点表示的不可展开的子查询。

EXPLAIN (costs off)
SELECT *
FROM flights f
WHERE f.scheduled_departure > ( -- SubPlan
  SELECT min(f2.scheduled_departure) FROM flights f2
  WHERE EXISTS ( -- InitPlan
    SELECT *
    FROM ticket_flights tf
    WHERE tf.flight_id = f.flight_id
  )
);
                      QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights f
   Filter: (scheduled_departure > (SubPlan 2))
   SubPlan 2
     > Finalize Aggregate
         InitPlan 1 (returns $1)
           > Seq Scan on ticket_flights tf
               Filter: (flight_id = f.flight_id)
         > Gather
             Workers Planned: 1
             Params Evaluated: $1
             > Partial Aggregate
                 > Result
                     OneTime Filter: $1
                     > Parallel Seq Scan on flights f2
(14 rows)

SubPlan 不同,InitPlan 节点仅执行一次(在本例中,每次执行 SubPlan 2 时执行一次)。

InitPlan 节点的父节点不能并行化,但使用到 InitPlan 输出的节点可以并行化,如上所示。

临时表。临时表只能按顺序扫描,因为只有领导进程才能访问它们。

CREATE TEMPORARY TABLE testpar_tmp AS SELECT * FROM testpar;

EXPLAIN (costs off)
SELECT count(*) FROM testpar_tmp;
          QUERY PLAN
-------------------------------
 Aggregate
   ->  Seq Scan on testpar_tmp
(2 rows)

并行受限函数。对标记为PARALLEL RESTRICTED的函数的调用,只允许出现在计划的顺序执行部分。您可以在系统表中找出受限的函数列表:

SELECT * FROM pg_proc WHERE proparallel = 'r';

只有在彻底弄清现有的限制之后,才应将您自己的函数标记为PARALLEL RESTRICTED,要非常小心(更不用说PARALLEL SAFE)。

了解更多

PostgreSQL 优化