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:
- Want to avoid reading the index creation rules, understand them, and implement them to find the appropriate indexes for your query/queries/workload.
- Want to continue using Couchbase Community Edition.
- Are using an older Couchbase version(version 5.5,6.0) 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.
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
|
1 2 3 4 |
ADVISE SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 INNER JOIN bucket_04 t_4 ON ( t_1.primary_key_id = t_4.primary_key_id ) INNER JOIN bucket_04 t_5 ON ( t_1.primary_key_id = t_5.primary_key_id ); |
that used to give
|
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 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_4" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`()", "keyspace_alias": "bucket_04_t_5" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_4", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_01`(`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 6. derived join filter as leading key." } ] } } }, "query": "SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1\nFROM bucket_01 t_1\nINNER JOIN bucket_04 t_4\nON ( t_1.primary_key_id = t_4.primary_key_id )\nINNER JOIN bucket_04 t_5\nON ( t_1.primary_key_id = t_5.primary_key_id );" } ] } |
This query failed to give covering index recommendation for bucket_01.
now gives
|
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 40 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id_int_field1_bool_field1_decimal_field1 ON `bucket_01`(`primary_key_id`,`int_field1`,`bool_field1`,`decimal_field1`)", "keyspace_alias": "bucket_01_t_1" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_4;bucket_04_t_5" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_01`(`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 6. derived join filter as leading key." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_4;bucket_04_t_5", "recommending_rule": { "bucket_04_t_4": "Index keys follow order of predicate types: 9. non-static join predicate.", "bucket_04_t_5": "Index keys follow order of predicate types: 9. non-static join predicate." } } ] } } }, "query": "SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 INNER JOIN bucket_04 t_4 ON ( t_1.primary_key_id = t_4.primary_key_id ) INNER JOIN bucket_04 t_5 ON ( t_1.primary_key_id = t_5.primary_key_id );" } ] } |
2. Advise sometimes returns an empty index recommendation
A query like:
|
1 2 3 4 5 6 7 |
ADVISE SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 LEFT JOIN bucket_05 t_1SiRAdlidCB ON ( t_1.primary_key_id = t_1SiRAdlidCB.primary_key_id ) LEFT JOIN bucket_010 t_1fLjtGGSnKf ON ( t_1.primary_key_id = t_1fLjtGGSnKf.primary_key_id ) INNER JOIN bucket_04 t_1GHMwUzdukS ON ( t_1.primary_key_id = t_1GHMwUzdukS.primary_key_id ) INNER JOIN bucket_04 t_1LjVrlSNfDc ON ( t_1.primary_key_id = t_1LjVrlSNfDc.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL)); |
used to give
|
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_1SiRAdlidCB" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_1fLjtGGSnKf" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_1GHMwUzdukS" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`()", "keyspace_alias": "bucket_04_t_1LjVrlSNfDc" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing), 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_1SiRAdlidCB", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_1fLjtGGSnKf", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_1GHMwUzdukS", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." } ] } } }, "query": "SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 LEFT JOIN bucket_05 t_1SiRAdlidCB ON ( t_1.primary_key_id = t_1SiRAdlidCB.primary_key_id ) LEFT JOIN bucket_010 t_1fLjtGGSnKf ON ( t_1.primary_key_id = t_1fLjtGGSnKf.primary_key_id ) INNER JOIN bucket_04 t_1GHMwUzdukS ON ( t_1.primary_key_id = t_1GHMwUzdukS.primary_key_id ) INNER JOIN bucket_04 t_1LjVrlSNfDc ON ( t_1.primary_key_id = t_1LjVrlSNfDc.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));" } ] } |
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
|
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_1SiRAdlidCB" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_1fLjtGGSnKf" }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_1GHMwUzdukS;bucket_04_t_1LjVrlSNfDc" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_1fLjtGGSnKf", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_1GHMwUzdukS;bucket_04_t_1LjVrlSNfDc", "recommending_rule": { "bucket_04_t_1GHMwUzdukS": "Index keys follow order of predicate types: 9. non-static join predicate.", "bucket_04_t_1LjVrlSNfDc": "Index keys follow order of predicate types: 9. non-static join predicate." } }, { "index_statement": "CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing), 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_1SiRAdlidCB", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." } ] } } }, "query": "SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 LEFT JOIN bucket_05 t_1SiRAdlidCB ON ( t_1.primary_key_id = t_1SiRAdlidCB.primary_key_id ) LEFT JOIN bucket_010 t_1fLjtGGSnKf ON ( t_1.primary_key_id = t_1fLjtGGSnKf.primary_key_id ) INNER JOIN bucket_04 t_1GHMwUzdukS ON ( t_1.primary_key_id = t_1GHMwUzdukS.primary_key_id ) INNER JOIN bucket_04 t_1LjVrlSNfDc ON ( t_1.primary_key_id = t_1LjVrlSNfDc.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));" } ] } |
3. Should give recommendation with desc sort on field
A query like
|
1 |
ADVISE select country from `default` where pro_account=true and country = "USA" order by country DESC |
used to give
|
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 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "<span style="color: #ff0000;" data-mce-style="color: #ff0000;">index_statement": "CREATE INDEX adv_pro_account_country ON `default`(`pro_account`,`country`)",</span> "keyspace_alias": "default" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_pro_account_country ON `default`(`pro_account`,`country`)", "keyspace_alias": "default", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } ] } } }, "query": "select country from default where pro_account=true and country = \"USA\" order by country DESC" } ] } |
now gives:
|
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 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_countryDESC_pro_account ON `default`(`country` DESC,`pro_account`)",</span> "keyspace_alias": "default" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_country_pro_account ON `default`(`country`,`pro_account`)", "keyspace_alias": "default", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } ] } } }, "query": "select country from default where pro_account=true and country = \"USA\" order by country DESC" } ] } |
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:
|
1 2 3 4 5 6 7 |
ADVISE SELECT * FROM bucket_01 t_1 LEFT JOIN bucket_05 t_2 ON ( t_1.primary_key_id = t_2.primary_key_id ) LEFT JOIN bucket_010 t_3 ON ( t_1.primary_key_id = t_3.primary_key_id ) INNER JOIN bucket_04 t_4 ON ( t_1.primary_key_id = t_4.primary_key_id ) INNER JOIN bucket_04 t_5 ON ( t_1.primary_key_id = t_5.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL)); |
used to give
|
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 40 41 42 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_3", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": "bucket_04_t_4", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing), 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_2", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." } ] } } }, "query": "SELECT * FROM bucket_01 t_1 LEFT JOIN bucket_05 t_2 ON ( t_1.primary_key_id = t_2.primary_key_id ) LEFT JOIN bucket_010 t_3 ON ( t_1.primary_key_id = t_3.primary_key_id ) INNER JOIN bucket_04 t_4 ON ( t_1.primary_key_id = t_4.primary_key_id ) INNER JOIN bucket_04 t_5 ON ( t_1.primary_key_id = t_5.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));" } ] } |
now gives:
|
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 40 41 42 43 44 45 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing), 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)", "keyspace_alias": "bucket_01_t_1", "recommending_rule": "Index keys follow order of predicate types: 7. not null/not missing/valued." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)", "keyspace_alias": "bucket_05_t_2", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)", "keyspace_alias": "bucket_010_t_3", "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate." }, { "index_statement": "CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)", "keyspace_alias": <span style="color: #0000ff;" data-mce-style="color: #0000ff;">"bucket_04_t_4;bucket_04_t_5"</span>, "recommending_rule": { "bucket_04_t_4": "Index keys follow order of predicate types: 9. non-static join predicate.", "bucket_04_t_5": "Index keys follow order of predicate types: 9. non-static join predicate." } } ] } } }, "query": "SELECT * \nFROM bucket_01 t_1 \nLEFT JOIN bucket_05 t_2 ON ( t_1.primary_key_id = t_2.primary_key_id ) \nLEFT JOIN bucket_010 t_3 ON ( t_1.primary_key_id = t_3.primary_key_id ) \nINNER JOIN bucket_04 t_4 ON ( t_1.primary_key_id = t_4.primary_key_id ) \nINNER JOIN bucket_04 t_5 ON ( t_1.primary_key_id = t_5.primary_key_id ) \nWHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));" } ] } |
5. Index advisor returns wrong index recommendation on an unnest query
A query like:
|
1 2 3 |
advise SELECT meta(t).id _id,t.type,branch.branchId FROM test AS t UNNEST t.branches AS branch WHERE t.type is valued AND branch.branchId > (t.currentBranch-2) order by branch.lastUpdateOn |
used to give:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_ALL_branches_type_2 ON `test`(<span style="color: #ff0000;" data-mce-style="color: #ff0000;">ALL `branches`,`type`,2</span>)", "keyspace_alias": "test_t", "recommending_rule": "Index keys follow order of predicate types: 1. leading array index for unnest, 7. not null/not missing/valued, 9. non-static join predicate." } ] } } }, "query": "SELECT meta(t).id _id,t.type,branch.branchId FROM test AS t UNNEST t.branches AS branch WHERE t.type is valued AND branch.branchId > (t.currentBranch-2) order by branch.lastUpdateOn" } ] } |
now gives
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_ALL_branches_branchId_type ON `test`(<span style="color: #0000ff;" data-mce-style="color: #0000ff;">ALL ARRAY `branch`.`branchId` FOR branch IN `branches` END,`type`</span>)", "keyspace_alias": "test_t", "recommending_rule": "Index keys follow order of predicate types: 1. leading array index for unnest, 7. not null/not missing/valued." } ] } } }, "query": "SELECT meta(t).id _id,t.type,branch.branchId\nFROM test AS t\nUNNEST t.branches AS branch\nWHERE t.type is valued AND branch.branchId > (t.currentBranch-2) order by branch.lastUpdateOn" } ] } |
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://www.couchbase.com/blog/index-advisor-service/
https://www.couchbase.com/blog/index-advisor-service-for-couchbase-n1qlfeb-refresh/
https://www.couchbase.com/blog/index-advisor-service-for-n1ql-march-refresh/
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
English