[CALCITE-1876] In CSV example, tweak cost to ensure that Project is pushed through Aggregate (Luis Fernando Kauer)
Solved by implementing CsvTableScan.computeSelfCost. Smooth the function by adding 2 to top and bottom of fraction (Julian Hyde). Close apache/calcite#562 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/77a35491 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/77a35491 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/77a35491 Branch: refs/heads/master Commit: 77a35491794724c32ad2fb7b3333744b84f1e18f Parents: 5a0403c Author: Luis Fernando Kauer <[email protected]> Authored: Tue Nov 7 17:29:26 2017 -0200 Committer: Julian Hyde <[email protected]> Committed: Wed Nov 8 08:41:33 2017 -0800 ---------------------------------------------------------------------- .../calcite/adapter/csv/CsvTableScan.java | 16 +++++++++ .../java/org/apache/calcite/test/CsvTest.java | 35 ++++++++++++++++++++ 2 files changed, 51 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/77a35491/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java ---------------------------------------------------------------------- diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java index a667a44..e55655c 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java @@ -25,12 +25,14 @@ import org.apache.calcite.linq4j.tree.Blocks; import org.apache.calcite.linq4j.tree.Expressions; import org.apache.calcite.linq4j.tree.Primitive; import org.apache.calcite.plan.RelOptCluster; +import org.apache.calcite.plan.RelOptCost; import org.apache.calcite.plan.RelOptPlanner; import org.apache.calcite.plan.RelOptTable; import org.apache.calcite.plan.RelTraitSet; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelWriter; import org.apache.calcite.rel.core.TableScan; +import org.apache.calcite.rel.metadata.RelMetadataQuery; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeField; @@ -79,6 +81,20 @@ public class CsvTableScan extends TableScan implements EnumerableRel { planner.addRule(CsvProjectTableScanRule.INSTANCE); } + @Override public RelOptCost computeSelfCost(RelOptPlanner planner, + RelMetadataQuery mq) { + // Multiply the cost by a factor that makes a scan more attractive if it + // has significantly fewer fields than the original scan. + // + // The "+ 2D" on top and bottom keeps the function fairly smooth. + // + // For example, if table has 3 fields, project has 1 field, + // then factor = (1 + 2) / (3 + 2) = 0.6 + return super.computeSelfCost(planner, mq) + .multiplyBy(((double) fields.length + 2D) + / ((double) table.getRowType().getFieldCount() + 2D)); + } + public Result implement(EnumerableRelImplementor implementor, Prefer pref) { PhysType physType = PhysTypeImpl.of( http://git-wip-us.apache.org/repos/asf/calcite/blob/77a35491/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java ---------------------------------------------------------------------- diff --git a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java index 785688b..af9d997 100644 --- a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java +++ b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java @@ -241,6 +241,41 @@ public class CsvTest { .ok(); } + @Test public void testPushDownProjectAggregate() throws SQLException { + final String sql = "explain plan for\n" + + "select gender, count(*) from EMPS group by gender"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{0}], EXPR$1=[COUNT()])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test public void testPushDownProjectAggregateWithFilter() throws SQLException { + final String sql = "explain plan for\n" + + "select max(empno) from EMPS where gender='F'"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=['F'], " + + "expr#3=[=($t1, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[0, 3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test public void testPushDownProjectAggregateNested() throws SQLException { + final String sql = "explain plan for\n" + + "select gender, max(qty)\n" + + "from (\n" + + " select name, gender, count(*) qty\n" + + " from EMPS\n" + + " group by name, gender) t\n" + + "group by gender"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{1}], EXPR$1=[MAX($2)])\n" + + " EnumerableAggregate(group=[{0, 1}], QTY=[COUNT()])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[1, 3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + @Test public void testFilterableSelect() throws SQLException { sql("filterable-model", "select name from EMPS").ok(); }
