A newer version of this documentation is available.

View Latest

ADVISOR Function

  • Developer Preview
    +
    The ADVISOR function provides index recommendations to optimize query response time. There are two main scenarios for using this function. One is to invoke the index advisor immediately for a given query or set of queries; the other is to start a session in which every query of interest is collected for a set time period, then invoke the index advisor asynchronously for that collection of queries when the session ends. Within these two scenarios, this function has several different usages. The operation and output of each usage depends on the function’s single argument. For clarity, each usage is listed separately on this page.

    This is a Developer Preview feature, intended for development purposes only. Do not use this feature in production. No Enterprise Support is provided for Developer Preview features.

    Refer to Developer Preview Mode for more information.

    ADVISOR(string)

    Description

    When used with a string argument, the function invokes the index advisor for a single N1QL query. The index advisor works with SELECT, UPDATE, DELETE, or MERGE queries.

    Arguments

    string

    A string, or an expression which resolves to a string, containing a single N1QL query.

    Return Value

    Returns an index advisor results object with the following properties.

    Results

    Name Description Schema

    current_used_indexes
    optional

    If the query engine can select any current primary or secondary indexes to use with an input query, this is an array of Index objects, each giving information about one of the current indexes.

    If the query engine cannot select a current index to use with an input query, this field does not appear.

    < Indexes > array

    recommended_covering_indexes
    optional

    If the index advisor recommends any indexes, this is an array of Index objects, each giving information about one of the recommended indexes.

    If the index advisor cannot recommend any covering indexes, this field does not appear.

    < Indexes > array

    recommended_indexes
    optional

    If the index advisor recommends any indexes, this is an array of Index objects, each giving information about one of the recommended indexes.

    If the index advisor cannot recommend any indexes, this field does not appear.

    < Indexes > array

    Indexes

    Name Description Schema

    index
    required

    The N1QL command used to define the index.

    string

    statements
    required

    An array of Statement objects, each giving information about one of the N1QL input queries associated with this index.

    < Statements > array

    Statements

    Name Description Schema

    run_count
    required

    When the function is used with a single N1QL input query, this is always 1.

    When the function is used with an array of queries, or a collection of queries from a session, this is the number of times that this N1QL input query occurs in the input array or session.

    integer

    statement
    required

    The N1QL input query.

    string

    Example

    Example 1. Get index advice for a single query
    SELECT ADVISOR("SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'") AS Single;
    Result
    [
      {
        "Single": {
          "current_used_indexes": [
            {
              "index": "CREATE INDEX def_type ON `travel-sample`(`type`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                }
              ]
            },
            {
              "index": "CREATE INDEX def_city ON `travel-sample`(`city`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                }
              ]
            }
          ],
          "recommended_indexes": [
            {
              "index": "CREATE INDEX adv_city_type ON `travel-sample`(`city`) WHERE `type` = 'hotel'",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                }
              ]
            }
          ]
        }
      }
    ]

    Only one statement occurs in these results, because the function was called with a single query input. In this case, the index advisor identifies two indexes which are currently used by the query, and recommends one secondary index. No covering indexes are recommended.

    ADVISOR(array)

    Description

    When used with an array argument, the function invokes the index advisor for multiple N1QL queries. The index advisor works with SELECT, UPDATE, DELETE, or MERGE queries.

    Arguments

    array

    An array of strings, or an expression which resolves to an array of strings, each of which contains a N1QL query.

    Return Value

    Returns an index advisor results object.

    Examples

    Example 2. Get index advice for multiple queries
    SELECT ADVISOR(["SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'", "SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city WHERE h.type = 'hotel' AND a.type = 'airport'"]) AS Multiple;
    Result
    [
      {
        "Multiple": {
          "current_used_indexes": [
            {
              "index": "CREATE INDEX def_city ON `travel-sample`(`city`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                },
                ...
              ]
            },
            {
              "index": "CREATE INDEX def_type ON `travel-sample`(`type`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                },
                ...
              ]
            }
          ],
          "recommended_indexes": [
            {
              "index": "CREATE INDEX adv_city_type ON `travel-sample`(`city`) WHERE `type` = 'hotel'",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris'"
                },
                {
                  "run_count": 1,
                  "statement": "SELECT * FROM `travel-sample` h JOIN `travel-sample` a ON a.city = h.city WHERE h.type = 'hotel' AND a.type = 'airport'"
                }
              ]
            }
          ]
        }
      }
    ]

    In this case, the index advisor recommends an index which would be suitable for both of the input queries. (Results are truncated for brevity.)

    Example 3. Get index advice for recent completed requests

    This example uses a subquery to get an array of statements from the system:completed_requests catalog.

    SELECT ADVISOR((SELECT RAW statement FROM system:completed_requests)) AS Recent;
    Result
    [
      {
        "Recent": {
          "current_used_indexes": [
            {
              "index": "CREATE INDEX def_type ON `travel-sample`(`type`)",
              "statements": [
                {
                  "run_count": 4,
                  "statement": "SELECT d.id, d.destinationairport, RATIO_TO_REPORT(d.distance) OVER (PARTITION BY d.destinationairport) AS `distance-ratio` FROM `travel-sample` AS d WHERE d.type='route' LIMIT 7;"
                },
                {
                  "run_count": 3,
                  "statement": "SELECT * FROM `travel-sample` r WHERE r.type = 'airport' LIMIT 3;"
                },
                ...
              ]
            },
            {
              "index": "CREATE INDEX def_city ON `travel-sample`(`city`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT h.name, h.city, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' AND a.type = 'airport' LIMIT 5;"
                },
                ...
              ]
            },
            ...
          ],
          "recommended_covering_indexes": [
            {
              "index": "CREATE INDEX adv_city_airportname ON `travel-sample`(`city`,`airportname`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT h.name, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' LIMIT 5;"
                },
                ...
              ]
            },
            {
              "index": "CREATE INDEX adv_city_type_name ON `travel-sample`(`city`,`name`) WHERE `type` = 'hotel'",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT h.name, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' LIMIT 5;"
                },
                ...
              ]
            },
            ...
          ],
          "recommended_indexes": [
            {
              "index": "CREATE INDEX adv_array_star_reviews_ratings_Cleanliness ON `travel-sample`(array_star((`reviews`)).`ratings`.`Cleanliness`)",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT ARRAY_AGG(reviews[*].ratings.Cleanliness) AS Reviews FROM `travel-sample`;"
                },
                ...
              ]
            },
            {
              "index": "CREATE INDEX adv_city_type ON `travel-sample`(`city`) WHERE `type` = 'hotel'",
              "statements": [
                {
                  "run_count": 1,
                  "statement": "SELECT h.name, h.city, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' AND a.type = 'airport' LIMIT 5;"
                },
                ...
              ]
            },
            ...
          ]
        }
      }
    ]

    In this case, the index advisor recommends several covering indexes and secondary indexes, each of which would be suitable for multiple recent queries. (Results are truncated for brevity.)

    ADVISOR(start_obj)

    Description

    When used with a start_obj object argument, the function can be used to start an index advisor session. As long as the session is running, any queries that meet the criteria you specify are collected for later analysis.

    By default, the session continues running for the duration you specify when you start the session. At the end of the duration, the index advisor analyzes any queries that have been collected by this session. The session and any resulting index advice are retained in the tasks cache. You can then get the results for this session to see the index advice.

    Arguments

    start_obj

    An object with the following properties:

    action

    [Required] The string start.

    profile

    [Optional] A string specifying the user profile whose queries you want to collect. If omitted, all queries are collected.

    response

    [Optional] A string representing a duration. All completed queries lasting longer than this threshold are collected for analysis by the index advisor. Valid time units are ns (nanoseconds), us (microseconds), ms (milliseconds), s (seconds), m (minutes), or h (hours). If omitted, the default setting is 0s.

    duration

    [Required] A string representing a duration. The index advisor session runs for the length of this duration. Valid time units are ns (nanoseconds), us (microseconds), ms (milliseconds), s (seconds), m (minutes), or h (hours).

    query-count

    [Optional] An integer specifying the maximum number of queries to be collected for analysis by the index advisor. If omitted, the default setting is the same as the service-level completed-limit setting. You can change the service-level completed-limit setting to change the default for this property.

    Return Value

    Returns an object with the following property:

    Name Description Schema

    session
    required

    The name of the index advisor session. You will need to refer to this name to get the results for this session, or to stop, abort, or purge this session.

    string (UUID)

    Example

    Example 4. Start an index advisor session

    The following example starts an index advisor session to run for one hour. All completed queries taking longer than 0 seconds will be collected.

    SELECT ADVISOR({"action": "start", "response": "0s", "duration": "1h"}) AS Collect;
    Result
    [
      {
        "Collect": {
          "session": "8c41a3c6-2252-437e-ab47-0b28f29f47fb"
        }
      }
    ]

    ADVISOR(list_obj)

    Description

    When used with a list_obj object argument, the function can be used to list index advisor sessions. Each index advisor session is stored as a scheduled task in the system:tasks_cache catalog.

    Arguments

    list_obj

    An object with the following properties:

    action

    [Required] The string list.

    status

    [Optional] A string specifying the status of the index advisor sessions to list. This must be one of the following:

    • completed — only list completed sessions

    • active — only list active sessions

    • all — list all sessions

    If omitted, the default is all.

    Return Value

    Returns an array of tasks cache objects, each of which has the following properties.

    Tasks Cache

    Name Description Schema

    tasks_cache
    required

    A nested object that gives information about an index advisor session.

    Session

    Name Description Schema

    class
    required

    The class of the session; in this case, advisor.

    string

    delay
    required

    The scheduled duration of the session.

    string (duration)

    id
    required

    The internal ID of the session.

    string (UUID)

    name
    required

    The name of the session. You will need to refer to this name to get the results for this session, or to stop, abort, or purge this session.

    string (UUID)

    node
    required

    The node where the session was started.

    string (address)

    state
    required

    The state of the session:

    • scheduled — the session is active.

    • cancelled — the session was stopped.

    • completed — the session is completed.

    enum (cancelled, completed, scheduled)

    subClass
    required

    The subclass of the session; in this case, analyze.

    string

    submitTime
    required

    The date and time when the function was called to start the session.

    string (date-time)

    startTime
    optional

    The date and time when the session started.

    If the session is still active, this field is not present.

    string (date-time)

    stopTime
    optional

    The date and time when the session stopped.

    If the session is still active, this field is not present.

    string (date-time)

    results
    optional

    An array containing a single index advisor results object.

    If the session is still active, this field is not present.

    < Results > array

    Returns an empty array if there are no index advisor sessions in the tasks cache.

    Example

    Example 5. List all index advisor sessions
    SELECT ADVISOR({"action": "list"}) AS List;
    Result
    [
      {
        "List": [
          {
            "tasks_cache": {
              "class": "advisor",
              "delay": "1h0m0s",
              "id": "bcd9f8e4-b324-504c-a98b-ace90dba869f",
              "name": "aa7f688a-bf29-438f-888f-eeaead87ca40",
              "node": "10.143.192.101:8091",
              "state": "scheduled",
              "subClass": "analyze",
              "submitTime": "2019-09-17 05:18:12.903122381 -0700 PDT m=+8460.550715992"
            }
          },
          {
            "tasks_cache": {
              "class": "advisor",
              "delay": "5m0s",
              "id": "254abec5-5782-543e-9ee0-d07da146b94e",
              "name": "ca2cfe56-01fa-4563-8eb0-a753af76d865",
              "node": "10.143.192.101:8091",
              "results": [
                ...
              ],
              "startTime": "2019-09-17 05:03:31.821597725 -0700 PDT m=+7579.469191487",
              "state": "completed",
              "stopTime": "2019-09-17 05:03:31.963133954 -0700 PDT m=+7579.610727539",
              "subClass": "analyze",
              "submitTime": "2019-09-17 04:58:31.821230131 -0700 PDT m=+7279.468823737"
            }
          }
        ]
      }
    ]

    (Results are truncated for brevity.)

    ADVISOR(stop_obj)

    Description

    When used with a stop_obj object argument, the function can be used to stop an index advisor session. In this case, the session is stopped, and the index advisor analyzes any queries that have been collected by this session so far. The session and any resulting index advice are retained in the tasks cache. You can then get the results for this session to see the index advice.

    Arguments

    stop_obj

    An object with the following properties:

    action

    [Required] The string stop.

    session

    [Required] A string specifying the name of a session.

    Return Value

    Returns an empty array.

    Example

    Example 6. Stop an index advisor session
    SELECT ADVISOR({"action": "stop", "session": "8c41a3c6-2252-437e-ab47-0b28f29f47fb"}) AS Stop;
    Result
    [
      {
        "Stop": []
      }
    ]

    ADVISOR(abort_obj)

    Description

    When used with an abort_obj object argument, the function can be used to abort an index advisor session. In this case, the session is stopped, and the session is removed from the tasks cache.

    Arguments

    abort_obj

    An object with the following properties:

    action

    [Required] The string abort.

    session

    [Required] A string specifying the name of a session.

    Return Value

    Returns an empty array.

    Example

    Example 7. Abort an index advisor session
    SELECT ADVISOR({"action": "abort", "session": "8c41a3c6-2252-437e-ab47-0b28f29f47fb"}) AS Abort;
    Result
    [
      {
        "Abort": []
      }
    ]

    ADVISOR(get_obj)

    Description

    When used with a get_obj object argument, the function can be used to get the results of a completed index advisor session. The index advisor is invoked for any collected SELECT, UPDATE, DELETE, or MERGE queries.

    Arguments

    get_obj

    An object with the following properties:

    action

    [Required] The string get.

    session

    [Required] A string specifying the name of a session.

    Return Value

    Returns an array containing an array, which in turn contains an index advisor results object.

    Returns an empty array if the specified session collected no queries, or if the specified session does not exist.

    Example

    Example 8. Get index advice for an index advisor session
    SELECT ADVISOR({"action": "get", "session": "8c41a3c6-2252-437e-ab47-0b28f29f47fb"}) AS Get;
    Result
    [
      {
        "Get": [
          [
            {
              "current_used_indexes": [
                {
                  "index": "CREATE INDEX def_type ON `travel-sample`(`type`)",
                  "statements": [
                    {
                      "run_count": 1,
                      "statement": "SELECT h.name, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' LIMIT 5;"
                    },
                    ...
                  ]
                },
                ...
              ],
              "recommended_covering_indexes": [
                {
                  "index": "CREATE INDEX adv_city_type_name ON `travel-sample`(`city`,`name`) WHERE `type` = 'hotel'",
                  "statements": [
                    {
                      "run_count": 1,
                      "statement": "SELECT h.name, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' LIMIT 5;"
                    }
                  ]
                },
                ...
              ],
              "recommended_indexes": [
                {
                  "index": "CREATE INDEX adv_city_type ON `travel-sample`(`city`) WHERE `type` = 'hotel'",
                  "statements": [
                    {
                      "run_count": 1,
                      "statement": "SELECT h.name, a.airportname FROM `travel-sample` h JOIN `travel-sample` a ON h.city = a.city WHERE h.type = 'hotel' LIMIT 5;"
                    }
                  ]
                },
                ...
              ]
            }
          ]
        ]
      }
    ]

    (Results are truncated for brevity.)

    ADVISOR(purge_obj)

    Description

    When used with a purge_obj object argument, the function can be used to purge the results of a completed index advisor session from the tasks cache.

    Arguments

    purge_obj

    An object with the following properties:

    action

    [Required] The string purge.

    session

    [Required] A string specifying the name of a session.

    Return Value

    Returns an empty array.

    Example

    Example 9. Purge an index advisor session
    SELECT ADVISOR({"action": "purge", "session": "8c41a3c6-2252-437e-ab47-0b28f29f47fb"}) AS Purge;
    Result
    [
      {
        "Purge": []
      }
    ]