ADVISE
- Enterprise Edition
- Beta
The ADVISE statement provides index recommendations to optimize query response time.
This is a pre-release Beta feature. Beta features may have some rough edges and bugs, and may change significantly before the final GA release. Note that this feature is available in Couchbase Server Enterprise Edition. You do not need to activate Developer Preview Mode to use this feature. You can obtain support for this feature by participating in a forum or submitting a ticket. Refer to Contact Couchbase for more information. |
Purpose
The ADVISE statement invokes the index advisor to provide index recommendations for a single query. You can use the ADVISE statement with any of the following types of query:
The index advisor can recommend regular secondary indexes, partial indexes, and array indexes for the following predicates and conditions:
-
Predicates in the WHERE clause
-
Join conditions in the ON clause for INDEX JOIN, ANSI JOIN, ANSI NEST, INDEX NEST, and ANSI MERGE operations
-
Predicates of elements in an UNNEST array
-
Predicates with the ANY expression
-
Predicates of subqueries in the FROM clause
The index advisor also suggests covering indexes and covering array indexes for queries in a single keyspace, including JOIN operations, ANY expressions, and UNNEST predicates.
The index advisor checks the indexes currently used by the query. If the query is already using the recommended indexes, the index advisor informs you, and does not recommend an index unnecessarily. Similarly, if the query is already using the optimal covering index, the index advisor informs you, and does not recommend a covering index.
Prerequisites
To execute the ADVISE statement, you must have the privileges required for the N1QL statement for which you want advice. For more details about user roles, see Authorization.
Usage
If you run the ADVISE statement in the Query Workbench, you can use the Table, JSON, or Tree link to see the result, just like any other query. You can also use the Advice link in the Query Workbench to see the result of the ADVISE statement in graphical format.
Return Value
The ADVISE statement returns an object with the following properties.
Name | Description | Schema |
---|---|---|
#operator |
The name of the operator — in this case, |
string |
advice |
An object giving advice returned by the operator. |
|
query |
The N1QL query used to generate the advice. |
string |
Advice
Name | Description | Schema |
---|---|---|
#operator |
The name of the operator — in this case, |
string |
adviseinfo |
An array of objects giving index information. |
< Information > array |
Information
Name | Description | Schema |
---|---|---|
current_indexes |
An array of Index objects, each giving information about one of the indexes (primary or secondary) that is currently used by the query. |
< Indexes > array |
recommended_indexes |
If the index advisor recommends any indexes, this is an object giving information about the recommended indexes. If the index advisor cannot recommend any indexes, this is a string stating that there are no recommended indexes at this time. |
Recommended Indexes
Name | Description | Schema |
---|---|---|
covering_indexes |
If there are any recommended covering indexes, this is an array of Index objects, each giving information about one of the recommended covering indexes. If there are no recommended covering indexes, this field does not appear. |
< Indexes > array |
indexes |
An array of Index objects, each giving information about one of the recommended secondary indexes. |
< Indexes > array |
Indexes
Name | Description | Schema |
---|---|---|
index_statement |
The N1QL command used to define the index. |
string |
keyspace_alias |
The keyspace to which the index belongs. If the query specifies an alias for this keyspace, the alias is appended to the keyspace name, joined by an underscore. This may help to distinguish the indexes for either side of a JOIN operation. |
string |
index_status |
Information on the status of the index, stating whether the index is identical to the recommended index, or whether the index is an optimal covering index. This field is only returned for current indexes. If the index is not identical to the recommended index, or if it is not an optimal covering index, this field does not appear. |
string |
recommending_rule |
The rules used to generate the recommendation. This field is only returned for recommended indexes, or for current indexes if they are identical to the recommended index. |
string |
Recommendation Rules
The index advisor recommends secondary indexes based on the query predicate, according to the following rules. The rules are listed below in priority order. Within each recommended index, if there is more than one index key, they are sorted according to the priority order of these rules.
Rule | Description | Recommendation |
---|---|---|
|
The query uses a predicate which applies to individual elements in an unnested array. Example: |
An array index, where the leading index key is an array expression indexing all elements in the unnested array. |
|
The query has a predicate with an equality, IS NULL, or IS MISSING expression. Example: |
A secondary index, where one index key is the field referenced by the predicate expression. |
|
The query has a predicate with an IN expression. Example: |
A secondary index, where one index key is the field referenced by the predicate expression. |
|
A secondary index, where one index key is the field referenced by the predicate expression. |
|
|
A secondary index, where one index key is the field referenced by the predicate expression. |
|
|
The query has a predicate with an ANY expression. Example: |
An array index, where the index key is an array expression recursively indexing all elements referenced by the predicate expression. |
|
The query has a join using an ON clause which filters on the left-hand side keyspace. Example: |
A secondary index, where the leading index key is the field from the left-hand side keyspace in the ON clause. |
|
The query has a predicate with IS NOT NULL, IS NOT MISSING, or IS NOT VALUED. Example: |
A secondary index, where one index key is the field referenced by the predicate expression. |
|
The query contains an indexable function. Example: |
A functional index, where the index key contains the function referenced by the predicate expression. |
|
The query has a join using an ON clause in which neither the left-hand side source object nor the right-hand side source object is static. Example: |
A secondary index, where one index key is the field from the right-hand side keyspace in the ON clause. |
|
The query includes filters on a particular flavor of document. Example: |
A partial index for that flavor of document. |
Index Names
The index advisor suggests a name for each index it recommends, starting with adv_
, followed by the DISTINCT
or ALL
keyword for array indexes if applicable, and including the names of the fields referenced in the index definition, separated by underscores — for example, adv_city_type_name
.
Some field names may be truncated if they are too long.
The names that the index advisor suggests are not guaranteed to be unique. You should check the suggested index names and change any that are duplicates. |
Examples
ADVISE SELECT * FROM `travel-sample` a WHERE a.type = 'hotel' AND a.country = 'France';
[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": [
{
"current_indexes": [
{
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
"keyspace_alias": "travel-sample_a"
}
],
"recommended_indexes": {
"indexes": [
{
"index_statement": "CREATE INDEX adv_country_type ON `travel-sample`(`country`) WHERE `type` = 'hotel'",
"keyspace_alias": "travel-sample_a",
"recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 11. flavor for partial index."
}
]
}
}
]
},
"query": "SELECT * FROM `travel-sample` a WHERE a.type = 'hotel' AND a.country = 'France';"
}
]
ADVISE SELECT airportname FROM `travel-sample` WHERE type = 'airport' AND geo.alt NOT BETWEEN 0 AND 100;
[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": [
{
"current_indexes": [
{
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
"keyspace_alias": "travel-sample"
}
],
"recommended_indexes": {
"covering_indexes": [
{
"index_statement": "CREATE INDEX adv_geo_alt_type_airportname ON `travel-sample`(`geo`.`alt`,`airportname`) WHERE `type` = 'airport'",
"keyspace_alias": "travel-sample"
}
],
"indexes": [
{
"index_statement": "CREATE INDEX adv_geo_alt_type ON `travel-sample`(`geo`.`alt`) WHERE `type` = 'airport'",
"keyspace_alias": "travel-sample",
"recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (5. less than/between/greater than), 11. flavor for partial index."
}
]
}
}
]
},
"query": "SELECT airportname FROM `travel-sample` WHERE type = 'airport' AND geo.alt NOT BETWEEN 0 AND 100;"
}
]
ADVISE SELECT * FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';
[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": [
{
"current_indexes": [
{
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
"index_status": "SAME TO THE INDEX WE CAN RECOMMEND",
"keyspace_alias": "travel-sample",
"recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (4. not less than/between/not greater than)."
}
],
"recommended_indexes": "No index recommendation at this time."
}
]
},
"query": "SELECT * FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';"
}
]
ADVISE SELECT type FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';
[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": [
{
"current_indexes": [
{
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
"index_status": "THIS IS AN OPTIMAL COVERING INDEX.",
"keyspace_alias": "travel-sample"
}
],
"recommended_indexes": "No index recommendation at this time."
}
]
},
"query": "SELECT type FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';"
}
]
ADVISE SELECT * FROM `travel-sample` LIMIT 5;
[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": [
{
"current_indexes": [
{
"index_statement": "CREATE PRIMARY INDEX def_primary ON `travel-sample`",
"keyspace_alias": "travel-sample"
}
],
"recommended_indexes": "No index recommendation at this time."
}
]
},
"query": "SELECT * FROM `travel-sample` LIMIT 5;"
}
]
Related Links
-
The Index Advisor in the Query Workbench
-
The ADVISOR function
-
Blog post: Index Advisor for N1QL Query Statement