SQL Query Optimizer

Analyzes and optimizes SQL queries across different data warehouse platforms with platform-specific recommendations.

Overview

This skill examines SQL queries to identify performance bottlenecks, suggest optimizations, and provide platform-specific recommendations for Snowflake, BigQuery, Redshift, and Databricks. It analyzes query execution plans, recommends indexes/clustering keys, and identifies anti-patterns.

Capabilities

Query execution plan analysis - Parse and analyze EXPLAIN outputs
Index recommendations - Suggest clustering keys, sort keys, partition keys
Join optimization - Identify inefficient join patterns and suggest improvements
Subquery elimination - Convert correlated subqueries to CTEs or joins
CTE optimization - Materialize vs reference optimization
Window function optimization - Frame and partition optimization
Predicate pushdown validation - Verify filter pushdown effectiveness
Clustering key recommendations - Platform-specific clustering strategies
Materialized view suggestions - Identify candidates for materialized views
Platform-specific optimizations - Snowflake, BigQuery, Redshift, Databricks

Input Schema

{
  "query": {
    "type": "string",
    "description": "The SQL query to analyze",
    "required": true
  },
  "platform": {
    "type": "string",
    "enum": ["snowflake", "bigquery", "redshift", "databricks", "postgres"],
    "required": true,
    "description": "Target data warehouse platform"
  },
  "tableStatistics": {
    "type": "object",
    "description": "Table statistics including row counts, column cardinality",
    "properties": {
      "tables": {
        "type": "array",
        "items": {
          "name": "string",
          "rowCount": "number",
          "sizeGB": "number",
          "columns": "array"
        }
      }
    }
  },
  "executionPlan": {
    "type": "object",
    "description": "Query execution plan (EXPLAIN output)"
  },
  "queryHistory": {
    "type": "object",
    "description": "Historical query performance metrics"
  },
  "optimizationGoals": {
    "type": "array",
    "items": {
      "type": "string",
      "enum": ["latency", "cost", "throughput", "scan_reduction"]
    },
    "default": ["latency", "cost"]
  }
}

Output Schema

{
  "optimizedQuery": {
    "type": "string",
    "description": "The optimized SQL query"
  },
  "improvements": {
    "type": "array",
    "items": {
      "type": {
        "type": "string",
        "enum": ["join", "predicate", "aggregation", "cte", "window", "scan", "index"]
      },
      "description": "string",
      "impact": "high|medium|low",
      "lineNumber": "number",
      "originalCode": "string",
      "optimizedCode": "string"
    }
  },
  "indexRecommendations": {
    "type": "array",
    "items": {
      "table": "string",
      "type": "clustering|sort|partition|index",
      "columns": "array",
      "rationale": "string",
      "ddl": "string"
    }
  },
  "estimatedImprovement": {
    "scanReduction": {
      "type": "number",
      "description": "Percentage reduction in data scanned"
    },
    "timeReduction": {
      "type": "number",
      "description": "Percentage reduction in execution time"
    },
    "costReduction": {
      "type": "number",
      "description": "Percentage reduction in query cost"
    }
  },
  "antiPatterns": {
    "type": "array",
    "items": {
      "pattern": "string",
      "severity": "high|medium|low",
      "location": "string",
      "suggestion": "string"
    }
  },
  "platformSpecificNotes": {
    "type": "array",
    "items": "string"
  }
}

Usage Examples

Basic Query Optimization

{
  "query": "SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at > '2024-01-01'",
  "platform": "snowflake"
}

With Execution Plan Analysis

{
  "query": "SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id",
  "platform": "bigquery",
  "executionPlan": {
    "stages": [...],
    "totalBytesProcessed": 1073741824
  },
  "optimizationGoals": ["cost", "scan_reduction"]
}

With Table Statistics

{
  "query": "SELECT ... complex query ...",
  "platform": "redshift",
  "tableStatistics": {
    "tables": [
      {
        "name": "orders",
        "rowCount": 10000000,
        "sizeGB": 50,
        "columns": [
          {"name": "order_id", "cardinality": 10000000},
          {"name": "customer_id", "cardinality": 500000}
        ]
      }
    ]
  }
}

Platform-Specific Optimizations

Snowflake

Optimization	Description
Clustering keys	Recommend micro-partition clustering
Result cache	Identify queries benefiting from caching
Query acceleration	Suggest QUERY_ACCELERATION_MAX_SCALE_FACTOR
Warehouse sizing	Right-size warehouse recommendations

BigQuery

Optimization	Description
Partitioning	DATE/TIMESTAMP partitioning recommendations
Clustering	Up to 4 clustering columns
BI Engine	Identify BI Engine-eligible queries
Slots	Estimate slot usage optimization

Redshift

Optimization	Description
Sort keys	COMPOUND vs INTERLEAVED recommendations
Distribution	KEY, EVEN, ALL distribution strategies
Compression	Column encoding recommendations
Vacuum	VACUUM and ANALYZE recommendations

Databricks

Optimization	Description
Z-ordering	Multi-column Z-order recommendations
Delta cache	Caching strategy recommendations
Photon	Photon-eligible query patterns
Adaptive execution	AQE configuration suggestions

Common Anti-Patterns Detected

Query Structure

Anti-Pattern	Impact	Fix
SELECT *	High	Specify columns explicitly
Correlated subqueries	High	Convert to JOIN or CTE
DISTINCT on large datasets	Medium	Use GROUP BY or window functions
Non-SARGable predicates	High	Rewrite for index usage

Join Issues

Anti-Pattern	Impact	Fix
Cartesian products	Critical	Add join conditions
Implicit joins	Medium	Use explicit JOIN syntax
Wrong join order	High	Reorder by selectivity
Missing indexes on join keys	High	Add clustering/sort keys

Aggregation Issues

Anti-Pattern	Impact	Fix
GROUP BY ordinal	Low	Use column names
Aggregating before filter	High	Filter first, then aggregate
Over-grouping	Medium	Reduce GROUP BY columns

sql-query-optimizer