This is an automated email from the ASF dual-hosted git repository.
yangzhg pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 663f7dddcc [improvement](planner) eliminating useless sort node
(#14377)
663f7dddcc is described below
commit 663f7dddccd2c2539178b5fad199ccfa47d4f7e5
Author: yinzhijian <373141...@qq.com>
AuthorDate: Tue Nov 22 15:13:25 2022 +0800
[improvement](planner) eliminating useless sort node (#14377)
---
.../java/org/apache/doris/analysis/SelectStmt.java | 76 ++++++++++++
.../java/org/apache/doris/qe/SessionVariable.java | 5 +
.../java/org/apache/doris/planner/PlannerTest.java | 137 +++++++++++++++++++++
3 files changed, 218 insertions(+)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
index ac4b5ce535..edd89f7510 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
@@ -20,6 +20,7 @@
package org.apache.doris.analysis;
+import org.apache.doris.analysis.CompoundPredicate.Operator;
import org.apache.doris.catalog.AggregateFunction;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.DatabaseIf;
@@ -36,6 +37,7 @@ import org.apache.doris.common.ColumnAliasGenerator;
import org.apache.doris.common.ErrorCode;
import org.apache.doris.common.ErrorReport;
import org.apache.doris.common.Pair;
+import org.apache.doris.common.Reference;
import org.apache.doris.common.TableAliasGenerator;
import org.apache.doris.common.TreeNode;
import org.apache.doris.common.UserException;
@@ -559,6 +561,7 @@ public class SelectStmt extends QueryStmt {
}
analyzeAggregation(analyzer);
createAnalyticInfo(analyzer);
+ eliminatingSortNode();
if (evaluateOrderBy) {
createSortTupleInfo(analyzer);
}
@@ -1734,6 +1737,79 @@ public class SelectStmt extends QueryStmt {
return expr;
}
+ public void eliminatingSortNode() {
+ // initial sql: select * from t1 where k1 = 1 order by k1
+ // optimized sql: select * from t1 where k1 = 1
+ if (ConnectContext.get() == null ||
!ConnectContext.get().getSessionVariable().enableEliminateSortNode) {
+ return;
+ }
+ if (!evaluateOrderBy() || getSortInfo() == null || getWhereClause() ==
null) {
+ return;
+ }
+ List<SlotRef> sortSlots = new ArrayList<>();
+ // get source slot ref from order by clause
+ for (Expr expr : getSortInfo().getOrderingExprs()) {
+ SlotRef source = expr.getSrcSlotRef();
+ if (source == null) {
+ return;
+ }
+ sortSlots.add(source);
+ }
+ if (sortSlots.isEmpty()) {
+ return;
+ }
+ if (checkSortNodeEliminable(getWhereClause(), sortSlots) &&
sortSlots.isEmpty()) {
+ evaluateOrderBy = false;
+ }
+ }
+
+ private boolean checkSortNodeEliminable(Expr expr, List<SlotRef>
sortSlotRefs) {
+ // 1. Check that the CompoundPredicates in the whereClause are all
AndCompound
+ if (expr instanceof CompoundPredicate) {
+ if (((CompoundPredicate) expr).getOp() != Operator.AND) {
+ // fail to eliminate
+ return false;
+ }
+ }
+ // 2. Check that all sort slots have:
+ // 2.1 at least one BinaryPredicate expression equal to a constant
+ // 2.2 OR at least one InPredicate expression containing only one
constant
+ // in the whereClause
+ if (expr instanceof BinaryPredicate) {
+ Reference<SlotRef> slotRefRef = new Reference<>();
+ BinaryPredicate binaryPredicate = (BinaryPredicate) expr;
+ if (binaryPredicate.isSingleColumnPredicate(slotRefRef, null)) {
+ if (binaryPredicate.getOp() != BinaryPredicate.Operator.EQ) {
+ // it's ok, try to check next expr
+ return true;
+ }
+ // remove it
+ sortSlotRefs.remove(slotRefRef.getRef());
+ }
+ } else if (expr instanceof InPredicate) {
+ if (((InPredicate) expr).isNotIn()) {
+ return true;
+ }
+ // there can only be two child nodes, one is a slotref and the
other is a constant
+ if (expr.getChildren().size() != 2) {
+ // it's ok, try to check next expr
+ return true;
+ }
+ if (!expr.getChild(1).isConstant()) {
+ // it's ok, try to check next expr
+ return true;
+ }
+ // remove it
+ sortSlotRefs.remove(expr.getChild(0).unwrapSlotRef());
+ }
+ for (Expr child : expr.getChildren()) {
+ if (!checkSortNodeEliminable(child, sortSlotRefs)) {
+ return false;
+ }
+ }
+ return true;
+ }
+
@Override
public String toSql() {
if (sqlString != null) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index 95fb7f8a02..59b653a88f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -225,6 +225,8 @@ public class SessionVariable implements Serializable,
Writable {
public static final String ENABLE_NEREIDS_STATS_DERIVE_V2 =
"enable_nereids_stats_derive_v2";
+ public static final String ENABLE_ELIMINATE_SORT_NODE =
"enable_eliminate_sort_node";
+
public static final String INTERNAL_SESSION = "internal_session";
// session origin value
@@ -593,6 +595,9 @@ public class SessionVariable implements Serializable,
Writable {
@VariableMgr.VarAttr(name = ENABLE_NEREIDS_STATS_DERIVE_V2)
public boolean enableNereidsStatsDeriveV2 = false;
+ @VariableMgr.VarAttr(name = ENABLE_ELIMINATE_SORT_NODE)
+ public boolean enableEliminateSortNode = true;
+
@VariableMgr.VarAttr(name = INTERNAL_SESSION)
public boolean internalSession = false;
diff --git a/fe/fe-core/src/test/java/org/apache/doris/planner/PlannerTest.java
b/fe/fe-core/src/test/java/org/apache/doris/planner/PlannerTest.java
index 7b835d7754..3d2293c670 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/planner/PlannerTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/planner/PlannerTest.java
@@ -526,4 +526,141 @@ public class PlannerTest extends TestWithFeService {
Assertions.assertFalse(plan2.contains("SORT INFO:"));
Assertions.assertFalse(plan2.contains("SORT LIMIT:"));
}
+
+ @Test
+ public void testEliminatingSortNode() throws Exception {
+ // fail case 1
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 order
by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n
`k2`"));
+ Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
+ }
+
+ // fail case 2
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k3
= 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n
`k2`"));
+ Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
+ }
+
+ // fail case 3
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2
!= 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n
`k2`"));
+ Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
+ }
+
+ // fail case 4
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 or k2
= 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n
`k2`"));
+ Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
+ }
+
+ // fail case 5
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2
= 2 or k3 = 3 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n
`k2`"));
+ Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
+ }
+
+ // fail case 6
+ // TODO, support: in (select 1)
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 in (select
1) and k2 = 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("order by:"));
+ }
+
+ // fail case 7
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 not in (1)
and k2 = 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertTrue(plan1.contains("order by:"));
+ }
+
+ // success case 1
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2
= 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertFalse(plan1.contains("SORT INFO:\n
`k1`\n `k2`"));
+ Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
+ }
+
+ // success case 2
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k3 = 3 and k2
= 2 and k1 = 1 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertFalse(plan1.contains("SORT INFO:\n
`k1`\n `k2`"));
+ Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
+ }
+
+ // success case 3
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 in (1) and
k2 in (2) and k2 !=2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertFalse(plan1.contains("SORT INFO:\n
`k1`\n `k2`"));
+ Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
+ }
+
+ // success case 4
+ {
+ String sql1 = "explain select k1 from db1.tbl1 where k1 in
(concat('1','2')) and k2 = 2 order by k1, k2";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertFalse(plan1.contains("SORT INFO:\n
`k1`\n `k2`"));
+ Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
+ }
+
+ // success case 5
+ {
+ String sql1 = "explain select tbl1.k1 from db1.tbl1 join db1.tbl2
on tbl1.k1 = tbl2.k1"
+ + " where tbl1.k1 = 1 and tbl2.k1 = 2 and tbl1.k2 = 3
order by tbl1.k1, tbl2.k1";
+ StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext,
sql1);
+ stmtExecutor1.execute();
+ Planner planner1 = stmtExecutor1.planner();
+ String plan1 = planner1.getExplainString(new ExplainOptions(false,
false));
+ Assertions.assertFalse(plan1.contains("SORT INFO:"));
+ Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
+ }
+
+
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
