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 2014/02/02 01:29:38 UTC

git commit: Add where-clause optimization in join queries

Updated Branches:
  refs/heads/master 79c60a27a -> 334b83663


Add where-clause optimization in join queries


Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/334b8366
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/334b8366
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/334b8366

Branch: refs/heads/master
Commit: 334b83663c3149e49b9694c87ece6e0c97633e0e
Parents: 79c60a2
Author: maryannxue <ma...@apache.org>
Authored: Sat Feb 1 19:28:05 2014 -0500
Committer: maryannxue <ma...@apache.org>
Committed: Sat Feb 1 19:28:05 2014 -0500

----------------------------------------------------------------------
 .../apache/phoenix/compile/JoinCompiler.java    | 67 +++++++++++++----
 .../phoenix/compile/JoinQueryCompileTest.java   | 79 +++++++++++++++++++-
 .../apache/phoenix/end2end/HashJoinTest.java    |  2 +-
 3 files changed, 133 insertions(+), 15 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/334b8366/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index 0fc9734..91652bf 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
@@ -106,6 +106,7 @@ public class JoinCompiler {
         private List<ParseNode> preFilters;
         private List<ParseNode> postFilters;
         private List<JoinTable> joinTables;
+        private Map<TableRef, JoinTable> tableRefToJoinTableMap;
         private Map<ColumnRef, ColumnRefType> columnRefs;
         
         private JoinSpec(SelectStatement statement, ColumnResolver resolver) throws SQLException {
@@ -120,36 +121,44 @@ public class JoinCompiler {
             this.joinTables = new ArrayList<JoinTable>(tableNodes.size() - 1);
             this.preFilters = new ArrayList<ParseNode>();
             this.postFilters = new ArrayList<ParseNode>();
+            this.tableRefToJoinTableMap = new HashMap<TableRef, JoinTable>();
             ColumnParseNodeVisitor generalRefVisitor = new ColumnParseNodeVisitor(resolver);
             ColumnParseNodeVisitor joinLocalRefVisitor = new ColumnParseNodeVisitor(resolver);
             ColumnParseNodeVisitor prefilterRefVisitor = new ColumnParseNodeVisitor(resolver);            
-            boolean hasRightJoin = false;
+            int lastRightJoinIndex = -1;
             TableNode tableNode = null;
+            int i = 0;
             while (iter.hasNext()) {
                 tableNode = iter.next();
                 if (!(tableNode instanceof JoinTableNode))
                     throw new SQLFeatureNotSupportedException("Full joins not supported.");
                 JoinTableNode joinTableNode = (JoinTableNode) tableNode;
                 JoinTable joinTable = new JoinTable(joinTableNode, tableRefIter.next(), selectList, resolver);
-                joinTables.add(joinTable);
-                for (ParseNode prefilter : joinTable.preFilters) {
-                    prefilter.accept(prefilterRefVisitor);
-                }
                 for (ParseNode condition : joinTable.conditions) {
                     ComparisonParseNode comparisonNode = (ComparisonParseNode) condition;
                     comparisonNode.getLHS().accept(generalRefVisitor);
                     comparisonNode.getRHS().accept(joinLocalRefVisitor);
                 }
                 if (joinTable.getType() == JoinType.Right) {
-                	hasRightJoin = true;
+                	lastRightJoinIndex = i;
+                }
+                joinTables.add(joinTable);
+                tableRefToJoinTableMap.put(joinTable.getTable(), joinTable);
+                i++;
+            }
+            List<TableRef> prefilterAcceptedTables = new ArrayList<TableRef>();
+            for (i = lastRightJoinIndex == -1 ? 0 : lastRightJoinIndex; i < joinTables.size(); i++) {
+                JoinTable joinTable = joinTables.get(i);
+                if (joinTable.getType() != JoinType.Left) {
+                    prefilterAcceptedTables.add(joinTable.getTable());
                 }
             }
             if (statement.getWhere() != null) {
-            	if (hasRightJoin) {
+            	if (lastRightJoinIndex > -1 && prefilterAcceptedTables.isEmpty()) {
             		// conditions can't be pushed down to the scan filter.
             		postFilters.add(statement.getWhere());
             	} else {
-            		statement.getWhere().accept(new WhereNodeVisitor(resolver));
+            		statement.getWhere().accept(new WhereNodeVisitor(resolver, lastRightJoinIndex > -1, prefilterAcceptedTables));
             		for (ParseNode prefilter : preFilters) {
             		    prefilter.accept(prefilterRefVisitor);
             		}
@@ -158,6 +167,12 @@ public class JoinCompiler {
             		postfilter.accept(generalRefVisitor);
             	}
             }
+            // Delayed to this point, since pre-filters might have been post-fixed by WhereNodeVisitor.
+            for (JoinTable joinTable : joinTables) {
+                for (ParseNode prefilter : joinTable.preFilters) {
+                    prefilter.accept(prefilterRefVisitor);
+                }
+            }
             for (AliasedNode node : selectList) {
                 node.getNode().accept(generalRefVisitor);
             }
@@ -338,9 +353,13 @@ public class JoinCompiler {
         
         private class WhereNodeVisitor  extends TraverseNoParseNodeVisitor<Void> {
             private ColumnResolver resolver;
+            private boolean hasRightJoin;
+            private List<TableRef> prefilterAcceptedTables;
             
-            public WhereNodeVisitor(ColumnResolver resolver) {
+            public WhereNodeVisitor(ColumnResolver resolver, boolean hasRightJoin, List<TableRef> prefilterAcceptedTables) {
                 this.resolver = resolver;
+                this.hasRightJoin = hasRightJoin;
+                this.prefilterAcceptedTables = prefilterAcceptedTables;
             }
             
             private Void leaveBooleanNode(ParseNode node,
@@ -348,11 +367,32 @@ public class JoinCompiler {
                 ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
                 node.accept(visitor);
                 ColumnParseNodeVisitor.ContentType type = visitor.getContentType(mainTable);
-                if (type == ColumnParseNodeVisitor.ContentType.NONE 
-                        || type == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
-                    preFilters.add(node);
-                } else {
+                switch (type) {
+                case NONE:
+                case SELF_ONLY:
+                    if (!hasRightJoin) {
+                        preFilters.add(node);
+                    } else {
+                        postFilters.add(node);
+                    }
+                    break;
+                case FOREIGN_ONLY:
+                    TableRef matched = null;
+                    for (TableRef table : prefilterAcceptedTables) {
+                        if (visitor.getContentType(table) == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+                            matched = table;
+                            break;
+                        }
+                    }
+                    if (matched != null) {
+                        tableRefToJoinTableMap.get(matched).preFilters.add(node);
+                    } else {
+                        postFilters.add(node);
+                    }
+                    break;
+                default:
                     postFilters.add(node);
+                    break;
                 }
                 return null;
             }
@@ -1136,3 +1176,4 @@ public class JoinCompiler {
     	return PNameFactory.newName(name);
     }
 }
+

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/334b8366/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompileTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompileTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompileTest.java
index 156fe5a..b3eaa1c 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompileTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompileTest.java
@@ -23,14 +23,23 @@ import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE;
 import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE;
 import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE;
 import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
 import static org.junit.Assert.assertEquals;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Collections;
+import java.util.List;
 
 import org.junit.Test;
-
+import org.apache.hadoop.hbase.client.Scan;
+import org.apache.phoenix.compile.JoinCompiler.JoinSpec;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.jdbc.PhoenixStatement;
+import org.apache.phoenix.parse.SQLParser;
+import org.apache.phoenix.parse.SelectStatement;
 import org.apache.phoenix.query.BaseConnectionlessQueryTest;
 import org.apache.phoenix.util.QueryUtil;
 
@@ -62,4 +71,72 @@ public class JoinQueryCompileTest extends BaseConnectionlessQueryTest {
         		"    AFTER-JOIN SERVER FILTER BY I.NAME LIKE 'T%'", QueryUtil.getExplainPlan(rs));
     }
 
+    @Test
+    public void testWhereClauseOptimization() throws Exception {
+        PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+        String queryTemplate = "SELECT t1.item_id, t2.item_id, t3.item_id FROM " + JOIN_ITEM_TABLE + " t1 " 
+                + "%s JOIN " + JOIN_ITEM_TABLE + " t2 ON t1.item_id = t2.item_id " 
+                + "%s JOIN " + JOIN_ITEM_TABLE + " t3 ON t1.item_id = t3.item_id " 
+                + "WHERE t1.item_id = '0000000001' AND t2.item_id = '0000000002' AND t3.item_id = '0000000003'";
+
+        String query = String.format(queryTemplate, "INNER", "INNER");
+        JoinSpec joinSpec = getJoinSpec(query, pconn);
+        assertEquals(1, joinSpec.getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "INNER", "LEFT");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(1, joinSpec.getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "INNER", "RIGHT");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(0, joinSpec.getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "LEFT", "INNER");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(1, joinSpec.getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "LEFT", "LEFT");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(1, joinSpec.getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "LEFT", "RIGHT");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(0, joinSpec.getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "RIGHT", "INNER");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(0, joinSpec.getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+
+        query = String.format(queryTemplate, "RIGHT", "RIGHT");
+        joinSpec = getJoinSpec(query, pconn);
+        assertEquals(0, joinSpec.getPreFilters().size());
+        assertEquals(0, joinSpec.getJoinTables().get(0).getPreFilters().size());
+        assertEquals(1, joinSpec.getJoinTables().get(1).getPreFilters().size());
+    }
+    
+    private static JoinSpec getJoinSpec(String query, PhoenixConnection connection) throws SQLException {
+        Scan scan = new Scan();
+        List<Object> binds = Collections.emptyList();
+        SQLParser parser = new SQLParser(query);
+        SelectStatement select = parser.parseQuery();
+        ColumnResolver resolver = FromCompiler.getResolver(select, connection);
+        select = StatementNormalizer.normalize(select, resolver);
+        StatementContext context = new StatementContext(new PhoenixStatement(connection), resolver, binds, scan);
+        return JoinCompiler.getJoinSpec(context, select);        
+    }
 }
+

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/334b8366/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
index 8d0dc76..071b0bd 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
@@ -1286,7 +1286,7 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
     @Test
     public void testJoinPlanWithIndex() throws Exception {
         String query1 = "SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM " + JOIN_ITEM_TABLE + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE + " supp ON substr(item.name, 2, 1) = substr(supp.name, 2, 1) AND (supp.name BETWEEN 'S1' AND 'S5') WHERE item.name BETWEEN 'T1' AND 'T5'";
-        String query2 = "SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM " + JOIN_ITEM_TABLE + " item INNER JOIN " + JOIN_SUPPLIER_TABLE + " supp ON item.supplier_id = supp.supplier_id AND (supp.name = 'S1' OR supp.name = 'S5') WHERE item.name = 'T1' OR item.name = 'T5'";
+        String query2 = "SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM " + JOIN_ITEM_TABLE + " item INNER JOIN " + JOIN_SUPPLIER_TABLE + " supp ON item.supplier_id = supp.supplier_id WHERE (item.name = 'T1' OR item.name = 'T5') AND (supp.name = 'S1' OR supp.name = 'S5')";
         Properties props = new Properties(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
         try {