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:
- Want to avoid reading the index creation rules, understand them, and implement them to find the appropriate indexes for your query/queries/workload.
- Do not want to download the latest Couchbase 6.5 server yet.
- Are using an older Couchbase version and need help creating the right indexes for your queries.
- 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:
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
- 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.
- 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.
|
1 |
ADVISE SELECT fname, age, age/7 AS age_dog_years FROM trial WHERE fname = 'Sara' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": [ { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_fname_age ON `trial`(`fname`,`age`)", "keyspace_alias": "trial" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_fname ON `trial`(`fname`)", "keyspace_alias": "trial", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } ] } } ] }, "query": "SELECT fname, age, age/7 AS age_dog_years FROM trial WHERE fname = 'Sara'" } ] } |
2.
|
1 2 |
ADVISE SELECT (DISTINCT purchases.customerId)FROM purchases WHERE purchases.purchasedAt BETWEEN "2014-03-01" AND "2014-03-31" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": [ { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_purchasedAt ON `purchases`(`purchasedAt`)", "keyspace_alias": "purchases", "recommending_rule": "Index keys follow order of predicate types: 4. not less than/between/not greater than." } ] } } ] }, "query": "SELECT (DISTINCT purchases.customerId) \nFROM purchases\nWHERE purchases.purchasedAt BETWEEN \"2014-03-01\" AND \"2014-03-31\"" } ] } |
3.
|
1 2 3 4 |
ADVISE SELECT DISTINCT airline.name,airport.name AS airport,route.distance FROM `travel-sample` airport INNER JOIN `travel-sample` route ON airport.faa = route.sourceairport AND route.type = "route" INNER JOIN `travel-sample` airline ON route.airline = airline.iata AND airline.type = "airline" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": [ { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_type_sourceairport_airline_distance ON `travel-sample`(`type`,`sourceairport`,`airline`,`distance`)", "keyspace_alias": "travel-sample_route" }, { "index_statement": "CREATE INDEX adv_type_iata_name ON `travel-sample`(`type`,`iata`,`name`)", "keyspace_alias": "travel-sample_airline" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_type_sourceairport ON `travel-sample`(`type`,`sourceairport`)", "keyspace_alias": "travel-sample_route", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 10. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_type_iata ON `travel-sample`(`type`,`iata`)", "keyspace_alias": "travel-sample_airline", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 10. non-static join predicate." } ] } } ] }, "query": "SELECT DISTINCT airline.name,airport.name AS airport,route.distance FROM `travel-sample` airport INNER JOIN `travel-sample` route ON airport.faa = route.sourceairport AND route.type = \"route\"\nINNER JOIN `travel-sample` airline ON route.airline = airline.iata AND airline.type = \"airline\"\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\";" } ] } |
4.
|
1 2 3 |
SELECT ADVISOR(["SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'", "SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city WHERE h.type = 'hotel' AND a.type = 'airport'"]) |
Result
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
{ "results": [ { "$1": { "recommended_indexes": [ { "index": "CREATE INDEX adv_type_city ON `travel-sample`(`type`,`city`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'" }, { "run_count": 1, "statement": "SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city WHERE h.type = 'hotel' AND a.type = 'airport'" } ] } ] } } ] } |
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://www.couchbase.com/blog/n1ql-index-advisor-improve-query-performance-and-productivity/
https://www.couchbase.com/blog/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
