You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2017/01/27 01:18:46 UTC
[02/26] phoenix git commit: PHOENIX-3516 Performance Issues with
queries that have compound filters and specify
phoenix.query.force.rowkeyorder=true
PHOENIX-3516 Performance Issues with queries that have compound filters and specify phoenix.query.force.rowkeyorder=true
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/c5046047
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/c5046047
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/c5046047
Branch: refs/heads/calcite
Commit: c5046047a78e0365d75bc696dff4870304c2b5b2
Parents: bd2acd5
Author: Thomas D'Silva <td...@salesforce.com>
Authored: Tue Dec 20 17:56:37 2016 -0800
Committer: Thomas D'Silva <td...@salesforce.com>
Committed: Thu Dec 22 10:54:24 2016 -0800
----------------------------------------------------------------------
.../end2end/TenantSpecificViewIndexIT.java | 47 ++++++++++++++++++++
.../apache/phoenix/compile/WhereCompiler.java | 3 +-
.../org/apache/phoenix/util/ExpressionUtil.java | 10 +++++
.../phoenix/query/KeyRangeIntersectTest.java | 9 +++-
4 files changed, 67 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c5046047/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
index b7b8902..6ae1445 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
@@ -19,6 +19,7 @@ package org.apache.phoenix.end2end;
import static org.apache.phoenix.util.MetaDataUtil.getViewIndexSequenceName;
import static org.apache.phoenix.util.MetaDataUtil.getViewIndexSequenceSchemaName;
+import static org.apache.phoenix.util.PhoenixRuntime.TENANT_ID_ATTRIB;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
@@ -28,6 +29,7 @@ import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.SQLException;
import java.util.Properties;
import org.apache.hadoop.hbase.util.Bytes;
@@ -284,4 +286,49 @@ public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
assertEquals("value1", rs.getString(1));
assertFalse("No other rows should have been returned for the tenant", rs.next()); // should have just returned one record since for org1 we have only one row.
}
+
+ @Test
+ public void testOverlappingDatesFilter() throws SQLException {
+ String tenantUrl = getUrl() + ';' + TENANT_ID_ATTRIB + "=tenant1" + ";" + QueryServices.FORCE_ROW_KEY_ORDER_ATTRIB + "=true";
+ String tableName = generateUniqueName();
+ String viewName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName
+ + "(ORGANIZATION_ID CHAR(15) NOT NULL, "
+ + "PARENT_TYPE CHAR(3) NOT NULL, "
+ + "PARENT_ID CHAR(15) NOT NULL,"
+ + "CREATED_DATE DATE NOT NULL "
+ + "CONSTRAINT PK PRIMARY KEY (ORGANIZATION_ID, PARENT_TYPE, PARENT_ID, CREATED_DATE DESC)"
+ + ") VERSIONS=1,MULTI_TENANT=true,REPLICATION_SCOPE=1";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Connection viewConn = DriverManager.getConnection(tenantUrl) ) {
+ // create table
+ conn.createStatement().execute(ddl);
+ // create index
+ conn.createStatement().execute("CREATE INDEX IF NOT EXISTS IDX ON " + tableName + "(PARENT_TYPE, CREATED_DATE, PARENT_ID)");
+ // create view
+ viewConn.createStatement().execute("CREATE VIEW IF NOT EXISTS " + viewName + " AS SELECT * FROM "+ tableName );
+
+ String query ="EXPLAIN SELECT PARENT_ID FROM " + viewName
+ + " WHERE PARENT_TYPE='001' "
+ + "AND (CREATED_DATE > to_date('2011-01-01') AND CREATED_DATE < to_date('2016-10-31'))"
+ + "ORDER BY PARENT_TYPE,CREATED_DATE LIMIT 501";
+
+ ResultSet rs = viewConn.createStatement().executeQuery(query);
+ String expectedPlanFormat = "CLIENT SERIAL 1-WAY RANGE SCAN OVER IDX ['tenant1 ','001','%s 00:00:00.001'] - ['tenant1 ','001','%s 00:00:00.000']" + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY" + "\n" +
+ " SERVER 501 ROW LIMIT" + "\n" +
+ "CLIENT 501 ROW LIMIT";
+ assertEquals(String.format(expectedPlanFormat, "2011-01-01", "2016-10-31"), QueryUtil.getExplainPlan(rs));
+
+ query ="EXPLAIN SELECT PARENT_ID FROM " + viewName
+ + " WHERE PARENT_TYPE='001' "
+ + " AND (CREATED_DATE >= to_date('2011-01-01') AND CREATED_DATE <= to_date('2016-01-01'))"
+ + " AND (CREATED_DATE > to_date('2012-10-21') AND CREATED_DATE < to_date('2016-10-31')) "
+ + "ORDER BY PARENT_TYPE,CREATED_DATE LIMIT 501";
+
+ rs = viewConn.createStatement().executeQuery(query);
+ assertEquals(String.format(expectedPlanFormat, "2012-10-21", "2016-01-01"), QueryUtil.getExplainPlan(rs));
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c5046047/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
index 13963d7..39451b8 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
@@ -56,6 +56,7 @@ import org.apache.phoenix.schema.TableRef;
import org.apache.phoenix.schema.TypeMismatchException;
import org.apache.phoenix.schema.types.PBoolean;
import org.apache.phoenix.util.ByteUtil;
+import org.apache.phoenix.util.ExpressionUtil;
import org.apache.phoenix.util.ScanUtil;
import org.apache.phoenix.util.SchemaUtil;
@@ -237,7 +238,7 @@ public class WhereCompiler {
if (LiteralExpression.isBooleanFalseOrNull(whereClause)) {
context.setScanRanges(ScanRanges.NOTHING);
- } else if (whereClause != null && !LiteralExpression.isTrue(whereClause) && !hashJoinOptimization) {
+ } else if (whereClause != null && !ExpressionUtil.evaluatesToTrue(whereClause) && !hashJoinOptimization) {
Filter filter = null;
final Counter counter = new Counter();
whereClause.accept(new KeyValueExpressionVisitor() {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c5046047/phoenix-core/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/ExpressionUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
index 65cf075..1fbb534 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/ExpressionUtil.java
@@ -15,6 +15,7 @@ import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
import org.apache.phoenix.expression.Determinism;
import org.apache.phoenix.expression.Expression;
import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.types.PBoolean;
import org.apache.phoenix.schema.types.PDataType;
public class ExpressionUtil {
@@ -43,5 +44,14 @@ public class ExpressionUtil {
public static LiteralExpression getNullExpression(Expression expression) throws SQLException {
return LiteralExpression.newConstant(null, expression.getDataType(), expression.getDeterminism());
}
+
+ public static boolean evaluatesToTrue(Expression expression) {
+ if (isConstant(expression)) {
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ expression.evaluate(null, ptr);
+ return Boolean.TRUE.equals(PBoolean.INSTANCE.toObject(ptr));
+ }
+ return false;
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c5046047/phoenix-core/src/test/java/org/apache/phoenix/query/KeyRangeIntersectTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/KeyRangeIntersectTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/KeyRangeIntersectTest.java
index 98c10a8..9e21b69 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/query/KeyRangeIntersectTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/query/KeyRangeIntersectTest.java
@@ -18,9 +18,9 @@
package org.apache.phoenix.query;
+import static org.apache.hadoop.hbase.util.Bytes.toBytes;
import static org.apache.phoenix.query.KeyRange.EMPTY_RANGE;
import static org.apache.phoenix.query.KeyRange.EVERYTHING_RANGE;
-import static org.apache.hadoop.hbase.util.Bytes.toBytes;
import java.util.Arrays;
import java.util.Collection;
@@ -28,6 +28,8 @@ import java.util.Collection;
import junit.framework.TestCase;
import org.apache.phoenix.schema.types.PChar;
+import org.apache.phoenix.schema.types.PDate;
+import org.apache.phoenix.util.DateUtil;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
@@ -86,6 +88,11 @@ public class KeyRangeIntersectTest extends TestCase {
PChar.INSTANCE.getKeyRange(toBytes("E"), false, toBytes("F"), true),
EMPTY_RANGE
},
+ {
+ PDate.INSTANCE.getKeyRange(PDate.INSTANCE.toBytes(DateUtil.parseDate("2011-01-01")), true, PDate.INSTANCE.toBytes(DateUtil.parseDate("2016-01-01")), true),
+ PDate.INSTANCE.getKeyRange(PDate.INSTANCE.toBytes(DateUtil.parseDate("2012-10-21")), false, PDate.INSTANCE.toBytes(DateUtil.parseDate("2016-10-31")), false),
+ PDate.INSTANCE.getKeyRange(PDate.INSTANCE.toBytes(DateUtil.parseDate("2012-10-21")), false, PDate.INSTANCE.toBytes(DateUtil.parseDate("2016-01-01")), true)
+ }
});
}
@Test