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/e8fafd33 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/e8fafd33 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/e8fafd33 Branch: refs/heads/4.x-cdh5.15 Commit: e8fafd3319f2f6e8c6f5326938ede7150e2c040c Parents: eb13ffd Author: Lars Hofhansl <la...@apache.org> Authored: Fri Oct 12 06:50:31 2018 +0100 Committer: Pedro Boado <pbo...@apache.org> Committed: Wed Oct 17 22:50:43 2018 +0100 ---------------------------------------------------------------------- .../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/e8fafd33/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 e260969..5a59c81 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/e8fafd33/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();
