You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by pb...@apache.org on 2018/12/03 19:41:52 UTC
[4/9] phoenix git commit: PHOENIX-4964 ORDER BY should use a LOCAL
index even if the query is not fully covered.
PHOENIX-4964 ORDER BY should use a LOCAL index even if the query is not fully covered.
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/0a65646c
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/0a65646c
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/0a65646c
Branch: refs/heads/4.14-cdh5.14
Commit: 0a65646c18abdf70568b7d837d00075a1c371f8c
Parents: 1d407cd
Author: Lars Hofhansl <la...@apache.org>
Authored: Fri Oct 12 06:46:53 2018 +0100
Committer: Pedro Boado <pb...@apache.org>
Committed: Mon Dec 3 08:36:53 2018 +0000
----------------------------------------------------------------------
.../phoenix/end2end/index/LocalIndexIT.java | 59 ++++++++++++++++++++
.../apache/phoenix/optimize/QueryOptimizer.java | 9 ++-
2 files changed, 66 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0a65646c/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index 796d5a2..42cdab3 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -266,6 +266,65 @@ public class LocalIndexIT extends BaseLocalIndexIT {
}
indexTable.close();
}
+
+ @Test
+ public void testLocalIndexUsedForUncoveredOrderBy() throws Exception {
+ String tableName = schemaName + "." + generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ TableName physicalTableName = SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
+ String indexPhysicalTableName = physicalTableName.getNameAsString();
+
+ createBaseTable(tableName, null, "('e','i','o')");
+ try (Connection conn1 = getConnection()) {
+ conn1.createStatement().execute("UPSERT INTO " + tableName + " values('b',1,2,4,'z')");
+ conn1.createStatement().execute("UPSERT INTO " + tableName + " values('f',1,2,3,'a')");
+ conn1.createStatement().execute("UPSERT INTO " + tableName + " values('j',2,4,2,'a')");
+ conn1.createStatement().execute("UPSERT INTO " + tableName + " values('q',3,1,1,'c')");
+ conn1.commit();
+ conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)");
+
+ String query = "SELECT * FROM " + tableName +" ORDER BY V1";
+ ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+
+ HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin();
+ int numRegions = admin.getTableRegions(physicalTableName).size();
+
+ assertEquals(
+ "CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+
+ rs = conn1.createStatement().executeQuery(query);
+ String v = "";
+ while(rs.next()) {
+ String next = rs.getString("v1");
+ assertTrue(v.compareTo(next) <= 0);
+ v = next;
+ }
+ rs.close();
+
+ query = "SELECT * FROM " + tableName +" ORDER BY V1 DESC NULLS LAST";
+ rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+ assertEquals(
+ "CLIENT PARALLEL " + numRegions + "-WAY REVERSE RANGE SCAN OVER "
+ + indexPhysicalTableName + " [1]\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",
+ QueryUtil.getExplainPlan(rs));
+
+ rs = conn1.createStatement().executeQuery(query);
+ v = "zz";
+ while(rs.next()) {
+ String next = rs.getString("v1");
+ assertTrue(v.compareTo(next) >= 0);
+ v = next;
+ }
+ rs.close();
+
+ }
+ }
@Test
public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0a65646c/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index 6d668cc..4595a36 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
@@ -322,13 +322,18 @@ public class QueryOptimizer {
QueryCompiler compiler = new QueryCompiler(statement, indexSelect, resolver, targetColumns, parallelIteratorFactory, dataPlan.getContext().getSequenceManager(), isProjected, true, dataPlans);
QueryPlan plan = compiler.compile();
+
+ boolean optimizedOrderBy = plan.getOrderBy().getOrderByExpressions().isEmpty() &&
+ !dataPlan.getOrderBy().getOrderByExpressions().isEmpty();
+
// If query doesn't have where clause and some of columns to project are missing
// in the index then we need to get missing columns from main table for each row in
// local index. It's like full scan of both local index and data table which is inefficient.
// Then we don't use the index. If all the columns to project are present in the index
- // then we can use the index even the query doesn't have where clause.
+ // then we can use the index even the query doesn't have where clause.
+ // We'll use the index anyway if it allowed us to optimize an ORDER BY clause away.
if (index.getIndexType() == IndexType.LOCAL && indexSelect.getWhere() == null
- && !plan.getContext().getDataColumns().isEmpty()) {
+ && !plan.getContext().getDataColumns().isEmpty() && !optimizedOrderBy) {
return null;
}
indexTableRef = plan.getTableRef();