--- title: ADVISOR Function description: The ADVISOR function provides index recommendations to optimize query response time. pubDate: 2026-08-17T09:53:44.266Z antora: editUrl: https://github.com/couchbaselabs/docs-devex/edit/release/7.6/modules/n1ql/pages/n1ql-language-reference/advisor.adoc xref: xref:7.6@server:n1ql:n1ql-language-reference/advisor.adoc[] --- [Consult the llms.txt file for a full list of contents](/llms.txt) [View original HTML](/server/7.6/n1ql/n1ql-language-reference/advisor.html) # ADVISOR Function 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. ## [](#advisor-string)ADVISOR(`string`) ### [](#description)Description When used with a string argument, the function invokes the index advisor for a single SQL++ query. The index advisor works with [SELECT](selectintro.md), [UPDATE](update.md), [DELETE](delete.md), or [MERGE](merge.md) queries. ### [](#arguments)Arguments string A string, or an expression which resolves to a string, containing a single SQL++ query. ### [](#return-value)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](#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](#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](#indexes) \> array | **Indexes** | Name | Description | Schema | | ------------------------- | ----------------------------------------------------------------------------------------------------------------------- | ------------------------------------ | | **index** _required_ | The SQL++ command used to define the index. | string | | **statements** _required_ | An array of Statement objects, each giving information about one of the SQL++ input queries associated with this index. | < [Statements](#statements) \> array | **Statements** | Name | Description | Schema | | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- | | **run\_count** _required_ | When the function is used with a single SQL++ 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 SQL++ input query occurs in the input array or session. | integer | | **statement** _required_ | The SQL++ input query. | string | ### [](#example)Example 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](../n1ql-intro/queriesandresults.md#query-context). Get index advice for a single query Query ```sqlpp SELECT ADVISOR("SELECT * FROM landmark WHERE activity = 'eat' AND city = 'Paris'") AS Single; ``` Result ```json [ { "Single": { "current_used_indexes": [ { "index": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\nWHERE activity = 'eat' AND city = 'Paris'" } ] } ], "recommended_indexes": [ { "index": "CREATE INDEX adv_city_activity ON `default`:`travel-sample`.`inventory`.`landmark`(`city`,`activity`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\nWHERE activity = 'eat' 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 one index which is currently used by the query, and recommends one secondary index. No covering indexes are recommended. ## [](#advisor-array)ADVISOR(`array`) ### [](#description-2)Description When used with an array argument, the function invokes the index advisor for multiple SQL++ queries. The index advisor works with [SELECT](selectintro.md), [UPDATE](update.md), [DELETE](delete.md), or [MERGE](merge.md) queries. ### [](#arguments-2)Arguments array An array of strings, or an expression which resolves to an array of strings, each of which contains a SQL++ query. ### [](#return-value-2)Return Value Returns an [index advisor results](#results) object. ### [](#examples)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](../n1ql-intro/queriesandresults.md#query-context). Get index advice for multiple queries Query ```sqlpp SELECT ADVISOR([ "SELECT * FROM landmark WHERE activity = 'eat' AND city = 'Paris'", "SELECT * FROM landmark WHERE activity = 'see' AND city = 'San Francisco'" ]) AS Multiple; ``` Result ```json [ { "Multiple": { "current_used_indexes": [ { "index": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\n WHERE activity = 'eat' AND city = 'Paris'" }, { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\n WHERE activity = 'see' AND city = 'San Francisco'" } ] } ], "recommended_indexes": [ { "index": "CREATE INDEX adv_city_activity ON `default`:`travel-sample`.`inventory`.`landmark`(`city`,`activity`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\n WHERE activity = 'eat' AND city = 'Paris'" } ] }, { "index": "CREATE INDEX adv_activity_city ON `default`:`travel-sample`.`inventory`.`landmark`(`activity`,`city`)", "statements": [ { "run_count": 1, "statement": "SELECT * FROM `travel-sample`.inventory.landmark\n WHERE activity = 'see' AND city = 'San Francisco'" } ] } ] } } ] ``` In this case, the index advisor recommends an index which would be suitable for both of the input queries. Get index advice for recent completed requests This example uses a subquery to get an array of statements from the [system:completed\_requests](../n1ql-manage/monitoring-n1ql-query.md#sys-completed-req) catalog. Query ```sqlpp SELECT ADVISOR((SELECT RAW statement FROM system:completed_requests)) AS Recent; ``` Result ```json [ { "Recent": { "current_used_indexes": [ { "index": "CREATE PRIMARY INDEX def_inventory_route_primary ON `default`:`travel-sample`.`inventory`.`route`", "statements": [ { "run_count": 3, "statement": "SELECT * FROM `travel-sample`.inventory.route r JOIN `travel-sample`.inventory.airline a ON r.airlineid= META(a).id WHERE a.country = \"France\";" }, { "run_count": 2, "statement": "SELECT d.id, d.destinationairport, RATIO_TO_REPORT(d.distance) OVER (PARTITION BY d.destinationairport) AS `distance-ratio` FROM `travel-sample`.inventory.route AS d LIMIT 7;" }, // ... ] }, { "index": "CREATE PRIMARY INDEX def_inventory_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`", "statements": [ { "run_count": 1, "statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;" } ] } ], "recommended_covering_indexes": [ { "index": "CREATE INDEX adv_geo_alt_airportname ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`,`airportname`)", "statements": [ { "run_count": 1, "statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;" } ] } ], "recommended_indexes": [ { "index": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)", "statements": [ { "run_count": 1, "statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;" } ] }, { "index": "CREATE INDEX adv_airlineid ON `default`:`travel-sample`.`inventory`.`route`(`airlineid`)", "statements": [ { "run_count": 3, "statement": "SELECT * FROM `travel-sample`.inventory.route r JOIN `travel-sample`.inventory.airline a ON r.airlineid= META(a).id WHERE a.country = \"France\";" } // ... ``` 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-session-start)ADVISOR(`start_obj`) ### [](#description-3)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](#advisor-session-get) for this session to see the index advice. ### [](#arguments-3)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](../n1ql-manage/query-settings.md#completed-limit) setting. You can change the service-level `completed-limit` setting to change the default for this property. ### [](#return-value-3)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](#advisor-session-get) for this session, or to [stop](#advisor-session-stop), [abort](#advisor-session-abort), or [purge](#advisor-session-purge) this session. | string (UUID) | ### [](#example-2)Example 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. Query ```sqlpp SELECT ADVISOR({"action": "start", "response": "0s", "duration": "1h"}) AS Collect; ``` Result ```json [ { "Collect": { "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60" } } ] ``` ## [](#advisor-session-list)ADVISOR(`list_obj`) ### [](#description-4)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](../n1ql-manage/monitoring-n1ql-query.md#sys-tasks-cache) catalog. ### [](#arguments-4)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-4)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](#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](#advisor-session-get) for this session, or to [stop](#advisor-session-stop), [abort](#advisor-session-abort), or [purge](#advisor-session-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](#results) object. If the session is still active, this field is not present. | < [Results](#results) \> array | Returns an empty array if there are no index advisor sessions in the tasks cache. ### [](#example-3)Example List all index advisor sessions Query ```sqlpp SELECT ADVISOR({"action": "list"}) AS List; ``` Result ```json [ { "List": [ { "tasks_cache": { "class": "advisor", "delay": "10s", "id": "583af6ae-841e-5090-9a74-3607784533fa", "name": "0cd09ae4-a083-4a7e-86cd-85e42c140d60", "node": "127.0.0.1:8091", "results": [ // ... ], "startTime": "2021-01-19 15:57:51.015716783 +0000 UTC m=+19106.791327072", "state": "completed", "stopTime": "2021-01-19 15:57:51.123751229 +0000 UTC m=+19106.899361513", "subClass": "analyze", "submitTime": "2021-01-19 15:57:41.01262637 +0000 UTC m=+19096.788236671" } }, { "tasks_cache": { "class": "advisor", "delay": "1h0m0s", "id": "ce4ec13f-720e-56ae-8790-8136ea0648e3", "name": "4e394fad-03d5-4fbf-b9a5-6ad902c8df75", "node": "127.0.0.1:8091", "results": [ {} ], "state": "cancelled", "subClass": "analyze", "submitTime": "2021-01-19 15:56:12.398458243 +0000 UTC m=+19008.174068538" } } ] } ] ``` (Results are truncated for brevity.) ## [](#advisor-session-stop)ADVISOR(`stop_obj`) ### [](#description-5)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](#advisor-session-get) for this session to see the index advice. ### [](#arguments-5)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-5)Return Value Returns an empty array. ### [](#example-4)Example Stop an index advisor session Query ```sqlpp SELECT ADVISOR({"action": "stop", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"}) AS Stop; ``` Result ```json [ { "Stop": [] } ] ``` ## [](#advisor-session-abort)ADVISOR(`abort_obj`) ### [](#description-6)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-6)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-6)Return Value Returns an empty array. ### [](#example-5)Example Abort an index advisor session Query ```sqlpp SELECT ADVISOR({"action": "abort", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"}) AS Abort; ``` Result ```json [ { "Abort": [] } ] ``` ## [](#advisor-session-get)ADVISOR(`get_obj`) ### [](#description-7)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](selectintro.md), [UPDATE](update.md), [DELETE](delete.md), or [MERGE](merge.md) queries. ### [](#arguments-7)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-7)Return Value Returns an array containing an array, which in turn contains an [index advisor results](#results) object. Returns an empty array if the specified session collected no queries, or if the specified session does not exist. ### [](#example-6)Example Get index advice for an index advisor session Query ```sqlpp SELECT ADVISOR({"action": "get", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"}) AS Get; ``` Result ```json [ { "Get": [ [ { "current_used_indexes": [ { "index": "CREATE PRIMARY INDEX idx_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`", "statements": [ { "run_count": 1, "statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;" } ] // ... } ], "recommended_covering_indexes": [ { "index": "CREATE INDEX adv_city_name ON `default`:`travel-sample`.`inventory`.`hotel`(`city`,`name`)", "statements": [ { "run_count": 1, "statement": "SELECT h.name, h.city, a.airportname FROM `travel-sample`.inventory.hotel h JOIN `travel-sample`.inventory.airport a ON h.city = a.city LIMIT 5;" } ] // ... } ], "recommended_indexes": [ { "index": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)", "statements": [ { "run_count": 1, "statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;" } ] } ] } ] ] } ] ``` (Results are truncated for brevity.) ## [](#advisor-session-purge)ADVISOR(`purge_obj`) ### [](#description-8)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-8)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-8)Return Value Returns an empty array. ### [](#example-7)Example Purge an index advisor session Query ```sqlpp SELECT ADVISOR({"action": "purge", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"}) AS Purge; ``` Result ```json [ { "Purge": [] } ] ``` ## [](#related-links)Related Links * The [ADVISE](advise.md) statement — also describes the index advisor [recommendation rules](advise.md#recommendation-rules) * The [Index Advisor](../../tools/query-workbench.md#index-advisor) in the Query Workbench * The [system:tasks\_cache](../n1ql-manage/monitoring-n1ql-query.md#sys-tasks-cache) catalog