pg参数之 work_mem

郑全

`work_mem` 是 PostgreSQL 中用于控制每个查询中排序操作（如 `ORDER BY`、`DISTINCT`、合并连接等）和哈希操作（如哈希连接、哈希聚合）的内存大小的参数。配置 `work_mem` 对查询性能尤其是复杂查询的优化至关重要。

### `work_mem` 的作用

`work_mem` 指定了在执行每个排序或哈希操作时，PostgreSQL 可以使用的内存大小。如果某个操作需要的内存超出了 `work_mem` 的设置，PostgreSQL 将把部分数据写入临时文件，并通过磁盘进行排序或哈希操作。这会导致 I/O 操作，从而影响性能。

### 如何设置 `work_mem`

- `work_mem` 是**每个操作**分配的内存，而不是每个查询。一个查询可能包含多个排序或哈希操作，因此内存使用量可能是 `work_mem` 的多倍。
- 例如，如果一个查询包含三个排序操作，并且 `work_mem` 设置为 4MB，那么该查询可能会使用最多 12MB 的内存。

### 示例设置

你可以在 `postgresql.conf` 文件中全局设置：

```conf
work_mem = 4MB
```

或者你可以在会话级别动态设置：

```sql
SET work_mem = '16MB';
```

这种临时设置适合在执行特定的大型查询或分析任务时使用。

### 调整 `work_mem` 的注意事项

1. **考虑系统的总内存**：
   由于 `work_mem` 是为每个操作分配的，因此在配置时需要考虑并发查询的数量。如果设置过大，多个并发查询可能导致内存耗尽，最终引发系统性能问题甚至崩溃。

2. **查询复杂性**：
   对于简单查询，默认的较小设置通常足够。但对于涉及复杂排序、大量聚合或连接的查询，适当增加 `work_mem` 可以避免磁盘操作，提高性能。

3. **根据负载进行测试和调整**：
   理想的 `work_mem` 配置需要在生产环境中进行测试和调整，以确保系统在高并发情况下不会因内存使用过多而影响稳定性。

### `work_mem` 与性能的关系

- **设置过小**：如果 `work_mem` 设置过小，大量查询可能会涉及磁盘临时文件操作，导致性能下降。
- **设置过大**：如果 `work_mem` 设置过大，并且系统上有大量并发查询，则可能耗尽系统内存，导致性能问题或系统崩溃。

### 性能优化建议

- **逐步调整**：从较小的值开始，并根据查询和内存使用情况逐步增加。
- **结合监控工具**：使用 PostgreSQL 的监控工具（如 `pg_stat_statements`）查看查询的内存使用情况和性能瓶颈。
- **针对特定查询设置**：对于复杂的报告查询或分析任务，可以临时提高 `work_mem`，在任务完成后再恢复默认设置。

### 总结

`work_mem` 是 PostgreSQL 查询优化的重要参数之一，它直接影响排序、哈希连接等操作的内存使用。合理配置可以减少磁盘 I/O，提高查询性能。在调整 `work_mem` 时，需要考虑并发性、系统内存总量以及实际查询的复杂度，并通过监控和测试找到合适的值。