PostgreSQL Oracle兼容性之 - 锁定执行计划(Outline system)-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL Oracle兼容性之 - 锁定执行计划(Outline system)

简介:

背景

绑定SQL执行计划,大家一定会想到SQL HINT,通过HINT告诉优化器你要用什么访问方法,用什么JOIN方法,JOIN的顺序,驱动表等等。

但是SQL HINT有一定的弊端,它需要修改应用程序中的SQL语句,把SQL加上HINT。

对pg_hint_plan感兴趣的同学,可以参考我写到文档
https://yq.aliyun.com/articles/57945

但是,有什么方法可以在不修改应用程序,不修改SQL的情况下,锁定SQL的执行计划呢?

锁定执行计划

要锁定执行计划,同样要用到PostgreSQL的 HOOK,在生成plan时动手。

源码
https://github.com/postgrespro/sr_plan

原理如下图
parser的hook,用于获取query text。
planner的hook,干了两间事情,
.1. 在开启了writer模式时,判断表中是否有该sql的执行计划,没有的话就将planner记录在表中。
.2. 从表中读取到对应的执行计划,传给executor,从而达到锁定执行计划的目的。
screenshot

用法介绍

安装略

在需要锁定执行计划的数据库中创建扩展

CREATE EXTENSION sr_plan;

生成正确的执行计划
连接到对应数据库,并打开一个会话

使用一些开关,使得接下来要记录的执行计划  
set enable_bitmapscan=off;
......

打开记录模式  
set sr_plan.write_mode = true;

执行需要生成执行计划的QUERY,例如  
select a,b,c from tbl where id=1 and c1=2;  

检查执行计划是否正确  
select * from sr_plans;

如果发现执行计划不正确,删除即可,重新生成。    
delett from sr_plans where query_hash=...;

记得关闭记录模式  
set sr_plan.write_mode = false;

如果要生成下一条QUERY的执行计划,继续前面的步骤即可。  

让优化器使用已生成的执行计划
生成后的执行计划,并不会马上生效,需要更新sr_plans.enable字段

select query_hash, enable, valid, query, explain_jsonb_plan(plan) from sr_plans;

 query_hash | enable | valid |                        query                         |                 explain_jsonb_plan                 
------------+--------+-------+------------------------------------------------------+----------------------------------------------------
 1783086253 | f      | t     | select query_hash from sr_plans where query_hash=10; | Bitmap Heap Scan on sr_plans                      +
            |        |       |                                                      |   Recheck Cond: (query_hash = 10)                 +
            |        |       |                                                      |   ->  Bitmap Index Scan on sr_plans_query_hash_idx+
            |        |       |                                                      |         Index Cond: (query_hash = 10)             +
            |        |       |                                                      | 

explain_jsonb_plan function allows you to display explain execute the plan of which lies in jsonb. 
By default, all the plans are off, you need enable it:

update sr_plans set enable=true where query_hash=1783086253;

(1783086253 for example only) After that, the plan for the query will be taken from the sr_plans.

对于使用参数的QUERY,同样适用。

In addition sr plan allows you to save a parameterized query plan. 
In this case, we have some constants in the query are not essential. 
For the parameters we use a special function _p (anyelement) example:

select query_hash from sr_plans where query_hash=1000+_p(10);

if we keep the plan for the query and enable it to be used also for the following queries:

select query_hash from sr_plans where query_hash=1000+_p(11);
select query_hash from sr_plans where query_hash=1000+_p(-5);

源码

.1. 获取query text
src/backend/optimizer/plan/planner.c

/*****************************************************************************
 *
 *         Query optimizer entry point
 *
 * To support loadable plugins that monitor or modify planner behavior,
 * we provide a hook variable that lets a plugin get control before and
 * after the standard planning process.  The plugin would normally call
 * standard_planner().
 *
 * Note to plugin authors: standard_planner() scribbles on its Query input,
 * so you'd better copy that data structure if you want to plan more than once.
 *
 *****************************************************************************/
PlannedStmt *
planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
        PlannedStmt *result;

        if (planner_hook)
                result = (*planner_hook) (parse, cursorOptions, boundParams);
        else
                result = standard_planner(parse, cursorOptions, boundParams);
        return result;
}

.2.
src/backend/parser/analyze.c

/*
 * parse_analyze
 *              Analyze a raw parse tree and transform it to Query form.
 *
 * Optionally, information about $n parameter types can be supplied.
 * References to $n indexes not defined by paramTypes[] are disallowed.
 *
 * The result is a Query node.  Optimizable statements require considerable
 * transformation, while utility-type statements are simply hung off
 * a dummy CMD_UTILITY Query node.
 */
Query *
parse_analyze(Node *parseTree, const char *sourceText,
                          Oid *paramTypes, int numParams)
{
        ParseState *pstate = make_parsestate(NULL);
        Query      *query;

        Assert(sourceText != NULL); /* required as of 8.4 */

        pstate->p_sourcetext = sourceText;

        if (numParams > 0)
                parse_fixed_parameters(pstate, paramTypes, numParams);

        query = transformTopLevelStmt(pstate, parseTree);

        if (post_parse_analyze_hook)
                (*post_parse_analyze_hook) (pstate, query);

        free_parsestate(pstate);

        return query;
}

/*
 * parse_analyze_varparams
 *
 * This variant is used when it's okay to deduce information about $n
 * symbol datatypes from context.  The passed-in paramTypes[] array can
 * be modified or enlarged (via repalloc).
 */
Query *
parse_analyze_varparams(Node *parseTree, const char *sourceText,
                                                Oid **paramTypes, int *numParams)
{
        ParseState *pstate = make_parsestate(NULL);
        Query      *query;

        Assert(sourceText != NULL); /* required as of 8.4 */

        pstate->p_sourcetext = sourceText;

        parse_variable_parameters(pstate, paramTypes, numParams);

        query = transformTopLevelStmt(pstate, parseTree);

        /* make sure all is well with parameter types */
        check_variable_parameters(pstate, query);

        if (post_parse_analyze_hook)
                (*post_parse_analyze_hook) (pstate, query);

        free_parsestate(pstate);

        return query;
}

小结

  • 对相应的表执行DDL之后,记得清理在sr_plans中对应表的执行计划。

祝大家玩得开心,欢迎随时来 阿里云促膝长谈 业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力做 最贴地气的云数据库

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接