Taewoo Kim has submitted this change and it was merged. Change subject: [ASTERIXDB-2437][COMP] Ensure the index-only plan on a composite index ......................................................................
[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]> --- M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java A asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp A asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan 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 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 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 A asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml 8 files changed, 278 insertions(+), 0 deletions(-) Approvals: Anon. E. Moose #1000171: Jenkins: Verified; No violations found; Verified Dmitry Lychagin: Looks good to me, approved 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 @@ 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. 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; + 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| 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; 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")); + 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; 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 } + 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> -- To view, visit https://asterix-gerrit.ics.uci.edu/2854 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: Idcc4cbe08323e0c6edb4a01637b2017128da1ab5 Gerrit-PatchSet: 6 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Taewoo Kim <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Dmitry Lychagin <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Taewoo Kim <[email protected]>
