ADVISE

You can preface a DQL statement with ADVISE to analyse it and receive advice on which indexes would benefit it. The statement is planned but never executed, and the result is a single document describing the indexes the planner recommends.

The statement isn’t executed when using ADVISE, only the parsing and planning stages take place. No documents are read or modified.

ADVISE is the mechanism the planner points you to when a query cannot run efficiently — or at all — without index support. For example, a join whose inner leg has no index on its join key fails with an error that explicitly suggests running ADVISE on the query.

Which statements can be advised

ADVISE can preface most statements — anything except a nested EXPLAIN, PROFILE, or ADVISE. Advice is only produced for statements that scan a collection:

SELECT
UPDATE
DELETE, EVICT, and TOMBSTONE
INSERT ... SELECT — the advice is derived from the embedded SELECT

Any other statement — including an INSERT with literal VALUES — is accepted but returns an outcome of no advice available for statement.

The advice document

The result is a single document with an advice field containing:

Field	Description
`statement`	The statement that was analysed.
`suggestedIndexes`	An array of recommended indexes. Each entry has the target `collection`, a ready-to-run `CREATE INDEX IF NOT EXISTS` `statement`, and a `reason` describing which parts of the query the index serves.
`existingIndexes`	Indexes already present that the planner considered for the statement, when any exist. Each entry has a `collection` and the index’s `CREATE INDEX` `statement`.
`outcome`	Present instead of `suggestedIndexes` when no advice can be given, e.g. `no keys to advise on`, `no advice available for statement`, or `collection <name> does not support indexing`.

Each recommendation combines the indexable clauses of a statement into as few composite indexes as possible. Keys are ordered as equality keys first, then ORDER BY keys, then range keys, followed by projected fields when a covering index is worthwhile. Aliased ORDER BY fields are resolved back to the underlying document field.

Examples

A statement with nothing to index on returns an outcome rather than suggestions:

DQL

ADVISE SELECT * FROM atest

produces:

{
  "advice": {
    "outcome": "no keys to advise on",
    "statement": "SELECT * FROM atest\n"
  }
}

An equality predicate is advised as a single-key index:

DQL

ADVISE SELECT * FROM atest WHERE e=1

produces:

{
  "advice": {
    "statement": "SELECT * FROM atest WHERE e=1\n",
    "suggestedIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e ON default:`atest` (`e` ASC)"
      }
    ]
  }
}

Equality and range predicates on the same collection are combined into one composite index, with the equality key ordered ahead of the range key:

DQL

ADVISE SELECT * FROM atest WHERE r>0 AND e=1

produces:

{
  "advice": {
    "statement": "SELECT * FROM atest WHERE r>0 AND e=1\n",
    "suggestedIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`; range predicates on `r`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e_r ON default:`atest` (`e` ASC, `r` ASC)"
      }
    ]
  }
}

ADVISE AND PROVISION

Adding the optional AND PROVISION clause makes ADVISE also create the suggested indexes as part of the same statement:

DQL

ADVISE AND PROVISION SELECT * FROM atest WHERE e=1

The recommendations that were created successfully are reported under createdIndexes (in place of suggestedIndexes):

{
  "advice": {
    "statement": "SELECT * FROM atest WHERE e=1\n",
    "createdIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e ON default:`atest` (`e` ASC)"
      }
    ]
  }
}

Any index that could not be created is reported under failedIndexes instead, each entry carrying an error describing why creation failed.

ADVISE is currently supported on the small peer only.

Statements

Types and Functions

Reference

Which statements can be advised

The advice document

Examples

ADVISE AND PROVISION

​Which statements can be advised

​The advice document

​Examples

​ADVISE AND PROVISION

Which statements can be advised

The advice document

Examples

ADVISE AND PROVISION