ADVISE

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.

Syntax

advise-index ::= ADVISE [ INDEX ] ( select | update | delete | merge )
'ADVISE' 'INDEX'? ( select | update | delete | merge )

The statement consists of the ADVISE keyword, and optionally the INDEX keyword, followed by the query for which you want index advice.

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
required

The name of the operator — in this case, Advise.

string

advice
required

An object giving advice returned by the operator.

Advice

query
required

The N1QL query used to generate the advice.

string

Advice

Name Description Schema

#operator
required

The name of the operator — in this case, IndexAdvice.

string

adviseinfo
required

An array of objects giving index information.

< Information > array

Information

Name Description Schema

current_indexes
required

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
required

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
optional

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
required

An array of Index objects, each giving information about one of the recommended secondary indexes.

< Indexes > array

Indexes

Name Description Schema

index_statement
required

The N1QL command used to define the index.

string

keyspace_alias
required

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
optional

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
optional

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

  1. Leading array index for UNNEST

The query uses a predicate which applies to individual elements in an unnested array.

Example: UNNEST schedule AS x WHERE x.day = 1

An array index, where the leading index key is an array expression indexing all elements in the unnested array.

  1. Equality / NULL / MISSING

The query has a predicate with an equality, IS NULL, or IS MISSING expression.

Example: WHERE id = 10

A secondary index, where one index key is the field referenced by the predicate expression.

  1. IN predicates

The query has a predicate with an IN expression.

Example: WHERE id IN [10, 20]

A secondary index, where one index key is the field referenced by the predicate expression.

  1. Not less than / between / not greater than

The query has a predicate with a <=, BETWEEN, or >= expression.

Example: WHERE id >= 10

A secondary index, where one index key is the field referenced by the predicate expression.

  1. Less than / between / greater than

The query has a predicate with a <, BETWEEN, or > expression.

Example: WHERE id BETWEEN 10 AND 25

A secondary index, where one index key is the field referenced by the predicate expression.

  1. Array predicate

The query has a predicate with an ANY expression.

Example: WHERE ANY v IN schedule SATISFIES v.utc > "19:00" END

An array index, where the index key is an array expression recursively indexing all elements referenced by the predicate expression.

  1. Derived join filter as leading key

The query has a join using an ON clause which filters on the left-hand side keyspace.

Example: FROM `travel-sample` r JOIN `travel-sample` a ON r.airlineid = META(a).id

A secondary index, where the leading index key is the field from the left-hand side keyspace in the ON clause.

  1. IS NOT NULL / MISSING / VALUED predicates

The query has a predicate with IS NOT NULL, IS NOT MISSING, or IS NOT VALUED.

Example: WHERE id IS NOT NULL

A secondary index, where one index key is the field referenced by the predicate expression.

  1. Functional predicates

The query contains an indexable function.

Example: WHERE LOWER(name) = "john"

A functional index, where the index key contains the function referenced by the predicate expression.

  1. Non-static join predicate

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: FROM `travel-sample` r JOIN `travel-sample` a ON r.airline = a.iata

A secondary index, where one index key is the field from the right-hand side keyspace in the ON clause.

  1. Flavor for partial index

The query includes filters on a particular flavor of document.

Example: WHERE type = "hotel"

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

Example 1. Recommended index only
ADVISE SELECT * FROM `travel-sample` a WHERE a.type = 'hotel' AND a.country = 'France';
Result
[
  {
    "#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';"
  }
]
Example 2. Recommended index and covering index
ADVISE SELECT airportname FROM `travel-sample` WHERE type = 'airport' AND geo.alt NOT BETWEEN 0 AND 100;
Result
[
  {
    "#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;"
  }
]
Example 3. Current index is identical to the recommended index
ADVISE SELECT * FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';
Result
[
  {
    "#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%';"
  }
]
Example 4. Current index is an optimal covering index
ADVISE SELECT type FROM `travel-sample` WHERE type LIKE 'air%'or type LIKE 'rou%';
Result
[
  {
    "#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%';"
  }
]
Example 5. No index can be recommended
ADVISE SELECT * FROM `travel-sample` LIMIT 5;
Result
[
  {
    "#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;"
  }
]