修改一行代码提升 Postgres 性能 100 倍

在一个(差)的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账号

英文原文:100x faster Postgres performance by changing 1 line

(0)

相关推荐

  • 修改一行代码提升 Postgres 性能 100 倍

    在一个(差)的PostgreSQL 查询中只要一个小小到改动(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查询时间从20s缩减到0.2s.从最简单的学习使用 EXPLAIN ANALYZE开始,到学习使用 Postgres community 大量学习时间的投入将有百倍时间到回报. 使用Postgres监测慢的Postgres查询 在这周早些时候,一个用于我们的图形编辑器上的小表(10GB,1500万行)的主键查询,在我们的一个(多个)数据库上发生来大的查询性能问

  • 一行代码让 Python 的运行速度提高100倍

    python一直被病垢运行速度太慢,但是实际上python的执行效率并不慢,慢的是python用的解释器Cpython运行效率太差. "一行代码让python的运行速度提高100倍"这绝不是哗众取宠的论调. 我们来看一下这个最简单的例子,从1一直累加到1亿. 最原始的代码: import time def foo(x,y): tt = time.time() s = 0 for i in range(x,y): s += i print('Time used: {} sec'.form

  • 只需要这一行代码就能让python计算速度提高十倍

    一.前言 Python语言近年来人气爆棚.它广泛应用于数据科学,人工智能,以及网络安全问题中,由于代码可读性较强,学习效率较高,吸引了许多非科班的同学进行学习.然而,使用Python一段时间以后,发现它在速度上完全没有优势可言,特别是计算密集型任务里,性能问题一直是Python的软肋.本文主要介绍了Python的JIT编译器Numba,能够在对代码侵入最少的情况下,极大加速计算核心函数的运行速度,适合数据分析业务相关的同学使用. 首先要回答这样一个问题:当运行同一个程序时,为什么Python会

  • 一个提升PostgreSQL性能的小技巧

    在一个(差)的PostgreSQL 查询中只要一个小小到改动(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查询时间从20s缩减到0.2s.从最简单的学习使用 EXPLAIN ANALYZE开始,到学习使用 Postgres community大量学习时间的投入将有百倍时间到回报. 使用Postgres监测慢的Postgres查询 在这周早些时候,一个用于我们的图形编辑器上的小表(10GB,1500万行)的主键查询,在我们的一个(多个)数据库上发生来大的查询性能问题

  • 提升Nginx性能的一些建议

    如果你的Web应用只跑在一台机器上,那要提升其性能非常简单:换一台更快的,多配几个处理器,多加几条内存,磁盘阵列也要高速的.换了以后,这台机器上跑的WordPress服务器.Node.js或Java应用速度都会加快.(要是应用还会访问另一台数据库服务器,那也简单:找两台更快的机器,用更快的网络连起来就行了) 麻烦在于,机器速度并不是问题.很多时候Web应用慢,是因为要在各种任务之间切换,一会儿要处理数千个连接上的用户请求,一会儿要向磁盘读写文件,一会儿又要运行应用的代码,一会儿又要去干别的.应用

  • Golang中字符串(string)与字节数组([]byte)一行代码互转实例

    目录 一.字符串与字节数组? 二.详细代码 1.简单的方式字节转字符串 2.简单的字符串转字节数组 3.字节转字符串 4.字符串转字节数组 5.完整运行测试 补充:一些结论如下 总结 一.字符串与字节数组? 字符串是 Go 语言中最常用的基础数据类型之一,本质上是只读的字符型数组,虽然字符串往往都被看做是一个整体,但是实际上字符串是一片连续的内存空间. Go 语言中另外一个类型字节(Byte).在ASCII中,一个英文字母占一个字节的空间,一个中文汉字占两个字节的空间.英文标点占一个字节,中文标

  • 十个迅速提升JQuery性能让你的JQuery跑得更快

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

  • 提升PHP性能的21种方法介绍

    1.用单引号来包含字符串要比双引号来包含字符串更快一些.因为PHP会在双引号包围的字符串中搜寻变量,单引号则不会.2.如果能将类的方法定义成static,就尽量定义成static,它的速度会提升将近4倍.3.$row['id'] 的速度是$row[id]的7倍.4.echo 比 print 快,并且使用echo的多重参数(译注:指用逗号而不是句点)代替字符串连接,比如echo $str1,$str2.5.在执行for循环之前确定最大循环数,不要每循环一次都计算最大值,最好运用foreach代替.

  • 使用spring-cache一行代码解决缓存击穿问题

    目录 引言 正文 目前缺陷 真正方案 缓存穿透 缓存击穿 缓存雪崩 文末 引言 今天,重新回顾一下缓存击穿这个问题! 之所以写这个文章呢,因为目前网上流传的文章落地性太差(什么布隆过滤器啊,布谷过滤器啊,嗯,你们懂的),其实这类方案并不适合在项目中直接落地. 那么,我们在项目中落地代码的时候,其实只需要一个注解就能解决这些问题,并不需要搞的那么复杂. 本文有一个前提,读者必须是java栈,且是用Springboot构建自己的项目,如果是go技术栈或者python技术栈的,可能介绍的思路仅供大家参

  • Android一行代码实现圆形头像

    效果图 在开发APP中,经常要实现圆形头像,那么该如何实现呢? 要裁剪吗,要重写draw函数吗?不用,只用一行代码就可以实现 Glide实现圆形图像 Glide.with(mContext) .load(R.drawable.iv_image_header) .error(R.drawable.ic_error_default) .transform(new GlideCircleTransform(mContext)) .into(mImage); 其中load后为载入的图像,error后为出

随机推荐