This is our June drop for Index Advisor service for N1QL after fixing some of the bugs that were found after our last refresh in May. We plan to keep improvising the Index Advisor service(What is it?). The fixes in Index Advisor that you would normally see in the next maintenance release are now available to you every month.

Who is it for? and When should you use it?

http://index-advisor.couchbase.com/

At the cost of repeating myself,

This service provides index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.Index Advisor is available as part of Enterprise Edition but this service is available to you for free!

You will find this service useful if you:

  1. Want to avoid reading the index creation rules, understand them, and implement them to find the appropriate indexes for your query/queries/workload.
  2.  Want to continue using Couchbase Community Edition.
  3.  Are using an older Couchbase version(version 5.5,6.0) and need help creating the right indexes for your queries.
  4. Want to generate advice for indexes without creating a bucket or uploading the schema or data.

Whats new?

Even if you have the Couchbase Server 6.5 server downloaded and can use Index Advisor from Query Workbench, this index Advisor is a more recent version(without a great looking UI) but with the following defects fixed.

1. Advise fails to give covering index for join operation.

With this improvement, a query like

that used to give

This query failed to give covering index recommendation for bucket_01.

now gives

 

2. Advise sometimes returns an empty index recommendation

A query like:

 

used to give

There is one blank index being recommended, plus it says those indexes are covering but they do not include fields from the predicate.

now gives

3. Should give recommendation with desc sort on field

A query like

used to give

 

now gives:

4. Index advisor to combine information for duplicate index definition

We remove duplicate index definition for one query.
But there are some cases duplicate index may apply to different keyspace_alias with different recommending rules.

A query like:

used to give

now gives:

5. Index advisor returns wrong index recommendation on an unnest query

A query like:

used to give:

 

now gives

Give it a try and any problems you see with Indexes recommended by Index Advisor Service, you can add them as a comment to this blog

More about the Index Advisor feature:

https://blog.couchbase.com/index-advisor-service/

https://blog.couchbase.com/index-advisor-service-for-couchbase-n1qlfeb-refresh/

https://blog.couchbase.com/index-advisor-service-for-n1ql-march-refresh/

https://blog.couchbase.com/n1ql-index-advisor-improve-query-performance-and-productivity/

https://blog.couchbase.com/index-advisor-for-query-workload/

https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/advise.html

https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/advisor.html

https://docs.couchbase.com/server/6.5/tools/query-workbench.html#index-advisor

Posted by Kamini Jagtiani

Kamini Jagtiani is a Senior Engineering Manager for the Query Team at Couchbase R&D. Before Couchbase, Kamini was 7 years at Futurewei as Kernel Architect/Manager and 13 years at IBM Informix as Software Engineer. Kamini has a Bachelors's degree in Computer Science and Engineering from Bombay University, India and holds 5 US patents.

Leave a reply