phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tdsi...@apache.org
Subject phoenix git commit: PHOENIX-3516 Performance Issues with queries that have compound filters and specify phoenix.query.force.rowkeyorder=true
Date Thu, 22 Dec 2016 19:00:34 GMT
Repository: phoenix
Updated Branches:
  refs/heads/4.9-HBase-1.2 bd227a7b4 -> baf3b7c04


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/baf3b7c0
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/baf3b7c0
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/baf3b7c0

Branch: refs/heads/4.9-HBase-1.2
Commit: baf3b7c04ab48b381b90b0cdba753232f72efe58
Parents: bd227a7
Author: Thomas D'Silva <tdsilva@salesforce.com>
Authored: Tue Dec 20 17:56:37 2016 -0800
Committer: Thomas D'Silva <tdsilva@salesforce.com>
Committed: Thu Dec 22 10:50:10 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/baf3b7c0/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/baf3b7c0/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/baf3b7c0/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/baf3b7c0/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


Mime
View raw message