Query Patterns

The patterns endpoint identifies frequently-executed query structures, enabling optimization through materialized views, indexing, or caching strategies.

Endpoint

GET /v1/analytics/patterns

curl "http://query-engine:8080/v1/analytics/patterns?days=7" \
  -H "Authorization: Bearer $JWT_TOKEN"

[
  {
    "pattern": "SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?",
    "executionCount": 4520,
    "uniqueUsers": 45,
    "avgExecutionTimeMs": 890,
    "totalBytesScanned": 107374182400,
    "cacheHitRate": 0.42,
    "suggestedOptimization": "Consider adding an index on (customer_id, created_at DESC)"
  },
  {
    "pattern": "SELECT region, SUM(revenue) FROM sales_facts WHERE date BETWEEN ? AND ? GROUP BY region",
    "executionCount": 2100,
    "uniqueUsers": 12,
    "avgExecutionTimeMs": 3200,
    "totalBytesScanned": 536870912000,
    "cacheHitRate": 0.65,
    "suggestedOptimization": "Consider creating a materialized view for region-level revenue aggregations"
  }
]

Pattern Detection

Patterns are identified by normalizing queries through the semantic cache's template extraction. Queries that share the same template hash are grouped into patterns. The analytics service tracks:

• Execution count: How many times the pattern has been executed
• Unique users: Number of distinct users running this pattern
• Average execution time: Mean latency across all executions
• Cache hit rate: Percentage served from cache
• Bytes scanned: Total data volume processed

Using Patterns for Optimization

Frequent patterns with high average execution times are prime candidates for:

1. Materialized views: Pre-compute aggregation patterns
2. Query rewriting: Optimize joins or filter pushdown
3. Index creation: Add indexes on frequently filtered columns
4. Cache warming: Proactively warm the cache for popular patterns