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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

小桥河西 2015-01-15 19:14:24 浏览765
展开阅读全文
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的效果
这是分区表的定义:

点击(此处)折叠或打开

  1. db1000=# \d maintb;
  2.        Table "public.maintb"
  3.  Column | Type | Modifiers
  4. --------+---------------+-----------
  5.  id | integer |
  6.  name | character(10) |
  7. Triggers:
  8.     insert_maintb_trigger BEFORE INSERT ON maintb FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger()
  9. 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毫秒。

点击(此处)折叠或打开

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

  12. Time: 56.631 ms

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

点击(此处)折叠或打开

  1. db1000=# select * from maintb where id=1;
  2.  id | name
  3. ----+------------
  4.   1 | abcde12345
  5.   1 | abcd12345
  6.   1 | abcd12345
  7.   1 | abcd12345
  8.   1 | abcd12345
  9.   1 | abcd12345
  10.   1 | abcd12345
  11.   1 | abcd12345
  12.   1 | abcd12345
  13. (9 rows)

  14. Time: 57.315 ms
  15. db1000=# select * from maintb where id=1;
  16.  id | name
  17. ----+------------
  18.   1 | abcde12345
  19.   1 | abcd12345
  20.   1 | abcd12345
  21.   1 | abcd12345
  22.   1 | abcd12345
  23.   1 | abcd12345
  24.   1 | abcd12345
  25.   1 | abcd12345
  26.   1 | abcd12345
  27. (9 rows)

  28. Time: 57.665 ms

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

  1. db1000=# select * from func1();
  2.  id | name
  3. ----+------------
  4.   1 | abcde12345
  5.   1 | abcd12345
  6.   1 | abcd12345
  7.   1 | abcd12345
  8.   1 | abcd12345
  9.   1 | abcd12345
  10.   1 | abcd12345
  11.   1 | abcd12345
  12.   1 | abcd12345
  13. (9 rows)

  14. Time: 48.998 ms

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

点击(此处)折叠或打开

  1. db1000=# select * from func1();
  2.  id | name
  3. ----+------------
  4.   1 | abcde12345
  5.   1 | abcd12345
  6.   1 | abcd12345
  7.   1 | abcd12345
  8.   1 | abcd12345
  9.   1 | abcd12345
  10.   1 | abcd12345
  11.   1 | abcd12345
  12.   1 | abcd12345
  13. (9 rows)

  14. Time: 2.366 ms

3.参考

http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/  (*)
http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
src/backend/tcop/postgres.c
src/backend/utils/cache/plancache.c
*)其实关于PG的Plan cache,德哥的这篇博客讲的更细。

网友评论

登录后评论
0/500
评论