You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by la...@apache.org on 2020/07/16 17:26:24 UTC
[phoenix] branch master updated: PHOENIX-6000 Client side DELETEs
should use local indexes for filtering.
This is an automated email from the ASF dual-hosted git repository.
larsh pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new 02c9d5d PHOENIX-6000 Client side DELETEs should use local indexes for filtering.
02c9d5d is described below
commit 02c9d5d15be727efaba8ce922857b4ba8d6f129b
Author: Lars <la...@apache.org>
AuthorDate: Thu Jul 16 10:26:31 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 +--------
...eleteCompiler.java => DeleteCompiler.java.orig} | 0
3 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 aa14385..a03b786 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
@@ -523,7 +523,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));
@@ -537,26 +537,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
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.orig
similarity index 100%
copy from phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java
copy to phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java.orig