Query Optimization

Executive Summary

The query optimizer combines rule-based transformations, cardinality estimation, real-time bottleneck detection, and AI-powered explanations to provide embedded database performance that rivals full-scale enterprise systems while maintaining a zero-configuration footprint.

Problem Being Solved

Core Problem Statement

Manual query tuning in embedded and edge deployments creates a resource bottleneck that slows development velocity, increases operational costs, and causes performance issues to reach production. Traditional databases require specialized DBA expertise for query optimization, forcing small teams to choose between hiring expensive specialists or accepting poor query performance that degrades user experience and wastes compute resources.

Concrete Examples with Code, Config & Architecture

Example 1: Slow Query Debugging - Self-Tuning Optimization

Scenario: E-commerce application with 1M products and 10M orders experiences slow dashboard queries showing recent high-value orders. Development team lacks DBA expertise to optimize complex joins.

Architecture:

Web Application (Rust/Axum)
    ↓
HeliosDB-Lite Embedded (In-Process)
    ↓
Query Optimizer (Automatic)
    ├── Join Reordering (small table first)
    ├── Index Selection (btree on order_date)
    ├── Projection Pruning (read only needed columns)
    └── Selection Pushdown (filter before join)
    ↓
Optimized Execution Plan
    ↓
LSM Storage Engine

Configuration (heliosdb.toml):

[database]
path = "/var/lib/heliosdb/ecommerce.db"
memory_limit_mb = 512
enable_wal = true

[optimizer]
enabled = true
max_optimization_passes = 10
timeout_ms = 5000
enable_cost_based = true
enable_statistics = true

[optimizer.rules]
constant_folding = true
selection_pushdown = true
projection_pruning = true
join_reordering = true
index_selection = true

[explain]
default_mode = "verbose"  # Include cost/cardinality estimates
enable_ai_explanations = false  # Optional LLM integration

Example 2: CI/CD Performance Gates - Regression Detection

Scenario: SaaS platform development team needs to prevent query performance regressions from reaching production.

CI/CD Script (scripts/check_query_regression.sh):

#!/bin/bash
set -e

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo "=================================="
echo "Query Performance Regression Check"
echo "=================================="

# Path to baseline
BASELINE_FILE="tests/performance/baseline_costs.json"
CURRENT_FILE="tests/performance/current_costs.json"
THRESHOLD=20  # Alert if cost increases >20%

# Initialize HeliosDB with test data
echo "Initializing test database..."
./target/release/heliosdb-cli --config test.toml < tests/setup_test_data.sql

# Extract query costs
echo "Analyzing query performance..."

# Run EXPLAIN on all critical queries
cat tests/critical_queries.sql | while read -r query; do
    echo "Checking: $query"

    # Get EXPLAIN output in JSON format
    echo "EXPLAIN (FORMAT JSON) $query" | \
        ./target/release/heliosdb-cli --config test.toml \
        --output json > /tmp/explain_output.json

    # Extract cost
    current_cost=$(jq -r '.total_cost' /tmp/explain_output.json)

    # Store in current costs file
    query_hash=$(echo "$query" | md5sum | cut -d' ' -f1)
    jq -n --arg hash "$query_hash" \
          --arg query "$query" \
          --argjson cost "$current_cost" \
          '{($hash): {query: $query, cost: $cost}}' >> "$CURRENT_FILE"
done

# Merge current costs into single JSON
jq -s 'add' "$CURRENT_FILE" > /tmp/merged_current.json
mv /tmp/merged_current.json "$CURRENT_FILE"

# Compare with baseline
echo ""
echo "Comparing with baseline..."

if [ ! -f "$BASELINE_FILE" ]; then
    echo "${YELLOW}No baseline found. Creating baseline from current run.${NC}"
    cp "$CURRENT_FILE" "$BASELINE_FILE"
    exit 0
fi

# Check each query for regression
REGRESSIONS=0

