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.

ADVISE

  • Capella Operational
  • reference
    +
    The ADVISE statement provides index recommendations to optimize query response time.

    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 SQL++ statement for which you want advice. For more details about user roles, see Authorization.

    Syntax

    advise ::= 'ADVISE' 'INDEX'? ( select | update | delete | merge )
    Syntax diagram: refer to source code listing

    The statement consists of the ADVISE keyword, and optionally the INDEX keyword, followed by the query for which you want index advice — a SELECT query, an UPDATE query, a DELETE query, or a MERGE query.

    Usage

    When you run an ADVISE statement in the Query tab, 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 tab 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 SQL++ 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 object giving index information.

    Information

    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 SQL++ 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_property
    optional

    The index pushdowns supported by the index.

    This field is only returned for covering indexes. If no index pushdowns are supported by the covering index, this field does not appear.

    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

    update_statistics
    optional

    The SQL++ command recommended for updating statistics on the index, for use by the cost-based optimizer.

    This field is only returned for indexes which are recommended by the cost-based optimizer, and only if optimizer statistics are missing for the index.

    string

    Recommendation Rules

    The index advisor recommends secondary indexes based on the query predicate.

    In Couchbase Capella, the index advisor initially makes use of the cost-based optimizer. To do this, the cost-based optimizer must be enabled, and statistics for the keyspace must already exist. If these prerequisites are met, the cost-based optimizer analyzes the query predicate and attempts to recommend an index.

    If the cost-based optimizer cannot recommend an index, the index advisor falls back on a rules-based approach. 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.

    Examples
    WHERE ANY v IN schedule
SATISFIES v.utc = "19:00" END

WHERE LOWER(name) = "john"

WHERE id = 10

    If the predicate contains an ANY expression: an array index, where the index key is an array expression recursively indexing all elements referenced by the predicate expression.

    If the predicate contains an indexable function: a functional index, where the index key contains the function referenced by the predicate expression.

    Otherwise: 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.

    Examples
    WHERE ANY v IN schedule
SATISFIES v.utc
IN ["19:00", "20:00"] END

WHERE LOWER(name)
IN ["jo", "john"]

WHERE id IN [10, 20]

    Refer to Rule 2.

    1. Not less than / between / not greater than

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

    Examples
    WHERE ANY v IN schedule
SATISFIES v.utc BETWEEN
"19:00" AND "20:00" END

WHERE LOWER(name)
BETWEEN "jo" AND "john"

WHERE id BETWEEN 10 AND 25

    Refer to Rule 2.

    1. Less than / greater than

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

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

WHERE LOWER(name) > "jo"

WHERE id > 10 AND id < 25

    Refer to Rule 2.

    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 route r JOIN airline 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.

    Examples
    WHERE ANY v IN schedule
SATISFIES v.utc IS NOT NULL
END

WHERE LOWER(name) IS NOT NULL

