EXPLAIN PLAN Quick Start Guide¶

Feature: AI-Powered Query Explanation Version: HeliosDB v7.0 Status: Production Ready

What is EXPLAIN?¶

EXPLAIN shows you how HeliosDB will execute your query. Unlike traditional databases, HeliosDB's EXPLAIN uses AI to explain query plans in plain English, making optimization accessible to everyone.

Quick Examples¶

1. Basic EXPLAIN¶

EXPLAIN SELECT * FROM users WHERE age > 25;

Output:

→ Scan users [cost=200.00, rows=1000]
  Filter: age > 25

2. AI-Powered EXPLAIN (Recommended)¶

EXPLAIN (AI) SELECT * FROM users WHERE age > 25;

Output:

This query searches all users to find those older than 25.
It reads the entire table (2,550 rows) because no index exists on 'age'.

Performance: MODERATE (50ms)
Suggestion: CREATE INDEX idx_users_age ON users(age);
Expected improvement: 90% faster

3. Full Analysis¶

EXPLAIN (ANALYZE) SELECT * FROM users WHERE age > 25;

Output: Complete analysis including: - AI explanation - Why-Not analysis (why optimizations weren't applied) - Optimizer decisions - Suggested improvements - Configuration recommendations

EXPLAIN Modes¶

Mode	When to Use	Output
`STANDARD`	Quick plan overview	Basic tree structure
`VERBOSE`	Detailed cost analysis	Costs + decisions
`AI`	Beginner-friendly	Natural language + suggestions
`ANALYZE`	Deep optimization	Full analysis + Why-Not

Usage¶

-- Standard mode (default)
EXPLAIN SELECT ...;

-- Verbose mode
EXPLAIN (VERBOSE) SELECT ...;

-- AI mode (natural language)
EXPLAIN (AI) SELECT ...;

-- Full analysis
EXPLAIN (ANALYZE) SELECT ...;

Output Formats¶

Text Format (Default)¶

EXPLAIN SELECT * FROM users;

Human-readable text with structure.

JSON Format¶

EXPLAIN (FORMAT JSON) SELECT * FROM users;

Machine-readable for tools and scripts.

YAML Format¶

EXPLAIN (FORMAT YAML) SELECT * FROM users;

Configuration-friendly format.

Tree Format¶

EXPLAIN (FORMAT TREE) SELECT * FROM users;

Visual tree with ANSI colors.

Combining Options¶

-- AI mode with JSON output
EXPLAIN (AI, FORMAT JSON) SELECT * FROM users WHERE age > 25;

-- Full analysis with YAML output
EXPLAIN (ANALYZE, FORMAT YAML) SELECT * FROM users JOIN orders ON users.id = orders.user_id;

Understanding Output¶

Cost¶

cost=250.00

Estimated query execution cost
Lower is better
Relative metric (not time)

Interpretation: - < 100: Fast - 100-1000: Moderate - 1000-10000: Slow - > 10000: Very slow

Rows¶

rows=1000

Estimated number of rows
Helps understand data volume
Used for join ordering

Node Types¶

Node	Meaning
`Scan`	Read table data
`Filter`	Apply WHERE conditions
`Join`	Combine tables
`Aggregate`	GROUP BY operations
`Sort`	ORDER BY operations
`Limit`	LIMIT/OFFSET

AI Explanations¶

Setting Up AI (Optional)¶

AI explanations work out-of-the-box with local models. For cloud LLMs:

Option 1: OpenAI¶

export HELIOSDB_LLM_PROVIDER=openai
export HELIOSDB_LLM_API_KEY=sk-...
export HELIOSDB_LLM_MODEL=gpt-4

Option 2: Anthropic Claude¶

export HELIOSDB_LLM_PROVIDER=anthropic
export HELIOSDB_LLM_API_KEY=sk-ant-...
export HELIOSDB_LLM_MODEL=claude-3-opus

Option 3: Local Ollama (Free, Private)¶

# Install Ollama
curl -fsSL https://ollama.com/install.sh | sh

# Download model
ollama pull llama2

# Configure HeliosDB
export HELIOSDB_LLM_PROVIDER=ollama
export HELIOSDB_LLM_MODEL=llama2

AI Explanation Components¶

Summary: High-level query description
Walkthrough: Step-by-step execution
Performance: Speed prediction
Bottlenecks: What's slowing it down
Suggestions: How to improve
Warnings: Things to watch out for

Why-Not Analysis¶

Explains why optimizations weren't applied.

Example¶

EXPLAIN (ANALYZE) SELECT * FROM users WHERE email = 'test@example.com';

Why-Not Output:

WHY NOT ANALYSIS

Index 'idx_users_email' exists but wasn't used because:
- Query uses SELECT * (requires all columns)
- Index is not covering (doesn't include all columns)
- Estimated cost: Index scan (150.0) vs Seq scan (100.0)
- Seq scan is actually faster for this query

Suggestion: If this query runs frequently, create a covering index:
  CREATE INDEX idx_users_email_covering ON users(email)
    INCLUDE (id, name, created_at);

What Why-Not Checks¶

Unused Indexes: Why indexes weren't used
Stale Statistics: If data is outdated
Configuration: If settings are suboptimal
Cardinality: If row estimates are off

Common Scenarios¶

Scenario 1: Slow Query¶

EXPLAIN (AI) SELECT * FROM orders WHERE created_at > '2024-01-01';

AI Response:

Performance: SLOW (2.5s)

Bottleneck: Full table scan on 10 million rows

Suggestion: CREATE INDEX idx_orders_created_at ON orders(created_at);
Expected improvement: 95% faster (2.5s → 125ms)

Scenario 2: Complex Join¶

EXPLAIN (AI)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 25
GROUP BY u.name;

AI Response:

This query joins users with orders, filters by age, and counts orders per user.

Execution:
1. Scan users table, filter age > 25
2. Build hash table from filtered users
3. Scan orders, probe hash table
4. Group results and count

Performance: MODERATE (150ms)

Optimization: Create index on users(age) to speed up filtering
Expected improvement: 40% faster

Scenario 3: Missing Index¶

EXPLAIN (ANALYZE) SELECT * FROM products WHERE category = 'electronics';

Why-Not Output:

No index exists on 'category' column

Current approach: Sequential scan (cost: 1000.0)
With index: Index scan (cost: 50.0)
Potential speedup: 20x faster

Recommendation:
  CREATE INDEX idx_products_category ON products(category);

Best Practices¶

1. Use AI Mode for Learning¶

-- Start with AI to understand your queries
EXPLAIN (AI) SELECT ...;

2. Check Why-Not for Optimization¶

-- When query is slow, use ANALYZE
EXPLAIN (ANALYZE) SELECT ...;

3. Use JSON for Automation¶

-- Export to JSON for scripts
EXPLAIN (FORMAT JSON) SELECT ... > plan.json

4. Compare Before/After¶

-- Before optimization
EXPLAIN (AI) SELECT * FROM users WHERE age > 25;

-- Create index
CREATE INDEX idx_users_age ON users(age);

-- After optimization
EXPLAIN (AI) SELECT * FROM users WHERE age > 25;

Interpreting Suggestions¶

Index Suggestions¶

Suggestion: CREATE INDEX idx_users_age ON users(age);

When to create: - Query runs frequently (>100 times/day) - Table is large (>10,000 rows) - Filter is selective (<10% of rows)

When not to create: - One-time query - Small table - Filter matches most rows

Configuration Suggestions¶

Suggestion: Increase work_mem to 512MB

When to apply: - Memory is available - Joins/sorts are spilling to disk - Query is critical

When not to apply: - Limited memory - Many concurrent queries - Not a bottleneck

Troubleshooting¶

"LLM not available"¶

Solution: Install Ollama (free, local)

curl -fsSL https://ollama.com/install.sh | sh
ollama pull llama2

"Cost seems wrong"¶

Solution: Run ANALYZE to update statistics

ANALYZE users;

"Suggestion doesn't help"¶

Solution: Use full analysis

EXPLAIN (ANALYZE, VERBOSE) SELECT ...;

Quick Reference¶

Syntax¶

EXPLAIN [ ( option [, ...] ) ] statement

Options:
  VERBOSE           Show detailed costs
  AI                Natural language explanation
  ANALYZE           Full analysis with Why-Not
  FORMAT { TEXT | JSON | YAML | TREE }

Examples¶

-- Basic
EXPLAIN SELECT * FROM users;

-- With options
EXPLAIN (VERBOSE) SELECT * FROM users;
EXPLAIN (AI) SELECT * FROM users;
EXPLAIN (ANALYZE) SELECT * FROM users;
EXPLAIN (AI, FORMAT JSON) SELECT * FROM users;

-- Complex query
EXPLAIN (ANALYZE)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age > 25
GROUP BY u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;

Performance Tips¶

Always EXPLAIN before running expensive queries
Use ANALYZE mode to understand bottlenecks
Follow AI suggestions (they're usually right)
Create indexes on filtered/joined columns
Run ANALYZE after large data changes
Monitor query costs over time
Use covering indexes for frequently accessed data

Learn More¶

Support¶

Issues: GitHub Issues
Community: Discord
Docs: heliosdb.dev/docs

Last Updated: 2025-11-14 HeliosDB Version: 7.0 Feature Status: Production Ready