N1QL in Couchbase has come a long way since it was first introduced in Couchbase Server 4.0. In Couchbase 5.0, things are taken to the next level in terms of performance. In terms of the March 2017 Developer build of Couchbase 5.0, there are performance enhancements to N1QL in the flavor of index projection, enhancements to COUNT and DISTINCT, and the much requested ORDER BY, LIMIT, and OFFSET operators.

So, what specifically was done to enhance all of these areas and how can we make use of the changes?

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:

CREATE INDEX idx ON default(type, firstname, lastname);

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:

SELECT firstname
FROM default
WHERE type = 'person'

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:

EXPLAIN SELECT firstname
FROM default
WHERE type = 'person'

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

...
"index_projection": {
    "entry_keys": [
        0,
        1
    ]
},
...

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

SELECT firstname
FROM default
WHERE type = 'person' AND lastname = 'Nic'

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

...
"index_projection": {
    "entry_keys": [
        0,
        1,
        2
    ]
},
...

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:

EXPLAIN SELECT COUNT(DISTINCT type)
FROM default;

In the results, you’ll notice that it is using IndexCountDistinctScan2 and 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:

EXPLAIN SELECT firstname
FROM default
WHERE type = 'person'
ORDER BY firstname
LIMIT 1
OFFSET 1;

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.