如何在PostgreSQL中基准测试查询？对比表聚类前后性能

先理清当前现象的原因 #

你看到的Heap Fetches: 0是聚簇生效的明确信号——聚簇后表数据的物理顺序和指定索引顺序完全一致，查询通过索引定位后直接读取连续磁盘块，无需回表抓取堆数据，这正是聚簇的核心价值。

至于聚类前后耗时相近，大概率是缓存干扰：PostgreSQL会把频繁访问的数据缓存到shared_buffers，甚至操作系统页缓存中。如果测试前未清理缓存，两次查询都命中缓存，磁盘IO的差异就被掩盖，聚簇带来的连续IO优势无法体现。

基准测试的正确姿势 #

要得到可靠对比数据，必须严格控制测试环境：

• 清理缓存：
  • 操作系统层面（需root权限）：sync; echo 3 > /proc/sys/vm/drop_caches，清空页缓存
  • PostgreSQL层面：执行DISCARD ALL;清理当前会话缓存，SELECT pg_stat_reset();重置统计信息
• 控制并发：测试时关闭其他业务查询，用单连接运行（避免并发资源竞争）
• 足够样本量：至少运行10-20次，避免单次波动影响结果

可用的基准测试工具 #

1. PostgreSQL自带pgBench #

pgBench是官方基准测试工具，适合批量运行查询并生成统计数据：

• 先创建测试脚本（如test_query.sql），写入目标查询
• 运行命令：

  pgbench -h localhost -U your_user -d your_db -n -c 1 -t 100 -f test_query.sql
  

  参数说明：
  • -n：跳过默认vacuum步骤（避免干扰）
  • -c 1：单连接测试
  • -t 100：执行100次查询
    输出包含平均耗时、最小/最大耗时、标准差，还可通过日志导出原始数据计算百分位数

2. pg_stat_statements扩展 #

启用后自动收集所有查询的执行统计：

• 在postgresql.conf中添加shared_preload_libraries = 'pg_stat_statements'，重启数据库
• 创建扩展：CREATE EXTENSION pg_stat_statements;
• 测试前重置统计：SELECT pg_stat_statements_reset();
• 多次运行查询后，执行以下语句获取详细统计：

  SELECT queryid, query, calls, mean_time, stddev_time, min_time, max_time
  FROM pg_stat_statements
  WHERE query LIKE '%your_query_pattern%';
  

3. 自定义脚本 #

用bash或Python写简单循环，手动记录每次执行耗时：

• Bash示例：

  for i in {1..20}; do
    psql -h localhost -U your_user -d your_db -c "SELECT clock_timestamp(); your_query; SELECT clock_timestamp();" | grep clock_timestamp | awk '{print $2}' | paste -d '-' - - | awk -F'-' '{print ($2 - $1)*1000}' >> results.txt
  done
  

  脚本会记录每次查询的耗时（毫秒），存入results.txt

统计数据与百分位数计算 #

拿到原始耗时数据后，计算百分位数能更直观展示性能分布：

• 用Python的pandas/numpy处理：

  import pandas as pd
  import numpy as np
  
  # 读取数据
  non_clustered = pd.read_csv('non_clustered_results.txt', header=None)[0]
  clustered = pd.read_csv('clustered_results.txt', header=None)[0]
  
  # 计算百分位数
  print("非聚簇表百分位数：")
  print(np.percentile(non_clustered, [25, 50, 75, 95, 99]))
  print("聚簇表百分位数：")
  print(np.percentile(clustered, [25, 50, 75, 95, 99]))
  

• 也可用Excel/Google Sheets：导入数据后用PERCENTILE.INC函数计算

可视化工具 #

1. Python可视化库 #

用matplotlib或seaborn做箱线图，对比两次测试的耗时分布：

import seaborn as sns
import matplotlib.pyplot as plt

data = pd.DataFrame({
    '耗时(ms)': pd.concat([non_clustered, clustered]),
    '表类型': ['非聚簇']*len(non_clustered) + ['聚簇']*len(clustered)
})

sns.boxplot(x='表类型', y='耗时(ms)', data=data)
plt.title('聚簇与非聚簇表查询耗时对比')
plt.show()

箱线图能清晰展示中位数、四分位数、异常值，直观体现聚簇对性能分布的影响。

2. Excel/Google Sheets #

导入数据后插入「箱线图」或「柱状图」，手动设置百分位数标签，适合快速生成可视化结果。

3. pgAdmin统计面板 #

pgAdmin的「统计」模块可查看pg_stat_statements的历史数据，虽可视化能力有限，但能直接在数据库管理界面查看平均耗时、调用次数等指标。

内容的提问来源于stack exchange，提问作者rela589n