WHERE id IS NOT NULL

    Refer to Rule 2.

    1. LIKE predicates

    The query has a predicate with a LIKE expression, where there is a % wildcard at the start of the match string.

    Example
    WHERE name LIKE "%base"

    A secondary index, where one index key is the field 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 route r JOIN airline 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.

    Pushdown Properties

    The index advisor optimizes any covering indexes that it recommends, in order to support the following pushdowns:

    • LIMIT pushdown

    • OFFSET pushdown

    • ORDER pushdown

    • Partial GROUP BY and aggregates pushdown

    • Full GROUP BY and aggregates pushdown

    The GROUP BY and aggregate pushdowns support aggregation using the MIN(), MAX(), SUM(), COUNTN(), and AVG() functions, with the DISTINCT aggregate quantifier if necessary.

    The GROUP BY and aggregate pushdowns may be full or partial. Full pushdown means the indexer handles the group aggregation fully, and the query engine can skip the entire operator. Partial pushdown means the indexer sends part of the group aggregation to the query, and the query engine merges the intermediate groups to create the final group and aggregation.

    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

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Example 1. Recommended index only

    Assume that the cost-based optimizer is enabled, and that statistics exist for the keyspace.

    ADVISE SELECT * FROM hotel a WHERE a.country = 'France';
    Result
    [
  {
    "#operator": "Advise",
    "advice": {
      "#operator": "IndexAdvice",
      "adviseinfo": {
        "current_indexes": [
          {
            "index_statement": "CREATE PRIMARY INDEX def_inventory_hotel_primary ON `default`:`travel-sample`.`inventory`.`hotel`",
            "keyspace_alias": "hotel_a"
          }
        ],
        "recommended_indexes": {
          "indexes": [
            {
              "index_statement": "CREATE INDEX adv_country ON `default`:`travel-sample`.`inventory`.`hotel`(`country`)",
              "keyspace_alias": "hotel_a",
              "recommending_rule": "Index keys follow cost-based order.", (1)
              "update_statistics": "UPDATE STATISTICS FOR `default`:`travel-sample`.`inventory`.`hotel`(`country`)" (2)
            }
          ]
        }
      }
    },
    "query": "SELECT * FROM `travel-sample`.inventory.hotel a WHERE a.country = 'France';"
  }
]
    1 Index is recommended by the cost-based optimizer
    2 Recommended command for updating statistics
    Example 2. Recommended index and covering index
    ADVISE SELECT airportname FROM airport
WHERE geo.alt NOT BETWEEN 0 AND 100;
    Result
    [
  {
    "#operator": "Advise",
    "advice": {
      "#operator": "IndexAdvice",
      "adviseinfo": {
        "current_indexes": [
          {
            "index_statement": "CREATE PRIMARY INDEX idx_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
            "keyspace_alias": "airport"
          }
        ],
        "recommended_indexes": {
          "covering_indexes": [
            {
              "index_statement": "CREATE INDEX adv_geo_alt_airportname ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`,`airportname`)",
              "keyspace_alias": "airport"
            }
          ],
          "indexes": [
            {
              "index_statement": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)",
              "keyspace_alias": "airport",
              "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (5. less than/greater than)."
            }
          ]
        }
      }
    },
    "query": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
  }
]
    Example 3. Current index is identical to the recommended index
    ADVISE SELECT * FROM landmark
WHERE city LIKE "Par%" OR city LIKE "Lon%";
    Result
    [
  {
    "#operator": "Advise",
    "advice": {
      "#operator": "IndexAdvice",
      "adviseinfo": {
        "current_indexes": [
          {
            "index_statement": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)",
            "index_status": "SAME TO THE INDEX WE CAN RECOMMEND",
            "keyspace_alias": "landmark",
            "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`.inventory.landmark WHERE city LIKE \"Par%\" OR city LIKE \"Lon%\";"
  }
]
    Example 4. Current index is an optimal covering index
    ADVISE SELECT city FROM landmark
WHERE city LIKE "Par%" OR city LIKE "Lon%";
    Result
    [
  {
    "#operator": "Advise",
    "advice": {
      "#operator": "IndexAdvice",
      "adviseinfo": {
        "current_indexes": [
          {
            "index_statement": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)",
            "index_status": "THIS IS AN OPTIMAL COVERING INDEX.",
            "keyspace_alias": "landmark"
          }
        ],
        "recommended_indexes": "No index recommendation at this time."
      }
    },
    "query": "SELECT city FROM `travel-sample`.inventory.landmark WHERE city LIKE \"Par%\" OR city LIKE \"Lon%\";"
  }
]
    Example 5. No index can be recommended
    ADVISE SELECT * FROM landmark LIMIT 5;
    Result
    [
  {
    "#operator": "Advise",
    "advice": {
      "#operator": "IndexAdvice",
      "adviseinfo": {
        "current_indexes": [
          {
            "index_statement": "CREATE PRIMARY INDEX def_inventory_landmark_primary ON `default`:`travel-sample`.`inventory`.`landmark`",
            "keyspace_alias": "landmark"
          }
        ],
        "recommended_indexes": "No index recommendation at this time."
      }
    },
    "query": "SELECT * FROM `travel-sample`.inventory.landmark LIMIT 5;"
  }
]