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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

背景

绑定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中对应表的执行计划。

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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
7天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
18 2
|
5月前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
5月前
|
SQL Oracle 关系型数据库
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
|
Oracle 关系型数据库 数据库
PostgreSQL和Oracle两种数据库有啥区别?如何选择?
PostgreSQL和Oracle两种数据库有啥区别?如何选择?
468 0
|
10月前
|
SQL Oracle 关系型数据库
Oracle,Postgresql等数据库使用
Oracle,Postgresql等数据库简单使用
161 0
Oracle,Postgresql等数据库使用
|
存储 SQL 关系型数据库
PostgreSQL技术大讲堂 - 第29讲:执行计划与成本估算
从零开始学PostgreSQL技术大讲堂 - 第29讲:执行计划与成本估算
184 1
|
SQL Oracle 关系型数据库
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
1512 0
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
1184 1
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
1605 2

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 下一篇
    无影云桌面