jq -r 'keys[]' "$CURRENT_FILE" | while read -r query_hash; do
    current_cost=$(jq -r ".[\"$query_hash\"].cost" "$CURRENT_FILE")
    baseline_cost=$(jq -r ".[\"$query_hash\"].cost // 0" "$BASELINE_FILE")
    query_text=$(jq -r ".[\"$query_hash\"].query" "$CURRENT_FILE")

    if [ "$baseline_cost" != "0" ]; then
        # Calculate percentage change
        increase=$(echo "scale=2; (($current_cost - $baseline_cost) / $baseline_cost) * 100" | bc)

        if (( $(echo "$increase > $THRESHOLD" | bc -l) )); then
            echo "${RED}REGRESSION DETECTED:${NC}"
            echo "  Query: $query_text"
            echo "  Baseline Cost: $baseline_cost"
            echo "  Current Cost: $current_cost"
            echo "  Increase: ${increase}%"
            echo ""
            REGRESSIONS=$((REGRESSIONS + 1))
        elif (( $(echo "$increase < -10" | bc -l) )); then
            echo "${GREEN}IMPROVEMENT:${NC}"
            echo "  Query: $query_text"
            echo "  Cost reduced by ${increase#-}%"
            echo ""
        fi
    fi
done

if [ "$REGRESSIONS" -gt 0 ]; then
    echo "${RED} CI Check Failed: $REGRESSIONS query regression(s) detected${NC}"
    exit 1
else
    echo "${GREEN} CI Check Passed: No performance regressions${NC}"
    exit 0
fi

GitHub Actions Workflow (.github/workflows/performance.yml):

name: Query Performance Regression Check

on:
  pull_request:
    branches: [main, develop]
  push:
    branches: [main]

jobs:
  performance-check:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v3

      - name: Setup Rust
        uses: actions-rs/toolchain@v1
        with:
          toolchain: stable
          override: true

      - name: Build HeliosDB-Lite
        run: cargo build --release

      - name: Download baseline costs
        uses: actions/download-artifact@v3
        with:
          name: baseline-costs
          path: tests/performance/
        continue-on-error: true  # First run won't have baseline

      - name: Run regression check
        id: regression_check
        run: |
          chmod +x scripts/check_query_regression.sh
          ./scripts/check_query_regression.sh

      - name: Upload current costs
        uses: actions/upload-artifact@v3
        if: always()
        with:
          name: baseline-costs
          path: tests/performance/baseline_costs.json

      - name: Comment on PR
        if: github.event_name == 'pull_request' && failure()
        uses: actions/github-script@v6
        with:
          script: |
            const fs = require('fs');
            const costs = JSON.parse(fs.readFileSync('tests/performance/current_costs.json'));

            let comment = '##  Query Performance Regression Detected\n\n';
            comment += 'The following queries have increased in cost by >20%:\n\n';
            comment += '| Query | Baseline Cost | Current Cost | Change |\n';
            comment += '|-------|---------------|--------------|--------|\n';

            // Add regression details
            for (const [hash, data] of Object.entries(costs)) {
              comment += `| \`${data.query.substring(0, 50)}...\` | ${data.baseline_cost} | ${data.cost} | +${data.change}% |\n`;
            }

            comment += '\n**Action Required**: Investigate query changes or update baseline if this is expected.\n';

            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: comment
            });

Critical Queries File (tests/critical_queries.sql):

-- Dashboard: Recent high-value orders
SELECT p.name, SUM(o.amount) as total_sales
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date > datetime('now', '-7 days')
AND o.amount > 100
GROUP BY p.name
ORDER BY total_sales DESC
LIMIT 20;

-- User activity report
SELECT u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > datetime('now', '-30 days')
GROUP BY u.email
HAVING order_count > 0
ORDER BY total_spent DESC;

-- Inventory low stock alert
SELECT p.name, p.stock_quantity, p.category
FROM products p
WHERE p.stock_quantity < p.reorder_level
AND p.active = 1
ORDER BY p.stock_quantity ASC
LIMIT 50;

Example 3: Bottleneck Analysis - Real-Time Monitoring

Scenario: Data analytics platform experiences intermittent slow queries on large dataset aggregations. Team needs to identify bottlenecks during execution, not just estimate costs.

Configuration (heliosdb.toml):

[database]
path = "/data/analytics.db"
memory_limit_mb = 2048
enable_wal = true

[optimizer]
enabled = true
enable_cost_based = true
enable_statistics = true

[monitoring]
enable_realtime_explain = true
track_execution_stats = true
bottleneck_detection = true
bottleneck_threshold = 70  # Score >70 = bottleneck

[explain]
default_mode = "analyze"  # Include actual execution stats
show_bottleneck_scores = true

Example 4: Cost Estimation - Capacity Planning

Scenario: DevOps team needs to estimate infrastructure requirements for new feature that will add complex reporting queries. Current approach of "deploy and monitor" leads to over-provisioning.

