前几天 PostgreSQL 社区有一篇比较有意思的文章:A Hairy PostgreSQL Incident备份),讲述了一个由于升级 PostgreSQL 导致线上出现慢查询的排查过程,作者写的非常诙谐幽默,这里简单复述下相关过程:

  1. 从之前某个版本升级 11 后,有一个服务出现慢查询,应急人员已经定位到导致问题的模块,但还不清楚与原因

  2. pg_stat_activity 由于包含大量 in (...) 的查询,无法定位出耗时高的 SQL

  3. 有一个可供对比的老版本数据库,无查询慢的问题

  4. 该文作者尝试做两个环境下,数据库的火焰图,但还是没明显线索

  5. 经过作者一番脑补分析,重新审视火焰图,发现了细微差别

  6. 成功复现,网上搜索找到了一个 hack 的方案,重新发布,结束

具体细节可以去阅读原文,该文一个美中不足的地方是没进行根因分析,找到修复方案后就没继续跟踪了,笔者正好最近读完了 The Internals of PostgreSQL,正好分析下该文的故障,来巩固相关知识点,看看从这次事故中能学到什么。

pg_stat_activity

原文评论中,有人提到可以用 where id = any() 来代替 where id in .. ,理由是这样在 pg_stat_activity 里面相似查询只会有一个 query id,便于查找问题 SQL。它俩的具体区别可参考下面这个问题:

这算是一个知识点,但更重要的是 pg_stat_activity 应该要记录哪些超时的 SQL,而不能仅仅只包含执行成功的,超时的往往意味着问题更大,从原文看是没有记录。笔者在设计系统时也犯类似错误,日志里忽略掉了那些超时的错误请求。

Bitmap Index Scan

一般来说,数据库为了支持加速查询,会使用 B+Tree 来做索引,这里的 bitmap index 又是做什么用的呢?

对于类似 where id = ? 的点查来说,先查索引,再查数据的方式是效率最高的,但是对于区间查询来说,情况就复杂了,极端情况就是区间查询会涉及到大量数据,先去查索引,再根据索引去查数据,就不如直接顺序扫描表了,因为索引对应的数据不是连续的,会造成大量随机 IO,而随机 IO 效率是很差的。

介于『先查索引再查数据表』与『直接扫描表』中间的,就是 bitmap index scan 了,这种方式通过索引查到合适的行时,不会立马去查对于的数据,而是记录在一个 bitmap 中,等查完索引后,再根据 bitmap 去顺序扫表即可。(图片来源

https://img.alicdn.com/imgextra/i3/581166664/O1CN01YMvPiT1z6A7jpJxZe_!!581166664.jpg
Bitmap Index Scan 示意图

Cost-based Query plan

关系型数据库在进行查询计划的优化时,一般会采用基于 cost 的优化器,相比启发式的优化器更灵活、更有效,他们的区别不是本文重点,感兴趣的读者可参考:How We Built a Cost-Based SQL Optimizer,这里不再赘述。优化器可以说是不同数据库差距最大的地方,那么 PostgreSQL 是怎么计算 cost 的呢?

先回到原文,造成慢查询的原因是 PostgreSQL 选择了一个不合适的索引(206G),相比好的查询,多了一个 BitmapAnd 操作,相关 SQL 如下:

1
2
3
4
SELECT *
FROM table
WHERE (text_col1, numeric_col3) IN (('first1',1),('second1',2), ... )
  AND timestamp_col1 <= :1

该表本身 845G,有两个二级索引:

  • index1, (text_col1, text_col2) 229G

  • index2, (numeric_col1, numeric_col2, numeric_col3, timestamp_col1) 206G

正常时候的查询计划如下(后文称为 PA):

 Bitmap Heap Scan on table  (cost=3791.09..80234213.68 rows=6713 width=273) (actual time=74.911..74.953 rows=0 loops=1)
   Output: <all columns>
   Recheck Cond: (((text_col1)::text = 'first1'::text) OR ... OR ((text_col1)::text = 'second1'::text))
   Filter: ((timestamp_col1 <= '2022-02-09 00:00:00'::timestamp without time zone) AND ((((text_col1)::text = 'first1'::text) AND ... AND (numeric_col3 = '2'::numeric))))
   ->  BitmapOr  (cost=3791.09..3791.09 rows=212565 width=0) (actual time=74.905..74.946 rows=0 loops=1)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.026..0.026 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'first1'::text)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.446..0.446 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'second1'::text)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.447..0.447 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'third1'::text)

