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 located 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"
      }
    ]
  }
}