使用codebuddy将pg_hint_plan性能提升20%

NickYoung

发布于 2026-07-09 16:38:16

给大家分享一个使用codebuddy优化PostgreSQL内核的实践案例。

背景

在PCC2024大会主题分享《pg_hint_plan技术内幕》中，我曾预留了一个对于hint_table的优化点。

简单回顾下hint_table：

hint_table主要的功能是根据queryid将对应的hint文本记录在专用table中，这样就不用将hint显式加入sql文本了，也就是不用业务改sql了。当sql执行时自动从hint_table中获取hint干预执行计划，这样对业务来说确实更加友好。

hint_table存在的问题：

目前是普通heap表形式，通过SPI(Server Programming Interface)的方式去查询hint_table获得hint文本，也就是通过c语言api执行sql。

因此：

1、每次执行都会存在和server建连，会产生连接开销；

2、会走完整的parser，planner，execute流程当并发高时，可能访问性能会明显变差

3、当前查询hint的sql会记录至pg_stat_statements,影响qps等指标的统计

优化思路：

参考system catalog table的形式，访问hint_table先访问syscache之类的缓存，这样理论上就能提升性能。同时不会影响业务qps统计。

方案

我的需求描述：

pg_hint_plan中的hint_table也就是hint_plan.hints是一个普通的heap表，通过SPI的方式访问。

为了提高性能，请做出以下修改：

1、将其改变为系统表类似于pg_class，访问系统表先访问syscache；

2、不要污染主干代码，只在pg_hint_plan中进行修改

我只是提出了明确的需求，codebuddy从理解需求到生成代码并编译验证只用了5min左右。最后还给我输出了需求文档和总结文档。

我并没有让它替我做测试以及压测，还是自己手动做的。其实搞个skill也ok，甚至这篇文章都可以自动写好，这样的话我只需提出需求，就可以自动化完成整个流程。

总结文档如下：

pg_hint_plan 性能优化：hint表访问优化

修改概述

本次修改优化了 pg_hint_plan 扩展中 hint_plan.hints 表的访问方式，将原本使用 SPI (Server Programming Interface) 的方式改为使用本地缓存机制，配合 PostgreSQL 的 relcache 系统，显著提高了性能。

修改内容

1. 添加缓存数据结构

在 pg_hint_plan.c 中添加了以下数据结构：

/*
 * Hint table cache entry structure.
 * This acts like a local syscache for hint_plan.hints table.
 */
typedefstruct HintTableEntry
{
    uint64      query_id;       /* query identifier */
    char       *application_name;    /* application name */
    char       *hints;          /* hint string */
} HintTableEntry;

/*
 * Hint table cache structure.
 * Provides fast access to hints without using SPI.
 */
typedefstruct HintTableCache
{
    HTAB       *hash_table;        /* hash table for hints */
    Oid         hints_table_oid;    /* OID of hint_plan.hints table */
    Oid         hints_index_oid;    /* OID of hints_id_and_app index */
    bool        is_valid;        /* cache validity flag */
} HintTableCache;

static HintTableCache *hint_cache = NULL;

2. 实现缓存管理函数

实现了以下缓存管理函数：

init_hint_cache(): 初始化hint表缓存
pg_hint_plan_invalidate_cache(): 标记缓存为无效
lookup_hint_entry(): 查找hint条目（使用直接表扫描而非SPI）

3. 优化 `get_hints_from_table()` 函数

将原本的SPI查询改为使用本地缓存+relcache的直接表扫描访问：

原实现（使用SPI）：

SPI_connect();
if (plan == NULL)
{
    SPIPlanPtr p;
    p = SPI_prepare(search_query, 2, argtypes);
    plan = SPI_saveplan(p);
    SPI_freeplan(p);
}
SPI_execute_plan(plan, values, nulls, true, 1);
// ... 处理结果
SPI_finish();

新实现（使用缓存+relcache）：

/* Look up hint using cache (falls back to direct table scan) */
entry = lookup_hint_entry(queryId, client_application);

if (entry != NULL)
{
    /* Copy hints to top memory context */
    MemoryContext oldctx = MemoryContextSwitchTo(TopMemoryContext);
    hints = pstrdup(entry->hints);
    MemoryContextSwitchTo(oldctx);
}

4. 添加缓存失效机制

通过触发器调用失效函数来处理hint表的变化：

缓存失效函数：


/*
 * SQL function to invalidate hint cache.
 * This function should be called by a trigger on hint_plan.hints table.
 */
PG_FUNCTION_INFO_V1(pg_hint_plan_invalidate_cache);

Datum
pg_hint_plan_invalidate_cache(PG_FUNCTION_ARGS)
{
 Oid   hints_relid;

/* Get the OID of hints table */
 hints_relid = RelnameGetRelid("hints");
if (!OidIsValid(hints_relid))
 {
  hints_relid = get_relname_relid("hints",
          get_namespace_oid("hint_plan", false));
 }

/* Send relcache invalidation message */
if (OidIsValid(hints_relid))
 {
  CacheInvalidateRelcacheByRelid(hints_relid);
 }

/* Trigger function must return NULL for AFTER triggers */
 PG_RETURN_POINTER(NULL);
}