出问题时候的查询计划如下(后文称为 PB):

 Bitmap Heap Scan on table  (cost=50887511.91..3195672174.06 rows=73035 width=24)
   Recheck Cond: ((((text_col1)::text = 'first1'::text) OR ...
     ...OR ((text_col1)::text = 'second1'::text)) AND (timestamp_col1 <= '2021-02-09 00:00:00'::timestamp without time zone))
   Filter: ((((text_col1)::text = 'first1'::text) AND (numeric_col3 = '1'::numeric)) OR ...
    ...OR (((text_col1)::text = 'second1'::text) AND (numeric_col3 = '2'::numeric)))
   ->  BitmapAnd  (cost=50887511.91..50887511.91 rows=418910 width=0)
         ->  BitmapOr  (cost=13320.96..13320.96 rows=513729 width=0)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'first1'::text)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'second1'::text)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'third1'::text)
         ->  Bitmap Index Scan on index2  (cost=0.00..50874172.44 rows=2597695782 width=0)
               Index Cond: (timestamp_col1 <= '2021-02-09 00:00:00'::timestamp without time zone)
(493 rows)

触发 BitmapAnd 的条件是 timestamp 列,在 2022-02-09 时没有 BitmapAnd,改成 2021-02-09 时则会出现。根据原文描述,timestamp 的取值范围很少。

一般正常来说,随着时间增加,数据量越来越多,即 timestamp<=2021-02-09 匹配的结果小于 timestamp<=2022-02-09 ,根据上面 bitmap index scan 的知识,这里推测下 PostgreSQL 的决策逻辑,不一定正确,仅供参考:

  • 在小于 2022 时,基本上所有数据都符合条件,如果这时再去选择 bitmap index scan,就不如直接顺序扫表了,又由于还有另一个条件,因此就选择直接 scan 它的结果集

  • 当小于 2021 时,匹配的结果相比上述条件会减少,PostgreSQL 认为 bitmap index scan 相比顺序扫表更合适,因此 bitmap 的方式就用上了,貌似 PostgreSQL 没有考虑其他 where 条件可能会过滤掉大部分数据,而是针对每个 where 条件,分别计算的 cost。

但这里有个明显的问题,在 where 条件涉及两个索引时,用其中一个条件则可以过滤掉大部分数据(可以看到,PB 的 BitmapOr 预估输入行只有 513729),第二个索引不一定要用得上,可以直接在第一个条件的结果集上进行过滤即可,这其实就是 PA 的计划:

  • 有 212565 条数据符合条件一(in 语句)

  • 把条件二(时间戳小于语句)应用到条件一的结果集上后,会有 6713 条记录

如何计算 cost

对于一次数据查询,分为两个过程:准备阶段(start_up_cost)与执行阶段(run_cost)。下面来介绍不同扫描方式下的 cost 计算。

顺序扫表

start_up_cost = 0
run_cost = cpu_run_cost + disk_run_cost
         = (cpu_tuple_cost + cpu_operator_cost) * NumOfTuple + seq_page_cost * NumOfPage
         = (0.001 + 0.0025) * NumOfTuple + 1.0 * NumOfPage

需要注意的是,上面参数没有单位,用的是相对概念,比如 sql_page_cost 表示顺序扫 page 的代价,默认是 1.0,与之对应的参数是 random_page_cost 默认值是 4.0,表示随机扫比顺序扫慢四倍,这是基于 HDD 盘的假设,对于 SSD 或者内存来说,就有些大了。这里有个真实案例供参考:

索引扫描

