I never understood why this rule did not handle inner join, but Haisheng’s analysis is plausible. (By the way, see https://issues.apache.org/jira/browse/CALCITE-892 <https://issues.apache.org/jira/browse/CALCITE-892> for when the rule was originally added.)
It’s not a good idea for rules to restrict themselves based on cost. They should apply based on correctness. It is for the engine to decide whether the result is more or less efficient. So, I agree that this rule should apply to inner join as well as left and right. Maybe the rule’s constructor would have a predicate so that people can create a variant that only matches, say, left joins. Julian > On Nov 25, 2019, at 4:48 PM, Haisheng Yuan <h.y...@alibaba-inc.com> wrote: > > Yes, we can. > > Currently the rule applies only on left/right outer join, because the > cardinality of join will always be greater than or equal with the cardinality > of outer relation. For inner join, the join cardinality may be much less, in > which case, sorting on the join output might be cheaper. You can change the > code to apply to inner join too. > > But be aware that this rule is a hack, because it may generate useless plan, > when the inner join is implemented as a MergeJoin that breaks the ordering of > the input. > > - Haisheng > > ------------------------------------------------------------------ > 发件人：Scott Reynolds<sdrreyno...@gmail.com> > 日 期：2019年11月26日 07:28:40 > 收件人：<dev@calcite.apache.org> > 主 题：Question about SortJoinTransposeRule and Inner Joins > > The performance of our queries are dependent on our ability to push the > filter and sort into the RPC layer. Today the planner's > SortJoinTransposeRule > <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java> > pushes the sort through the join for LEFT OUTER and RIGHT OUTER joins. The > logic comment states the following > // 1) If join is not a left or right outer, we bail out > // 2) If sort is not a trivial order-by, and if there is > // any sort column that is not part of the input where the > // sort is pushed, we bail out > // 3) If sort has an offset, and if the non-preserved side > // of the join is not count-preserving against the join > // condition, we bail out > > I am wondering if we can actually push the sort down through the INNER JOIN > if all the sort conditions are on one side of the join. > > SELECT b.title, b.published_date, b.sales > FROM Book b > INNER JOIN Author a ON b.author = a.id > ORDER BY b.published_date, b.sales, b.title >
