Hi Scott, I think the rule did not handle the case of inner joins since along with the sort it pushes also the limit and offset. In order to work properly for inner joins you have to take into account foreign keys (see the discussion in [1]).
Recently we added another rule, i.e., SortJoinCopyRule [2], which as the name indicates, copies a sort below a (any) join and may be useful for your needs. This rule is not part of the default ruleset since it may generate a lot of useless permutations. As discussed in CALCITE-2624 [3] a better way would be to do it via properties but we have not advanced towards this direction yet. Contributions are welcomed! Best, Stamatis [1] https://lists.apache.org/thread.html/53b3d2336d59d92f299f1bab9ed1380285598b27df0b78086818f930@%3Cdev.calcite.apache.org%3E [2] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/SortJoinCopyRule.java [3] https://issues.apache.org/jira/browse/CALCITE-2624 On Tue, Nov 26, 2019 at 3:11 AM Julian Hyde <[email protected]> wrote: > 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 <[email protected]> > 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<[email protected]> > > 日 期：2019年11月26日 07:28:40 > > 收件人：<[email protected]> > > 主 题：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 > > > >
