calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Tzolov <ctzo...@pivotal.io>
Subject Re: Project + Sort on single and on multiple columns
Date Fri, 10 Nov 2017 18:07:30 GMT
Not sure i understand. Lets say that we have rows of size 10MB each. IMO it
is not the same if we perform the sort on the complete row (e.g. 10MB) or
on subset left after the Project. E.g. my intuition is that the Project
will shrink the size and therefore should be performed before the Sort (As
it does for single column sort)
Or is this intuition wrong?


On 10 November 2017 at 18:57, Michael Mior <mmior@uwaterloo.ca> wrote:

> Since the cost of the project doesn't depend on the number of columns being
> projected or the size of the input, putting the project before or after the
> sort will result in the same estimated cost. One approach would be to scale
> the cost of the projection based on the fraction of columns projected.
>
> --
> Michael Mior
> mmior@apache.org
>
> 2017-11-10 12:42 GMT-05:00 Christian Tzolov <ctzolov@pivotal.io>:
>
> > ​I've observed in my
> > no-sql adapter
> > ​ implementation that for q
> > ueries with
> > ​P
> > roject
> > ​ +
> > ​S
> > ort by
> > ​ONE
> >  column
> > ​t​
> > he
> > ​Project
> >
> > ​is pushed (as expected) ​
> > before the Sort but for Sort
> > ​on MULTIPLE
> >  columns
> > ​the Sort is before the Project.
> > For example
> > ​for a query with one
> > sort column:
> >
> > SELECT yearPublished FROM BookMaster ORDER BY yearPublished ASC
> >
> > ​The plan looks like expected (project before the sort)
> >
> >
> > PLAN=GeodeToEnumerableConverterRel
> >   *GeodeSortRel*(sort0=[$0], dir0=[ASC])
> >        GeodeProjectRel(yearPublished=[$2])
> >            GeodeTableScanRel(table=[[TEST, BookMaster]])
> >
> > But
> > ​ for sort​
> > with
> > ​two​
> > ​
> > columns:
> >
> > SELECT yearPublished, itemNumber from BookMaster ORDER BY yearPublished
> > ASC, itemNumber ASC
> >
> > The
> > ​the plan is:
> >
> >
> > PLAN=GeodeToEnumerableConverterRel
> >   GeodeProjectRel(yearPublished=[$2], itemNumber=[$0])
> >     *GeodeSortRel*(sort0=[$2], sort1=[$0], dir0=[ASC], dir1=[ASC])
> >       GeodeTableScanRel(table=[[TEST, BookMaster]])
> >
> > I'm not sure i can explain
> > ​ why in the second case the Sort appears before the Project?
> > Here
> > ​are my cost functions:
> >
> > ​* ​
> > GeodeSortRel
> > ​:
> > https://github.com/tzolov/calcite/blob/geode-1.3/geode/
> > src/main/java/org/apache/calcite/adapter/geode/rel/GeodeSortRel.java#L51
> >
> > * GoedeProjectRel:
> > https://github.com/tzolov/calcite/blob/4a631d9055340f64f5e644454551f9
> > 64ea08f9de/geode/src/main/java/org/apache/calcite/adapter/geode/rel/
> > GeodeProjectRel.java#L52
> > ​
> >
> > ​
> > ​Cheers,
> > Christian​
> >
>



-- 
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Principle Software
Engineer | Pivotal <http://pivotal.io/> | ctzolov@pivotal.io |+31610285517

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message