EXPLAIN

DQL language syntax for displaying the execution plan produced by the query planner for a statement.

You can preface any DQL statement with EXPLAIN to instruct the query engine to return only the execution plan for the statement. This takes the form of a series of operators presented as JSON objects which represent the various component actions the planner has determined as necessary in order to execute the statement.

The statement isn’t executed when using EXPLAIN, only the parsing and planning stages take place.

Example:

DQL

EXPLAIN SELECT test.* FROM test;

produces:

{
  "plan": {
    "#operator": "sequence",
    "children": [
      {
        "#operator": "scan",
        "alias": "test",
        "collection": "test",
        "datasource": "default",
        "descriptor": {
          "path": {
            "full_scan": {}
          }
        }
      },
      {
        "#operator": "projection",
        "projections": [
          {
            "expression": "`test`",
            "wildcard": true
          }
        ]
      }
    ]
  }
}

A query plan is read from top to bottom to follow the flow of data through it. In the above example the first action is to scan the collection with a full collection scan (see Access paths) then project the entire document. In the example below an index scan is used to locate the data which matches “field1 = 1”, is then filtered (the index scan filter is re-applied along with application of the other filter (“field2 = 2”)), is then grouped, projected and the results ordered (the final projection is just a second part to the projection present when necessary in certain plans):

DQL

EXPLAIN
SELECT field3,count(*) the_count
FROM test
WHERE (field1 = 1 AND field2 = 2)
GROUP BY field3
ORDER BY the_count DESC;

{
  "plan": {
    "#operator": "sequence",
    "children": [
      {
        "#operator": "index_scan",
        "alias": "test",
        "collection": "test",
        "datasource": "default",
        "desc": {
          "index": "ix_f1",
          "spans": [
            {
              "index_key": {
                "direction": "asc",
                "include_missing": true,
                "key": [
                  "field1"
                ]
              },
              "range": {
                "high": {
                  "included": true,
                  "value": 1
                },
                "low": {
                  "included": true,
                  "value": 1
                }
              }
            }
          ]
        }
      },
      {
        "#operator": "filter",
        "condition": "((`test`.`field1` = 1) AND (`test`.`field2` = 2))"
      },
      {
        "#operator": "groupBy",
        "aggregates": [
          {
            "expr": "true",
            "name": "count(true)"
          }
        ],
        "keys": [
          {
            "alias": "$$(group_by_key_1)$$",
            "expression": "`test`.`field3`"
          }
        ]
      },
      {
        "#operator": "projection",
        "projections": [
          {
            "alias": "field3",
            "expression": ".`$$(group_by_key_1)$$`"
          },
          {
            "alias": "the_count",
            "expression": "count(true)"
          }
        ]
      },
      {
        "#operator": "sort",
        "orderBy": [
          {
            "direction": "desc",
            "key": ".`$$(projection)$$`.`the_count`"
          }
        ]
      },
      {
        "#operator": "final_projection"
      }
    ]
  }
}

Join plans

When a SELECT statement uses one or more JOIN terms, each join appears in the plan as an nlJoin (nested-loop join) operator. Read the plan from top to bottom: the outer (driving) collection is scanned or probed first, then for each outer row the nlJoin operator evaluates its inner sub-plan to find matching documents in the joined collection.

The nlJoin operator

DQL

EXPLAIN SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id

{
  "plan": {
    "#operator": "sequence",
    "children": [
      {
        "#operator": "scan",
        "alias": "c",
        "collection": "customers",
        "datasource": "default",
        "descriptor": { "diff_scan_condition": "never" }
      },
      {
        "#operator": "nlJoin",
        "condition": "(`c`.`cust_id` = `o`.`cust_id`)",
        "inner": {
          "#operator": "sequence",
          "children": [
            {
              "#operator": "indexScan",
              "alias": "o",
              "collection": "orders",
              "datasource": "default",
              "desc": {
                "index": "ix_orders_cust_id",
                "spans": [
                  [
                    {
                      "index_key": { "direction": "asc", "include_missing": true, "key": ["cust_id"] },
                      "range": {
                        "high": { "expr": "`c`.`cust_id`", "included": true },
                        "low":  { "expr": "`c`.`cust_id`", "included": true }
                      }
                    }
                  ]
                ]
              }
            },
            {
              "#operator": "fetch",
              "alias": "o",
              "collection": "orders",
              "datasource": "default"
            }
          ]
        }
      },
      {
        "#operator": "projection",
        "projections": [
          { "alias": "name",   "expression": "`c`.`name`" },
          { "alias": "amount", "expression": "`o`.`amount`" }
        ]
      }
    ]
  }
}

