一个提升PostgreSQL性能的小技巧_数据库其它

 在一个(差)的PostgreSQL 查询中只要一个小小到改动(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查询时间从20s缩减到0.2s。从最简单的学习使用 EXPLAIN ANALYZE开始,到学习使用 Postgres community 大量学习时间的投入将有百倍时间到回报。

使用Postgres监测慢的Postgres查询

在这周早些时候,一个用于我们的图形编辑器上的小表(10GB,1500万行)的主键查询,在我们的一个(多个)数据库上发生来大的查询性能问题。

99.9%到查询都是非常迅速流畅的,但是在一些使用大量的枚举值的地方,这些查询会需要20秒。花费如此多到时间在数据库上,意味着使用者必须在浏览器面前等待图形编辑器的响应。很明显只因为这0.01%就会造成很不好到影响。

查询和查询计划

下面是这个出问题的查询
 

SELECT c.key,
    c.x_key,
    c.tags,
    x.name
 FROM context c
 JOIN x
  ON c.x_key = x.key
WHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)])
 AND c.x_key = 1
 AND c.tags @> ARRAY[E'blah'];

表X有几千行数据,表C有1500万条数据。两张表的主键值“key”都有适当的索引。这是一个非常简单清晰的主键查询。但有趣的是,当增加主键内容的数量,如在主键有11,000个值的时候,通过在查询语句上加上 EXPLAIN (ANALYZE, BUFFERS)我们得到如下的查询计划。
 

Nested Loop (cost=6923.33..11770.59 rows=1 width=362) (actual time=17128.188..22109.283 rows=10858 loops=1)
 Buffers: shared hit=83494
 -> Bitmap Heap Scan on context c (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
    Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
    Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
    Buffers: shared hit=50919
    -> BitmapAnd (cost=6923.33..6923.33 rows=269 width=0) (actual time=132.910..132.910 rows=0 loops=1)
       Buffers: shared hit=1342
       -> Bitmap Index Scan on context_tags_idx (cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614 rows=264777 loops=1)
          Index Cond: (tags @> '{blah}'::text[])
          Buffers: shared hit=401
       -> Bitmap Index Scan on context_x_id_source_type_id_idx (cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648 rows=267659 loops=1)
          Index Cond: (x_id = 1)
          Buffers: shared hit=941
 -> Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=10858)
    Index Cond: (x.key = 1)
    Buffers: shared hit=32575
Total runtime: 22117.417 ms

在结果的最底部你可以看到,这个查询总共花费22秒。我们可以非常直观的通过下面的CPU使用率图观察到这22秒的花费。大部分的时间花费在 Postgres和 OS 上, 只有很少部分用于I/O . 

 在最低的层面,这些查询看起来就像是这些CPU利用率的峰值。CPU图很少有用,但是在这种条件下它证实了关键的一点:数据库并没有等待磁盘去读取数据。它在做一些排序,哈希以及行比较之类的事情。

第二个有趣的度量,就是距离这些峰值很近的轨迹,它们是由Postgres“取得”的行数(本例中没有返回,就看看再忽略掉吧)。 

 显然有些动作在规则的有条不紊的浏览过许多行:我们的查询。
 
Postgres 的问题所在:位图扫描

下面是行匹配的查询计划

 

Buffers: shared hit=83494
 -> Bitmap Heap Scan on context c (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
    Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
    Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
    Buffers: shared hit=50919

Postgres 使用位图扫描表C. 当主键的数据量小的时候,它能有效的使用索引在内存里建立位图。如果位图太大,最优查询计划就改变查询方式了。在我们这个查询中,因为主键包含的数据量很大,所以查询就使用最优(系统自己判断的)的方式去检索查询候选行,并且立即查询所有和主键匹配的数据。就是这些¨放入内存¨和¨立即查询¨花费太多的时间(查询计划中的Recheck Cond)。

幸好只有30%的数据被导入到内存中,所以还不至于像从硬盘里读取那么坏。但它仍然对性能有非常明显的影响。记住,查询是非常简单的。这是一个主键查询所以没有很多明了的方式来确定它有没有戏剧性的重新架构数据库或应用程序。PGSQL-Performance mailing list给予了我们很大的帮助.
 
解决方案

这是我们喜欢开源和喜欢帮助用户的另外一个原因。Tom Lane是开源代码作者中最盛产的程序员之一,他建议我们做如下尝试:
 

SELECT c.key,
    c.x_key,
    c.tags,
    x.name
 FROM context c
 JOIN x
  ON c.x_key = x.key
WHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --)
 AND c.x_key = 1
 AND c.tags @> ARRAY[E'blah'];

