Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
6.5.0, 6.0.0, 5.5.0
-
Untriaged
-
Unknown
Description
When using named parameters, queries using "ANY ... SATISFIES ... END" produce different plans for execution that are not as optimised as compared to not using named parameters.
Using travel-sample, the following Index is used :
CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);
|
The following query which does not use a named parameter produces an expected query plan :
SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = "US681" END; |
{
|
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "926ns" |
},
|
"~children": [ |
{
|
"#operator": "Authorize", |
"#stats": { |
"#phaseSwitches": 3, |
"execTime": "8.086µs", |
"servTime": "1.249702ms" |
},
|
"privileges": { |
"List": [ |
{
|
"Target": "default:travel-sample", |
"Priv": 7 |
}
|
]
|
},
|
"~child": { |
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "8.908µs" |
},
|
"~children": [ |
{
|
"#operator": "DistinctScan", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 157, |
"execTime": "91.318µs", |
"kernTime": "13.324669ms" |
},
|
"scan": { |
"#operator": "IndexScan3", |
"#stats": { |
"#itemsOut": 38, |
"#phaseSwitches": 157, |
"execTime": "124.857µs", |
"kernTime": "13.258µs", |
"servTime": "13.168046ms" |
},
|
"covers": [ |
"cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))", |
"cover ((meta(`travel-sample`).`id`))" |
],
|
"filter_covers": { |
"cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)": true |
},
|
"index": "idx_flight_test", |
"index_id": "b6334268434aba20", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"US681\"", |
"inclusion": 3, |
"low": "\"US681\"" |
}
|
]
|
}
|
],
|
"using": "gsi", |
"#time_normal": "00:00.0132", |
"#time_absolute": 0.013292903000000002 |
},
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000091318 |
},
|
{
|
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "6.068µs" |
},
|
"~children": [ |
{
|
"#operator": "Filter", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 155, |
"execTime": "3.446106ms", |
"kernTime": "13.437146ms" |
},
|
"condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)", |
"#time_normal": "00:00.0034", |
"#time_absolute": 0.003446106 |
},
|
{
|
"#operator": "InitialProject", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 119, |
"execTime": "61.761µs", |
"kernTime": "16.892771ms" |
},
|
"result_terms": [ |
{
|
"expr": "cover ((meta(`travel-sample`).`id`))" |
}
|
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.00006176100000000001 |
},
|
{
|
"#operator": "FinalProject", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 115, |
"execTime": "25.357µs", |
"kernTime": "152.933µs" |
},
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000025356999999999998 |
}
|
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000006067999999999999 |
}
|
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000008908 |
},
|
"#time_normal": "00:00.0012", |
"#time_absolute": 0.0012577880000000001 |
},
|
{
|
"#operator": "Stream", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 155, |
"execTime": "5.46µs", |
"kernTime": "18.425336ms" |
},
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.00000546 |
}
|
],
|
"~versions": [ |
"2.0.0-N1QL", |
"5.5.2-3733-enterprise" |
],
|
"#time_normal": "00:00", |
"#time_absolute": 0 |
}
|
Using a named parameter however produces a different plan:
SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;
|
{
|
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "12.611µs" |
},
|
"~children": [ |
{
|
"#operator": "Authorize", |
"#stats": { |
"#phaseSwitches": 3, |
"execTime": "17.278µs", |
"servTime": "20.387192ms" |
},
|
"privileges": { |
"List": [ |
{
|
"Target": "default:travel-sample", |
"Priv": 7 |
}
|
]
|
},
|
"~child": { |
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "29.364µs" |
},
|
"~children": [ |
{
|
"#operator": "DistinctScan", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 157, |
"execTime": "121.912µs", |
"kernTime": "24.965667ms" |
},
|
"scan": { |
"#operator": "IndexScan3", |
"#stats": { |
"#itemsOut": 38, |
"#phaseSwitches": 157, |
"execTime": "79.143µs", |
"kernTime": "7.815µs", |
"servTime": "24.856622ms" |
},
|
"index": "idx_flight_test", |
"index_id": "b6334268434aba20", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"US681\"", |
"inclusion": 3, |
"low": "\"US681\"" |
}
|
]
|
}
|
],
|
"using": "gsi", |
"#time_normal": "00:00.0249", |
"#time_absolute": 0.024935765000000002 |
},
|
"#time_normal": "00:00.0001", |
"#time_absolute": 0.00012191200000000001 |
},
|
{
|
"#operator": "Fetch", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 161, |
"execTime": "125.7µs", |
"kernTime": "25.113325ms", |
"servTime": "218.202109ms" |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"#time_normal": "00:00.2183", |
"#time_absolute": 0.218327809 |
},
|
{
|
"#operator": "Sequence", |
"#stats": { |
"#phaseSwitches": 1, |
"execTime": "7.403µs" |
},
|
"~children": [ |
{
|
"#operator": "Filter", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 155, |
"execTime": "9.705067ms", |
"kernTime": "234.098171ms" |
},
|
"condition": "any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end", |
"#time_normal": "00:00.0097", |
"#time_absolute": 0.009705067 |
},
|
{
|
"#operator": "InitialProject", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 119, |
"execTime": "200.696µs", |
"kernTime": "241.29278ms" |
},
|
"result_terms": [ |
{
|
"expr": "(meta(`travel-sample`).`id`)" |
}
|
],
|
"#time_normal": "00:00.0002", |
"#time_absolute": 0.000200696 |
},
|
{
|
"#operator": "FinalProject", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 115, |
"execTime": "17.587µs", |
"kernTime": "2.347419ms" |
},
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000017587 |
}
|
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000007402999999999999 |
}
|
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000029364000000000002 |
},
|
"#time_normal": "00:00.0204", |
"#time_absolute": 0.020404469999999997 |
},
|
{
|
"#operator": "Stream", |
"#stats": { |
"#itemsIn": 38, |
"#itemsOut": 38, |
"#phaseSwitches": 155, |
"execTime": "13.726µs", |
"kernTime": "264.321019ms" |
},
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000013726 |
}
|
],
|
"~versions": [ |
"2.0.0-N1QL", |
"5.5.2-3733-enterprise" |
],
|
"#time_normal": "00:00.0000", |
"#time_absolute": 0.000012611 |
}
|
Attachments
Issue Links
- causes
-
MB-52090 adhoc query index selection issue with LIKE as index condition and query parameters
-
- Resolved
-
-
MB-48266 Array Index WHEN clause and query named parameters not working
-
- Closed
-
-
MB-52161 [BP to 7.0.4,7.1.1] - adhoc query index selection issue with LIKE as index condition and query parameters
-
- Closed
-
- is duplicated by
-
MB-42038 Filter operator applied twice for adhoc query parameters
-
- Closed
-
- is triggering
-
MB-42956 aggregation regression
-
- Closed
-
- relates to
-
MB-36204 Query not covered when query parameter avialble
-
- Closed
-
For Gerrit Dashboard: MB-33009 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
140398,3 | MB-33009 Delay processing of query parameters till actual use | master | query | Status: MERGED | +2 | +1 |
140399,2 | MB-33009 Delay processing of query parameters till actual use | master | query-ee | Status: MERGED | +2 | +1 |
141269,4 | MB-42956 Replace query parameters for generation of index spans for IN and LIKE clauses | master | query | Status: MERGED | +2 | +1 |
146582,2 | MB-33009 Delay processing of query parameters till actual use | mad-hatter | query | Status: MERGED | +2 | +1 |
146583,2 | MB-33009 Delay processing of query parameters till actual use | mad-hatter | query-ee | Status: MERGED | +2 | +1 |
160668,2 | MB-48266 Replace query parameters for WHEN clause of ANY predicates | master | query | Status: MERGED | +2 | +1 |
160669,2 | MB-48266 Replace query parameters for WHEN clause of ANY predicates | cheshire-cat | query | Status: MERGED | +2 | +1 |
Activity
Field | Original Value | New Value |
---|---|---|
Link | This issue blocks CBSE-6398 [ CBSE-6398 ] |
Fix Version/s | Mad-Hatter [ 15037 ] |
Issue Type | Bug [ 1 ] | Improvement [ 4 ] |
Fix Version/s | feature-backlog [ 10342 ] | |
Fix Version/s | Mad-Hatter [ 15037 ] |
Assignee | Keshav Murthy [ keshav ] | Kamini Jagtiani [ kamini.jagtiani ] |
Link | This issue blocks CBSE-8464 [ CBSE-8464 ] |
Affects Version/s | 6.5.0 [ 15037 ] |
Link | This issue blocks CBSE-8565 [ CBSE-8565 ] |
Link | This issue blocks CBSE-8773 [ CBSE-8773 ] |
Issue Type | Improvement [ 4 ] | Bug [ 1 ] |
Fix Version/s | Cheshire-Cat [ 15915 ] | |
Fix Version/s | feature-backlog [ 10342 ] |
Assignee | Kamini Jagtiani [ kamini.jagtiani ] | Mihir Kamdar [ mihir.kamdar ] |
Resolution | Fixed [ 1 ] | |
Status | Open [ 1 ] | Resolved [ 5 ] |
Assignee | Mihir Kamdar [ mihir.kamdar ] | Bingjie Miao [ bingjie.miao ] |
Resolution | Fixed [ 1 ] | |
Status | Resolved [ 5 ] | Reopened [ 4 ] |
Link | This issue blocks MB-43310 [ MB-43310 ] |
Link | This issue blocks CBSE-9705 [ CBSE-9705 ] |
Assignee | Bingjie Miao [ bingjie.miao ] | Mihir Kamdar [ mihir.kamdar ] |
Resolution | Fixed [ 1 ] | |
Status | Reopened [ 4 ] | Resolved [ 5 ] |
Fix Version/s | 6.6.2 [ 17103 ] |
Assignee | Mihir Kamdar [ mihir.kamdar ] | Pierre Regazzoni [ JIRAUSER25157 ] |
Status | Resolved [ 5 ] | Closed [ 6 ] |
Labels | approved-for-6.6.2 |
Fix Version/s | 7.0.0 [ 17233 ] |
Fix Version/s | Cheshire-Cat [ 15915 ] |
Labels | approved-for-6.6.2 | approved-for-6.6.2 needs_automation |