This is an automated email from the ASF dual-hosted git repository. larsh pushed a commit to branch 4.x in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.x by this push: new fd22478 PHOENIX-6000 Client side DELETEs should use local indexes for filtering. fd22478 is described below commit fd22478d522ce5dcfc43c67696018a360e043d6c Author: Lars <la...@apache.org> AuthorDate: Thu Jul 16 10:27:18 2020 -0700 PHOENIX-6000 Client side DELETEs should use local indexes for filtering. --- .../end2end/index/GlobalIndexOptimizationIT.java | 55 ++++++++++++++++++++-- .../org/apache/phoenix/compile/DeleteCompiler.java | 22 +-------- 2 files changed, 53 insertions(+), 24 deletions(-) diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java index 5c2558e..0d0556b 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java @@ -50,14 +50,61 @@ public class GlobalIndexOptimizationIT extends ParallelStatsDisabledIT { conn.close(); } - private void createIndex(String indexName, String tableName, String columns) throws SQLException { + private void createIndex(String indexName, String tableName, String columns, String includes, boolean local) throws SQLException { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE INDEX " + indexName + " ON " + tableName + " (" + columns + ")"; + String ddl = "CREATE " + (local ? "LOCAL " : "") + "INDEX " + indexName + " ON " + tableName + " (" + columns + ")" + (includes != null ? " INCLUDE (" + includes + ")" : ""); conn.createStatement().execute(ddl); conn.close(); } @Test + public void testIndexDeleteOptimizationWithLocalIndex() throws Exception { + String dataTableName = generateUniqueName(); + String indexTableName = generateUniqueName(); + createBaseTable(dataTableName, null, null, false); + // create a local index that only covers k3 + createIndex(indexTableName+"L", dataTableName, "k3", null, true); + // create a gloval index covering v1, and k3 + createIndex(indexTableName+"G", dataTableName, "v1", "k3", false); + + String query = "DELETE FROM " + dataTableName + " where k3 < 100"; + try (Connection conn1 = DriverManager.getConnection(getUrl())) { + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(TO_CHAR(rand()*100),rand()*10000,rand()*10000,rand()*10000,TO_CHAR(rand()*100))"); + for (int i=0; i<16; i++) { + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " SELECT TO_CHAR(rand()*100),rand()*10000,rand()*10000,rand()*10000,TO_CHAR(rand()*100) FROM " + dataTableName); + } + ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); + String expected = + "DELETE ROWS CLIENT SELECT\n" + + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName +" [1,*] - [1,100]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT"; + String actual = QueryUtil.getExplainPlan(rs); + assertEquals(expected, actual); + rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + dataTableName); + rs.next(); + int count = rs.getInt(1); + int deleted = conn1.createStatement().executeUpdate(query); + int expectedCount = count - deleted; + + rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + dataTableName); + rs.next(); + count = rs.getInt(1); + assertEquals(expectedCount, count); + + rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName+"L"); + rs.next(); + count = rs.getInt(1); + assertEquals(expectedCount, count); + + rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName+"G"); + rs.next(); + count = rs.getInt(1); + assertEquals(expectedCount, count); + } + } + + @Test public void testGlobalIndexOptimization() throws Exception { String dataTableName = generateUniqueName(); String indexTableName = generateUniqueName(); @@ -98,7 +145,7 @@ public class GlobalIndexOptimizationIT extends ParallelStatsDisabledIT { conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('j',2,4,2,'a')"); conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('q',3,1,1,'c')"); conn1.commit(); - createIndex(indexTableName, dataTableName, "v1"); + createIndex(indexTableName, dataTableName, "v1", null, false); String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ * FROM " + dataTableName +" where v1='a'"; ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); @@ -284,7 +331,7 @@ public class GlobalIndexOptimizationIT extends ParallelStatsDisabledIT { conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(2,4,2,'a')"); conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(3,1,1,'c')"); conn1.commit(); - createIndex(indexTableName, dataTableName, "v1"); + createIndex(indexTableName, dataTableName, "v1", null, false); String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ k1,k2,k3,v1 FROM " + dataTableName +" where v1='a'"; ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java index a3ab50f..4341679 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java @@ -522,7 +522,7 @@ public class DeleteCompiler { // that is being upserted for conflict detection purposes. // If we have immutable indexes, we'd increase the number of bytes scanned by executing // separate queries against each index, so better to drive from a single table in that case. - boolean runOnServer = isAutoCommit && !hasPreOrPostProcessing && !table.isTransactional() && !hasClientSideIndexes; + boolean runOnServer = isAutoCommit && !hasPreOrPostProcessing && !table.isTransactional() && !hasClientSideIndexes && allowServerMutations; HintNode hint = delete.getHint(); if (runOnServer && !delete.getHint().hasHint(Hint.USE_INDEX_OVER_DATA_TABLE)) { select = SelectStatement.create(select, HintNode.create(hint, Hint.USE_DATA_OVER_INDEX_TABLE)); @@ -536,26 +536,8 @@ public class DeleteCompiler { queryPlans = Lists.newArrayList(!clientSideIndexes.isEmpty() ? optimizer.getApplicablePlans(dataPlan, statement, select, resolverToBe, Collections.<PColumn>emptyList(), parallelIteratorFactoryToBe) : optimizer.getBestPlan(dataPlan, statement, select, resolverToBe, Collections.<PColumn>emptyList(), parallelIteratorFactoryToBe)); - // Filter out any local indexes that don't contain all indexed columns. - // We have to do this manually because local indexes are still used - // when referenced columns aren't in the index, so they won't be - // filtered by the optimizer. - queryPlans = new ArrayList<>(queryPlans); - Iterator<QueryPlan> iterator = queryPlans.iterator(); - while (iterator.hasNext()) { - QueryPlan plan = iterator.next(); - if (plan.getTableRef().getTable().getIndexType() == IndexType.LOCAL) { - if (!plan.getContext().getDataColumns().isEmpty()) { - iterator.remove(); - } - } - } - if (queryPlans.isEmpty()) { - queryPlans = Collections.singletonList(dataPlan); - } - + runOnServer &= queryPlans.get(0).getTableRef().getTable().getType() != PTableType.INDEX; - runOnServer &= allowServerMutations; // We need to have all indexed columns available in all immutable indexes in order // to generate the delete markers from the query. We also cannot have any filters