用chatGPT写代码:统计全表扫描的表及其SQL

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

群里小伙伴问了一个问题, 如何统计被全表扫描的表以及对应的SQL语句. 

为了实现这个需求, 可以通过PostgreSQL的钩子来实现, 例如在生成执行计划或执行开始时的钩子, 在此时即可获得对应的计划或表. 所以可以这么问chatgpt: PostgreSQL如何通过hook添加这样的功能? 统计被全表扫描的表对应的SQL.

下面是chatGPT给的答案:

在PostgreSQL中，可以通过创建一个自定义的插件来使用钩子（hook）机制，从而实现统计被全表扫描的表及其对应SQL的功能。PostgreSQL的钩子机制允许开发人员在不修改PostgreSQL核心代码的情况下，在关键操作点插入自定义的行为。

要实现这样的功能，可能需要使用执行器钩子（Executor Hook）或计划器钩子（Planner Hook），具体取决于你希望在什么时刻捕获全表扫描的信息。一个可能的方法是使用ExecutorStart_hook或ExecutorRun_hook，这两个钩子分别在执行计划开始执行和实际执行之前被调用。

下面是一个简化的例子，说明如何使用ExecutorStart_hook来添加一个钩子以统计全表扫描：

1. 创建一个新的插件项目，编写自定义的钩子代码。

#include "postgres.h"  
#include "executor/executor.h"  
#include "nodes/plannodes.h"  
  PG_MODULE_MAGIC;  
  /* 声明原始的 ExecutorStart 函数指针 */  
static ExecutorStart_hook_type prev_ExecutorStart = NULL;  
  /* 自定义的 ExecutorStart 钩子实现 */  
void MyExecutorStart(QueryDesc *queryDesc, int eflags)  
{  
    /* 在调用原始的 ExecutorStart 之前检查计划 */  
    if (queryDesc->plannedstmt != NULL && queryDesc->plannedstmt->planTree != NULL)  
    {  
        Plan *plan = queryDesc->plannedstmt->planTree;  
        if (nodeTag(plan) == T_SeqScan)  
        {  
            /* 发现一个全表扫描，你可以在这里进行记录或统计 */  
            /* 示例：输出到服务器日志 */  
            elog(LOG, "全表扫描被执行: %s", queryDesc->sourceText);  
        }  
    }  
      /* 调用原始的 ExecutorStart 函数 */  
    if (prev_ExecutorStart)  
    {  
        prev_ExecutorStart(queryDesc, eflags);  
    }  
    else  
    {  
        standard_ExecutorStart(queryDesc, eflags);  
    }  
}  
  /* 模块加载时的初始化函数 */  
void _PG_init(void)  
{  
    /* 保存原始的 ExecutorStart 函数指针 */  
    prev_ExecutorStart = ExecutorStart_hook;  
          /* 设置新的钩子 */  
    ExecutorStart_hook = MyExecutorStart;  
}  
  /* 模块卸载时的清理函数 */  
void _PG_fini(void)  
{  
    /* 恢复原始的钩子 */  
    ExecutorStart_hook = prev_ExecutorStart;  
}

2. 编译你的插件代码为共享库，并确保它位于PostgreSQL的库路径中。

3. 在PostgreSQL中加载这个插件。可以通过设置shared_preload_libraries参数在数据库启动时预加载这个插件，或者使用LOAD命令动态加载它。

-- 添加到 postgresql.conf  
shared_preload_libraries = 'my_custom_plugin'  
  -- 或使用 SQL 命令动态加载  
LOAD 'my_custom_plugin';

4. 重启PostgreSQL服务以使设置生效，或者执行LOAD命令。

pg_ctl restart

这个例子中的钩子会在执行计划开始之前检查当前执行的计划是否包含全表扫描（SeqScan），并将相关信息写入日志。

请注意，实际使用中需要处理更复杂的情况，例如嵌套的节点、多表扫描、不同类型的扫描等。你可能需要遍历整个计划树来准确地检测所有的全表扫描。

在实施这样的自定义钩子之前，务必进行充分的测试和代码审查，确保不会影响数据库的性能和稳定性。

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.