phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [1/2] PHOENIX-850 Optimize ORDER BY and GROUP BY for tenant specific indexes (JamesTaylor)
Date Sun, 16 Mar 2014 06:22:07 GMT
Repository: incubator-phoenix
Updated Branches:
  refs/heads/4.0 50f8fb206 -> 816e1358f


http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/816e1358/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java
new file mode 100644
index 0000000..df9bf06
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java
@@ -0,0 +1,84 @@
+package org.apache.phoenix.compile;
+
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.apache.phoenix.query.BaseConnectionlessQueryTest;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Test;
+
+public class TenantSpecificViewIndexCompileTest extends BaseConnectionlessQueryTest {
+
+    @Test
+    public void testOrderByOptimizedOut() throws Exception {
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.createStatement().execute("CREATE TABLE t(t_id VARCHAR NOT NULL, k1 VARCHAR,
k2 VARCHAR, v1 VARCHAR," +
+        		" CONSTRAINT pk PRIMARY KEY(t_id, k1, k2)) multi_tenant=true");
+        
+        String tenantId = "me";
+        props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); // connection is tenant-specific
+        conn = DriverManager.getConnection(getUrl(), props);
+        conn.createStatement().execute("CREATE VIEW v(v2 VARCHAR) AS SELECT * FROM t WHERE
k1 = 'a'");
+        conn.createStatement().execute("CREATE INDEX i1 ON v(v2) INCLUDE(v1)");
+        
+        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN SELECT v1,v2 FROM v WHERE
v2 > 'a' ORDER BY v2");
+        assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['me',-32768,'a'] - ['me',-32768,*]",
+                QueryUtil.getExplainPlan(rs));
+    }
+
+    @Test
+    public void testViewConstantsOptimizedOut() throws Exception {
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.createStatement().execute("CREATE TABLE t(t_id VARCHAR NOT NULL, k1 VARCHAR,
k2 VARCHAR, v1 VARCHAR," +
+                " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2)) multi_tenant=true");
+        
+        String tenantId = "me";
+        props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); // connection is tenant-specific
+        conn = DriverManager.getConnection(getUrl(), props);
+        conn.createStatement().execute("CREATE VIEW v(v2 VARCHAR) AS SELECT * FROM t WHERE
k2 = 'a'");
+        conn.createStatement().execute("CREATE INDEX i1 ON v(v2)");
+        
+        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN SELECT v2 FROM v WHERE
v2 > 'a' and k2 = 'a' ORDER BY v2,k2");
+        assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['me',-32768,'a'] - ['me',-32768,*]",
+                QueryUtil.getExplainPlan(rs));
+        
+        // Won't use index b/c v1 is not in index, but should optimize out k2 still from
the order by
+        // K2 will still be referenced in the filter, as these are automatically tacked on
to the where clause.
+        rs = conn.createStatement().executeQuery("EXPLAIN SELECT v1 FROM v WHERE v2 >
'a' ORDER BY k2");
+        assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['me']\n" + 
+                "    SERVER FILTER BY (V2 > 'a' AND K2 = 'a')",
+                QueryUtil.getExplainPlan(rs));
+
+        // If we match K2 against a constant not equal to it's view constant, we should get
a degenerate plan
+        rs = conn.createStatement().executeQuery("EXPLAIN SELECT v1 FROM v WHERE v2 >
'a' and k2='b' ORDER BY k2");
+        assertEquals("DEGENERATE SCAN OVER V",
+                QueryUtil.getExplainPlan(rs));
+    }
+
+    @Test
+    public void testViewConstantsOptimizedOutOnReadOnlyView() throws Exception {
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.createStatement().execute("CREATE TABLE t(t_id VARCHAR NOT NULL, k1 VARCHAR,
k2 VARCHAR, v1 VARCHAR," +
+                " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2)) multi_tenant=true");
+        
+        String tenantId = "me";
+        props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); // connection is tenant-specific
+        conn = DriverManager.getConnection(getUrl(), props);
+        conn.createStatement().execute("CREATE VIEW v(v2 VARCHAR) AS SELECT * FROM t WHERE
k2 = 'a'");
+        conn.createStatement().execute("CREATE VIEW v2(v3 VARCHAR) AS SELECT * FROM v WHERE
k1 > 'a'");
+        conn.createStatement().execute("CREATE INDEX i2 ON v2(v3) include(v2)");
+        
+        // Confirm that a read-only view on an updatable view still optimizes out the read-olnly
parts of the updatable view
+        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN SELECT v2 FROM v2 WHERE
v3 > 'a' and k2 = 'a' ORDER BY v3,k2");
+        assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['me',-32768,'a'] - ['me',-32768,*]",
+                QueryUtil.getExplainPlan(rs));
+    }
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/816e1358/phoenix-core/src/test/java/org/apache/phoenix/expression/ColumnExpressionTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/expression/ColumnExpressionTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/expression/ColumnExpressionTest.java
index 8e20a28..d63fc0e 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/expression/ColumnExpressionTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/expression/ColumnExpressionTest.java
@@ -39,7 +39,7 @@ public class ColumnExpressionTest {
         int maxLen = 30;
         int scale = 5;
         PColumn column = new PColumnImpl(PNameFactory.newName("c1"), PNameFactory.newName("f1"),
PDataType.DECIMAL, maxLen, scale,
-                true, 20, SortOrder.getDefault(), 0, null);
+                true, 20, SortOrder.getDefault(), 0, null, false);
         ColumnExpression colExp = new KeyValueColumnExpression(column);
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         DataOutputStream dOut = new DataOutputStream(baos);
@@ -59,7 +59,7 @@ public class ColumnExpressionTest {
     public void testSerializationWithNullScale() throws Exception {
         int maxLen = 30;
         PColumn column = new PColumnImpl(PNameFactory.newName("c1"), PNameFactory.newName("f1"),
PDataType.BINARY, maxLen, null,
-                true, 20, SortOrder.getDefault(), 0, null);
+                true, 20, SortOrder.getDefault(), 0, null, false);
         ColumnExpression colExp = new KeyValueColumnExpression(column);
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         DataOutputStream dOut = new DataOutputStream(baos);
@@ -79,7 +79,7 @@ public class ColumnExpressionTest {
     public void testSerializationWithNullMaxLength() throws Exception {
         int scale = 5;
         PColumn column = new PColumnImpl(PNameFactory.newName("c1"), PNameFactory.newName("f1"),
PDataType.VARCHAR, null, scale,
-                true, 20, SortOrder.getDefault(), 0, null);
+                true, 20, SortOrder.getDefault(), 0, null, false);
         ColumnExpression colExp = new KeyValueColumnExpression(column);
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         DataOutputStream dOut = new DataOutputStream(baos);
@@ -98,7 +98,7 @@ public class ColumnExpressionTest {
     @Test
     public void testSerializationWithNullScaleAndMaxLength() throws Exception {
         PColumn column = new PColumnImpl(PNameFactory.newName("c1"), PNameFactory.newName("f1"),
PDataType.DECIMAL, null, null, true,
-                20, SortOrder.getDefault(), 0, null);
+                20, SortOrder.getDefault(), 0, null, false);
         ColumnExpression colExp = new KeyValueColumnExpression(column);
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         DataOutputStream dOut = new DataOutputStream(baos);

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/816e1358/phoenix-core/src/test/java/org/apache/phoenix/iterate/AggregateResultScannerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/iterate/AggregateResultScannerTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/iterate/AggregateResultScannerTest.java
index fb032ae..39c092b 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/iterate/AggregateResultScannerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/iterate/AggregateResultScannerTest.java
@@ -115,6 +115,11 @@ public class AggregateResultScannerTest extends BaseConnectionlessQueryTest
{
             public byte[] getViewConstant() {
                 return null;
             }
+            
+            @Override
+            public boolean isViewReferenced() {
+                return false;
+            }
         })), null);
         aggregationManager.setAggregators(new ClientAggregators(Collections.<SingleAggregateFunction>singletonList(func),
1));
         ResultIterators iterators = new ResultIterators() {

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/816e1358/phoenix-protocol/src/main/PTable.proto
----------------------------------------------------------------------
diff --git a/phoenix-protocol/src/main/PTable.proto b/phoenix-protocol/src/main/PTable.proto
index d5c3322..96bb52e 100644
--- a/phoenix-protocol/src/main/PTable.proto
+++ b/phoenix-protocol/src/main/PTable.proto
@@ -43,6 +43,7 @@ message PColumn {
   required int32 sortOrder = 8;
   optional int32 arraySize = 9;
   optional bytes viewConstant = 10;
+  optional bool viewReferenced = 11;
 }
 
 message PTableStats {


Mime
View raw message