Sounds reasonable. Please log a jira. Consider implementing this rule by inspecting predicates or unique keys, rather than by matching a Project or Filter. If you can ascertain that a sort column has only one value (counting NULL as a value for these purposes) then you can remove it from the sort.
I find that using metadata in this way is very effective. You get the desired effect without moving relational operators around. Julian > On Mar 7, 2022, at 1:01 AM, 徐仁和 <[email protected]> wrote: > > Hi community: > > Could calcite support the rule to pull up constant project from sort, just > like `AggregateProjectPullUpConstantsRule`? > > -- sql > select pay_amount, pay_id, user_id > from pay_tbl > where pay_id = 1234 > group by pay_amount, pay_id, user_id > order by pay_amount, pay_id, user_id > > -- origin rel tree > LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], > dir2=[ASC]) > LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1]) > LogicalAggregate(group=[{0, 1}]) > LogicalProject(pay_amount=[$1], user_id=[$3]) > LogicalFilter(condition=[=($0, 1234)]) > LogicalTableScan(table=[[default, pay_tbl]]) > > -- optimized rel tree > LogicalProject(pay_amount=[$0], pay_id=[1234], user_id=[$1]) > LogicalSort(sort0=[$0], sort2=[$1], dir0=[ASC], dir2=[ASC]) > LogicalProject(pay_amount=[$0], user_id=[$1]) > LogicalAggregate(group=[{0, 1}]) > LogicalProject(pay_amount=[$1], user_id=[$3]) > LogicalFilter(condition=[=($0, 1234)]) > LogicalTableScan(table=[[default, pay_tbl]])
