This is our March drop for Index Advisor service for N1QL after fixing some of the bugs that were found after our last refresh in Feb. 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 will now be 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 will provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.

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.  Do not want to download the latest Couchbase 6.5 server yet.
  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.Index Advisor to support TTL (meta().expiration & meta.cas)

With this improvement, a query like

that used to give

now gives

or

that used to give:

 

now gives

 

2. Advise to adjust the order of index keys for functional and array predicates

This defect slightly changed the rules we had before:

  1. Delete the rule of functional index keys.
  2. Add rule “like” in the same position for scenario ” a like “%adv%”.This rule is applicable only when the pattern is of the type “%x”.If it is of the type “x%” the rule does not apply.
  3. Index Keys from functional predicates follow the order of EQ/in/LE/LT

A query like:

used to give

now gives

3. Adjust the order of array index keys on its SATISFIES condition

  1. Put array predicates in the order of SATISFIES condition.
  2. For disjunction in SATISFIES: get the common terms -> pick the lowest priority one -> (EQ<IN<LE<LT<IS NOT NULL< LIKE)
  3. For conjunction in SATISFIES: pick the lowest priority one.

The new rules after fix#2 and #3:

1: leading array index for unnest,
2: equality/null/missing,
3: in,
4: not less than/between/not greater than,
5: less than/greater than,
6: derived join filter as leading key,
7: not null/not missing/valued,
8: like,
9: non-static join predicate,
10:flavor for partial index,

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/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