PostgreSQL 教程: 优化 work_mem 设置

九月 3, 2025

摘要：在本教程中，您将学习如何在 PostgreSQL 中优化work_mem设置。

目录

什么是 work_mem？

在 PostgreSQL 中，work_mem用于设置进行排序和哈希处理等查询操作时，在写入到磁盘中的临时文件之前要使用的最大内存量。这对于 ORDER BY、DISTINCT 和 JOIN 等操作至关重要。默认情况下，work_mem设置为 4MB。它可以在 postgresql.conf 文件中设置，也可以在会话级别设置。

set work_mem to '64MB';

PostgreSQL 会为超出work_mem的操作，在PGDATA/base/pgsql_tmp目录内分配临时文件。work_mem是针对单个操作进行分配的，而不是整个查询。例如，如果单个查询有 8 个排序操作，则将分配 8 次work_mem，每个排序操作一次。

PostgreSQL 中的并行工作进程允许使用多个进程处理单个查询，从而划分任务以提高性能。这些工作进程通过共享内存共享中间结果进行协作。如果 2 个并行工作进程正在处理具有排序操作的查询，则每个并行工作进程会单独使用各自的work_mem。

何时该调整 work_mem？

PostgreSQL 查询面临的一个最严重的性能问题，就是排序或哈希操作不得不依赖磁盘完成。当这类内存密集型操作所需内存超过可用配额时，PostgreSQL 会转而使用磁盘空间。由于磁盘的读写速度远慢于内存，这通常会导致操作耗时大幅增加。

解决该问题的最佳方案是尽量避免这类操作本身，例如通过合理添加索引（减少排序或哈希需求）。

次优方案是减少操作所需的内存空间：例如减少处理的行数，或减少每行数据的大小。若能将内存需求控制在work_mem范围内，操作就能在内存中完成，无需依赖速度较慢的磁盘访问。

但上述两种方案并非在所有场景下都可行。若服务器有足够的空闲内存，让 PostgreSQL 在使用磁盘前分配更多内存给这类操作，通常是合理的选择。这一需求可通过调整work_mem系统配置参数实现。

在许多情况下，优化work_mem可以提高性能：

  • 当您在运行涉及大量排序或哈希操作的查询时。
  • 在磁盘上创建了大量临时文件时。
  • 当您在 EXPLAIN ANALYZE 的计划输出中看到external merge disk操作时。

调大 work_mem 的好处

调大work_mem参数有很多好处。例如，调大work_mem可以：

  • 对大型结果集进行排序是一项常见的数据库任务，适当的设置work_mem可以显著加快这一过程。它允许在内存中对更多数据进行排序，从而减少对缓慢的磁盘操作的需要。
  • 哈希连接用于在查询执行期间合并来自各种表的数据。较大的work_mem值可以更多的使用内存进行数据存储，最小化磁盘 I/O 操作的需要，来增强哈希连接的性能。
  • 通过更快的排序和连接，查询可以更快地完成执行。这也让数据库性能表现更快速，用户响应时间更短。
  • 适当调整work_mem可提升并行能力，并充分利用多核处理器，进一步提高查询效率。

注意：从 PostgreSQL 13 版本开始，新增了 hash_mem_multiplier 参数。该参数允许单独增大哈希类操作的最大可用内存，而不影响排序类操作。顾名思义，它以work_mem的值为基础进行倍数计算，默认值为 1.0。若你希望允许更大规模的哈希操作，同时避免因内存中执行更多排序操作而导致内存压力增大，增大该参数会很有帮助。

调整 work_mem 的风险

调整work_mem的值存在一定风险。work_mem定义的是单个操作（如一次独立的哈希、位图扫描或排序）可使用的内存，因此即便是单个查询，也可能会使用数倍于该值的内存。考虑到服务器通常需要同时处理多个查询，若将work_mem设置过高，极易导致服务器内存耗尽。显然，这是我们应极力避免的情况。

反之，若work_mem设置过小，会导致大量操作不得不依赖磁盘完成，而磁盘效率远低于内存，最终影响查询性能。

需要重点注意的是：work_mem过高会导致内存占用过量，可能引发内存竞争问题。因此，根据具体的工作负载找到合适的平衡点，是调整work_mem的关键。

基于此，在为整个服务器修改work_mem之前，建议先进行局部测试。你可以仅在当前会话中调整work_mem，验证效果后再决定是否全局应用。

优化 work_mem 示例

让我们通过一个示例，来更好地了解优化work_mem如何带来实质性的性能改进。

假设我们有一个名为sales_data的表：

CREATE TABLE sales_data (
  id SERIAL PRIMARY KEY,
  date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

要为sales_data表生成数百万行示例数据，您可以使用 PostgreSQL 中的generate_series函数。下面是一个示例，说明如何将 100 万行示例数据插入到sales_data表中。

下面是一个示例 SQL 查询，用于生成数据：

INSERT INTO sales_data (date, amount)
SELECT
generate_series('2020-01-01'::date, '2022-12-31'::date, '1 minute'::interval) AS date,
round(random()::numeric * 1000 + 50, 2) AS amount
FROM generate_series(1, 20);

在此查询中：

1. 我们用generate_series创建了从 2020 年 1 月 1 日到 2022 年 12 月 31 日的一系列日期，每个日期之间间隔一分钟。这将创建大约 150 万个唯一的时间戳。

2. 对于每个生成的日期，我们将创建 20 行，随机数额介于 50 到 1050 之间，四舍五入到小数点后两位。您可以更改这些限制，以填充想要填充的任意数量的行。

3. 然后使用查询的INSERT INTO sales_data (date, amount)部分，将生成的数据插入到sales_data表中。

请根据需要调整日期范围、间隔和每个时间戳的行数。如果需要超过 100 万行，可以调整generate_series函数的参数，或增加每个时间戳生成的行数。

我们已经用数百万行的示例数据填充了此表。

现在，假设我们要按日期排序检索总的销售额：

EXPLAIN ANALYZE SELECT date, SUM(amount) FROM sales_data GROUP BY date ORDER BY date;
                     QUERY PLAN
----------------------------------------------------
 ...
 Sort Method: external merge Disk: 31528kB
 Execution Time: 1426.504 ms

使用默认的work_mem设置时，您可能会注意到 PostgreSQL 正在使用基于磁盘的排序操作，这可能会很慢。

现在，让我们将work_mem调大到 64MB，看看它如何影响性能：

SET work_mem = '64MB';

EXPLAIN ANALYZE SELECT date, SUM(amount) FROM sales_data GROUP BY date ORDER BY date;
                     QUERY PLAN
----------------------------------------------------
 ...
 Sort Method: quicksort Memory: 52428kB
 Execution Time: 438.372 ms

现在，查询性能好了很多。我们将执行时间缩短了三倍以上！

了解更多

PostgreSQL 优化

搜索

分类

标签