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
-
-
- Resolved
-
-
-
- Closed
-
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- is triggering
-
-
- Closed
-
- relates to
-
-
- Closed
-
(1 relates to)
| 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 |