EXPLAIN

Enterprise command reference.

Command Snapshot

Field	Value
Category	DQL
Mutates Data	No
Scope	Statement
Privilege Model	Requires read privilege (DQL) on referenced relations.

Purpose

Executes the EXPLAIN SQL command with MonkDB distributed runtime semantics.

Syntax

EXPLAIN [ ANALYZE | VERBOSE ] statement
EXPLAIN [ ( option [, ...] ) ] statement

Operational Notes

Use schema-qualified identifiers in automation and automation pipelines.
Validate behavior in staging for cluster-impacting or governance-impacting changes.
Confirm runtime effects through system tables and metrics before and after execution.

When to Use

Use for read/analytics workloads where query correctness and plan shape are primary concerns.
Use when you need SQL-native filtering, joins, aggregation, or explainability.

When Not to Use

Avoid for heavy recurring ETL if precomputed tables or materialized pipelines are more efficient.
Avoid unbounded scans in production without partition or predicate controls.

Common Errors and Troubleshooting

Symptom	Likely Cause	Action
Permission denied / unauthorized	Missing privilege on object or cluster scope	Re-run with required grants or elevated admin role.
Analysis/parse error	Syntax variant or object shape mismatch	Compare with canonical syntax and object definition.
Runtime failure under load	Resource limits, breaker pressure, or node state transitions	Check `sys.jobs`, `sys.operations`, `sys.checks`, and retry after mitigation.

Cross-References

Detailed Reference

The EXPLAIN command in MonkDB and other SQL databases is a powerful tool for understanding and optimizing query execution. Below is an expanded explanation of its usage, options, and output.

SQL Statement

EXPLAIN [ ANALYZE | VERBOSE ] statement
EXPLAIN [ ( option [, ...] ) ] statement

Description

The EXPLAIN command displays the execution plan generated by the query planner for a given SQL statement. This plan outlines how the database will execute the query, including details about operations such as scans, joins, filters, and aggregations. It helps developers optimize queries by identifying bottlenecks or inefficient operations.

Options

ANALYZE: Executes the query and provides runtime statistics for each step in the execution plan.
VERBOSE: Displays additional details about the query plan, such as optimizer steps and low-level information.
COSTS: By default enabled, shows estimated costs for operations. Can be disabled with COSTS FALSE.

Key Features

Execution Plan Details

The output includes:

Node Types: Operations like sequential scans (Seq Scan), index scans (Index Scan), joins (Nested Loop, Hash Join).
Join Types: Specifies algorithms used for joining tables.
Filter Conditions: Conditions applied during query execution.
Costs: Estimated computational effort required for each node.
Execution Order: Sequence of operations.

`ANALYZE` Option

When paired with EXPLAIN, it executes the query and provides actual runtime statistics, rows processed by each operation, timing information for each step.

`VERBOSE` Option

Provides a detailed breakdown of optimizer steps, showing intermediate logical plans and transformations.

Interpreting Output

Example Output

EXPLAIN VERBOSE
SELECT employees.id
FROM employees, departments
WHERE employees.dept_id = departments.id AND departments.name = 'HR';

