I am evaluating CB 6.5 New Features. CB 6.5 states CBO (Cost Based Optimizer) is available as a DP (Developer Preview).
https://docs.couchbase.com/server/6.5/developer-preview/preview-mode.html
I have converted my lab setup of CB 6.5 into DP mode, however when I go to “Settings”, under the “Advanced Query Settings”, I do not see the check box of “Use Cost-Based Optimizer” as mentioned in the URL General | Couchbase Docs
How do I test CBO feature in CB 6.5 DP mode ?
Regards,
Pratik Mehta
CB 6.5 is very old almost end of life. CBO is GA in later releases. Try with latest 7.0.x, If stats are available CBO is chosen default other wise it uses rule based plan.
Advanced Query Settings added only in 7.0
IN 6.5 DP once DP enabled follow the links create stats and run the query (you might have to give USE CBO hint as part of the request pre 7.0). cc @bingjie.miao
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/cost-based-optimizer.html
The checkbox for “Use Cost-Based Optimizer” under Advanced Query Settings is available in version 7.0 and later. As @vsr1 suggested try with version 7.0 and later, the CBO functionality is more complete in version 7.0.
@vsr1 @bingjie.miao Thanks for your response. I am aware that CBO feature is available from version 7.0, but my point is Developer Preview Mode and Features | Couchbase Docs is incorrect.
My goal is not to use CBO feature, however we are evaluating New Features of CB 6.5. The very first feature talks about “Developer Preview”. Above URL has 4 such features including CBO. However it fails to work.
In your original post, the second documentation link where you find “Use Cost-Based Optimizer” under Advanced Query Settings, points to the current version of the documentation which is for 7.0. The documentation for 6.5 should not have this description since it is not available.
Yes, correct. My 2nd URL is belonging to CB7 however in CB 6.5 Developer Preview mode there is no way to test Cost based Optimizer feature. I even verified with curl command. So if you read the first URL, then that part is not possible.
If evaluation 6.5 features same of them available latest release (7.0.x) with more improvements with production use, you can evaluate over there. Also 6.5 very old .
The second link URL if you look there current (i.e. 7.0) If you need 6.5 page you should replace that with 6.5 (In 6.5 there is no check box in settings)
7.0
In 7.0 Check box added so that enable /disable the CBO at cluster level.
https://docs.couchbase.com/server/current/manage/manage-settings/general-settings.html#query-settings
6.5
https://docs.couchbase.com/server/6.5/manage/manage-settings/general-settings.html#query-settings
In Pre 7.0 to use CBO you must create statistics via UPDATE stats and provide the hint to query via USE CBO. If qualified it uses CBO. (In 6.5 you must also enable developer preview).
Yes, that’s obvious because 6.5 does not support Cost Based Optimizer, however Developer Preview of 6.5 does support Cost Based Optimizer.
Do you know how to evaluate cost based Optimizer in CB6.5 Developer Preview mode?
/opt/couchbase/bin/couchbase-cli enable-developer-preview --enable -c localhost:8091 -u Administrator -p password
Example:
travel-sample(type) ; explain select t.* from `travel-sample` where type = "hotel";
{
"requestID": "30a05420-292b-4d79-b435-9e6b42cd2512",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"cardinality": 750.2091737150292,
"cost": 264.82383832140533,
"index": "def_type",
"index_id": "d388aa4e9cae2b0e",
"index_projection": {
"primary_key": true
},
"keyspace": "travel-sample",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"hotel\"",
"inclusion": 3,
"low": "\"hotel\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"cardinality": 750.2091737150292,
"cost": 5658.827797332467,
"keyspace": "travel-sample",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"cardinality": 750.2091737150292,
"condition": "((`travel-sample`.`type`) = \"hotel\")",
"cost": 5661.078424853612
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`travel-sample`.`t`)",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "select t.* from `travel-sample` where type = \"hotel\";"
}
],
"status": "success",
"metrics": {
"elapsedTime": "10.875156ms",
"executionTime": "10.74722ms",
"resultCount": 1,
"resultSize": 2532
}
}