A newer version of this documentation is available.

View Latest
February 16, 2025
+ 12

How to use the Index Advisor to recommend indexes for your queries.
This guide is for Couchbase Server.

Introduction

You must create an index on a keyspace to be able to query that keyspace. You can improve the performance of your query by using a well-designed index. In Couchbase Server Enterprise Edition, the Index Advisor can analyze your queries and provide recommended indexes to optimize response times.

The Index Advisor works with SELECT, UPDATE, DELETE, or MERGE queries.

If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

Advice for a Single Query

To get index recommendations for a single query, you can use the Index Advisor in the Query Workbench, the ADVISE statement, or the ADVISOR() function.

To get index recommendations for a single query, do one of the following:

  • Enter the query in the Query Editor and click Index Advisor.

  • Alternatively, if the query has already been executed, click Advice in the Results area.

The Results area displays the details of any current indexes used by the query, and any indexes that the Index Advisor recommends.

If there are any recommended indexes, you can click Create & Build Indexes to create them.

Similarly, if there are any recommended covering indexes, you can click Create & Build Covering Indexes to create them.

Index advice for the query

For more details, refer to Index Advisor.

Advice for Multiple Queries

The ADVISOR() function also enables you to get index recommendations for multiple queries.

To get advice for multiple queries, use the ADVISOR() function with an array argument containing strings which represent each query.

You can query the system:completed_requests catalog to get a list of recently-completed queries.

The following example gets index advice for recently-completed queries.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT ADVISOR((SELECT RAW statement FROM system:completed_requests)) AS Recent;
Result
[
  {
    "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\";"
            }
// ...

For more details, refer to ADVISOR(array).

Advice for a Session

The ADVISOR() function also enables you to get index recommendations for all the queries that you run in an Index Advisor session.

To run an Index Advisor session:

  1. Use the ADVISOR() function with a start object argument to start the session. The object argument must contain the property "action": "start", and must also contain a "duration" property, specifying the duration of the session.

    The query returns a session ID, which you must use to get the results for this session, and to perform other actions on this session.

  2. Run all the queries for which you require index recommendations.

  3. If you want to stop the session early, use the ADVISOR() function with a stop object argument. The object argument must contain the property "action": "stop", and must also contain a "session" property, specifying the session ID.

  4. When the session is complete, use the ADVISOR() function with a get object argument to get the index recommendations. The object argument must contain the property "action": "get", and must also contain a "session" property, specifying the session ID.

You can also use the ADVISOR() function to abandon a session without recording any results; to list active and completed sessions; and to purge the results of an Index Advisor session.

The following example starts an Index Advisor session with a duration of 1 hour. All queries taking longer than 0 seconds will be collected.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT ADVISOR({"action": "start", "response": "0s", "duration": "1h"}) AS Collect;
Result
[
  {
    "Collect": {
      "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"
    }
  }
]

The following example stops the Index Advisor session early and saves the results.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT ADVISOR({"action": "stop", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"})
AS Stop;
Result
[
  {
    "Stop": []
  }
]

The following example returns index recommendations for the queries in the Index Advisor session.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT ADVISOR({"action": "get", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"})
AS Get;
Result
[
  {
    "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;"
                }
              ]
            }
          ]
        }
      ]
    ]
  }
]

For more details, refer to ADVISOR(start_obj).

Reference and explanation:

Administrator guides:

Online Index Advisor tool:

Indexes with SDKs: