PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜

简介: 早上写了一篇文章,可以看到PostgreSQL为开发人员着想的,设计得非常人性化。《为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题》https://yq.aliyun.com/articles/55698 同时也收到了一些朋友发来的问题,有朋友问我Postg

早上写了一篇文章《为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题》,可以看到PostgreSQL为开发人员着想的,设计得非常人性化。
https://yq.aliyun.com/articles/55698

同时也收到了一些朋友发来的问题,有朋友问我PostgreSQL plan cache有没有计划倾斜的问题。
本文将针对这个问题展开和大家聊一聊。

什么是执行计划缓存倾斜

我先解释一下什么是执行计划缓存倾斜。
例如一张表数据本来就有倾斜,在使用绑定变量后,如果所有的条件都走同一个执行计划,那就可能是倾斜了?
例子

create table tbl (id int , info text);
insert into tbl select 1 from generate_series(1,20000000);
insert into tbl select generate_series(1,200);

2000万行 id = 1  
另外 id = 1~200 每个一行  

create index idx_tbl_id on tbl(id);
vacuum analyze tbl;

prepare p(int) as select * from tbl where id=$1;
execute p(2);  --  正常情况下它应该走索引扫描
execute p(1);  --  它应该走全表扫描

如果plan cache是index scan, 假设出现倾斜,那么execute p(1)也会走索引(但实际上走索引的COST更高,不应该走索引。)

PostgreSQL 不会出现这样的问题。
看实际的例子
生成一个服务端绑定SQL

postgres=# prepare p(int) as select * from tbl where id=$1;
PREPARE

查看参数是2和1的执行计划的COST

postgres=# explain execute p(2);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using idx_tbl_id on tbl  (cost=0.44..4.46 rows=1 width=36)
   Index Cond: (id = 2)
(2 rows)

参数为1时,成本最低的是全表扫描 cost = 338500.00

postgres=# explain execute p(1);
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..338500.00 rows=20000240 width=36)
   Filter: (id = 1)
(2 rows)

参数为1时,index scan cost = 657868.64

postgres=# set enable_seqscan=off;
postgres=# explain execute p(1);
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Scan using idx_tbl_id on tbl  (cost=0.44..657868.64 rows=20000240 width=36)
   Index Cond: (id = 1)
(2 rows)

参数为1时,index scan cost = 712866.30

postgres=# set enable_indexscan=off;
postgres=# explain execute p(1);
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=374366.30..712866.30 rows=20000240 width=36)
   Recheck Cond: (id = 1)
   ->  Bitmap Index Scan on idx_tbl_id  (cost=0.00..369366.24 rows=20000240 width=0)
         Index Cond: (id = 1)
(4 rows)
postgres=# \q

好了,接下来看看实际是不是这样呢?

postgres=# prepare p(int) as select * from tbl where id=$1;
postgres=# explain analyze execute p(2);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tbl_id on tbl  (cost=0.44..4.46 rows=1 width=36) (actual time=0.013..0.013 rows=1 loops=1)
   Index Cond: (id = 2)
 Execution time: 0.030 ms
(3 rows)

执行5次会生成generic plan cache。
然后换参数1,PostgreSQL很聪敏,没有走索引,而是选择了对于1这个参数最优的全表扫描

