We are excited to announce the release of :

Couchbase Index Advisor Service

http://index-advisor.couchbase.com

Yet Another service? Why?

We released N1QL(SQL for JSON) a few years ago- so that you could use SQL to retrieve and manipulate JSON data. But then unless good indexes are created- the queries don’t perform as well! How would you know if the index you created is a good enough index for our query engine? So, we decided to put out all the rules that make a good index. But that involved too much reading! So we are going one step further to empower you. We are now releasing a service –Index Advisor-that accepts a query and gives out an index recommendation that would meet the expectations of our query engine-all without downloading the latest Couchbase server.

For Who?

This service will provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.

Index Advisor is released as part of Couchbase Server 6.5. If you have downloaded that version, then you have everything (and more) than what this service provides.

When should you use this service?

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.  Do not want to download the latest Couchbase 6.5 server yet.
  3.  Are using an older Couchbase version 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.

Background:

N1QL is the SQL  for JSON data and metadata. Every query written in N1QL has a query plan prepared by the N1QL query engine. The performance and efficiency of a query depend on its plan. Creation of right indexes for the data helps in selection of those indexes which can retrieve the result set in the most efficient manner. Even though JSON document itself has no schema, the index needs to have a schema.

For e.g index ix1(field1,field2) is different from index ix2(field2,field1).

So not only the fields we select for an index are important, the order in which they are present in an index is also important for GSI indexes. (FTS index -we shall cover in another blog.)

Life of a Query:

Screen Shot 2016-12-12 at 2.10.40 PM.png

 

Depending on how good step 4 is- we can minimize/completely skip steps 5 and 6 and thereby improve the performance of the query.So deciding fields in an index is a very important part. To help with this we are releasing Index Advisor.

http://index-advisor.couchbase.com

Index Advisor – the service!

With Index Advisor, you can

  1. Provide a query or multiple queries from even a server older than 6.5 and the service will recommend the indexes you should have/create to get the best performance.
  2. Since we do not have your data or schema, we are not able to see if you currently have those indexes. We will soon be improving this interface to accept data/statistics/schema/infer op/current indexes – or whatever else you can provide for us to make better recommendations.

Once you give us the query with ADVISE directive, the tool will give the recommendations for minimal index and covering index.

  • Indexes: This section lists the recommended indexes based on predicates in WHERE/ON clause, along with the corresponding recommendation rule that each index follows.
  • Covering Indexes: This section lists the covering indexes applicable to the input query i.e. an index that includes all fields referenced in the query to avoid the extra hop to data service.

Here is the look and feel of the tool. Click on the gif below to see it clearly.

Examples:

1.

2.

3.

4.

 

Result

 

The index candidates are generated following the design rules specified here.

What remains to be done:

1. Improve UI for this tool. Provide index recommendations in text for easier cut and paste. It currently provides a JSON output.

2. Accept flavors- so we can generate partial index recommendations.

3. Provide a way to let the user enter schema, existing indexes, infer output, maybe even test data.

We would love to hear from you on how you liked the tool, any additional features you would like to see. Please share your feedback via the comments.

More about the Index Advisor feature:

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

Summary:

The index advisor (ADVISE statement) provides index recommendations for a single query. It advises regular index,  array index, and covering index and provides information on the corresponding recommendation rule that each index key follows.

The index advisor (ADVISOR statement) provides index recommendations for multiple queries. It recommends as few indexes as possible that would be suitable for all the queries in the array.

Do try this at home!

http://index-advisor.couchbase.com

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