[P2] Add Query Plan / Explain functionality

[sajeetharan]

Feature Request

Priority: P2 (Medium)

Description

CosmosDB Shell shows query metrics and RU cost but does not provide query plan visualization or explain functionality. Understanding how queries are executed helps developers optimize performance.

Comparable Features in Other Shells

• mongosh: cursor.explain(), PlanCache methods
• sqlcmd: Execution plans via SET SHOWPLAN

Proposed Behavior

Add an explain command or --explain flag:

• Show query execution plan
• Display index utilization details
• Show which indexes were used vs. scanned
• Indicate full scan vs. index seek

Example Usage

query "SELECT * FROM c WHERE c.city = 'Seattle'" --explain
explain "SELECT * FROM c WHERE c.status = 'active'"

[tnaum-ms]

Full ack, this is a great addition.

One thing worth going further on though: beyond printing the raw JSON, consider adding a brief plain-language evaluation of the plan inline.

In the vscode-documentdb extension we did quite a bit of work parsing explain output and surfacing a short human-readable summary in the UI: whether an index was used, if there is a full collection scan, which index was selected. It made a real difference for users who do not read explain JSON fluently.

This issue actually makes me realize we should bring the same idea into the terminal output of the documentdb shell as well, not just keep it in the rich UI. A short evaluation line right after the explain output would be a strong differentiator here too.

[sajeetharan]

Agentic & Automation Roadmap framing (item G3)

This maps to G3 — explain / query plan surfacing in the draft agentic roadmap (docs/agentic-roadmap.md). For guardrails and observability, exposing the query plan (index usage, cross-partition fan-out, estimated RU) lets an agent reason about cost before executing an expensive query and lets CI flag regressions. Complements RU-cost surfacing (item G2, #162).