start_up_cost = (ceil(log2 NumOfTuple) + HeightOfIndex + 1) * 50 * cpu_operator_cost
run_cost = index_cpu_cost + table_cpu_cost + index_io_cost + table_io_cost
# 其中
index_cpu_cost = selectivity * NumOfIndexTuple * (cpu_index_tuple_cost + qual_op_cost)
table_cpu_cost = selectivity * NumOfTuple * (cpu_tuple_cost)
index_io_cost = ceil(selectivity * NumOfPage) * random_page_cost
table_io_cost = max_io_cost + indexCorrelation^2 *(min_io_cost - max_io_cost)
# 其中
max_io_cost = NumOfPage * random_page_cost
min_io_cost = (1 * random_page_cost + ceil(selectivity * NumOfPage) - 1) * seq_page_cost
  • 准备阶段只考虑从索引树的 root 开始,遍历到叶子节点,并将这个 page 的第一个 tuple 的读入为止,不需要读取到符合 where 条件的 tuple

  • HeightOfIndex 表示 index 树的高度

  • 运行阶段涉及到索引与表,是它们两者 CPU 与 IO cost 的和

  • selectivity 表示 where 条件的选择性,主要有两种计算方式:MCV 与 histogram,这里不再展开介绍,可参考 The Internals of PostgreSQL : Chapter 3 Query Processing 相关章节

  • indexCorrelation 表示索引的相关性,表示的是一个行的物理存储顺序与其值顺序的相关性,取值范围 [-1, 1] ,越靠近两侧,相关性越高,cost 代价也越低

  • max_io_cost 表示 IO 的最坏 cost,即随机扫

  • min_io_cost 表示 IO 最好的 cost, 也就是顺序扫描所选表的所有页

https://img.alicdn.com/imgextra/i4/581166664/O1CN01DZA2431z6A7lgbUrt_!!581166664.jpg
索引相关性示意图

这里只是介绍了索引扫描 cost 的基本计算方式,需要结合实际案例分析来巩固,可以参考下面这篇文章,该作者从问题出发,一步步深入分析了慢 SQL 产生的原因,如何改变查询计划来解决问题,甚至还有源码分析,非常具有参考价值,值得反复阅读

Query Hint

在该文评论中,批评最为激烈的是:都 2022 年了,为什么 PostgreSQL 不支持 query hint,而要用 CTE 这种 hack 的方式!关于这个问题 PostgreSQL 社区也讨论了很久,甚至有一个专门的 WIKI 页面:

核心论点如下:

  • Hint 是一种解决问题的临时方案,随着数据的增长,数据分布可能会不一样,因此 hint 可能会失效

  • PostgreSQL 作为世界上最先进的开源数据库,在多数情况下,比 DBA 更了解如何执行一个 query

  • Hint 是很多商业数据库的主要卖点,算是一种商业模式,但这并不代表 hint 的必要性

  • 相比之前带有 hint 的数据库,PostgreSQL 可以通过调整参数来达到改变 query plan,2ndQuadrant 公司的 Hinting at PostgreSQL 这篇文章,有更具体介绍

看到这部分时,突然想到知乎上的一个问题:

早期互联网的发展讲究的是『快糙猛』,出了问题要赶紧修,管他什么原理,先修复了再说,而这种观点正是 PostgreSQL 所摒弃的。而 PostgreSQL 近几年又有燎原之势,恰恰说明了其先进性,之前的数据库无法满足业务的复杂查询需求。

https://img.alicdn.com/imgextra/i1/581166664/O1CN01rCAJ1i1z6A7YO7idU_!!581166664.jpg
db-engine 常见关系型数据趋势图

当然,PostgreSQL 也不是完美的,Hint 也不是一文不值,在看 PostgreSQL Optimizer Methodology (Robert Haas) - YouTube 时,作者就介绍了 PostgreSQL 一个不能很好处理的 case:

https://img.alicdn.com/imgextra/i4/581166664/O1CN0148MqIJ1z6A7Tb3rxd_!!581166664.jpg
The Most Annoying Optimizer Fail in PostgreSQL

Robert Haas 是 PostgreSQL 的主要贡献者,EnterpriseDB 的 VP,他在 Talking about the PostgreSQL Optimizer at CMU 一文中也承认了 hint 的价值。

总结

PostgreSQL 作为世界上最先进的开源数据库,是业界许多产品的鼻祖,比如华为的 GaussDB,MPP 架构的 Greenplum 等。在没看 The Internals of PostgreSQL 之前,一直以为计算 cost 是多深不可测的东西,但其实这都是心理作用,再复杂的逻辑也是一行行代码写出来。当然笔者的 PostgreSQL 学习之路也才刚开始,文中难免有不足之处,敬请包涵和指教。