把ARRAY改成VALUES,你能指出他们的不同点吗?

我们使用ARRAY[...]列举出所有的关键字以用来查询,但是这却欺骗了查询优化器。然而Values(...)却能够让优化器充分使用关键字索引。仅仅是一行代码的改变,并且没有产生任何语义的改变。

下面是新查询语句的写法,差别就在于第三和第十四行。
 

Nested Loop (cost=168.22..2116.29 rows=148 width=362) (actual time=22.134..256.531 rows=10858 loops=1)
 Buffers: shared hit=44967
 -> Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37) (actual time=0.071..0.073 rows=1 loops=1)
    Index Cond: (id = 1)
    Buffers: shared hit=4
 -> Nested Loop (cost=168.22..2106.54 rows=148 width=329) (actual time=22.060..242.406 rows=10858 loops=1)
    Buffers: shared hit=44963
    -> HashAggregate (cost=168.22..170.22 rows=200 width=4) (actual time=21.529..32.820 rows=11215 loops=1)
       -> Values Scan on "*VALUES*" (cost=0.00..140.19 rows=11215 width=4) (actual time=0.005..9.527 rows=11215 loops=1)
    -> Index Scan using context_pkey on context c (cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1 loops=11215)
       Index Cond: (c.key = "*VALUES*".column1)
       Filter: ((c.tags @> '{blah}'::text[]) AND (c.x_id = 1))
       Buffers: shared hit=44963
Total runtime: 263.639 ms

查询时间从22000ms下降到200ms,仅仅一行代码的改变效率就提高了100倍。

在生产中使用的新查询

即将发布的一段代码:
它使数据库看起来更美观轻松. 

 第三方工具

postgres慢查询不存在了。但是有谁乐意被0.1%不幸的少数折磨。要立即验证修改查询的影响,就需要Datadog来帮助我们判断修改是否是正确的。

如果你想要找出对Postgres查询改变的影响,可能需要几分钟来注册一个免费的Datadog账号。

以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索postgresql
数据库 性能提升、win7提升性能小技巧、postgresql恢复数据库、postgresql查看数据库、postgresql创建数据库,以便于您获取更多的相关知识。

时间: 2024-09-19 01:58:14

一个提升PostgreSQL性能的小技巧_数据库其它的相关文章

jQuery实用小技巧_输入框文字获取和失去焦点的简单实例_jquery

