You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by la...@apache.org on 2016/06/11 02:32:00 UTC
phoenix git commit: PHOENIX-2986 Some queries are misoptimized by
PHOENIX-258.
Repository: phoenix
Updated Branches:
refs/heads/4.x-HBase-0.98 3a8d272c8 -> 7cc8a98e2
PHOENIX-2986 Some queries are misoptimized by PHOENIX-258.
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/7cc8a98e
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/7cc8a98e
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/7cc8a98e
Branch: refs/heads/4.x-HBase-0.98
Commit: 7cc8a98e277c4250c15a29df90fff527fbe22a0f
Parents: 3a8d272
Author: Lars Hofhansl <la...@apache.org>
Authored: Fri Jun 10 19:33:19 2016 -0700
Committer: Lars Hofhansl <la...@apache.org>
Committed: Fri Jun 10 19:33:19 2016 -0700
----------------------------------------------------------------------
.../phoenix/end2end/DistinctPrefixFilterIT.java | 167 +++++++++----------
.../phoenix/iterate/BaseResultIterators.java | 3 +-
2 files changed, 77 insertions(+), 93 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/7cc8a98e/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java
index c2776ed..4050314 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java
@@ -19,9 +19,10 @@ import org.junit.BeforeClass;
import org.junit.Test;
public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT {
- private static String testTableF = generateRandomString();
- private static String testTableV = generateRandomString();
- private static String testSeq = testTableF + "_seq";
+ private static final String testTableF = generateRandomString();
+ private static final String testTableV = generateRandomString();
+ private static final String testSeq = testTableF + "_seq";
+ private static final String PREFIX = "SERVER DISTINCT PREFIX";
private static Connection conn;
@BeforeClass
@@ -118,104 +119,55 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT {
@Test
public void testPlans() throws Exception {
- final String PREFIX = "SERVER DISTINCT PREFIX";
-
// use the filter even when the SkipScan filter is used
- String dataSql = "SELECT DISTINCT prefix1, prefix2 FROM "+testTableF+ " WHERE prefix1 IN (1,2)";
- ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(PREFIX));
-
- dataSql = "SELECT prefix1, 1, 2 FROM "+testTableF+" GROUP BY prefix1 HAVING prefix1 = 1";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(PREFIX));
-
- dataSql = "SELECT prefix1 FROM "+testTableF+" GROUP BY prefix1, TRUNC(prefix1), TRUNC(prefix2)";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(PREFIX));
-
- dataSql = "SELECT DISTINCT prefix1, prefix2 FROM "+testTableV+ " WHERE prefix1 IN ('1','2')";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(PREFIX));
-
- dataSql = "SELECT prefix1, 1, 2 FROM "+testTableV+" GROUP BY prefix1 HAVING prefix1 = '1'";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(PREFIX));
+ testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTableF+ " WHERE prefix1 IN (1,2)", true);
+ testPlan("SELECT prefix1, 1, 2 FROM "+testTableF+" GROUP BY prefix1 HAVING prefix1 = 1", true);
+ testPlan("SELECT prefix1 FROM "+testTableF+" GROUP BY prefix1, TRUNC(prefix1), TRUNC(prefix2)", true);
+ testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTableV+ " WHERE prefix1 IN ('1','2')", true);
+ testPlan("SELECT prefix1, 1, 2 FROM "+testTableV+" GROUP BY prefix1 HAVING prefix1 = '1'", true);
+ // make sure we do not mis-optimize this case
+ testPlan("SELECT DISTINCT SUM(prefix1) FROM "+testTableF+" GROUP BY prefix1", false);
testCommonPlans(testTableF, PREFIX);
testCommonPlans(testTableV, PREFIX);
}
private void testCommonPlans(String testTable, String contains) throws Exception {
-
- String dataSql = "SELECT DISTINCT prefix1 FROM "+testTable;
- ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT /*+ RANGE_SCAN */ DISTINCT prefix1 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT DISTINCT prefix1, prefix2 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
+ testPlan("SELECT DISTINCT prefix1 FROM "+testTable, true);
+
+ // COUNT(DISTINCT) is not yet optimized
+ testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable, false);
+ testPlan("SELECT COUNT(DISTINCT prefix1), COUNT(DISTINCT prefix2) FROM "+testTable, false);
+ testPlan("SELECT COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1,prefix2)) FROM "+testTable, false);
+ // a plain aggregate, cannot optimize
+ testPlan("SELECT COUNT(prefix1), COUNT(DISTINCT prefix1) FROM "+testTable, false);
+ testPlan("SELECT COUNT(*) FROM (SELECT DISTINCT(prefix1) FROM "+testTable+")", true);
+ testPlan("SELECT /*+ RANGE_SCAN */ DISTINCT prefix1 FROM "+testTable, false);
+ testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTable, true);
// use the filter even when the boolean expression filter is used
- dataSql = "SELECT DISTINCT prefix1, prefix2 FROM "+testTable+ " WHERE col1 > 0.5";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
+ testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTable+ " WHERE col1 > 0.5", true);
// do not use the filter when the distinct is on the entire key
- dataSql = "SELECT DISTINCT prefix1, prefix2, prefix3 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT DISTINCT (prefix1, prefix2, prefix3) FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT DISTINCT prefix1, prefix2, col1, prefix3 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT DISTINCT prefix1, prefix2, col1 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT DISTINCT col1, prefix1, prefix2 FROM "+testTable;
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1 FROM "+testTable+" GROUP BY prefix1";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1, count(*) FROM "+testTable+" GROUP BY prefix1";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2, prefix3";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT (prefix1, prefix2, prefix3) FROM "+testTable+" GROUP BY (prefix1, prefix2, prefix3)";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1, 1, 2 FROM "+testTable+" GROUP BY prefix1";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
-
- dataSql = "SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, col1";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertFalse(QueryUtil.getExplainPlan(rs).contains(contains));
+ testPlan("SELECT DISTINCT prefix1, prefix2, prefix3 FROM "+testTable, false);
+ testPlan("SELECT DISTINCT (prefix1, prefix2, prefix3) FROM "+testTable, false);
+ testPlan("SELECT DISTINCT prefix1, prefix2, col1, prefix3 FROM "+testTable, false);
+ testPlan("SELECT DISTINCT prefix1, prefix2, col1 FROM "+testTable, false);
+ testPlan("SELECT DISTINCT col1, prefix1, prefix2 FROM "+testTable, false);;
+ testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1", true);
+ testPlan("SELECT COUNT(prefix1) FROM (SELECT prefix1 FROM "+testTable+" GROUP BY prefix1)", true);
+ // aggregate over the group by, cannot optimize
+ testPlan("SELECT prefix1, count(*) FROM "+testTable+" GROUP BY prefix1", false);
+ testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2", true);
+ // again using full key
+ testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2, prefix3", false);
+ testPlan("SELECT (prefix1, prefix2, prefix3) FROM "+testTable+" GROUP BY (prefix1, prefix2, prefix3)", false);
+ testPlan("SELECT prefix1, 1, 2 FROM "+testTable+" GROUP BY prefix1", true);
+ testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, col1", false);
+ testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTable+" WHERE col1 > 0.5", true);
+ }
- dataSql = "SELECT DISTINCT prefix1, prefix2 FROM "+testTable+" WHERE col1 > 0.5";
- rs = conn.createStatement().executeQuery("EXPLAIN "+dataSql);
- assertTrue(QueryUtil.getExplainPlan(rs).contains(contains));
+ private void testPlan(String query, boolean optimizable) throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+query);
+ assertEquals(QueryUtil.getExplainPlan(rs).contains(PREFIX), optimizable);
}
@Test
@@ -260,6 +212,9 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT {
// mix distinct and boolean expression filters
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE col1 > 0.99 AND prefix1 IN (1,2)", -1);
+ testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTableF + " WHERE prefix2=2", 3, 3);
+ testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTableF + " WHERE prefix1=2", 1, 3);
+
// mix distinct prefix and SkipScan filters
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('1','2')", 6);
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('3','22')", 5);
@@ -282,6 +237,14 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT {
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " ORDER BY prefix1, prefix2 DESC", 11);
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99", -1);
testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99 ORDER BY prefix1, prefix2 DESC", -1);
+
+ testCount("SELECT %s COUNT(DISTINCT prefix1) FROM " + testTable, 4);
+ testCount("SELECT COUNT(*) FROM (SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + ")", 11);
+ testCount("SELECT %s COUNT(DISTINCT prefix1) FROM " + testTable + " WHERE col1 > 0.99", -1);
+ testCount("SELECT COUNT(*) FROM (SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99)", -1);
+ testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT prefix2) FROM " + testTable, 4, 4);
+ testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTable, 4, 11);
+ testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTable + " WHERE col1 > 0.99", -1, -1);
}
@Test
@@ -335,6 +298,28 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT {
assertEquals(count, count1);
}
+ private void testCount(String q, int... expected) throws SQLException {
+ String q1 = String.format(q, "");
+ PreparedStatement stmt = conn.prepareStatement(q1);
+ ResultSet res = stmt.executeQuery();
+ int[] count = new int[expected.length];
+ assertTrue(res.next());
+ for (int i=0; i<expected.length; i++) {
+ count[i] = res.getInt(i+1);
+ if (expected[i] > 0) assertEquals(expected[i], count[i]);
+ }
+ assertFalse(res.next());
+
+ q1 = String.format(q, "/*+ RANGE_SCAN */");
+ stmt = conn.prepareStatement(q1);
+ res = stmt.executeQuery();
+ assertTrue(res.next());
+ for (int i=0; i<expected.length; i++) {
+ assertEquals(count[i], res.getInt(i+1));
+ }
+ assertFalse(res.next());
+ }
+
private static void insertPrefixF(int prefix1, int prefix2) throws SQLException {
String query = "UPSERT INTO " + testTableF
+ "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())";
http://git-wip-us.apache.org/repos/asf/phoenix/blob/7cc8a98e/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java b/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
index f9c4a1a..856cd7b 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
@@ -229,8 +229,7 @@ public abstract class BaseResultIterators extends ExplainTable implements Result
if (cols > 0 &&
!plan.getStatement().getHint().hasHint(HintNode.Hint.RANGE_SCAN) &&
cols < plan.getTableRef().getTable().getRowKeySchema().getFieldCount() &&
- plan.getGroupBy().isOrderPreserving() &&
- (plan.getStatement().isDistinct() || context.getAggregationManager().isEmpty()))
+ plan.getGroupBy().isOrderPreserving() && context.getAggregationManager().isEmpty())
{
ScanUtil.andFilterAtEnd(context.getScan(),
new DistinctPrefixFilter(plan.getTableRef().getTable().getRowKeySchema(),