{"id":9765,"date":"2021-08-10T00:00:34","date_gmt":"2021-08-10T07:00:34","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=9765"},"modified":"2025-06-13T18:43:33","modified_gmt":"2025-06-14T01:43:33","slug":"cost-based-optimization-with-couchbase-json-database","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/cost-based-optimization-with-couchbase-json-database\/","title":{"rendered":"Boost Query Efficiency with Cost-Based Optimization in Couchbase"},"content":{"rendered":"

Database users of every kind have come to expect cost-based optimization<\/strong> for their queries.<\/p>\n

It’s a standard feature of all traditional relational databases (RDBMS), but there hasn’t yet been a cost-based optimizer (CBO) for NoSQL document databases. That is, until now.<\/p>\n

Cost-based optimization for Couchbase queries was released as a Developer Preview in Couchbase 6.5, and it’s now ready for general availability with Couchbase 7.0<\/a>.<\/p>\n

Let’s take a closer look at the new CBO features of Couchbase Server 7.0.<\/p>\n

The Old Way: Rule-Based Optimization<\/h2>\n

It’s impossible to discuss cost-based optimization without referring to the previous approach to database query optimization: rule-based optimization (RBO).<\/p>\n

With rule-based optimization, the query optimizer considers using any<\/em> index that provides a path to retrieve the query result. However, when the optimizer is presented with more than one path to process the query \u2013 that is, when the query could use multiple indexes \u2013 it could end up using all<\/em> indexes.<\/p>\n

With cost-based optimization, the query optimizer calculates the cost of using each index, and then decides on the execution plan with the lowest cost.<\/p>\n

What Is Cost-Based Optimization?<\/h2>\n

Cost-based optimization enables the database query optimizer to choose the best (lowest-cost) plan to process a query.<\/p>\n

This matters most whenever a query has more than one potential execution path, i.e., more than one qualified index. Each query plan path is assigned a total cost for all of the operations that the query needs to perform. These costs particularly depend on the specified predicates’ selectivity. To arrive at the selectivity value of a predicate, the statistics of the predicate fields need to have been pre-computed.<\/p>\n

Please note that the cost value is an arbitrary unit of statistics calculation: it does not equate to the resource cost or CPU usage.<\/p>\n

Cost-Based Optimization vs Rule-Based Optimization<\/h2>\n

Cost-based query optimization always provides a better execution path when the optimizer is presented with more than one option. The computed statistics ensure that the optimizer is equipped with the knowledge about the data distribution, and subsequently the efficiency of one query plan versus another.<\/p>\n

For instance, imagine a Couchbase Collection<\/a> that contains customer information and their addresses, with an index on ix1(zipcode)<\/code> and ix2(city)<\/code>. Statistically, there are more ZIP codes than there are cities in the U.S. But that doesn’t necessarily mean that using the ix1(zipcode)<\/code> index has a lower cost (and therefore better performance) than the ix2(city)<\/code> index. Why? Because we don’t yet know if the customers in the system are evenly distributed across all U.S. cities and ZIP codes. Chances are that they’re not.<\/p>\n

In this scenario, it’s the statistics collected by the cost-based optimizer beforehand that help it decide the lowest-cost query path at runtime.<\/p>\n

With rule-based optimization, if a query has both city and ZIP code predicates, then the query optimizer uses both indexes with intersect scan. However, the CBO only uses one index \u2013 whichever one has the lowest cost based on the data within the Collection.<\/p>\n

How to Enable Cost-Based Optimization in Couchbase<\/h2>\n

There are two important steps you need to take in order to use cost-based optimization in Couchbase Server 7.0:<\/p>\n

1. Enable the CBO Feature<\/strong><\/p>\n

Currently the cost-based optimizer is enabled by default in Couchbase 7.0.<\/p>\n

However, you can disable it with the N1QL<\/a> Feature Control value in Settings->Query Settings. If you plan to use cost-based optimization, then make sure it’s enabled.<\/p>\n

2. Collect Statistics<\/strong><\/p>\n

In addition to having the cost-based optimizer enabled in your cluster, you must also have collected statistics on the underlying fields for the indexes used by your queries. Here’s a sample query on how you’d do that:<\/p>\n

UPDATE STATISTICS FOR<keyspace> (<index_expr1>,..) \r\n<\/pre>\n
<\/div>\n
For example, using the travel-sample<\/code> dataset, this above query would look like this:<\/p>\n
UPDATE STATISTICS FOR `travel-sample`._default.hotel (city);\r\n<\/pre>\n
<\/div>\n
You can also collect the statistics according to the index name:<\/p>\n
UPDATE STATISTICS FOR `travel-sample`._default.route.def_route_schedule_utc;\r\n<\/pre>\n
<\/div>\n
Note that you can also disable the cost-based optimizer by either using the cbo_flag<\/code> in your API, or by deleting the statistics with UPDATE STATISTICS <keyspace> DELETE (<index_expr1>,..)<\/code>.<\/p>\n
The CBO System Bucket<\/h2>\n
When you’ve enabled cost-based optimization and you’ve collected statistics, you’ll notice an additional Bucket in your cluster called N1QL_SYSTEM_BUCKET.N1QL_SYSTEM_SCOPE.N1QL_CBO_STAT<\/code>. Direct access to this Bucket is restricted to system administrators.<\/p>\n
Using the UPDATE STATISTICS<\/code> command, you can either add more stats to the Bucket or delete the stats with UPDATE STATISTICS FOR <keyspace> DELETE<\/code>.<\/p>\n
Cost-Based Optimizer in Action<\/h2>\n
Let’s take a closer look at how cost-based optimization works in Couchbase 7.0 using the `travel-sample`<\/code> dataset in the following scenario.<\/p>\n
Improved Index Selection with Cost-Based Optimization<\/h3>\n
Let’s say you have defined two indexes for the `travel-sample`._default.airport<\/code> Collection.<\/p>\n
CREATE INDEX def_airport_city_country ON airport(city,country); \r\nCREATE INDEX def_airport_faa ON airport(faa);\r\n<\/pre>\n
<\/div>\n
And you have a query below:<\/p>\n
SELECT * FROM airport WHERE city = \"New York\" AND faa = \"JFK\";\r\n<\/pre>\n
<\/div>\n
If the above cost-based optimization weren’t used, the rules-based optimizer execution plan for the above query would be:<\/p>\n
<\/div>\n
\"rules-based<\/p>\n
<\/div>\n
Note that since the query predicates are on both the city<\/code> and faa<\/code> fields, both indexes are used in the intersect scan. Learn more about intersect scans in this article: Performance Ingredients for NoSQL: Intersect Scans in N1QL<\/a>.<\/p>\n
Next, you enable cost-based optimization for the database query. Since CBO is already enabled by default, all you need to do is to collect the statistics.<\/p>\n
UPDATE STATISTICS FOR airport(city, faa);\r\n<\/pre>\n
<\/div>\n
After the statistics are collected, run the SELECT<\/code> query again.<\/p>\n
You’ll see that the execution plan changes to using only the airport.faa<\/code> index. This change is because the statistics are telling the optimizer that the faa<\/code> field has a better selectivity compared to the city<\/code> field. Therefore the airport.faa<\/code> index is the more efficient index for the query to use.<\/p>\n
<\/div>\n
\"Couchbase<\/p>\n
<\/div>\n
A Better JOIN with Cost-Based Optimization<\/h3>\n
Let\u2019s look at another cost-based query optimization example that illustrates how the cost-based optimizer chooses a better JOIN type between airport<\/code> and route<\/code>.<\/p>\n
First, we create an index to track the source airport:<\/p>\n
CREATE INDEX `def_route_sourceairport` ON `travel-sample`.`_default`.`route`(`sourceairport`)\r\n<\/pre>\n
<\/div>\n
And then we use the query below:<\/p>\n
SELECT DISTINCT route.destinationairport FROM airport JOIN route ON airport.faa = route.sourceairport;\r\n<\/pre>\n
<\/div>\n
With the old rules-based optimizer, the execution plan for the above query would look like:<\/p>\n
<\/div>\n
\"rules-based<\/p>\n
<\/div>\n
Note that the RBO execution plan above uses a nested loop to JOIN the airport Collection to the route Collection.<\/p>\n
Next, you need to enable cost-based optimization for the query. Since CBO is already enabled by default, all you have to do is to collect the statistics for the field in the Collection.<\/p>\n
UPDATE STATISTICS FOR route(sourceairport);\r\nUPDATE STATISTICS FOR airport(faa);\r\n<\/pre>\n
<\/div>\n
With cost-based optimization stats available on the two underlying fields for the indexes, the optimizer switches over to using a HASH<\/code> for the Collection JOIN.<\/p>\n
<\/div>\n
\"Couchbase<\/p>\n
<\/div>\n
Cost-Based Optimizer Troubleshooting in Couchbase<\/h2>\n
    \n
  1. Cost-based optimization is enabled by default in Couchbase 7.0. However, statistics need to be present on the specific fields in your Bucket\/Collections before the cost-based optimizer functions effectively.<\/li>\n
  2. You can disable cost-based optimization at the cluster level using the N1QL Feature Control field by adding 16 to the value. It is a set with x10 bit.<\/li>\n
  3. CBO statistics are not updated automatically by the Query Service. You should collect statistics on a regular basis, or after a significant change to your Collection content. I recommend collecting statistics after a large upload of data; otherwise, a weekly run should suffice.<\/li>\n
  4. Cost-based optimization is only considered for a query if the statistics are available for all of the indexes that were being considered by the optimizer<\/em>. You can collect the statistics for all the indexes on a given keyspace using the following:\n
    UPDATE STATISTICS FOR <collection> INDEX((SELECT RAW name from system:indexes \r\n    WHERE state='online' AND keyspace_id='<collection_name>' AND bucket_id='<bucket_name>' AND scope_id='<scope_name>'))\r\n<\/pre>\n<\/li>\n<\/ol>\n
    <\/div>\n
    More Resources<\/h2>\n
    To learn more about the cost-based optimizer for JSON \u2013 and the Couchbase 7.0 release in general \u2013 check out the following resources:<\/p>\n