optimizing-query-by-id

# Optimize Query from Query ID **Fetch query → Get profile → Apply best practices → Verify improvement → Return optimized query** ## Workflow ### 1. Fetch Query Details from Query ID ```sql SELECT query_id, query_text, total_elapsed_time/1000 as seconds, bytes_scanned/1e9 as gb_scanned, bytes_spilled_to_local_storage/1e9 as gb_spilled_local, bytes_spilled_to_remote_storage/1e9 as gb_spilled_remote, partitions_scanned, partitions_total, rows_produced FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE query_id = '<query_id>'; ``` Note the key metrics: - `seconds`: Total execution time - `gb_scanned`: Data read (lower is better) - `gb_spilled`: Spillage indicates memory pressure - `partitions_scanned/total`: Partition pruning effectiveness ### 2. Get Query Profile Details ```sql -- Get operator-level statistics SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>')); ``` Look for: - Operators with high `output_rows` vs `input_rows` (explosions) - TableScan operators with high bytes - Sort/Aggregate operators with spillage ### 3. Identify Optimization Opportunities Based on profile, look for: | Metric | Issue | Fix | |--------|-------|-----| | partitions_scanned = partitions_total | No pruning | Add filter on cluster key | | gb_spilled > 0 | Memory pressure | Simplify query, increase warehouse | | High bytes_scanned | Full scan | Add selective filters, reduce columns | | Join explosion | Cartesian or bad key | Fix join conditio...