开发者社区> 小桥河西> 正文

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

简介: SQL的执行大致分为解析,优化和执行几个步骤。解析和优化的结果是执行计划,通常相同的SQL语句要被执行无数遍,并且每次执行很可能采用的是相同的执行计划。生成执行计划的过程是要花费时间的,特别是对一些复杂SQL。
+关注继续查看
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,德哥的这篇博客讲的更细。

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

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23596 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22372 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
16676 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
22550 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
19834 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
11999 0
+关注
小桥河西
半个PostgreSQL DBA,热衷于数据库及分布式技术。 - https://github.com/ChenHuajun - https://pan.baidu.com/s/1eRQsdAa
187
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载