>From Vijay Sarathy <[email protected]>:
Vijay Sarathy has uploaded this change for review. (
https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/17487 )
Change subject: [ASTERIXDB-3162][COMP] Use index hint not honored for AND
predicates when CBO is on
......................................................................
[ASTERIXDB-3162][COMP] Use index hint not honored for AND predicates when CBO
is on
Change-Id: I9a6c16d3f1537153f8b3353a1f858d7e3df8d209
---
M
asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
A
asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_2.sqlpp
M
asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
A
asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_1.plan
A
asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_1.sqlpp
A
asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_2.plan
6 files changed, 154 insertions(+), 4 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/87/17487/1
diff --git
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
index c8ac57e..8c28217 100644
---
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
+++
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
@@ -28,6 +28,7 @@
import java.util.Set;
import org.apache.asterix.common.annotations.IndexedNLJoinExpressionAnnotation;
+import
org.apache.asterix.common.annotations.SecondaryIndexSearchPreferenceAnnotation;
import org.apache.asterix.metadata.declared.DataSource;
import org.apache.asterix.metadata.declared.DataSourceId;
import org.apache.asterix.metadata.entities.Index;
@@ -298,6 +299,25 @@
return null;
}
+ public boolean findUseIndexHint(AbstractFunctionCallExpression condition) {
+ if
(condition.getExpressionTag().equals(LogicalExpressionTag.FUNCTION_CALL)) {
+ if
(condition.hasAnnotation(SecondaryIndexSearchPreferenceAnnotation.class)) {
+ return true;
+ }
+ } else if
(condition.getFunctionIdentifier().equals(AlgebricksBuiltinFunctions.AND)) {
+ for (int i = 0; i < condition.getArguments().size(); i++) {
+ ILogicalExpression expr =
condition.getArguments().get(i).getValue();
+ if
(expr.getExpressionTag().equals(LogicalExpressionTag.FUNCTION_CALL)) {
+ AbstractFunctionCallExpression AFCexpr =
(AbstractFunctionCallExpression) expr;
+ if
(AFCexpr.hasAnnotation(SecondaryIndexSearchPreferenceAnnotation.class)) {
+ return true;
+ }
+ }
+ }
+ }
+ return false;
+ }
+
public int findJoinNodeIndexByName(String name) {
for (int i = 1; i <= this.numberOfTerms; i++) {
if (name.equals(jnArray[i].datasetNames.get(0))) {
diff --git
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
index 43247d4..1f9300d 100644
---
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
+++
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
@@ -309,13 +309,13 @@
return PlanNode.NO_PLAN;
}
- protected void buildIndexPlan() {
+ protected void buildIndexPlan(boolean forceIndexPlan) {
List<PlanNode> allPlans = joinEnum.allPlans;
ICost opCost, totalCost;
opCost = joinEnum.getCostMethodsHandle().costIndexScan(this);
totalCost = opCost;
- if (this.cheapestPlanIndex == PlanNode.NO_PLAN ||
opCost.costLT(this.cheapestPlanCost)) {
+ if (this.cheapestPlanIndex == PlanNode.NO_PLAN ||
opCost.costLT(this.cheapestPlanCost) || forceIndexPlan) {
// for now just add one plan
PlanNode pn = new PlanNode(allPlans.size(), joinEnum);
pn.jn = this;
@@ -354,13 +354,15 @@
AbstractFunctionCallExpression afce = expr.getFuncExpr();
PredicateCardinalityAnnotation selectivityAnnotation =
afce.getAnnotation(PredicateCardinalityAnnotation.class);
- if (selectivityAnnotation != null) {
+ if (joinEnum.findUseIndexHint(afce)) {
+ buildIndexPlan(true);
+ } else if (selectivityAnnotation != null) {
sel = selectivityAnnotation.getSelectivity();
if (sel >=
joinEnum.stats.SELECTIVITY_FOR_SECONDARY_INDEX_SELECTION) {
afce.putAnnotation(SkipSecondaryIndexSearchExpressionAnnotation
.newInstance(Collections.singleton(chosenIndex.getIndexName())));
} else {
- buildIndexPlan();
+ buildIndexPlan(false);
}
}
}
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_1.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_1.sqlpp
new file mode 100644
index 0000000..6ced081
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_1.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset orders(id uuid not unknown) open type primary key `id`
autogenerated;
+CREATE INDEX orderline_delivery_d ON orders(UNNEST o_orderline SELECT
ol_delivery_d:STRING) EXCLUDE UNKNOWN KEY;
+
+/* Test with use-index hint on both arguments of AND condition, use-index hint
is honored. */
+SELECT count(*) as revenue
+FROM orders o, o.o_orderline ol
+WHERE ol.ol_delivery_d /*+ use-index (orderline_delivery_d) */ >=
'2016-01-01 00:00:00.000000'
+ AND ol.ol_delivery_d /*+ use-index (orderline_delivery_d) */ < '2017-01-01
00:00:00.000000';
\ No newline at end of file
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_2.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_2.sqlpp
new file mode 100644
index 0000000..c925b06
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/index_hint/index_hint_2.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset orders(id uuid not unknown) open type primary key `id`
autogenerated;
+CREATE INDEX orderline_delivery_d ON orders(UNNEST o_orderline SELECT
ol_delivery_d:STRING) EXCLUDE UNKNOWN KEY;
+
+/* Test with conflicting use-index and skip-index hint on arguments of AND
condition.
+ use-index wins and is honored */
+SELECT count(*) as revenue
+FROM orders o, o.o_orderline ol
+WHERE ol.ol_delivery_d /*+ use-index (orderline_delivery_d) */ >=
'2016-01-01 00:00:00.000000'
+ AND ol.ol_delivery_d /*+ skip-index (orderline_delivery_d) */ <
'2017-01-01 00:00:00.000000';
\ No newline at end of file
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_1.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_1.plan
new file mode 100644
index 0000000..c226460
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_1.plan
@@ -0,0 +1,28 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (test.orders.orders)
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$56(ASC)]
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- BTREE_SEARCH
(test.orders.orderline_delivery_d) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE
|PARTITIONED|
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_2.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_2.plan
new file mode 100644
index 0000000..27f35b1
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/index_hint/index_hint_2.plan
@@ -0,0 +1,28 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (test.orders.orders)
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$55(ASC)]
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- BTREE_SEARCH
(test.orders.orderline_delivery_d) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE
|PARTITIONED|
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/17487
To unsubscribe, or for help writing mail filters, visit
https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: neo
Gerrit-Change-Id: I9a6c16d3f1537153f8b3353a1f858d7e3df8d209
Gerrit-Change-Number: 17487
Gerrit-PatchSet: 1
Gerrit-Owner: Vijay Sarathy <[email protected]>
Gerrit-MessageType: newchange
