Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

ADVISE

  • reference
    +
    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;"
  }
]