UNNEST with covering array index should remove DistinctScan
Description
Components
Affects versions
Labels
Environment
Link to Log File, atop/blg, CBCollectInfo, Core dump
Release Notes Description
blocks
Activity
CB robot April 17, 2017 at 8:35 PM
Build 5.0.0-2653 contains testrunner commit 7a59c65d8441bfe8016d0523711fcdbb21a9c7bc with commit message:
https://couchbasecloud.atlassian.net/browse/CBQE-4085#icft=CBQE-4085/https://couchbasecloud.atlassian.net/browse/MB-22111#icft=MB-22111:
https://github.com/couchbase/testrunner/commit/7a59c65d8441bfe8016d0523711fcdbb21a9c7bc
CB robot April 17, 2017 at 8:01 PM
Build 4.6.2-3895 contains testrunner commit f2ba46461fa22df27f87366d937a188280d1afdd with commit message:
https://couchbasecloud.atlassian.net/browse/CBQE-4085#icft=CBQE-4085/https://couchbasecloud.atlassian.net/browse/MB-22111#icft=MB-22111:
https://github.com/couchbase/testrunner/commit/f2ba46461fa22df27f87366d937a188280d1afdd
Ajay Pal Bhullar April 14, 2017 at 7:14 PM
Bug fix verified in 4.6.2-3889 and 5.0.0-2634
Tracking ticket for automated test case creation: https://couchbasecloud.atlassian.net/browse/CBQE-4085
Sitaram Vemulapalli April 14, 2017 at 6:45 PMEdited
by_replies used no DistinctScan
by_replies2 requires DistinctScan in this situation it may not use by_replies2 due to DISTINCT in index
Ajay Pal Bhullar April 14, 2017 at 6:40 PM
@Sitaram Vemulapalli what happens if you have both indexes? for your example in the comments?
CREATE INDEX by_replies ON default (ALL ARRAY r.`user` FOR r IN replies END, title);
CREATE INDEX by_replies2 ON default (DISTINCT ARRAY r.`user` FOR r IN replies END, replies, title);
explain SELECT t.title, r.`user` FROM default t UNNEST t.replies AS r WHERE r.`user` = "bcf160-000000000538";
This should have distinctScan or no? Currently the by_replies index is being used and there is no distinctScan, but I think this is expected behavior?
Unnest using covered index we may have to remove DistinctScan (due to distinct on meta().id).
CREATE INDEX by_replies ON default (ALL ARRAY r.`user` FOR r IN replies END, title);
explain SELECT t.title, r.`user` FROM default t UNNEST t.replies AS r WHERE r.`user` = "bcf160-000000000538”;
explain SELECT t.title, r.`user` FROM default t UNNEST t.replies AS r WHERE r.`user` IN [ "bcf160-000000000538", "ccf160-000000000538" ];
{
"requestID": "c18df553-57f3-4aa7-901c-dcd9139df051",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"covers": [
"cover ((`r`.`user`))",
"cover ((`t`.`title`))",
"cover ((meta(`t`).`id`))"
],
"index": "by_replies",
"index_id": "aa6e10c282d257b6",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"bcf160-000000000538\""
],
"Inclusion": 3,
"Low": [
"\"bcf160-000000000538\""
]
}
},
{
"Range": {
"High": [
"\"ccf160-000000000538\""
],
"Inclusion": 3,
"Low": [
"\"ccf160-000000000538\""
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(cover ((`r`.`user`)) in [\"bcf160-000000000538\", \"ccf160-000000000538\"])"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`t`.`title`))"
},
{
"expr": "cover ((`r`.`user`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT t.title, r.`user` FROM default t UNNEST t.replies AS r WHERE r.`user` IN [ \"bcf160-000000000538\", \"ccf160-000000000538\" ];"
}
],
"status": "success",
"metrics": {
"elapsedTime": "5.339364ms",
"executionTime": "5.319885ms",
"resultCount": 1,
"resultSize": 3360
}
}