Repository: phoenix Updated Branches: refs/heads/calcite 22162037e -> ca5674f30
PHOENIX-2089 Use index when skip-scan is possible or when ordering key matches Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/ca5674f3 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/ca5674f3 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/ca5674f3 Branch: refs/heads/calcite Commit: ca5674f3008aac353b8f4cd023bb30cf2f7431f1 Parents: 2216203 Author: maryannxue <wei....@intel.com> Authored: Tue Jun 30 12:33:32 2015 -0400 Committer: maryannxue <wei....@intel.com> Committed: Tue Jun 30 12:33:32 2015 -0400 ---------------------------------------------------------------------- .../org/apache/phoenix/calcite/CalciteTest.java | 125 +++++++++++++++++++ .../apache/phoenix/calcite/PhoenixSchema.java | 2 +- .../phoenix/calcite/rel/PhoenixTableScan.java | 38 +++++- 3 files changed, 162 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/ca5674f3/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteTest.java b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteTest.java index d65eb80..7a0324c 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteTest.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/calcite/CalciteTest.java @@ -3,9 +3,13 @@ package org.apache.phoenix.calcite; import com.google.common.collect.Lists; import com.google.common.collect.Maps; +import org.apache.calcite.config.CalciteConnectionProperty; import org.apache.calcite.jdbc.CalciteConnection; import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.util.JsonBuilder; import org.apache.phoenix.end2end.BaseClientManagedTimeIT; +import org.apache.phoenix.schema.TableAlreadyExistsException; +import org.apache.phoenix.util.PropertiesUtil; import org.junit.Before; import org.junit.Test; @@ -15,11 +19,13 @@ import java.io.PrintWriter; import java.sql.*; import java.util.List; import java.util.Map; +import java.util.Properties; import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; import static org.junit.Assert.*; /** @@ -219,6 +225,50 @@ public class CalciteTest extends BaseClientManagedTimeIT { DriverManager.getConnection("jdbc:phoenixcalcite:model=" + file.getAbsolutePath()); return connection; } + + private static Connection connectWithMaterialization(String... materializations) throws Exception { + assert materializations.length % 2 == 0; + final JsonBuilder builder = new JsonBuilder(); + final List<Object> list = builder.list(); + for (int i = 0; i < materializations.length; i++) { + String table = materializations[i++]; + final Map<String, Object> map = builder.map(); + map.put("table", table); + map.put("view", table + "v"); + String sql = materializations[i]; + final String sql2 = sql + .replaceAll("`", "\""); + map.put("sql", sql2); + list.add(map); + } + final String buf = + "materializations: " + builder.toJsonString(list); + final String schema = PHOENIX_SCHEMA.replace("type: ", + buf + ",\n" + + "type: "); + + final File file = File.createTempFile("model", ".json"); + final PrintWriter pw = new PrintWriter(new FileWriter(file)); + pw.print( + "{\n" + + " version: '1.0',\n" + + " defaultSchema: 'phoenix',\n" + + " schemas: [\n" + + schema + "\n" + + " ]\n" + + "}\n"); + pw.close(); + Properties props = new Properties(); + props.setProperty( + CalciteConnectionProperty.MATERIALIZATIONS_ENABLED.camelName(), + Boolean.toString(true)); + props.setProperty( + CalciteConnectionProperty.CREATE_MATERIALIZATIONS.camelName(), + Boolean.toString(false)); + final Connection connection = + DriverManager.getConnection("jdbc:phoenixcalcite:model=" + file.getAbsolutePath(), props); + return connection; + } @Before public void initTable() throws Exception { @@ -235,6 +285,18 @@ public class CalciteTest extends BaseClientManagedTimeIT { connection.close(); } + protected void createIndices(String... indexDDL) throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + for (String ddl : indexDDL) { + try { + conn.createStatement().execute(ddl); + } catch (TableAlreadyExistsException e) { + } + } + conn.close(); + } + @Test public void testTableScan() throws Exception { start().sql("select * from aTable where a_string = 'a'") .explainIs("PhoenixToEnumerableConverter\n" + @@ -809,6 +871,69 @@ public class CalciteTest extends BaseClientManagedTimeIT { .close();; } + @Test public void testIndex() { + try { + createIndices( + "CREATE INDEX IDX1 ON aTable (a_string) INCLUDE (b_string, x_integer)", + "CREATE INDEX IDX2 ON aTable (b_string) INCLUDE (a_string, y_integer)", + "CREATE INDEX IDX_FULL ON aTable (b_string) INCLUDE (a_string, a_integer, a_date, a_time, a_timestamp, x_decimal, x_long, x_integer, y_integer, a_byte, a_short, a_float, a_double, a_unsigned_float, a_unsigned_double)"); + } catch (Exception e) { + throw new RuntimeException(e); + } + final Start start = new Start() { + @Override + Connection createConnection() throws Exception { + return connectWithMaterialization( + "IDX1", "select a_string as \"0:A_STRING\", organization_id as \":ORGANIZATION_ID\", entity_id as \":ENTITY_ID\", b_string as \"0:B_STRING\", x_integer as \"0:X_INTEGER\" from aTable", + "IDX2", "select b_string as \"0:B_STRING\", organization_id as \":ORGANIZATION_ID\", entity_id as \":ENTITY_ID\", a_string as \"0:A_STRING\", y_integer as \"0:Y_INTEGER\" from aTable", + "IDX_FULL", "select b_string as \"0:B_STRING\", organization_id as \":ORGANIZATION_ID\", entity_id as \":ENTITY_ID\", a_string as \"0:A_STRING\", a_integer as \"0:A_INTEGER\", a_date as \"0:A_DATE\", a_time as \"0:A_TIME\", a_timestamp as \"0:A_TIMESTAMP\", x_decimal as \"0:X_DECIMAL\", x_long as \"0:X_LONG\", x_integer as \"0:X_INTEGER\", y_integer as \"0:Y_INTEGER\", a_byte as \"0:A_BYTE\", a_short as \"0:A_SHORT\", a_float as \"0:A_FLOAT\", a_double as \"0:A_DOUBLE\", a_unsigned_float as \"0:A_UNSIGNED_FLOAT\", a_unsigned_double as \"0:A_UNSIGNED_DOUBLE\" from aTable"); + } + }; + start.sql("select x_integer from aTable") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(X_INTEGER=[$4])\n" + + " PhoenixTableScan(table=[[phoenix, IDX1]])\n") + .close(); + start.sql("select a_string from aTable order by a_string") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(A_STRING=[$0])\n" + + " PhoenixTableScan(table=[[phoenix, IDX1]])\n") + .close(); + start.sql("select a_string from aTable order by organization_id") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(A_STRING=[$2])\n" + + " PhoenixTableScan(table=[[phoenix, ATABLE]])\n") + .close(); + start.sql("select a_integer from aTable order by a_string") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixClientProject(A_INTEGER=[$1])\n" + + " PhoenixServerSort(sort0=[$0], dir0=[ASC])\n" + + " PhoenixServerProject(A_STRING=[$2], A_INTEGER=[$4])\n" + + " PhoenixTableScan(table=[[phoenix, ATABLE]])\n") + .close(); + start.sql("select a_string, b_string from aTable where a_string = 'a'") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(0:A_STRING=[$0], 0:B_STRING=[$3])\n" + + " PhoenixTableScan(table=[[phoenix, IDX1]], filter=[=($0, 'a')])\n") + .close(); + start.sql("select a_string, b_string from aTable where b_string = 'b'") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0])\n" + + " PhoenixTableScan(table=[[phoenix, IDX2]], filter=[=($0, 'b')])\n") + .close(); + start.sql("select a_string, b_string, x_integer, y_integer from aTable where b_string = 'b'") + .explainIs("PhoenixToEnumerableConverter\n" + + " PhoenixToClientConverter\n" + + " PhoenixServerProject(A_STRING=[$3], B_STRING=[$0], X_INTEGER=[$10], Y_INTEGER=[$11])\n" + + " PhoenixTableScan(table=[[phoenix, IDX_FULL]], filter=[=($0, 'b')])\n") + .close(); + } + @Test public void testConnectJoinHsqldb() { final Start start = new Start() { @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/ca5674f3/phoenix-core/src/main/java/org/apache/phoenix/calcite/PhoenixSchema.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/calcite/PhoenixSchema.java b/phoenix-core/src/main/java/org/apache/phoenix/calcite/PhoenixSchema.java index 5fb407b..ce4c02b 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/calcite/PhoenixSchema.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/calcite/PhoenixSchema.java @@ -146,7 +146,7 @@ public class PhoenixSchema implements Schema { @Override public boolean isMutable() { - return false; + return true; } @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/ca5674f3/phoenix-core/src/main/java/org/apache/phoenix/calcite/rel/PhoenixTableScan.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/calcite/rel/PhoenixTableScan.java b/phoenix-core/src/main/java/org/apache/phoenix/calcite/rel/PhoenixTableScan.java index d646a99..429b73f 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/calcite/rel/PhoenixTableScan.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/calcite/rel/PhoenixTableScan.java @@ -24,6 +24,7 @@ import org.apache.phoenix.compile.FromCompiler; import org.apache.phoenix.compile.OrderByCompiler.OrderBy; import org.apache.phoenix.compile.QueryPlan; import org.apache.phoenix.compile.RowProjector; +import org.apache.phoenix.compile.ScanRanges; import org.apache.phoenix.compile.SequenceManager; import org.apache.phoenix.compile.StatementContext; import org.apache.phoenix.compile.WhereCompiler; @@ -51,6 +52,8 @@ import com.google.common.collect.Lists; public class PhoenixTableScan extends TableScan implements PhoenixRel { public final RexNode filter; + private final ScanRanges scanRanges; + /** * This will not make a difference in implement(), but rather give a more accurate * estimate of the row count. @@ -77,6 +80,28 @@ public class PhoenixTableScan extends TableScan implements PhoenixRel { super(cluster, traits, table); this.filter = filter; this.statelessFetch = statelessFetch; + + ScanRanges scanRanges = null; + if (filter != null) { + try { + // TODO simplify this code + final PhoenixTable phoenixTable = table.unwrap(PhoenixTable.class); + PTable pTable = phoenixTable.getTable(); + TableRef tableRef = new TableRef(CalciteUtils.createTempAlias(), pTable, HConstants.LATEST_TIMESTAMP, false); + Implementor tmpImplementor = new PhoenixRelImplementorImpl(); + tmpImplementor.setTableRef(tableRef); + SelectStatement select = SelectStatement.SELECT_STAR; + PhoenixStatement stmt = new PhoenixStatement(phoenixTable.pc); + ColumnResolver resolver = FromCompiler.getResolver(tableRef); + StatementContext context = new StatementContext(stmt, resolver, new Scan(), new SequenceManager(stmt)); + Expression filterExpr = CalciteUtils.toExpression(filter, tmpImplementor); + filterExpr = WhereOptimizer.pushKeyExpressionsToScan(context, select, filterExpr); + scanRanges = context.getScanRanges(); + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + this.scanRanges = scanRanges; } @Override @@ -94,9 +119,18 @@ public class PhoenixTableScan extends TableScan implements PhoenixRel { @Override public RelOptCost computeSelfCost(RelOptPlanner planner) { - double rowCount = RelMetadataQuery.getRowCount(this); + double rowCount = super.getRows(); + if (scanRanges != null) { + if (scanRanges.isPointLookup()) { + rowCount = 1; + } else if (scanRanges.getPkColumnSpan() > 0) { + // TODO + rowCount = rowCount * RelMetadataQuery.getSelectivity(this, filter); + } + } + int fieldCount = this.table.getRowType().getFieldCount(); return planner.getCostFactory() - .makeCost(rowCount, rowCount + 1, 0) + .makeCost(rowCount * 2 * fieldCount / (fieldCount + 1), rowCount + 1, 0) .multiplyBy(PHOENIX_FACTOR); }
