Query Optimization for ECM-QL

Query Performance Fundamentals

ECM-QL query performance depends on index utilization, predicate ordering, and execution planning. This guide covers optimization techniques.

Index Utilization

Analyze Index Coverage

// Query with index scan
{
  "filter": {
    "and": [
      {"field": "context_type", "op": "eq", "value": "user-context"},
      {"field": "tenant_id", "op": "eq", "value": "tenant-123"}
    ]
  }
}
// Index: (context_type, tenant_id) - Efficient

// Query requiring full scan
{
  "filter": {
    "field": "data.preferences.theme", "op": "eq", "value": "dark"
  }
}
// No index on nested field - Full scan

Composite Index Design

// Design indices for common query patterns
const indices = [
  // High-selectivity field first
  { fields: ['tenant_id', 'context_type', 'created_at'] },
  
  // Support range queries on last field
  { fields: ['context_type', 'updated_at'] },
  
  // Covering index for common projection
  { fields: ['id', 'context_type'], include: ['data.name', 'metadata'] }
];

Filter Optimization

Predicate Ordering

// Optimizer reorders predicates by selectivity
{
  "filter": {
    "and": [
      // Low selectivity - 50% of data
      {"field": "active", "op": "eq", "value": true},
      // High selectivity - 0.1% of data (evaluated first)
      {"field": "customer_id", "op": "eq", "value": "cust-123"}
    ]
  }
}

Short-Circuit Evaluation

// OR filters benefit from short-circuit
{
  "filter": {
    "or": [
      // If this matches, skip remaining predicates
      {"field": "priority", "op": "eq", "value": "critical"},
      {"field": "age_days", "op": "gt", "value": 30}
    ]
  }
}

Projection Optimization

Field Selection

// Only fetch needed fields
{
  "filter": {...},
  "projection": ["id", "data.summary", "metadata.updated_at"]
}

// Benefits:
// - Less data transferred
// - Covering index possible
// - Faster serialization

Pagination Strategies

Cursor-Based Pagination

// Efficient for large offsets
{
  "filter": {...},
  "sort": [{"field": "created_at", "order": "desc"}],
  "cursor": "eyJ0IjoiMjAyNC0wMS0xNVQxMDowMDowMFoifQ==",
  "limit": 100
}

// Cursor decodes to: {"t": "2024-01-15T10:00:00Z"}
// Query: WHERE created_at < cursor.t ORDER BY created_at DESC LIMIT 100

// vs OFFSET-based:
// OFFSET 10000 LIMIT 100 - Must skip 10000 rows

Query Analysis

Explain Query

// Request query execution plan
{
  "operation": "context.explain",
  "query": {...}
}

// Response shows execution plan
{
  "plan": {
    "type": "index_scan",
    "index": "tenant_type_idx",
    "filter_pushdown": ["tenant_id", "context_type"],
    "estimated_rows": 150,
    "estimated_cost": 0.5
  }
}

Conclusion

ECM-QL query optimization requires proper indexing, predicate ordering, selective projections, and efficient pagination. Use query analysis tools to understand execution plans and optimize accordingly.

> Query Optimization for ECM-QL