Execution Plans

Execution Plans help you see how your database executes a query so you can debug, tune performance, and avoid costly mistakes. DBCode supports database native plan generation (EXPLAIN and, where supported, ANALYZE/actual plans) and provides an interactive Plan Explorer for deep inspection.

  • EXPLAIN: Generates the estimated execution plan without running the query.
  • ANALYZE / Actual Plan: Executes the query and returns actual timing and row counts (only on engines that support it).
  • Plan Explorer: An interactive view to explore nodes, costs, timings, cardinality, predicates, and potential bottlenecks.

How To Use

You can run Execution Plans from both the SQL Editor and DBCode Notebooks:

  1. Write or select a SQL query.
  2. Choose one of:
    • Explain: Runs the database’s EXPLAIN for the selected query.
    • Analyze: Runs the database’s ANALYZE/actual plan (where supported) and returns runtime metrics.
  3. View results in the Plan Explorer panel.

Tip: If no text is selected, DBCode uses the active statement under your cursor.

Plan Explorer

The Plan Explorer renders plans in a clear, navigable tree so you can quickly find hotspots and understand operator behavior.

  • Node Tree: Expand/collapse nodes to explore scans, joins, sorts, aggregates, etc.
  • Metrics At A Glance: View estimated vs. actual rows, cost, and time (when available).
  • Predicates & Filters: Inspect join conditions, index usage, filter predicates, and projections.
  • Hotspot Highlighting: Identify the most expensive operators to focus tuning efforts.
  • Search & Navigation: Quickly jump to nodes by name or operator type.

Plan Explorer interactive example. Open in a new tab

Supported Engines

DBCode issues database native commands for plan generation and only shows options that are supported by your engine and permissions. Common examples include:

  • PostgreSQL: EXPLAIN, EXPLAIN ANALYZE
  • MySQL/MariaDB: EXPLAIN (ANALYZE available on modern versions)
  • SQLite: EXPLAIN QUERY PLAN
  • SQL Server: Estimated vs. actual execution plans
  • Oracle: EXPLAIN PLAN

When To Use Explain vs. Analyze

  • Use EXPLAIN to review the optimizer’s plan safely without executing the query.
  • Use ANALYZE to compare estimates with actuals, validate cardinality, and pinpoint miss estimates and slow operators. This will execute the query.

Tuning Workflow

  1. Explain the query to understand operator choices and index usage.
  2. Identify potential issues (full scans, mismatched join orders, sorts, spills).
  3. Analyze (where supported) to compare estimated vs. actual rows and timings.
  4. Apply changes (indexes, rewritten predicates, smaller result sets, better join order).
  5. Re-run Explain/Analyze to validate improvements.

Troubleshooting

  • Permissions: Some databases require specific roles/privileges to generate plans.
  • Long Running Queries: ANALYZE executes the query; prefer EXPLAIN during iteration.
  • Parameter Effects: Different parameter values can lead to different plans; test representative inputs.
  • Engine Support: If a button isn’t shown, your database/driver may not support that plan type yet in DBCode.

Execution Plans and the Plan Explorer give you clear visibility into query behavior so you can optimize confidently, without leaving VS Code.