Phoenix project solves this problem by modeling indexes as materialized views that are sorted projections of the base table. Thus if you execute
select deptName from Emp order by deptno and there is an index I_Emp_DeptNo on (deptno, deptName) Calcite will consider two plans - (a) a scan of Emp followed by a sort, (b) a scan of I_Emp_DeptNo - and choose whichever has the cheapest plan. Details in this talk: https://www.slideshare.net/julianhyde/costbased-query-optimization-in-apache-phoenix-using-apache-calcite <https://www.slideshare.net/julianhyde/costbased-query-optimization-in-apache-phoenix-using-apache-calcite> Julian > On Aug 16, 2017, at 10:36 AM, Kaiwang Chen <kaiwang.c...@gmail.com> wrote: > > Hi there, > > HepPlanner chooses the one with smallest Cumulative Cost from > transformations by a rule. LoptOptimizeJoinRule searches for the best > ordering of a MultiJoin. How to consider indices during the search? Is > there any RelNode that is suitable to hold physical access path? > > Looks like TableScan is not a good one. A table scan is usually the worst > access path; any relation operator above table scan adds additional cost. > An index scan can be better, which firstly use the index to locate a range > of rows, then access each row with random IO. > > By the way, in MySQL, there is a greedy search tries almost all kinds of > orderings, and during the search it also choose the best access path of a > table. > > Thanks, > Kaiwang
