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 Planned 和 Workers 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)

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

不能并行化的查询

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

  • 修改或锁定数据的查询（UPDATE、DELETE、SELECT FOR UPDATE等）。

    在 PostgreSQL 11 中，当在命令CREATE TABLE AS、SELECT INTO和CREATE 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
                     One−Time 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 优化

搜索

分类

标签