背景

1、产品的问题点

• PG 不支持index skip scan

2、问题点背后涉及的技术原理

• PG 的索引扫描方法仅支持index scan, index only scan, bitmap index scan.

• • index scan 为索引叶子节点链表顺序扫描.
  • index only scan与index scan类似, 只是某些情况下不需要回表.
  • bitmap index scan先汇总ctid的block id, 然后按block id顺序回表再recheck.

• 以上扫描方法都不支持跳跃式扫描, 例如distinct gid, 即使GID字段有索引, 走索引扫描方法需要扫描整个索引才能得到distinct gid.

• • 并不能拿到1个GID后, 直接回到ROOT节点找大与上一个GID的下一个GID. 因此效率非常低.

3、这个问题将影响哪些行业以及业务场景

• 稀疏值的统计、去重. 例如在用户的行为日志中, 求某个时间段的活跃用户(count distinct uid)

4、会导致什么问题?

• 性能较差

5、业务上应该如何避免这个坑

使用递归查询来解决这个问题:

• 《重新发现PostgreSQL之美 - 6 index链表跳跳糖 (CTE recursive 递归的详细用例)》
• 《递归+排序字段加权 skip scan 解决 窗口查询多列分组去重的性能问题》
• 《PostgreSQL 排序去重limit查询优化 - 递归 vs group分组 (loop降到极限, block scan降到极限)》
• 《PostgreSQL 递归妙用案例 - 分组数据去重与打散》
• 《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》
• 《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
• 《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》
• 《PostgreSQL雕虫小技cte 递归查询，分组TOP性能提升44倍》
• 《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
• 《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• SQL难度急剧增加, 一般开发者可能不会写递归SQL.
• 如果是框架生成的SQL则无法改写. 导致性能差.

7、数据库未来产品迭代如何修复这个坑

• 希望引入index skip scan的功能, 直接在索引扫描层面解决, 而不是通过使用递归SQL来解决这个性能问题.

PostgreSQL 许愿链接