jQuery实用小技巧_输入框文字获取和失去焦点的简单实例 <input id="txt" class="text1" type="text" /> <script src="js/jquery-1.7.1.min.js"></script> <script type="text/javascript"> $(function () { $("inp

.Net应用程序性能提高小技巧

程序|技巧|性能 使用.Net做开发,性能毫无疑问非常重要,如何在最短的时间内,得到最大的性能,是我们每个人都非常关心的问题.这里,以MSDN的提示做指导,我来做一些整理工作.      所有的应用程序都应该遵守的      这里的列举的是所有的应用程序都可以用来提高性能的一些小建议:      尽早地抛出例外:Throw Exception      Exception是非常地消耗的,你可以数一下你的程序里面有多少个Exception,你看了之后就会大吃惊,注意,不要忘了,一些系统自己的方法也

即刻提升jQuery性能的十个技巧[TUTS+]

本文提供即刻提升你的脚本性能的十个步骤.不用担心,这并不是什么高深的技巧.人人皆可运用!这些技巧包括: 使用最新版本 合并.最小化脚本 用for替代each 用ID替代class选择器 给选择器指定前后文 建立缓存 避免DOM操作 避免使用concat(),利用join()处理长字串 返回false值 利用小抄和参考文档   1. 使用最新版本 jQuery一直处于不断的开发和改进过程中. John 和他的团队不断研究着提升程序性能的新方法. 一点题外话,几个月前他还发布了Sizzle,一个据说

Win7提升U盘速度小技巧

  现在的U盘一般是8G.16G了,写入速度却相对显得不足,现在为你介绍一个方法,可以修改一下系统就大大提升Windows 7下的U盘的性能. 这个方法跟我们所知的提升SATA硬盘性能的方法差不多,也就是开启"高级写入缓存(advanced write caching)",这种方法修改之后会增大作为写入缓存的系统内存容量,从而提升写入U盘的性能.不过它并不能让你更快的塞满一个8G大小的U盘,而是使得U盘对其他系统进程的响应速度更快,对于向U盘传输大量小文件或者是在优盘上运行某个应用程序

ASP.NET常用小技巧_实用技巧

今天为大家介绍6个ASP.NET常用技巧,使用操作简单,具有很高的实用性,记得收藏哦 1.跟踪页面执行  设置断点是页面调试过程中的常用手段,除此之外,还可以通过查看页面的跟踪信息进行错误排查以及性能优化.ASP.NET中启用页面跟踪非常方便,只需在Page指令中加入Trace="True"属性即可:设置断点是页面调试过程中的常用手段,除此之外,还可以通过查看页面的跟踪信息进行错误排查以及性能优化.ASP.NET中启用页面跟踪非常方便,只需在Page指令中加入Trace="T

客户端js性能优化小技巧整理_javascript技巧

下面是一些关于客户端JS性能的一些优化的小技巧: 1. 关于JS的循环,循环是一种常用的流程控制.JS提供了三种循环:for(;;).while().for(in).在这三种循环中 for(in)的效率最差,因为它需要查询Hash键,因此应尽量少用for(in)循环,for(;;).while()循环的性能基本持平.当然,推荐使用for循环,如果循环变量递增或递减,不要单独对循环变量赋值,而应该使用嵌套的++或--运算符. 2. 如果需要遍历数组,应该先缓存数组长度var len=arr.len

自动档汽车如何省油的小技巧_生活健康

1.先说自动档车的起步:不少朋友起步给油太少,这样没必要,其实也不好.自动档的换挡设计在2500转(热车时在2200左右换挡),给油太少,达不到2500转时,换挡很慢,不但发动机声音大,而且容易使发动机产生积炭,还会影响发动机的寿命,故多给一些油,让转速迅速到达2500转.这样做的目的不但起步快,对发动机也有利,虽然瞬时油耗可以到达40L,但平均油耗降低. 2.自动档车的带挡滑行最省油!加油到适当时候,松掉油门,这时候的瞬时油耗:0 !!,而且根据行驶状况可以保持很长时间.在市内特别有用:看到远

HTML实现title 属性换行小技巧_经验交流

在换行的地方添加 或 实现换行 <tr title="指标名称:${target.intename} 指标说明:${target.remark} 指标名称:${target.intename}"> 是不是超级美观~,简单的2个字符就搞定了,有需要的小伙伴参考下吧. 以上是小编为您精心准备的的内容,在的博客.问答.公众号.人物.课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索换行 title 倩女幽魂2 经验小技巧.html title 换行.title 换行.m

介绍PostgreSQL中的Lateral类型_数据库其它

PostgreSQL 9.3 用了一种新的联合类型! Lateral联合的推出比较低调,但它实现了之前需要使用编写程序才能获得的强大的新查询. 在本文中, 我将会介绍一个在 PostgreSQL 9.2 不可能被实现的渠道转换分析. 什么是 LATERAL 联合? 对此的最佳描述在文档中 可选 FROM 语句清单 的底部: LATERAL 关键词可以在前缀一个 SELECT FROM 子项. 这能让 SELECT 子项在FROM项出现之前就引用到FROM项中的列. (没有 LATERAL 的话,