postgres=# explain analyze execute p(1);
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..338500.00 rows=20000240 width=36) (actual time=0.014..2480.369 rows=20000001 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 199
 Execution time: 3395.182 ms
(4 rows)

后面大家可以继续演示,选择的执行计划都是正确的。
那么为什么PostgreSQL这么智能呢?它是通过什么方法避免的执行计划缓存倾斜?

从代码角度解释为什么PostgreSQL不会出现plan cache倾斜

其实原理也很简单,PG在生成执行计划缓存前,会先走若干次(目前代码写死了5次)custom plan。
同时会记录总的custom plan 的cost, 以及custom plan的次数。
然后会生成generic plan。
以后,每次bind时,会根据缓存的执行计划以及给定的参数值计算一个COST,如果这个COST 小于前面存储的custom plan cost的平均值,则使用当前缓存的执行计划。
如果这个COST大于前面存储的custom plan cost的平均值,则使用custom plan(即重新生成执行计划),同时custom plan的次数加1,custom plan总成本也会累加进去。
循环往复。
如图
1

因此在上面的例子中,是怎么影响执行计划的呢?
前面5次产生的是custom plan

count (custom plan) = 5   
sum (custom plan cost) = 4.46*5   
avg (custom plan cost) = 4.46

第六次调用时, 参数=1 会使用generic plan 计算cost, 算出来是generic plan (index scan)对应的657868.64
明显大于avg (custom plan cost),所以会选择custom plan,优化器选择了seq scan.
对custom plan进行累加得到

count (custom plan) = 6  
sum (custom plan cost) = 4.46*5 +    338500.00   
avg (custom plan cost) = (4.46*5 +    338500.00) / 6 = 56420.3833

第七次调用,参数如果=2,会走plan cache, 如果参数=1则继续走custom plan,选择 全表扫描、。

源码如下
接收客户端请求
PostgresMain @ src/backend/tcop/postgres.c

prepare消息略,我们看bind的消息。
exec_bind_message @ src/backend/tcop/postgres.c

在bind里会调用获取执行计划缓存, 获取执行计划缓存是会根据bind的参数以及plansource(即缓存的计划)计算是否需要使用customplan
GetCachedPlan @ src/backend/utils/cache/plancache.c

...
            customplan = choose_custom_plan(plansource, boundParams);
...
    if (customplan)
    {
        /* Build a custom plan */
        plan = BuildCachedPlan(plansource, qlist, boundParams);
        /* Accumulate total costs of custom plans, but 'ware overflow */
        if (plansource->num_custom_plans < INT_MAX)
        {
            plansource->total_custom_cost += cached_plan_cost(plan, true);
            plansource->num_custom_plans++;
        }
    }
..

计算是否需要custom plan的算法如下
choose_custom_plan @ src/backend/utils/cache/plancache.c

/*
 * choose_custom_plan: choose whether to use custom or generic plan
 *
 * This defines the policy followed by GetCachedPlan.
 */
static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{
        double          avg_custom_cost;

        /* One-shot plans will always be considered custom */
        if (plansource->is_oneshot)
                return true;

        /* Otherwise, never any point in a custom plan if there's no parameters */
        if (boundParams == NULL)
                return false;
        /* ... nor for transaction control statements */
        if (IsTransactionStmtPlan(plansource))
                return false;

        /* See if caller wants to force the decision */
        if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
                return false;
        if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
                return true;

        /* Generate custom plans until we have done at least 5 (arbitrary) */
        if (plansource->num_custom_plans < 5)
                return true;

        avg_custom_cost = plansource->total_custom_cost / plansource->num_custom_plans;

        /*
         * Prefer generic plan if it's less expensive than the average custom
         * plan.  (Because we include a charge for cost of planning in the
         * custom-plan costs, this means the generic plan only has to be less
         * expensive than the execution cost plus replan cost of the custom
         * plans.)
         *
         * Note that if generic_cost is -1 (indicating we've not yet determined
         * the generic plan cost), we'll always prefer generic at this point.
         */
        if (plansource->generic_cost < avg_custom_cost)
                return false;

        return true;
}

小结

.1. PostgreSQL的bind使用choose_custom_plan巧妙的解决了执行计划缓存倾斜的问题。
用户再也不用担心一个执行计划会导致某些数据倾斜是不适用某些VALUE。
.2. 需要注意一点,generic plan的选择,是和前面几次调用有关,如果前面几次调用的都是p(1),seq scan将会变成generic plan,然后avg custom plan的值会很高,达到338500.00 . 接下来的p(2),全表扫描算出来的成本也会低于avg custom plan, 会继续走seq scan。 这个算法对于这种情况还没辙。 所以算法还有优化的空间,但是还要注意算法太复杂的话,会影响高并发下的性能。所以PG还提供了一种选择,那就是plan hint,这个和Oracle用法一样。先安装pg hint插件就好了。
阿里云RDS PG已经打包了这个插件。

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

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

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

官方博客
最新文章
相关文章
链接