二月 3, 2024
摘要：在本教程中，您将学习如何在 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？
在许多情况下，优化work_mem可以提高性能：
- 当您在运行涉及大量排序或哈希操作的查询时。
- 在磁盘上创建了大量临时文件时。
- 当您在 EXPLAIN ANALYZE 的计划输出中看到
external merge disk操作时。
调大 work_mem 的好处
调大work_mem参数有很多好处。例如，调大work_mem可以：
- 对大型结果集进行排序是一项常见的数据库任务，适当的设置
work_mem可以显著加快这一过程。它允许在内存中对更多数据进行排序，从而减少对缓慢的磁盘操作的需要。 - 哈希连接用于在查询执行期间合并来自各种表的数据。较大的
work_mem值可以更多的使用内存进行数据存储，最小化磁盘 I/O 操作的需要，来增强哈希连接的性能。 - 通过更快的排序和连接，查询可以更快地完成执行。这也让数据库性能表现更快速，用户响应时间更短。
- 适当调整
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
现在，查询性能好了很多。我们将执行时间缩短了三倍以上！