4. 测试环境搭建：使用 pgbench 创建测试表与数据，配置 shared_buffers、effective_cache_size 参数
在进行索引优化与查询性能测试之前，必须搭建一个可复现、可控制的测试环境。本节将指导你使用 PostgreSQL 内置的 pgbench 工具快速生成标准测试表与数据，并详细说明两个核心内存参数——shared_buffers 和 effective_cache_size——的配置原理与测试策略。
4.1 使用 pgbench 创建测试表与数据
pgbench 是 PostgreSQL 自带的基准测试工具，其 -i（initialize）模式可以快速创建标准测试表并填充数据。默认生成的表结构包含 pgbench_accounts、pgbench_branches、pgbench_tellers 和 pgbench_history，其中 pgbench_accounts 是查询测试的核心表。
4.1.1 初始化测试数据库
-- 创建专用测试数据库
CREATE DATABASE pgbench_test;
-- 使用 pgbench 初始化，缩放因子 100（约 1000 万行 accounts 数据）
pgbench -i -s 100 -U postgres -d pgbench_test参数说明：
-i：初始化模式，创建表并加载数据。-s 100：缩放因子（scale factor），控制数据量。默认pgbench_accounts表行数为100000 × scale，即s=100时生成约 1000 万行。-U postgres：指定数据库用户。-d pgbench_test：目标数据库名称。
4.1.2 验证表结构与数据量
-- 查看表结构
\d pgbench_accounts
-- 验证数据行数
SELECT count(*) FROM pgbench_accounts;
-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('pgbench_accounts'));初始化完成后，pgbench_accounts 表默认仅在 aid（账户 ID）列上存在主键索引。后续章节的索引优化测试将基于此表添加不同类型的索引。
4.2 核心内存参数配置：shared_buffers 与 effective_cache_size
这两个参数直接影响 PostgreSQL 的缓存行为与查询计划器的成本估算，是缓存回归测试中必须明确配置的变量。
4.2.1 参数对比与配置建议
| 参数 | 作用域 | 默认值 | 测试推荐值 | 说明 |
|---|---|---|---|---|
shared_buffers |
共享内存 | 128MB | 总内存的 15%~25% | PostgreSQL 自身用于缓存数据页的共享内存缓冲区大小。增大可减少磁盘 I/O。 |
effective_cache_size |
计划器成本 | 4GB | 总内存的 50%~75% | 告诉查询计划器操作系统和 PostgreSQL 总共可用于文件系统缓存的内存量。不影响实际分配，只影响索引扫描 vs 顺序扫描的成本估算。 |
测试环境配置原则：在回归测试中，建议将
shared_buffers 设置为一个固定值（如 2GB），并在不同测试轮次中调整 effective_cache_size，观察其对查询计划选择的影响。切勿在生产环境与测试环境之间混用参数值。
4.2.2 配置方法
-- 方法一：修改 postgresql.conf 文件
shared_buffers = '2GB'
effective_cache_size = '8GB'
-- 方法二：使用 ALTER SYSTEM 命令（无需重启，但需 reload）
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET effective_cache_size = '8GB';
-- 重新加载配置
SELECT pg_reload_conf();
注意：
shared_buffers 的修改需要重启 PostgreSQL 服务才能生效；effective_cache_size 仅需 pg_reload_conf() 即可。在测试脚本中应明确记录每次测试前的参数快照。
4.3 测试环境参数配置流程
以下流程图展示了从数据库初始化到参数配置的完整步骤：
4.4 测试环境参数快照记录
为了确保回归测试的可追溯性，每次测试前应记录当前环境的参数快照：
-- 记录关键内存参数
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem');
-- 记录当前数据库大小
SELECT pg_size_pretty(pg_database_size('pgbench_test')) AS db_size;
-- 记录 pgbench_accounts 表统计信息
SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'pgbench_accounts';
最佳实践：将上述查询结果保存为
env_snapshot_YYYYMMDD_HH24MI.txt 文件，与测试结果一同归档。这可以避免因环境参数不一致导致的“缓存回归误判”。
4.5 测试数据准备清单
完成本节操作后，应确认以下项目均已就绪：
- 数据库：
pgbench_test已创建并可用。 - 测试表：
pgbench_accounts包含约 1000 万行数据，aid列有主键索引。 - 内存参数：
shared_buffers设置为 2GB（已重启生效），effective_cache_size设置为 8GB（已 reload）。 - 参数快照：已记录当前环境参数并归档。
- 无干扰进程：测试期间关闭 autovacuum 或将其阈值调高，避免后台进程干扰缓存状态。