关于PostgreSQL中的plan cache(或预备语句)

SQL的执行大致分为解析，优化和执行几个步骤。解析和优化的结果是执行计划，通常相同的SQL语句要被执行无数遍，并且每次执行很可能采用的是相同的执行计划。生成执行计划的过程是要花费时间的，特别是对一些复杂SQL。那么如果把执行计划缓存起来，下次执行的时候跳过优化这一步不就可以提高性能了吗？不错，这就是所谓的plan cache。

严格来讲，PostgreSQL并没有提供plan cache这样的功能，PG提供的是预备语言(preparedstatement)。关键区别在于，预备语言要求应用给这个语句起个名字，之后应用再通过这个名字请求服务端执行，并且由应用负责回收。而plan cache对应用应该是透明的。

1.PG的预备语句

PG的预备语言的特点可简单概括如下：
1)预备语言的有效范围
  预备语言是会话级别而不是全局的，所以预备语言的名称必须在会话内是唯一的。

2）创建预备语句的方式
创建一个预备语句主要有下几种方式
  a)PREPARE语句
  b)在"扩展查询"协议中使用命名的语句
  c)SPI_prepare_plan()

应用程序都是通过API驱动访问PG后端的。API驱动一般会有条件的通过方式b)使用PG的预备语言功能。
比如对pgjdbc，通常是使用扩展查询协议的未命名语句。但在满足以下条件时，使用命名语句。
i)应用程序使用PreparedStatement，且
ii)该PreparedStatement被执行的次数达到了PrepareThreshold（默认是5）
参考：http://jdbc.postgresql.org/documentation/head/server-prepare.html

对Npgsql，通常是使用简单查询协议。但在满足以下条件时，使用扩展查询协议的命名语句。
i)应用主动调了Prepare()方法，或
ii)应用设置了AlwaysPrepare参数

3)预备语句与查询计划的关联
a)无参的预备语句
  对于无参的预备语句,在第一次执行预备语句时生成执行计划，以后的执行会重用这个执行计划。
b)带参的预备语句
  对于带参的预备语句，最优的执行计划可能会因参数值而不同，每次都根据传入参数生成新的执行计划又可能造成无谓的浪费。对此，PG采用了以下策略:
i)前5次执行预备语句，每次都根据参数生成新的执行计划，称为custom plan。
ii)第6次执行预备语句，生成一个不依赖参数的执行计划并保存起来，称为generic plan。如果generic plan的代价小于之前所有custom plan的平均代价的1.1倍，则采用generic plan，否则根据参数生成新的custom plan。
iii)再以后的执行与第6次执行类似，除了不用生成generic plan，因为第6次执行已经生成过generic plan了。

*)上面的描述把执行计划失效等一些琐碎事情略过了

4)预备语句的释放
预备语句使用完毕后需要主动释放预备语言，释放方法和创建方法是相对应的
  a)DEALLOCATE
  b)通过"扩展查询"协议发送CLOSE消息
  c)SPI_freeplan()

预备语言被释放时，会顺便释放与之关联的generic plan。(custom plan在每次执行结束后就释放了,所以不用额外释放)。如果应用忘了释放预备语言，预备语言会一致持续到会话结束。

2.如何达到对应用透明的plan cache的效果？

JDBC中有个StatmentCache功能，可以把已经close掉的PreparedStatement缓存起来，以备下次使用。在使用连接池时，即使连接关掉了，缓存的PreparedStatement依然有效，因为缓存的PreparedStatement是挂在物理连接而不是逻辑连接上。但是，目前的pgjdbc并没有实现StatmentCache，所以这条路走不通。(去年我们在Symfoware的jdbc中倒是实现了StatementCache)

然而PG倒是在plsql中实现了plan cache。plsql的解释器会把SQL以预备语句的方式执行，并在会话里保存这些预备语句。
下面以分区表的单点查询作为例子演示一下plsql的plan cache的效果。
这是分区表的定义：

db1000=# \d maintb;
       Table "public.maintb"
 Column | Type | Modifiers
--------+---------------+-----------
 id | integer |
 name | character(10) |
Triggers:
    insert_maintb_trigger BEFORE INSERT ON maintb FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger()
Number of child tables: 1000 (Use \d+ to list them.)

*）详细表定义参照：http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4732253

由于有1000个分区，所以生成执行计划很耗时，要56毫秒。

db1000=# explain select * from maintb where id=1;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Append (cost=0.00..44.73 rows=12 width=18)
   -> Seq Scan on maintb (cost=0.00..0.00 rows=1 width=48)
         Filter: (id = 1)
   -> Bitmap Heap Scan on childtb_1 (cost=4.50..44.73 rows=11 width=15)
         Recheck Cond: (id = 1)
         -> Bitmap Index Scan on childtb_idx_1 (cost=0.00..4.50 rows=11 width=0)
               Index Cond: (id = 1)
(7 rows)

Time: 56.631 ms

执行了2次，时间都是57毫秒。大部分时间花在生成执行计划上。

db1000=# select * from maintb where id=1;
 id | name
----+------------
  1 | abcde12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
(9 rows)

Time: 57.315 ms
db1000=# select * from maintb where id=1;
 id | name
----+------------
  1 | abcde12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
(9 rows)

Time: 57.665 ms

现在定义一个plsql的存储过程。

db1000=# CREATE FUNCTION func1() RETURNS TABLE(id int, name char(10)) AS $$
db1000$# BEGIN
db1000$# RETURN QUERY select * from maintb where maintb.id=1;
db1000$# END;
db1000$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 4.313 ms

通过这个存储过程完成和前面相同的任务。第一次执行存储过程，耗时48毫秒。这个时间包含了执行计划的生成。

db1000=# select * from func1();
 id | name
----+------------
  1 | abcde12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
(9 rows)

Time: 48.998 ms

第2次执行，plsql会直接利用上次执行生成的执行计划，所以只花了2毫秒。

db1000=# select * from func1();
 id | name
----+------------
  1 | abcde12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
  1 | abcd12345
(9 rows)

Time: 2.366 ms

3.参考

http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/  (*)

src/backend/tcop/postgres.c
src/backend/utils/cache/plancache.c
*)其实关于PG的Plan cache，德哥的这篇博客讲的更细。