+------------------------------------+----------------------------------------------------------------------+
| STEP                               | QUERY PLAN                                                           |
+------------------------------------+----------------------------------------------------------------------+
| Initial logical plan               | Eval[id] (rows=0)                                                    |
|                                    |   └ Filter[(name = 'HR')] (rows=0)                                   |
|                                    |     └ Join[INNER | (dept_id = id)] (rows=3)                          |
|                                    |       ├ Collect[doc.employees | [id, dept_id] | true] (rows=18)      |
|                                    |       └ Collect[doc.departments | [id, name] | true] (rows=6)        |
| optimizer_move_filter_beneath_join | Eval[id] (rows=3)                                                    |
|                                    |   └ Join[INNER | (dept_id = id)] (rows=3)                            |
|                                    |     ├ Collect[doc.employees | [id, dept_id] | true] (rows=18)        |
|                                    |     └ Filter[(name = 'HR')] (rows=1)                                 |
|                                    |       └ Collect[doc.departments | [id, name] | true] (rows=6)        |
| optimizer_rewrite_join_plan        | Eval[id] (rows=3)                                                    |
|                                    |   └ HashJoin[INNER | (dept_id = id)] (rows=3)                        |
|                                    |     ├ Collect[doc.employees | [id, dept_id] | true] (rows=18)        |
|                                    |     └ Filter[(name = 'HR')] (rows=1)                                 |
|                                    |       └ Collect[doc.departments | [id, name] | true] (rows=6)        |
| optimizer_merge_filter_and_collect | Eval[id] (rows=3)                                                    |
|                                    |   └ HashJoin[INNER | (dept_id = id)] (rows=3)                        |
|                                    |     ├ Collect[doc.employees | [id, dept_id] | true] (rows=18)        |
|                                    |     └ Collect[doc.departments | [id, name] | (name = 'HR')] (rows=1) |
| Final logical plan                 | Eval[id] (rows=3)                                                    |
|                                    |   └ HashJoin[INNER | (dept_id = id)] (rows=3)                        |
|                                    |     ├ Collect[doc.employees | [id, dept_id] | true] (rows=18)        |
|                                    |     └ Collect[doc.departments | [id] | (name = 'HR')] (rows=1)       |
+------------------------------------+----------------------------------------------------------------------+
EXPLAIN 5 rows in set (... sec)

Key components include:

Logical steps performed by the optimizer.
Intermediate transformations like filter pushdown or join rewrites.
Final optimized plan showing efficient operations like hash joins or indexed scans.

Performance Optimization Tips

Avoid Sequential Scans: Use indexes to improve performance on large tables.
Reduce Large Sort Operations: Ensure columns used in ORDER BY are indexed.
Optimize Joins: Analyze join types and conditions; prefer hash joins for large datasets.
Cache Results: For repetitive queries, consider caching results to reduce load.

Advanced Profiling

For queries involving Lucene-based indexing or distributed systems

Query breakdown includes shard-level details and metrics like create_weight, score, next_doc.
Metrics help identify expensive query components.

Use Cases

The EXPLAIN command is essential for:

Debugging slow queries.
Understanding execution strategies for complex SQL statements.
Optimizing database performance by refining indexes or restructuring queries.

By leveraging EXPLAIN, developers can make informed decisions to improve query efficiency in MonkDB or other SQL-based systems.

Examples

Example 1. Basic `EXPLAIN`

Shows the logical query plan without executing the query.

EXPLAIN SELECT * FROM my_table WHERE status = 'active';

Example 2. EXPLAIN ANALYZE

Executes the query and returns the actual execution plan with runtime metrics.

EXPLAIN ANALYZE SELECT * FROM my_table WHERE status = 'active';

Example 3. EXPLAIN VERBOSE

Provides additional internal information about the logical plan.

EXPLAIN VERBOSE SELECT * FROM my_table WHERE age > 30;

Example 4. EXPLAIN (option [, ...])

Use multiple options together: - ANALYZE: Execute and show actual metrics. - VERBOSE: More details. - TYPE: One of LOGICAL, DISTRIBUTED, or PLAN.

EXPLAIN (ANALYZE, VERBOSE)
SELECT id, name FROM users WHERE city = 'Bangalore';

Example 5: Basic EXPLAIN

EXPLAIN SELECT id, name FROM customers WHERE country = 'USA';

Output

Gather[customers | doc.customers | 0] → Filter[(doc.country = 'USA')]
└ Collect[doc.customers | [id, name] | country = 'USA'] | shards: [0, 1]

Example 6: EXPLAIN ANALYZE with Join

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

Possible Output

NestedLoopJoin
├ Filter[(c.country = 'USA')]
├ Collect[doc.orders | [id, customer_id]]
└ Collect[doc.customers | [id, name, country]]