Repository: asterixdb Updated Branches: refs/heads/master 5531ef8bb -> cb3ca25f3
[ASTERIXDB-2437][COMP] Ensure the index-only plan on a composite index - user model changes: no - storage format changes: no - interface changes: no Details: - Ensure the proper build of an index-only plan on a composite index where both fields are used in the SELECT condition and only one field is returned. Change-Id: Idcc4cbe08323e0c6edb4a01637b2017128da1ab5 Reviewed-on: https://asterix-gerrit.ics.uci.edu/2854 Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Dmitry Lychagin <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/cb3ca25f Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/cb3ca25f Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/cb3ca25f Branch: refs/heads/master Commit: cb3ca25f3c1178c47570246cc361614ef6bfb377 Parents: 5531ef8 Author: Taewoo Kim <[email protected]> Authored: Sat Sep 22 16:12:10 2018 -0700 Committer: Taewoo Kim <[email protected]> Committed: Mon Sep 24 13:56:56 2018 -0700 ---------------------------------------------------------------------- .../optimizer/rules/am/AccessMethodUtils.java | 24 +++++ .../btree-index/btree-sidx-idxonly-10.sqlpp | 102 +++++++++++++++++++ .../btree-index/btree-sidx-idxonly-10.plan | 35 +++++++ .../btree-sidx-composite-idxonly-04.1.ddl.sqlpp | 60 +++++++++++ ...ree-sidx-composite-idxonly-04.2.update.sqlpp | 25 +++++ ...tree-sidx-composite-idxonly-04.3.query.sqlpp | 25 +++++ .../btree-sidx-composite-idxonly-04.1.adm | 2 + .../resources/runtimets/testsuite_sqlpp.xml | 5 + 8 files changed, 278 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java index c925b55..10037f0 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java @@ -1196,6 +1196,30 @@ public class AccessMethodUtils { origVarToSIdxUnnestMapOpVarMap.put(tVar, skVarsFromSIdxUnnestMap.get(sIndexIdx)); } + // For B-Tree case: if the given secondary key field variable is used only in the select or + // join condition, we were not able to catch the mapping between the the SK from the original + // data-scan and the SK from the secondary index search since they are different logical variables. + // (E.g., we are sending a query on a composite index but returns only one field.) + List<LogicalVariable> varsUsedInTopOpButNotAfterwards = new ArrayList<>(); + copyVarsToAnotherList(uniqueUsedVarsInTopOp, varsUsedInTopOpButNotAfterwards); + varsUsedInTopOpButNotAfterwards.removeAll(usedVarsAfterTopOp); + if (idxType == IndexType.BTREE) { + for (LogicalVariable v : varsUsedInTopOpButNotAfterwards) { + int sIndexIdx = chosenIndexFieldNames.indexOf(subTree.getVarsToFieldNameMap().get(v)); + // For the join-case, the match might not exist. + // In this case, we just propagate the variables later. + if (sIndexIdx == -1) { + continue; + } + LogicalVariable replacedVar = context.newVar(); + origPKRecAndSKVarToleftPathMap.put(v, replacedVar); + origVarToOutputVarMap.put(skVarsFromSIdxUnnestMap.get(sIndexIdx), v); + // Constructs the mapping between the SK from the original data-scan + // and the SK from the secondary index search since they are different logical variables. + origVarToSIdxUnnestMapOpVarMap.put(v, skVarsFromSIdxUnnestMap.get(sIndexIdx)); + } + } + // For R-Tree case: if the given secondary key field variable is used only in the select or join condition, // we were not able to catch the mapping between the original secondary key field and the newly restored // secondary key field in the assign operator in the right path. http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp new file mode 100644 index 0000000..3259a28 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp @@ -0,0 +1,102 @@ +/* + * 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. + */ + +/* + * Description : Secondary BTree Index index-only selection plan verification test + * : The test is intended to verify that the secondary BTree index is used in the optimized query plan. + * : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index. + * : The plan should have two paths after the secondary index-lookup. + * : The left path: + * ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ... + * : The right path: + * ... -> unnest-map (sidx) -> split -> -> union -> ... + * Expected Result : Success + * +*/ + +drop dataverse twitter if exists; +create dataverse twitter if not exists; +use twitter; + +create type typeUser if not exists as open { + id: int64, + name: string, + screen_name : string, + profile_image_url : string, + lang : string, + location: string, + create_at: date, + description: string, + followers_count: int32, + friends_count: int32, + status_count: int64 +}; + +create type typePlace if not exists as open{ + country : string, + country_code : string, + full_name : string, + id : string, + name : string, + place_type : string, + bounding_box : rectangle +}; + +create type typeGeoTag if not exists as open { + stateID: int32, + stateName: string, + countyID: int32, + countyName: string, + cityID: int32?, + cityName: string? +}; + +create type typeTweet if not exists as open { + create_at : datetime, + id: int64, + text: string, + in_reply_to_status : int64, + in_reply_to_user : int64, + favorite_count : int64, + coordinate: point?, + retweet_count : int64, + lang : string, + is_retweet: boolean, + hashtags : {{ string }} ?, + user_mentions : {{ int64 }} ? , + user : typeUser, + place : typePlace?, + geo_tag: typeGeoTag +}; + +create dataset ds_tweet(typeTweet) if not exists primary key id; + +create index create_at_status_count_idx on ds_tweet(user.create_at, user.status_count); + +select value count(first.create_at) from ( +select t.user.create_at, t.user.status_count, t.id from ds_tweet t +where + t.user.create_at >= + date_from_unix_time_in_days(10000) and + t.user.create_at < + date_from_unix_time_in_days(12000) and + t.user.status_count >= 0 and + t.user.status_count < 1000000 +) first; + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan new file mode 100644 index 0000000..89e06cc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan @@ -0,0 +1,35 @@ +-- DISTRIBUTE_RESULT |UNPARTITIONED| + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + -- AGGREGATE |UNPARTITIONED| + -- RANDOM_MERGE_EXCHANGE |PARTITIONED| + -- AGGREGATE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- UNION_ALL |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- STREAM_SELECT |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- BTREE_SEARCH |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- SPLIT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- BTREE_SEARCH |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- STREAM_SELECT |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- SPLIT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- BTREE_SEARCH |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp new file mode 100644 index 0000000..4530d8d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp @@ -0,0 +1,60 @@ +/* + * 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. + */ + +/* + * Description : Secondary BTree Index index-only selection plan verification test + * : This test is intended to verify that the secondary BTree index is + * : used in the optimized query plan. + * : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index. + * : The plan should have two paths after the secondary index-lookup. + * : The left path: + * ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ... + * : The right path: + * ... -> unnest-map (sidx) -> split -> select (the second condition) -> union -> ... + * Expected Result : Success + * +*/ + +drop dataverse test if exists; +create dataverse test; +use test; + +create type MyRecord as closed { + id: int64, + docid: int64, + val1: int64, + title: string, + point: point, + kwds: string, + line1: line, + line2: line, + poly1: polygon, + poly2: polygon, + rec: rectangle, + circle: circle +}; + +create dataset MyData(MyRecord) + primary key id; + +create index btree_index_docid_val1 on MyData(docid,val1) type btree; +create index rtree_index_point on MyData(point) type rtree; +create index rtree_index_rec on MyData(rec) type rtree; +create index ngram_index_title on MyData(title) type ngram(3); +create index keyword_index_title on MyData(title) type keyword; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp new file mode 100644 index 0000000..bc705b2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.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. + */ + +use test; + +load dataset MyData +using localfs +(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp new file mode 100644 index 0000000..8ccb930 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.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. + */ + +use test; + +select element {"pk":o.id, "sk":o.docid} +from MyData o +where o.docid < 3 and o.val1 >= 3 +order by o.id; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm new file mode 100644 index 0000000..98ee5d4 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm @@ -0,0 +1,2 @@ +{ "pk": 1, "sk": 1 } + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/cb3ca25f/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml index 9bb995e..18e93e1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml @@ -3377,6 +3377,11 @@ </compilation-unit> </test-case> <test-case FilePath="index-selection"> + <compilation-unit name="btree-sidx-composite-idxonly-04"> + <output-dir compare="Text">btree-sidx-composite-idxonly-04</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="index-selection"> <compilation-unit name="btree-sidx-idxonly-01"> <output-dir compare="Text">btree-sidx-idxonly-01</output-dir> </compilation-unit>
