PostgreSQL 16 preview - 支持 Right Anti Join , 对hash right join (non-nullable input)可选择small table作为hash table.
digoal
2023-04-06
PostgreSQL , PolarDB , join , right join , non-nullable input , join 算法 , join 顺序 , 内表 , 外表 , hash table , Right Anti Join
PostgreSQL 16支持 Right Anti Join, 可能更加有效的提升hash right join的性能, 因为当right JOIN的一系列table输入是non-nullable的数据时, 可以调整right join的顺序.
join的优化除了算法(hash, nest, merge), 还有在很多JOIN时, 保证逻辑正确的情况下 调整JOIN先后顺序, 快速过滤结果到更小范围. 以及hash join 选择更小的表作为hash table 等.
PostgreSQL 16支持 Right Anti Join, 给出的优化对hash right join可能更有好处, 当right JOIN的一系列table输入是non-nullable的数据时, 可以调整right join的顺序, 采用更小的表作为hash table.
Support "Right Anti Join" plan shapes.
author Tom Lane <tgl@sss.pgh.pa.us>
Wed, 5 Apr 2023 20:59:00 +0000 (16:59 -0400)
committer Tom Lane <tgl@sss.pgh.pa.us>
Wed, 5 Apr 2023 20:59:09 +0000 (16:59 -0400)
commit 16dc2703c5413534d4989e08253e8f4fcb0e2aab
tree c25f739d183d93510418b734ebd36fc3e2e9fde9 tree
parent dad50f677c42de207168a3f08982ba23c9fc6720 commit | diff
Support "Right Anti Join" plan shapes.
Merge and hash joins can support antijoin with the non-nullable input
on the right, using very simple combinations of their existing logic
for right join and anti join. This gives the planner more freedom
about how to order the join. It's particularly useful for hash join,
since we may now have the option to hash the smaller table instead
of the larger.
Richard Guo, reviewed by Ronan Dunklau and myself
Discussion: https://postgr.es/m/CAMbWs48xh9hMzXzSy3VaPzGAz+fkxXXTUbCLohX1_L8THFRm2Q@mail.gmail.com
