【DB吐槽大會】第62期 - PG 不支援index skip scan

背景

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來解決這個性能問題.

https://github.com/digoal/blog/blob/master/202109/20210929_07.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 PostgreSQL 許願連結