sql-query-optimizer

# 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 ```json { "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 ```json { "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 ```json { "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 ```json { "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 ```json { "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 | ## Integration Points ### MCP Server Integration - **Snowflake MCP** - Real-time execution plan analysis - **BigQuery MCP** - Cost estimation and slot analysis - **Redshift MCP** - Query execution and statistics ### Related Skills - Data Quality Profiler (SK-DEA-005) - Table statistics gathering - dbt Project Analyzer (SK-DEA-003) - Model query optimization ### Applicable Processes - Query Optimization (`query-optimization.js`) - Data Warehouse Setup (`data-warehouse-setup.js`) - BI Dashboard Development (`bi-dashboard.js`) - OBT Creation (`obt-creation.js`) ## References - [Snowflake Query Performance](https://docs.snowflake.com/en/user-guide/performance-query) - [BigQuery Best Practices](https://cloud.google.com/bigquery/docs/best-practices-performance-overview) - [Redshift Performance Tuning](https://docs.aws.amazon.com/redshift/latest/dg/c_designing-queries-best-practices.html) - [Databricks SQL Best Practices](https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select.html) ## Version History - **1.0.0** - Initial release with multi-platform support