PostgreSQL 11 preview - 增加强制custom plan GUC开关(plancache

背景

对于高并发的小事务，使用绑定变量（prepared statement）来缓存执行计划，可以降低简单SQL在sql parser, plan上的开销。

《PostgreSQL 11 preview - 强制auto prepared statment开关(自动化plan cache)(类似Oracle cursor_sharing force)》

但是对于比较复杂的SQL或者说输入的参数会导致执行计划倾斜的SQL，使用绑定变量会导致性能抖动。

例子，下面的数据在ID=1上面有非常严重的倾斜，其他值比较均匀。

create table test (id int , info text, crt_time timestamp);  
  
insert into test select generate_series(1,1000000);  
  
insert into test select 1 from generate_series(1,10000000);

因此select count(*) from test where id=1时，可能用全表扫描更合适。而当id=其他值时，使用索引更好。

PostgreSQL在使用CACHED PLAN时，依旧会使用传入参数代入CACHE PLAN进行计算，得到成本，然后对比之前5次custom plan的成本，如果相差较大（有阈值），则会重新发起custom plan，但是并不一定适合所有场景，有时候这种倾斜会一直下去导致执行计划不正确。可以看如下文章中的例子。

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

《执行计划选择算法与绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

那么如何解决这个问题？

我们知道数据库有几种使用绑定变量的方法：

1、数据库端prepare, execute。

2、驱动层使用prepare, execute接口。

3、使用UDF函数。

4、设置强制使用prepared statement的开关。

《PostgreSQL 11 preview - 强制auto prepared statment开关(自动化plan cache)(类似Oracle cursor_sharing force)》

PostgreSQL 11可能引入一个GUC参数，可以让你强制使用custom plan。因为PATCH还没有提交，最后提交的时候会怎么实现现在还不得而知。

https://commitfest.postgresql.org/17/990/

Hi,  
  
this patch is based on discussions related to plpgsql2 project.  
  
Currently we cannot to control plan cache from plpgsql directly. We can use  
dynamic SQL if we can enforce oneshot plan - but it means little bit less  
readable code (if we enforce dynamic SQL from performance reasons). It  
means so the code cannot be checked by plpgsql check too.  
  
The plan cache subsystem allows some control by options  
CURSOR_OPT_GENERIC_PLAN and CURSOR_OPT_CUSTOM_PLAN. So we just a interface  
how to use these options from PLpgSQL. I used Ada language feature (used in  
PL/SQL too) - PRAGMA statement. It allows to set compiler directives. The  
syntax of PRAGMA statements allows to set a level where entered compiler  
directive should be applied. It can works on function level or block level.  
  
Attached patch introduces PRAGMA plan_cache with options: DEFAULT,  
FORCE_CUSTOM_PLAN, FORCE_GENERIC_PLAN. Plan cache is partially used every  
time - the parser/analyzer result is cached every time.  
  
Examples:  
  
CREATE OR REPLACE FUNCTION foo(a int)  
RETURNS int AS  $$  
DECLARE ..  
BEGIN  
  
   DECLARE  
     /* block level (local scope) pragma */  
     PRAGMA plan_cache(FORCE_CUSTOM_PLAN);  
   BEGIN  
     SELECT /* slow query - dynamic sql is not necessary */  
   END;  
  
 END;  
  
Benefits:  
  
1. remove one case where dynamic sql is necessary now - security, static  
check  
2. introduce PRAGMAs - possible usage: autonomous transactions, implicit  
namespaces settings (namespace for auto variables, namespace for function  
arguments).  
  
Comments, notes?  
  
Regards  
  
Pavel

PATCH新增参数如下

+static const struct config_enum_entry plancache_mode_options[] = {  
+	{"default", PLANCACHE_DEFAULT, false},  
+	{"force_generic_plan", PLANCACHE_FORCE_GENERIC_PLAN, false},  
+	{"force_custom_plan", PLANCACHE_FORCE_CUSTOM_PLAN, false},  
+	{NULL, 0, false}  
+};

参数的使用方法

+--  
+-- Test plan cache strategy  
+--  
+create table test_strategy(a int);  
+insert into test_strategy select 1 from generate_series(1,1000) union all select 2;  
+create index on test_strategy(a);  
+analyze test_strategy;  
+prepare test_strategy_pp(int) as select count(*) from test_strategy where a = $1;  
+-- without 5 evaluation pg uses custom plan  
+explain (costs off) execute test_strategy_pp(2);  
+                            QUERY PLAN                              
+------------------------------------------------------------------  
+ Aggregate  
+   ->  Index Only Scan using test_strategy_a_idx on test_strategy  
+         Index Cond: (a = 2)  
+(3 rows)  
+  
+-- we can force to generic plan  
+set plancache_mode to force_generic_plan;  
+explain (costs off) execute test_strategy_pp(2);  
+           QUERY PLAN              
+---------------------------------  
+ Aggregate  
+   ->  Seq Scan on test_strategy  
+         Filter: (a = $1)  
+(3 rows)  
+  
+-- we can fix generic plan by 5 execution  
+set plancache_mode to default;  
+execute test_strategy_pp(1); -- 1x  
+ count   
+-------  
+  1000  
+(1 row)  
+  
+execute test_strategy_pp(1); -- 2x  
+ count   
+-------  
+  1000  
+(1 row)  
+  
+execute test_strategy_pp(1); -- 3x  
+ count   
+-------  
+  1000  
+(1 row)  
+  
+execute test_strategy_pp(1); -- 4x  
+ count   
+-------  
+  1000  
+(1 row)  
+  
+execute test_strategy_pp(1); -- 5x  
+ count   
+-------  
+  1000  
+(1 row)  
+  
+-- we should to get really bad plan  
+explain (costs off) execute test_strategy_pp(2);  
+           QUERY PLAN              
+---------------------------------  
+ Aggregate  
+   ->  Seq Scan on test_strategy  
+         Filter: (a = $1)  
+(3 rows)  
+  
+-- but we can force to custom plan  
+set plancache_mode to force_custom_plan;  
+explain (costs off) execute test_strategy_pp(2);  
+                            QUERY PLAN                              
+------------------------------------------------------------------  
+ Aggregate  
+   ->  Index Only Scan using test_strategy_a_idx on test_strategy  
+         Index Cond: (a = 2)  
+(3 rows)  
+  
+drop table test_strategy;

小结

通过设置一个开关，可以让原本使用generic plan的SQL，强制使用custom plan。

比如我们的SQL如果本身就是分析型（或者说本身就是SLOW SQL）的情况下，实际上generic plan带来的好处是微乎其微的，反而可能因为generic plan用的是cache plan会带来不便。因为generic plan的执行计划被固定，特别不适合那种因为输入条件的变化而导致执行计划变化的SQL（通常是复杂SQL）。这种情况下，我们就可以考虑强制使用custom plan.

那么有人会说,在UDF中使用动态SQL不就好了吗(动态SQL每次都需要custom plan),要知道动态SQL在UDF中是不被CHECK的,所以可能出一些语法或者什么问题,很难检查.而使用这个开关,我们就不用担心这个问题了.即能检查SQL语法,又能强制使用custom plan。

一个反例是动态SQL中使用cache plan：《PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量)》