optimizing-query-text · diff
git:20260121.476f750 to git:20260121.5b69ef4
135 added, 71 removed. Audit A to A.
---
name: optimizing-query-text
description: |
Optimizes Snowflake SQL query performance from provided query text. Use when optimizing Snowflake SQL for:
(1) User provides or pastes a SQL query and asks to optimize, tune, or improve it
(2) Task mentions "slow query", "make faster", "improve performance", "optimize SQL", or "query tuning"
- (3) Reviewing SQL for performance anti-patterns (SELECT *, function on filter column, etc.)
+ (3) Reviewing SQL for performance anti-patterns (function on filter column, implicit joins, etc.)
(4) User asks why a query is slow or how to speed it up
- Analyzes EXPLAIN plan, applies optimization patterns, returns improved SQL with changes summary.
---
# Optimize Query from SQL Text
- **Get explain plan → Apply best practices → Verify improvement → Return optimized query**
+ ## OUTPUT FORMAT
- ## Workflow
+ Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.
- ### 1. Get Explain Plan for Original Query
+ ## CRITICAL: Semantic Preservation Rules
- ```sql
- EXPLAIN USING JSON
- <original_query>;
- ```
+ **The optimized query MUST return IDENTICAL results to the original.**
- Save the explain plan output. Note key metrics:
- - `totalPartitions` vs `assignedPartitions` (partition pruning)
- - `rowsProduced` at each step
- - Join types and order
+ Before returning ANY optimization, verify:
+ - **Same columns**: Exact same columns in exact same order with exact same aliases
+ - **Same rows**: Filter conditions must be semantically equivalent
+ - **Same ordering**: Preserve `ORDER BY` exactly as written
+ - **Same limits**: If original has `LIMIT N`, keep `LIMIT N`. If no LIMIT, do NOT add one.
- ### 2. Identify Optimization Opportunities
+ **If you cannot guarantee identical results, return the original query unchanged.**
- Review the query for common issues:
+ ---
- | Pattern | Issue | Fix |
- |---------|-------|-----|
- | `SELECT *` | Scans all columns | Select only needed columns |
- | `WHERE DATE(col) = '...'` | Function prevents pruning | Use range: `col >= '...' AND col < '...'` |
- | `WHERE col LIKE '%value%'` | Leading wildcard = full scan | Use `col LIKE 'value%'` if possible |
- | `OR` across columns | May prevent index use | Rewrite as `UNION ALL` |
- | Large table joined first | Memory pressure | Filter early with CTE |
- | Repeated subquery | Multiple scans | Use CTE |
- | `DISTINCT` on many columns | Expensive | Check if needed, or use `GROUP BY` |
- | `ORDER BY` without `LIMIT` | Sorts entire result | Add `LIMIT` or remove if not needed |
+ ## Pattern 1: Function on Filter Column
- ### 3. Apply Optimizations
+ **Problem**: Functions on columns in WHERE clause prevent partition pruning and index usage.
- Rewrite the query following these principles:
- - Select only needed columns
- - Filter early (before joins)
- - Use CTEs to avoid repeated scans
- - Ensure filter columns match clustering keys
- - Push predicates into subqueries
+ ### CAN Fix
- ### 4. Get Explain Plan for Optimized Query
+ | Original | Optimized | Why Safe |
+ |----------|-----------|----------|
+ | `WHERE DATE(ts) = '2024-01-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-01-02'` | Equivalent range |
+ | `WHERE YEAR(dt) = 2024` | `WHERE dt >= '2024-01-01' AND dt < '2025-01-01'` | Equivalent range |
+ | `WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024` | `WHERE dt >= '2024-03-01' AND dt < '2024-04-01'` | Equivalent range |
+ | `WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-02-01'` | Same boundaries |
+ | `WHERE YEAR(dt) BETWEEN 1995 AND 1996` | `WHERE dt >= '1995-01-01' AND dt < '1997-01-01'` | Equivalent range |
- ```sql
- EXPLAIN USING JSON
- <optimized_query>;
- ```
+ ### CANNOT Fix
- ### 5. Compare and Verify
+ | Pattern | Why Not |
+ |---------|---------|
+ | `WHERE YEAR(dt) IN (SELECT year FROM ...)` | Dynamic values, cannot precompute range |
+ | `WHERE DATE(ts) = DATE(other_col)` | Comparing two columns, both need function |
+ | `WHERE EXTRACT(DOW FROM dt) = 1` | Day-of-week has no contiguous range |
+ | `WHERE DATE_TRUNC('month', dt) = '2024-01-01'` in GROUP BY | Needed for grouping logic |
+ | `SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt)` | Function in SELECT/GROUP BY is fine, only filter matters |
- Compare the two explain plans:
- - Are fewer partitions scanned?
- - Are fewer rows produced at intermediate steps?
- - Is the join order improved?
+ ---
- If the optimized query is NOT better, reconsider the approach.
+ ## Pattern 2: Function on JOIN Column
- ### 6. Return Results
+ **Problem**: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.
- Provide:
- 1. The optimized query
- 2. Summary of changes made
- 3. Expected improvement based on explain plan comparison
+ ### CAN Fix
- ## Example
+ | Original | Optimized | Why Safe |
+ |----------|-----------|----------|
+ | `ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR)` | `ON a.id = b.id` | If both are same type (e.g., INTEGER) |
+ | `ON UPPER(a.code) = UPPER(b.code)` | `ON a.code = b.code` | If data is already consistently cased |
+ | `ON TRIM(a.name) = TRIM(b.name)` | `ON a.name = b.name` | If data has no leading/trailing spaces |
- **Original:**
- ```sql
- SELECT * FROM orders o
- JOIN customers c ON o.customer_id = c.id
- WHERE DATE(o.created_at) = '2024-01-01';
- ```
+ ### CANNOT Fix
- **Optimized:**
- ```sql
- WITH filtered_orders AS (
- SELECT order_id, customer_id, amount, created_at
- FROM orders
- WHERE created_at >= '2024-01-01'
- AND created_at < '2024-01-02'
- )
- SELECT fo.order_id, fo.amount, fo.created_at, c.name, c.email
- FROM filtered_orders fo
- JOIN customers c ON fo.customer_id = c.id;
- ```
+ | Pattern | Why Not |
+ |---------|---------|
+ | `ON CAST(a.id AS VARCHAR) = b.string_id` | Types genuinely differ, CAST required |
+ | `ON DATE(a.timestamp) = b.date_col` | Different granularity, DATE() required |
+ | `ON UPPER(a.code) = b.code` | If b.code might have different case |
+ | `ON a.id = b.id + 1` | Arithmetic transformation, cannot remove |
- **Changes:**
- - Replaced `SELECT *` with specific columns
- - Replaced `DATE()` function with range filter for partition pruning
- - Used CTE to filter before join
+ ---
+
+ ## Pattern 3: NOT IN Subquery
+
+ **Problem**: `NOT IN` has poor performance and unexpected NULL behavior.
+
+ ### CAN Fix
+
+ | Original | Optimized | Why Safe |
+ |----------|-----------|----------|
+ | `WHERE id NOT IN (SELECT id FROM t WHERE ...)` | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...)` | Equivalent when subquery column is NOT NULL |
+ | `WHERE id NOT IN (SELECT id FROM t)` where id has NOT NULL constraint | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id)` | NOT NULL guarantees equivalence |
+
+ ### CANNOT Fix
+
+ | Pattern | Why Not |
+ |---------|---------|
+ | `WHERE id NOT IN (SELECT nullable_col FROM t)` | If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't |
+ | `WHERE (a, b) NOT IN (SELECT x, y FROM t)` | Multi-column NOT IN has complex NULL semantics |
+
+ **Key Rule**: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.
+
+ ---
+
+ ## Pattern 4: Repeated Subquery
+
+ **Problem**: Same subquery executed multiple times causes redundant scans.
+
+ ### CAN Fix
+
+ | Original | Optimized |
+ |----------|-----------|
+ | Subquery appears 2+ times identically | Extract to CTE, reference CTE multiple times |
+ | Same aggregation used in multiple places | Compute once in CTE |
+
+ ### CANNOT Fix
+
+ | Pattern | Why Not |
+ |---------|---------|
+ | Correlated subquery (references outer table) | Each execution is different, cannot cache |
+ | Subqueries with different filters | Not actually the same subquery |
+ | Subquery in SELECT that depends on current row | Correlation prevents extraction |
+
+ ---
+
+ ## Pattern 5: Implicit Comma Joins
+
+ **Problem**: Comma-separated tables in FROM clause are harder to read and optimize.
+
+ ### CAN Fix - Always
+
+ Convert `FROM a, b, c WHERE a.id = b.id AND b.id = c.id` to explicit JOIN syntax.
+
+ This is always safe - just restructuring, no semantic change.
+
+ ---
+
+ ## UNSAFE Optimizations (NEVER apply)
+
+ - **UNION to UNION ALL**: UNION deduplicates rows, UNION ALL does not - different results
+ - **Changing window functions**: Do not modify `SUM(SUM(x)) OVER(...)` or similar nested aggregates
+ - **Adding redundant filters**: Do not add filters in JOIN ON if same filter exists in WHERE
+ - **Changing column names**: Copy column names EXACTLY from original - do not "simplify" or rename
+ - **Changing column aliases**: Keep all aliases exactly as original
+ - **Adding early filtering in JOINs**: If a filter is in WHERE, do not duplicate it in JOIN ON clause
+
+ ---
+
+ ## Principles
+
+ 1. **Minimal changes**: Make the fewest changes necessary. Simpler optimizations are more reliable.
+ 2. **Preserve structure**: Keep subqueries, CTEs, and overall query structure unless there's a clear benefit.
+ 3. **When in doubt, don't**: If unsure whether a change preserves semantics, skip it.
+ 4. **Copy exactly**: Column names, table aliases, and expressions should be copied character-for-character.
+
+ ---
+
+ ## Priority Order
+
+ 1. **Date/time functions on filter columns** - Highest impact
+ 2. **Implicit joins to explicit JOIN** - Always safe, improves readability
+ 3. **NOT IN to NOT EXISTS** - Only if NULL-safe
+
+ ---
+
+ ## Requirements
+
+ - **Results must be identical**: Same rows, same columns, same order
+ - **Valid Snowflake SQL**: Output must execute without errors in Snowflake