N1QL Performance and Feature Enhancements in Couchbase 5.0

With Couchbase 5.0 nearing stable release, it is a good idea to revisit some of the enhancements, both in performance and features, that are coming with the N1QL technology.

So what were some of the enhancements made on the subject of performance?

Performance Enhancements to N1QL

Let’s take index projection for example.  When creating an index, you can create one with any number of properties.  For example, take the following index:

The above statement will create a covering index on the default Bucket for the type, firstname, and lastname properties of any given document.

Now let’s say we created the following N1QL query to retrieve a few documents with the idx index we had created:

The above query would use the idx index and return only the firstname property for every document that matches.  The concept of querying this way is nothing new, however, what happens behind the scenes has changed.  You’ll notice that even though our index has many keys, we’re only interested in a subset, or in this case two keys.

So what is happening and why is this important?

In previous versions of Couchbase all keys of the index were taken into consideration regardless if only a subset were used.  As a result, more network, CPU, and memory were needed to accommodate what was happening.  Now this is not the case.

So how do you know index projection is happening?

Do an EXPLAIN on the query that you’re running:

In the results you should see something regarding index_projection that looks like the following:

The entry_keys property will change based on your query.  For example, what if we add one WHERE condition like so?:

In the above scenario, we would get an EXPLAIN result that looks like the following:

Now the above query wasn’t an index projection because we used all keys in our covering index.

Creating proper indexes paired with index projection can really help in overall performance and scaling your Couchbase Server cluster.

Index projection wasn’t the only performance enhancement made in the March 2017 build right?  That is correct, there is more!

Let’s take the COUNT(DISTINCT) operation for example.  Now let’s use that operation in the following query:

In the results you’ll notice that it is using IndexCountDistinctScan2 and what it is doing is storing all type in the index and processing the distinct values.  While it happens in the indexer in Couchbase 5.0, it previously happened in the N1QL service in prior editions.  By offloading this operation in the indexer, we can experience significant performance gains.

Similarly, take the OFFSET, LIMIT, and ORDER BY operators that can be used in N1QL queries.  Take the following query for example:

You’ll notice that the LIMIT, ORDER BY, and OFFSET operators will appear in the indexer.  Prior to 5.0, the LIMIT operator appeared in the indexer, but now the others do as well.  This is a huge win because in previous versions of Couchbase if you were to offset the results, N1QL would get all X number of results, and drop everything before the offset.

This brings us to the topic of N1QL and indexing feature enhancements.

Simplified Array Indexing

With Couchbase Server 4.5 came the array indexing. Take the following sample document for example:

New Array Index Syntax

Before Couchbase 5.0, to index the array elements found in social-media you had to write an index that looked something like the following:

In the above example, the FOR operator was necessary for array indexing.  In Couchbase Server 5.0 there is a much more simplified syntax.  The same index can be created via the following:

To make sure your index works, you can execute the following N1QL query:

When looking through the results of the EXPLAIN you should see that it is using the ism index that was previously created.

Now just because the simplified syntax exists doesn’t mean you can’t use the previous syntax when array indexing.  The following would be a perfect example of why the previous syntax would still be valid:

Notice that a WHEN operator was used when creating the ism_website index above.

More information on array indexing can be found here, along with other useful documentation on creating indexes in Couchbase.

Relaxed Variable Match Requirement for Array Indexes

In 4.x releases, the array indexing required usage of exactly same variable names in the SELECT query that were used in the CREATE INDEX statement. Find more details here.

For example, referring to the previous queries seen above, note that the variable media that is used to iterate through the array social-media is very important. Array indexing in 4.x mandates the exact variable name media to be used in the previous SELECT query.

Couchbase 5.0 release relaxes this requirement, and following query would work perfectly fine:

Note that, the index ism uses variable name media, but the query above uses m. Still, the above query can use the index ism successfully.

A Relaxed Whole Array Index Key Requirement for Array Indexes

Also recall, in 4.x releases, the covered array indexing requires the whole array attribute as a mandatory index-key in the array index definition.  For example, take the following query again:

The covered array index corresponding to the above query would be:

Note that, the second index key social-media is mandatory, for example to cover following query:

In Couchbase 5.0, this same query will be covered by index ism at the very start of this article.

It is important to note that, this feature brings lot of power to Array Indexes. Because, now each entry in the array index consumes less storage space and memory. It enables following benefits:

• Usage of covered array indexes for larger arrays
• Brings more efficiency and performance to queries using covered array indexes

Indexing Document Meta Information

Let’s take a turn here and discuss the new meta information that can be indexed.  Previously indexes could be created on the meta().id property, but now both the meta().cas and meta().expiration properties are supported.

So how do we create an index that uses the meta properties as keys?  It isn’t any different from what you already know.  Take the following covering indexes for example:

Now if I wanted to use the idx_cas and idx_exp indexes in a N1QL query, I could do something like the following:

So why would being able to index these properties?  Well, what if you wanted to do a query for all documents that had expired today or all documents that were modified today?

For more information on the CAS and expiration properties, visit here.  For more information on indexing or using N1QL with Couchbase, check out the Couchbase Developer Portal.