Michael, Yes, this would work in this case but what about a bit more complicated example with join
SELECT R1.I FROM R1 JOIN R2 on R1.I = R2.II ORDER BY R1.I; Here, the order will still be guaranteed by the R1 index and the sort can be eliminated but there will be a LogicalJoin in between the LogicalSort and the TableScan that in general can not be pushed thought the LogicalSort, right? Mike On Tue, Jun 20, 2017 at 1:14 PM, Michael Mior <[email protected]> wrote: > You may find it easier to just have your rule explicitly match a > LogicalSort on top of a TableScan. Then you can just simply check the > indices of the fields. Existing rules should help reorder other operators > to produce a tree of this form if needed. > > -- > Michael Mior > [email protected] > > 2017-06-19 16:39 GMT-04:00 Michael Alexeev <[email protected]>: > > > Hi All, > > > > I am trying to write a rule to eliminate a potentially redundant > > LogicalSort node. > > > > Consider > > > > SELECT I FROM R1 ORDER BY I; > > > > The initial tree is > > LogicalSort(sort0=[$0], dir0=[DESC]) > > LogicalProject(I=[$0]) > > MyTableScan(table=[[R1]], expr#0..5=[{inputs}], proj#0..5=[{exprs}]) > > > > and after all the transformations are applied it becomes > > > > LogicalSort(sort0=[$0], dir0=[DESC]) > > MyTableScan(table=[[R1]], expr#0..5=[{inputs}], I=[$t0]) > > > > The LogicalProject is merged with the MyTableScan which retains all the > > original project expressions inside a local RexProgram instance > > > > If MyTableScan already produces rows in an order that matches the ORDER > BY > > (for example, an underlying table could have an index that guarantees the > > ordering if the scan uses that index to retrieve the rows) then > LogicalSort > > is not required and could be eliminated. > > My approach is to write a transformation rule to match the LogicalSort > node > > and its onMatch method would use a custom RelShuttleImpl class to visit a > > child TableScan. The shuttle can compare the collation fields list with > the > > list of the project expressions to decide whether the sort order > > is compatible with the index order and the sort can be eliminated. > > > > Does this approach sound reasonable? > > > > The problem is that I can't figure out how to establish a correspondence > > between the sort's collation fields and the scan's columns. Is there a > > proper way? If not, what would be a recommended approach? > > > > > > Any help is greatly appreciated, > > Mike > > >
