Skip to content
HeliosDB HeliosDB Full

Tutorial: Understanding EXPLAIN Plans

Tutorial: Understanding EXPLAIN Plans

Level: Intermediate | Time: 15 minutes | Version: 7.2.0

HeliosDB’s EXPLAIN system provides 7 output formats and AI-powered analysis.

Basic EXPLAIN

-- Default text format
EXPLAIN SELECT * FROM users WHERE age > 25;


-- With estimated cost and row counts
-- Output includes:
-- - Physical plan tree
-- - Estimated cost & rows
-- - Active features (SIMD, indexing, etc.)
-- - Optimizer decisions with reasoning

Output Formats

-- JSON (for tools/scripts)
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE age > 25;


-- YAML (human-readable structured)
EXPLAIN (FORMAT YAML) SELECT * FROM users WHERE age > 25;


-- XML (enterprise ETL integration)
EXPLAIN (FORMAT XML) SELECT * FROM users WHERE age > 25;


-- HTML (styled web dashboard with full analysis)
EXPLAIN (FORMAT HTML) SELECT * FROM users WHERE age > 25;


-- GraphViz DOT (generate PNG/PDF diagrams)
EXPLAIN (FORMAT GRAPHVIZ) SELECT * FROM users WHERE age > 25;
-- Then: dot -Tpng output.dot -o plan.png


-- Mermaid (GitHub/GitLab markdown)
EXPLAIN (FORMAT MERMAID) SELECT * FROM users WHERE age > 25;

Advanced Options

-- ANALYZE: attempt actual execution with timing
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;


-- The HTML format automatically enables all analysis:
-- VERBOSE + FEATURES + WHY_NOT + STORAGE
EXPLAIN (FORMAT HTML) SELECT * FROM orders JOIN users ON orders.user_id = users.id;

Understanding the Output

Feature Detection

HeliosDB automatically detects which optimizations are active:

  • Partition Pruning — Range predicates on partitioned tables
  • SIMD Vectorization — Numeric aggregations with 4x speedup
  • Index Usage — Automatic index scan selection
  • Parallelism — Multi-worker parallel scans/joins
  • Predicate Pushdown — Filter pushed to storage layer

”Why Not” Analysis

Explains why optimizations WEREN’T applied:

  • Missing indexes with suggested fix
  • Stale statistics impact
  • Cardinality estimation errors
  • Alternative optimization suggestions

What’s Next