>From Preetham Poluparthi <[email protected]>: Preetham Poluparthi has submitted this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20589?usp=email )
Change subject: [ASTERIXDB-3632]: Fix self-join query parsing in index advisor ...................................................................... [ASTERIXDB-3632]: Fix self-join query parsing in index advisor - user model changes: no - storage format changes: no - interface changes: no Ext-ref: MB-69430 Details: Earlier, queries containing self joins were not processed properly by the advisor. This update adds proper parsing and handling for self-join queries so index recommendations are generated accurately. Change-Id: Idf20b4c16d729c225798398e5985fc88f07e6bd0 Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20589 Reviewed-by: Ali Alsuliman <[email protected]> Tested-by: Ali Alsuliman <[email protected]> --- M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/indexadvisor/FakeIndexProvider.java M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.3.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.4.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.5.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.6.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.4.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.5.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.6.adm M asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/simple-advise1/simple-advise1.5.adm 14 files changed, 192 insertions(+), 18 deletions(-) Approvals: Ali Alsuliman: Looks good to me, approved; Verified Anon. E. Moose #1000171: diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java index e14cbf3..f1eba1c 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java @@ -337,12 +337,12 @@ accessMethod.add(new RemoveUnusedOneToOneEquiJoinRule()); accessMethod.add(new PushSimilarityFunctionsBelowJoin()); accessMethod.add(new RemoveUnusedAssignAndAggregateRule()); - accessMethod.add(new AdviseIndexRule()); return accessMethod; } public static List<IAlgebraicRewriteRule> buildPlanCleanupRuleCollection() { List<IAlgebraicRewriteRule> planCleanupRules = new LinkedList<>(); + planCleanupRules.add(new AdviseIndexRule()); planCleanupRules.add(new SwitchInnerJoinBranchRule()); planCleanupRules.add(new AsterixPushMapOperatorThroughUnionRule(LogicalOperatorTag.ASSIGN)); planCleanupRules.add(new ExtractCommonExpressionsRule()); diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java index 14cb453..b7e2a28 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java @@ -595,16 +595,18 @@ for (LimitType limitType : lowKeyLimits) { if (limitType != null) { numLowKeys++; - } else + } else { break; + } } int numHighKeys = 0; for (LimitType limitType : highKeyLimits) { if (limitType != null) { numHighKeys++; - } else + } else { break; + } } for (int i = 0; i < lowKeyExprs.length; i++) { diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/indexadvisor/FakeIndexProvider.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/indexadvisor/FakeIndexProvider.java index fc72069..a0ade94 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/indexadvisor/FakeIndexProvider.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/indexadvisor/FakeIndexProvider.java @@ -40,7 +40,6 @@ import org.apache.asterix.om.constants.AsterixConstantValue; import org.apache.asterix.om.types.IAType; import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException; -import org.apache.hyracks.algebricks.common.utils.Pair; import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable; import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression; import org.apache.hyracks.algebricks.core.algebra.expressions.IAlgebricksConstantValue; @@ -119,8 +118,7 @@ } private void addSingleDataSourceIndexes(List<AdvisorScanPlanNode> scanPlanNodes) { - Map<DatasetFullyQualifiedName, Pair<DataSourceScanOperator, Set<List<String>>>> singleDataSourceFieldNamesMap = - new HashMap<>(); + Map<DataSourceScanOperator, Set<List<String>>> singleDataSourceFieldNamesMap = new HashMap<>(); for (AdvisorScanPlanNode scanPlanNode : scanPlanNodes) { DataSourceScanOperator scanOperator = scanPlanNode.getScanOperator(); @@ -138,26 +136,34 @@ } } - singleDataSourceFieldNamesMap.put(fullyQualifiedName, new Pair<>(scanOperator, datasetFieldNames)); + singleDataSourceFieldNamesMap.put(scanOperator, datasetFieldNames); } - for (Map.Entry<DatasetFullyQualifiedName, Pair<DataSourceScanOperator, Set<List<String>>>> entry : singleDataSourceFieldNamesMap - .entrySet()) { - DatasetFullyQualifiedName qualifiedName = entry.getKey(); - Set<List<String>> fieldNames = entry.getValue().getSecond(); - DataSourceScanOperator scanOperator = entry.getValue().getFirst(); - - if (((DatasetDataSource) scanOperator.getDataSource()).getDataset().getDatasetDetails() - .getDatasetType() != DatasetConfig.DatasetType.INTERNAL) { + for (Map.Entry<DataSourceScanOperator, Set<List<String>>> entry : singleDataSourceFieldNamesMap.entrySet()) { + DataSourceScanOperator scanOperator = entry.getKey(); + Set<List<String>> fieldNames = entry.getValue(); + if (!(scanOperator.getDataSource() instanceof DatasetDataSource dataSource)) { continue; } + if (dataSource.getDataset().getDatasetDetails().getDatasetType() != DatasetConfig.DatasetType.INTERNAL) { + continue; + } + + DatasetFullyQualifiedName qualifiedName = dataSource.getDataset().getDatasetFullyQualifiedName(); + List<List<String>> primaryKeys = ((InternalDatasetDetails) ((DatasetDataSource) scanOperator.getDataSource()).getDataset() .getDatasetDetails()).getPrimaryKey(); - Map<String, Index> datasetIndexes = new HashMap<>(); - filterIndexesMap.put(qualifiedName, datasetIndexes); + Map<String, Index> datasetIndexes; + + if (filterIndexesMap.containsKey(qualifiedName)) { + datasetIndexes = filterIndexesMap.get(qualifiedName); + } else { + datasetIndexes = new HashMap<>(); + filterIndexesMap.put(qualifiedName, datasetIndexes); + } String primaryKeyName = ((DataSourceId) scanOperator.getDataSource().getId()).getDatasourceName(); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml index ab75e0c..7c910c9 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml @@ -56,4 +56,9 @@ <output-dir compare="Text">nested-array-index</output-dir> </compilation-unit> </test-case> + <test-case FilePath="cbo-join/index-advisor"> + <compilation-unit name="self-join-advise"> + <output-dir compare="Text">self-join-advise</output-dir> + </compilation-unit> + </test-case> </test-group> diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.1.ddl.sqlpp new file mode 100644 index 0000000..bf8b931 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.1.ddl.sqlpp @@ -0,0 +1,25 @@ +/* + * 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 A PRIMARY KEY ( a_0 : integer); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.2.update.sqlpp new file mode 100644 index 0000000..924f8e1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.2.update.sqlpp @@ -0,0 +1,30 @@ +/* + * 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. + */ + + +use test; + + + +INSERT INTO A( + SELECT VALUE {"a_0": i, "a_1": i%20 , "a_2" : i, "a_3" : i} + FROM range(1, 5000) i +); + + diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.3.update.sqlpp new file mode 100644 index 0000000..d8d3c91 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.3.update.sqlpp @@ -0,0 +1,23 @@ +/* + * 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. + */ + +use test; + + +ANALYZE DATASET A with {"sample": "high", "sample-seed": 2}; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.4.query.sqlpp new file mode 100644 index 0000000..dc15af7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.4.query.sqlpp @@ -0,0 +1,27 @@ +/* + * 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. + */ + + +use test; + + +advise SELECT * +FROM A AS h1, A AS h2 +WHERE h1.`a_2` = 900; + diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.5.query.sqlpp new file mode 100644 index 0000000..99f2bfc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.5.query.sqlpp @@ -0,0 +1,26 @@ +/* + * 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. + */ + + +use test; + +advise SELECT * +FROM A AS h1, A AS h2 +WHERE h2.`a_2` = 900; + diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.6.query.sqlpp new file mode 100644 index 0000000..6f7b6ad --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/index-advisor/self-join-advise/self-join-advise.6.query.sqlpp @@ -0,0 +1,27 @@ +/* + * 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. + */ + + +use test; + +advise SELECT * +FROM A d1 +JOIN A d2 +ON d1.a_1 /* +indexnl */ = d2.a_1 +WHERE d2.`a_2` = 800 and d1.`a_3` = 900; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.4.adm new file mode 100644 index 0000000..a1000b0 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.4.adm @@ -0,0 +1 @@ +[{"#operator":"Advise","advice":{"#operator":"IndexAdvice","adviseinfo":{"current_indexes":[],"recommended_indexes":{"indexes":[{"index_statement":"CREATE INDEX idx_a_2 ON `Default`.`test`.`A`(`a_2`);"}]}}}}] \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.5.adm new file mode 100644 index 0000000..a1000b0 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.5.adm @@ -0,0 +1 @@ +[{"#operator":"Advise","advice":{"#operator":"IndexAdvice","adviseinfo":{"current_indexes":[],"recommended_indexes":{"indexes":[{"index_statement":"CREATE INDEX idx_a_2 ON `Default`.`test`.`A`(`a_2`);"}]}}}}] \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.6.adm new file mode 100644 index 0000000..f39ecc2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/self-join-advise/self-join-advise.6.adm @@ -0,0 +1 @@ +[{"#operator":"Advise","advice":{"#operator":"IndexAdvice","adviseinfo":{"current_indexes":[],"recommended_indexes":{"indexes":[{"index_statement":"CREATE INDEX idx_a_3 ON `Default`.`test`.`A`(`a_3`);"},{"index_statement":"CREATE INDEX idx_a_1 ON `Default`.`test`.`A`(`a_1`);"}]}}}}] \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/simple-advise1/simple-advise1.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/simple-advise1/simple-advise1.5.adm index e2b4c29..e7a4a45 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/simple-advise1/simple-advise1.5.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/index-advisor/simple-advise1/simple-advise1.5.adm @@ -1 +1 @@ -[{"#operator":"Advise","advice":{"#operator":"IndexAdvice","adviseinfo":{"current_indexes":[],"recommended_indexes":{"indexes":[{"index_statement":"CREATE INDEX idx_b ON `Default`.`test`.`A`(`b`);"}]}}}}] \ No newline at end of file +[{"#operator":"Advise","advice":{"#operator":"IndexAdvice","adviseinfo":{"current_indexes":[],"recommended_indexes":{"indexes":[{"index_statement":"CREATE INDEX idx_b ON `Default`.`test`.`A`(`b`);"},{"index_statement":"CREATE INDEX idx_c ON `Default`.`test`.`B`(`c`);"}]}}}}] \ No newline at end of file -- To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20589?usp=email To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings?usp=email Gerrit-MessageType: merged Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Change-Id: Idf20b4c16d729c225798398e5985fc88f07e6bd0 Gerrit-Change-Number: 20589 Gerrit-PatchSet: 8 Gerrit-Owner: Preetham Poluparthi <[email protected]> Gerrit-Reviewer: Ali Alsuliman <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Preetham Poluparthi <[email protected]>
