Query Performance Tuning AI Prompt

Tune this slow data warehouse query for performance.

Query: {{slow_query}}
Current runtime: {{current_runtime}}
Target runtime: {{target_runtime}}
Platform: {{platform}}

Work through these optimizations in order:

1. Execution plan analysis:
   - Run EXPLAIN ANALYZE (or platform equivalent)
   - Identify the most expensive operations: full table scans, hash joins on large tables, sorts on large datasets
   - Check estimated vs actual row counts — large divergence indicates stale statistics

2. Filter pushdown:
   - Ensure WHERE clause filters on partitioned/clustered columns appear as early as possible
   - Check if filters are being applied before or after a JOIN — move them before the JOIN
   - Replace HAVING with WHERE where possible (filter before aggregation)

3. Join optimization:
   - Order JOINs from smallest to largest result set
   - Use broadcast/replicate hint for small dimension tables
   - Check for accidental cartesian products (missing JOIN conditions)
   - Replace correlated subqueries with JOINs or window functions

4. Aggregation optimization:
   - Pre-aggregate before joining to reduce row count going into the join
   - Use approximate aggregations (APPROX_COUNT_DISTINCT) where exact precision is not required
   - Push GROUP BY to a subquery before the outer SELECT

5. Materialization:
   - If this query runs frequently: materialize it as a table and schedule refresh
   - Create a summary table at the right grain to avoid full re-aggregation each time

6. Statistics:
   - Run ANALYZE TABLE to refresh statistics if the query plan looks wrong
   - Check column statistics: histograms for skewed columns, NDV for join columns

Return: annotated execution plan, specific rewrites for each optimization applied, and before/after runtime comparison.