Repository: phoenix Updated Branches: refs/heads/4.9-HBase-1.1 cd77473fc -> 8f2b38c68
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/8f2b38c6 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/8f2b38c6 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/8f2b38c6 Branch: refs/heads/4.9-HBase-1.1 Commit: 8f2b38c68b871eb84271e35a5b01613ca9b36956 Parents: cd77473 Author: Thomas D'Silva <tdsi...@salesforce.com> Authored: Tue Dec 20 17:56:37 2016 -0800 Committer: Thomas D'Silva <tdsi...@salesforce.com> Committed: Thu Dec 22 10:50: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/8f2b38c6/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 c0c0bfd..85bdd8b 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; @@ -285,4 +287,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/8f2b38c6/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/8f2b38c6/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/8f2b38c6/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