Key things to observe:

• Dynamic span bounds. The index scan span on the inner collection references the outer alias — "expr": "c.cust_id" — meaning the span is re-evaluated for every outer row. This is how the nested-loop join drives the inner index lookup.
• fetch step. When the query projects fields not stored in the index, a fetch operator retrieves the full document after the index scan. If all projected fields are covered by the index, the fetch step is absent entirely (see Covering index scans below).

LEFT OUTER JOIN

A LEFT OUTER JOIN produces the same plan shape as INNER JOIN with the addition of "outer": true on the nlJoin operator, which instructs the engine to emit a padded row (inner fields set to MISSING) when the inner collection has no match:

{
  "#operator": "nlJoin",
  "condition": "(`c`.`cust_id` = `o`.`cust_id`)",
  "inner": { "..." : "..." },
  "outer": true
}

RIGHT OUTER JOIN (rewritten)

A RIGHT OUTER JOIN is silently rewritten so that the originally-right collection becomes the outer (driving) leg. The plan therefore shows the right-hand collection scanned first, with the left-hand collection as the nlJoin inner leg and "outer": true set on the operator:

DQL

EXPLAIN SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.cust_id = o.cust_id

In the resulting plan orders (o) is the outer leg and customers (c) is the inner leg, looked up via an index on cust_id.

Multi-collection plans

A three-collection join produces two sequential nlJoin operators. The second operator’s condition may reference aliases introduced by the first join:

{
  "#operator": "sequence",
  "children": [
    { "#operator": "scan",   "alias": "c", "collection": "customers" },
    { "#operator": "nlJoin", "condition": "(`c`.`cust_id` = `o`.`cust_id`)",   "inner": { "...": "..." } },
    { "#operator": "nlJoin", "condition": "(`o`.`order_id` = `p`.`order_id`)", "inner": { "...": "..." } },
    { "#operator": "projection" }
  ]
}

Each additional JOIN term appends a further nlJoin to the sequence.

Covering index scans in joins

When the query projects only fields stored in the inner collection’s index (plus _id, which is always available), the planner uses a covering index scan and omits the fetch step entirely, reducing document reads significantly. The index scan descriptor includes "covering": true:

DQL

-- ix_orders_cust_id covers cust_id; o._id is always available — no fetch needed
EXPLAIN SELECT c.name, o._id
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id

To maximise the chance of a covering scan, create a composite index that includes both the join key and any additional fields projected or filtered from the inner collection:

DQL

CREATE INDEX ix_orders_cust_amt ON orders (cust_id INCLUDE MISSING, amount INCLUDE MISSING)

Intersect scans on join inner legs

When the ON condition or WHERE clause provides multiple filterable predicates against the inner collection, the planner may combine several index scans into an intersectScan before the fetch — exactly as for non-join queries. The intersectScan appears inside the nlJoin’s inner sequence:

DQL

-- With indexes on both cust_id and status, the planner may intersect them
EXPLAIN SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'

ID-based joins

When the ON condition equates an outer field to _id of the inner collection, the planner uses an idScan — a direct document lookup by ID — rather than a secondary index scan. No index on the inner collection is required:

{
  "#operator": "nlJoin",
  "condition": "(`c`.`latest_order_id` = `o`.`_id`)",
  "inner": {
    "#operator": "sequence",
    "children": [
      {
        "#operator": "idScan",
        "alias": "o",
        "collection": "orders",
        "datasource": "default",
        "ids": ["`c`.`latest_order_id`"]
      }
    ]
  }
}

For more on join syntax, index requirements, and query directives, see Joins.