Example 5: Optimizer Hints - Advanced Tuning (Edge Cases)

Scenario: Complex query with unusual data distribution where automatic optimizer makes suboptimal choice. Developer needs ability to override optimizer decisions for specific edge cases.

Architecture:

┌────────────────────────────────────────────────┐
│  Query with Optimizer Hints (Advanced Users)  │
├────────────────────────────────────────────────┤
│  /*+ HINT(parameter=value) */                  │
│    ↓                                            │
│  Hint Parser                                   │
│    └─ Extract optimizer directives             │
│                                                 │
│  Cost-Based Optimizer                          │
│    ├─ Apply hints as constraints               │
│    ├─ Force specific join algorithm            │
│    ├─ Disable certain optimization rules       │
│    └─ Override cost parameters                 │
│                                                 │
│  Execution Plan (Hint-Guided)                  │
└────────────────────────────────────────────────┘

Configuration (heliosdb.toml):

[optimizer]
enabled = true
enable_cost_based = true
enable_hints = true  # Allow query hints

# Hint behavior
hint_override_cost_threshold = 2.0
warn_on_bad_hints = true  # Alert if hint degrades performance

Supported Optimizer Hints:

-- Join algorithm hints
/*+ USE_NL(table1, table2) */          -- Force nested loop join
/*+ USE_HASH(table1, table2) */         -- Force hash join
/*+ USE_MERGE(table1, table2) */        -- Force merge join

-- Index hints
/*+ FORCE_INDEX(table, index_name) */   -- Force specific index
/*+ NO_INDEX(table, index_name) */      -- Avoid specific index
/*+ INDEX_SCAN(table) */                -- Prefer index scan over seq scan

-- Optimization rule hints
/*+ NO_PUSHDOWN */                      -- Disable filter/projection pushdown
/*+ NO_REORDER */                       -- Disable join reordering
/*+ MATERIALIZE(subquery) */            -- Force subquery materialization

-- Parallelism hints
/*+ PARALLEL(4) */                      -- Use 4 parallel workers
/*+ NO_PARALLEL */                      -- Disable parallelism

-- Cost parameter overrides
/*+ SET(random_page_cost=2.0) */       -- Override cost parameter
/*+ SET(work_mem='512MB') */            -- Override memory limit

When to Use Hints:

• Edge cases where statistics are stale or unrepresentative
• Time-sensitive queries requiring guaranteed performance
• Advanced users who understand query optimization internals

Adoption Strategy

Phase 1: Proof of Concept (Weeks 1-4)

Target: Validate query optimization benefits in development environment

Tactics:

• Identify 10-20 critical slow queries from production logs
• Run EXPLAIN ANALYZE on current database to establish baseline
• Migrate test dataset to HeliosDB Lite
• Compare query performance and optimization insights
• Demonstrate cost reduction and bottleneck detection to stakeholders

Phase 2: Pilot Deployment (Weeks 5-12)

Target: Deploy to non-critical microservices or development environments

Tactics:

• Integrate HeliosDB Lite into 1-3 microservices (low-risk deployments)
• Enable regression detection in CI/CD pipeline
• Monitor query performance and optimization effectiveness
• Train development team on EXPLAIN usage and optimizer hints
• Collect metrics: query latency, infrastructure costs, developer time saved

Phase 3: Full Rollout

Target: Organization-wide deployment across all microservices and applications

Tactics:

• Establish performance baseline for all services
• Deploy automated regression detection to all CI/CD pipelines
• Create internal documentation and best practices guide
• Monitor cost savings and performance improvements
• Share success metrics with leadership (cost reduction, velocity increase)

Conclusion

Query optimization has traditionally been the domain of specialized database administrators, creating a bottleneck that slows development teams and leads to over-provisioned infrastructure. HeliosDB Lite eliminates this barrier by delivering a self-tuning database engine that provides PostgreSQL-level query optimization in an embedded, zero-configuration package. By combining cost-based optimization, real-time bottleneck detection, automatic regression prevention, and AI-powered explanations, HeliosDB Lite empowers development teams to ship high-performance applications without SQL performance expertise.

The result is a fundamental shift from reactive performance firefighting to proactive optimization, enabling teams to focus on building features instead of tuning databases.

Take Action: Eliminate the DBA bottleneck and slash infrastructure costs while accelerating development velocity. Download HeliosDB Lite today and experience automatic query optimization that just works—no configuration, no manual tuning, no specialized expertise required.