简介 #
在数据库性能调优过程中，执行计划是了解查询如何执行的重要工具。为了更好地理解查询的性能表现，我们可以通过修改 PostgreSQL 的源码，为执行计划添加缓存命中率的显示。这将有助于深入分析查询性能，尤其是在使用 Memoize 节点时。
实验环境 #
- 操作系统：Ubuntu 22.04.4 LTS
- PostgreSQL 版本：16.4
- 编程语言：C（PostgreSQL 源码修改）
实现步骤 #
1. 创建测试数据 #
首先，我们需要创建测试表并插入大量数据，以便生成可以分析的执行计划。
-- 删除并重新创建表
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary INT
);
-- 插入数据
INSERT INTO departments (dept_id, dept_name)
SELECT generate_series(1, 100), 'Department ' || generate_series(1, 100);
INSERT INTO employees (emp_id, emp_name, dept_id, salary)
SELECT generate_series(1, 200000),
'Employee ' || generate_series(1, 200000),
(random() * 100 + 1)::int,
(random() * 50000 + 30000)::int;
2. 设置查询环境并获取修改前的执行计划 #
在执行计划生成之前，先进行一些查询设置，以确保使用 Memoize 节点。
SET enable_mergejoin = off;
SET enable_hashjoin = off;
SET enable_nestloop = on;
SET seq_page_cost = 2.0;
SET cpu_index_tuple_cost = 0.005;
SET work_mem = '100MB';
SET enable_memoize = on;
然后，生成查询的执行计划，以便与后续的修改结果进行对比。
EXPLAIN (ANALYZE, VERBOSE)
SELECT e.emp_name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 40000
ORDER BY e.salary DESC
LIMIT 1000;
输出结果（省略部分不相关内容）：
-> Memoize (cost=0.15..0.17 rows=1 width=17)
Output: d.dept_name, d.dept_id
Cache Key: e.dept_id
Cache Mode: logical
Hits: 81005 Misses: 101 Evictions: 0 Overflows: 0 Memory Usage: 12kB
Worker 0: actual time=0.000..0.000 rows=1 loops=78915
Hits: 78814 Misses: 101 Evictions: 0 Overflows: 0 Memory Usage: 12kB
-> Index Scan using departments_pkey on public.departments d
Output: d.dept_name, d.dept_id
Index Cond: (d.dept_id = e.dept_id)
3. 修改 PostgreSQL 源码 #
接下来，修改 PostgreSQL 的源码以增加缓存命中率的显示。
在 VSCode 编辑器中，打开 src/backend/commands/explain.c 文件，并检索 if (mstate->stats.cache_misses > 0) 语句（位于 show_memoize_info 函数中），在此处添加以下代码：
if (mstate->stats.cache_misses > 0)
{
char hint[128];
double hitRate = 100.0 * mstate->stats.cache_hits / (mstate->stats.cache_hits + mstate->stats.cache_misses);
elog(DEBUG1, "Hit Rate calculated: %.1f%%", hitRate);
if (hitRate < 100.0) // 检查是否小于100%，考虑到浮点数的精度问题，可以使用一个很接近100的数值
{
elog(DEBUG1, "Hit Rate is less than 100%%, printing optimization hint.");
snprintf(hint, sizeof(hint), "命中率 %.1f%%.", hitRate);
ExplainPropertyText("Optimization Hint", hint, es);
}
else
{
elog(DEBUG1, "Hit Rate is 100%%, no optimization hint needed.");
}
// 其他部分保持不变。
}
注意：上面代码中的 “命中率 %.1f%%.” 将会显示在执行计划中，这为优化提供了直接的建议。
4. 编译并重启 PostgreSQL #
使用以下命令编译并安装修改后的 PostgreSQL：
./configure --prefix=$HOME/pgsql --enable-debug --enable-cassert CFLAGS="-O0 -g"
make -j$(nproc)
make install
$HOME/pgsql/bin/pg_ctl -D $HOME/pgsql/data restart
5. 设置查询环境并查看修改后的执行计划 #
在每次执行查询前，先执行以下设置以确保环境一致性：
SET enable_mergejoin = off;
SET enable_hashjoin = off;
SET enable_nestloop = on;
SET seq_page_cost = 2.0;
SET cpu_index_tuple_cost = 0.005;
SET work_mem = '100MB';
SET enable_memoize = on;
然后，执行与之前相同的查询语句，观察执行计划中是否显示了缓存命中率。
EXPLAIN (ANALYZE, VERBOSE)
SELECT e.emp_name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 40000
ORDER BY e.salary DESC
LIMIT 1000;
输出结果（省略部分不相关内容）：
-> Memoize (cost=0.15..0.17 rows=1 width=17)
Output: d.dept_name, d.dept_id
Cache Key: e.dept_id
Cache Mode: logical
Optimization Hint: 命中率 99.9%.
Hits: 81120 Misses: 101 Evictions: 0 Overflows: 0 Memory Usage: 12kB
Worker 0: actual time=0.000..0.000 rows=1 loops=78800
Hits: 78699 Misses: 101 Evictions: 0 Overflows: 0 Memory Usage: 12kB
-> Index Scan using departments_pkey on public.departments d
Output: d.dept_name, d.dept_id
Index Cond: (d.dept_id = e.dept_id)
在这个执行计划中，Optimization Hint: 命中率 99.9%. 清晰地显示在执行计划中。
6. 总结 #
通过这次实验，我们成功地在 PostgreSQL 的执行计划中添加了缓存命中率的显示。这为我们在调优数据库查询性能时提供了更多的信息，帮助我们更好地理解查询的行为。
参考资料 #
注意：本实验是在受控的开发环境中进行的。在对生产系统应用任何更改之前，请务必备份数据并进行彻底测试。