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 {