Details
-
Bug
-
Resolution: Fixed
-
Major
-
5.5.0, 6.0.0, 6.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 |