> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# 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:
```sql DQL theme={null}
EXPLAIN SELECT test.* FROM test;
```
produces:
```text theme={null}
{
"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](/dql/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):
```sql DQL theme={null}
EXPLAIN
SELECT field3,count(*) the_count
FROM test
WHERE (field1 = 1 AND field2 = 2)
GROUP BY field3
ORDER BY the_count DESC;
```
```text theme={null}
{
"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
```sql DQL theme={null}
EXPLAIN SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
```json theme={null}
{
"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](#covering-index-scans-in-joins) 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:
```json theme={null}
{
"#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:
```sql DQL theme={null}
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:
```json theme={null}
{
"#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`:
```sql DQL theme={null}
-- 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:
```sql DQL theme={null}
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:
```sql DQL theme={null}
-- 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:
```json theme={null}
{
"#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](/dql/select#joins).
## Related topics
- [SELECT](/dql/select.md)
- [Change Data Capture](/cloud/cdc.md)
- [PROFILE](/dql/profile.md)