创建触发器，当hints表发生INSERT、UPDATE、DELETE、TRUNCATE后调用失效函数。

-- Create function to invalidate hint cache
-- This function is called by trigger when hints table is modified
CREATEORREPLACEFUNCTION hint_plan.pg_hint_plan_invalidate_cache()
RETURNStrigger
AS'MODULE_PATHNAME', 'pg_hint_plan_invalidate_cache'
LANGUAGE C;

-- Create trigger to invalidate cache on any modification to hints table
CREATETRIGGER hint_plan_hints_invalidate_trigger
AFTERINSERTORUPDATEORDELETEORTRUNCATEON hint_plan.hints
FOREACHSTATEMENTEXECUTEFUNCTION hint_plan.pg_hint_plan_invalidate_cache();

失效后下次查询时重新加载缓存。

5. 添加必要的头文件

为了支持relcache和hash表功能，添加了以下头文件：

#include "utils/hsearch.h"
#include "utils/inval.h"
#include "catalog/namespace.h"

性能优势

访问方式对比

方式	SPI (原实现)	缓存+relcache (新实现)
首次访问	需要SPI连接和查询	直接表扫描（relcache）
后续访问	每次都需要SPI查询	从哈希缓存直接获取
连接开销	每次需要SPI_connect()/SPI_finish()	无连接开销
查询开销	SQL解析和执行	哈希表查找
缓存失效	不支持	自动失效（触发器）

性能提升

消除SPI连接开销: 不再需要每次调用 SPI_connect() 和 SPI_finish()
减少SQL解析: 使用直接表扫描而非SQL查询
哈希缓存: 后续查询直接从内存哈希表获取，无需扫描表
自动失效: 通过触发器自动检测表变化并使缓存失效

修改范围

本次修改完全限制在 pg_hint_plan 扩展内部，未对PostgreSQL主干代码做任何修改。

修改的文件

contrib/pg_hint_plan-PG18/pg_hint_plan.c

未修改的部分

PostgreSQL核心代码
其他扩展文件
SQL安装脚本
测试文件

兼容性

完全向后兼容
不改变现有API
不影响现有的hint表结构和数据
缓存失效机制确保数据一致性

注意事项

缓存存储在 TopMemoryContext 中，在会话结束时自动释放
当 pg_hint_plan.enable_hint_table 关闭时，缓存会被重置
缓存大小默认为256个条目，可根据实际需求调整
调试模式下（pg_hint_plan.debug_print > 0）会记录缓存失效事件

测试建议

基本功能测试：确保hint表查询正常工作
性能测试：对比优化前后的查询时间
缓存失效测试：修改hint表后验证缓存正确失效
并发测试：多个会话同时访问hint表

测试

通过多轮pgbench压测对比了8 - 64并发下的性能差异，优化后整体提升20%左右。

我的vm配置较差，性能数据仅供参考：原生：

pgbench -c 16 -j 16 -d postgres -p 5432 -f /data/postgres/postgres-REL_18_1/data/test.sql -T 600
pgbench (18.1)
starting vacuum...end.
transaction type: /data/postgres/postgres-REL_18_1/data/test.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
maximum number of tries: 1
duration: 600 s
number of transactions actually processed: 9556151
number of failed transactions: 0 (0.000%)
latency average = 1.005 ms
initial connection time = 13.182 ms
tps = 15927.203351 (without initial connection time)

优化后：

pgbench -c 16 -j 16 -d postgres -p 5432 -f /data/postgres/postgres-REL_18_1/data/test.sql -T 600
pgbench (18.1)
starting vacuum...end.
transaction type: /data/postgres/postgres-REL_18_1/data/test.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
maximum number of tries: 1
duration: 600 s
number of transactions actually processed: 12164201
number of failed transactions: 0 (0.000%)
latency average = 0.789 ms
initial connection time = 14.099 ms
tps = 20274.000590 (without initial connection time)

小结

这里主要给大家分享下感悟吧。

有了AI的辅助确实能提升效率，让一些专业性比较强的事情也变得简单起来来；同时确实对我们的冲击不小，在当下我们得主动转变，拥抱AI，用好AI。

在AI时代，我认为最值钱的是idea。有好的idea就能利用AI做出高质量的方案。当然这其实对个人的要求是越来越高了，怎么才能有好的idea？我认为肯定这两方面因素是必不可少的：

1、对于该事物全面透彻的认知

2、出色的抽象能力

只有对想做的事情有全面的认知和合理的抽象，才能提出高质量的需求。这样的话就能用更短的时间，更少的token，做出更优的方案。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-03-16，如有侵权请联系 cloudcommunity@tencent.com 删除

测试

本文分享自 PostgreSQL运维之道微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度