一次火烧眉毛的 PostgreSQL 事故分析

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

• sql - IN vs ANY operator in PostgreSQL - Stack Overflow

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

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

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

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

关系型数据库在进行查询计划的优化时，一般会采用基于 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 条记录

对于一次数据查询，分为两个过程：准备阶段（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

索引扫描

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, 也就是顺序扫描所选表的所有页

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

• 从一个有趣的SQL优化案例，自顶向下了解数据库优化机制 （PDF 备份）

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

• OptimizerHintsDiscussion - PostgreSQL wiki

核心论点如下：

• Hint 是一种解决问题的临时方案，随着数据的增长，数据分布可能会不一样，因此 hint 可能会失效
• PostgreSQL 作为世界上最先进的开源数据库，在多数情况下，比 DBA 更了解如何执行一个 query
• Hint 是很多商业数据库的主要卖点，算是一种商业模式，但这并不代表 hint 的必要性
• 相比之前带有 hint 的数据库，PostgreSQL 可以通过调整参数来达到改变 query plan，2ndQuadrant 公司的 Hinting at PostgreSQL 这篇文章，有更具体介绍

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

• PostgreSQL 也很强大，为何在中国大陆，MySQL 成为主流，PostgreSQL 屈居二线呢？

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

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

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

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

• A Hairy PostgreSQL Incident | Hacker News
• A hairy PostgreSQL incident : Reddit
• Indexes in PostgreSQL — 4 (Btree) : Postgres Professional
• PostgreSQL: Documentation: 10: 69.1. Row Estimation Examples
• Understanding bitmap indexes in postgresql - Stack Overflow
• Postgres uses Bitmap Index Scan instead of normal Index Scan - Stack Overflow