作者
digoal
日期
2021-09-29
标签
PostgreSQL , index skip scan , 递归 , cte , distinct
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 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
