InsightWorker Logo
  • contact@verticalserve.com

Slow Query Diagnosis & Rewrite

Analyze expensive SQL queries, execution plans, joins, partitions, clustering, and scanned bytes to suggest safer rewrites.

← All use cases
Data Warehouse Optimization
Data engineers, analytics engineers, DBAs, and platform teams diagnosing slow or expensive SQL.
SQLquery plansSnowflakeBigQueryRedshift

The problem

  • Slow queries often hide the real cause inside execution plans, join order, scanned bytes, and missing indexes.
  • Teams spot obvious issues like SELECT *, but miss SARGability problems, implicit conversions, and partition pruning gaps.
  • Manual tuning takes too long across Snowflake, BigQuery, Redshift, Oracle, PostgreSQL, and MySQL.
  • Applying indexes or DDL changes directly is risky unless analysis, dry-run comparison, and approval are separated.

How InsightWorker handles it

1
Read input/query.yaml with the database type, SQL, and optional context such as frequency, table sizes, and known indexes.input/query.yaml
2
Connect to the configured database when credentials are available, or run in offline/static mode when they are not.config.yaml · offline mode
3
Collect EXPLAIN plans, table/index stats, scanned bytes, rows processed, and expensive operations such as full scans, sorts, and hash joins.
4
Detect anti-patterns such as leading wildcard LIKE, function-wrapped columns, implicit conversions, correlated subqueries, SELECT *, and OR-heavy filters.
5
Generate a rewritten query, DDL recommendations, before/after comparisons, findings.json, and optimization_report.md.
6
Apply recommendations only when the user explicitly approves the second phase with --approve.apply.py --approve

Screenshots

InsightWorker analyzing slow SQL query with EXPLAIN plan and anti-pattern detection

InsightWorker reads the input query, collects the EXPLAIN plan, and detects anti-patterns such as implicit conversions, full scans, and SARGability issues.

Optimized SQL rewrite with before/after cost comparison and DDL recommendations

Optimized rewrite generated with before/after cost comparison, index recommendations, and DDL changes staged for approval-gated execution.

Sample prompt

Natural Language Prompt
"Optimize the SQL query provided in the input/ folder. Detect anti-patterns, collect EXPLAIN plans, and suggest optimized rewrites. Include before/after cost comparisons and index recommendations. Wait for explicit approval before applying any DDL changes."
Input Configuration (query.yaml)
database_type: oracle sql: | SELECT b.BOOKING_ID, b.CUSTOMER_ID, b.VEHICLE_ID, b.LOCATION_ID, b.PICKUP_DATE, b.RETURN_DATE, b.TOTAL_AMOUNT, b.BOOKING_STATUS, COUNT(*) OVER(PARTITION BY b.CUSTOMER_ID) AS TOTAL_BOOKINGS, SUM(TO_NUMBER(b.TOTAL_AMOUNT)) OVER(PARTITION BY b.LOCATION_ID) AS LOCATION_REVENUE FROM ADMIN.BOOKING b WHERE TO_DATE(b.PICKUP_DATE, 'YYYY-MM-DD') >= TO_DATE('2024-01-01', 'YYYY-MM-DD') OR TO_DATE(b.RETURN_DATE, 'YYYY-MM-DD') <= SYSDATE OR b.BOOKING_STATUS LIKE '%COMPLETE%' OR b.TOTAL_AMOUNT IS NOT NULL ORDER BY TO_NUMBER(b.TOTAL_AMOUNT) DESC, b.CUSTOMER_ID; context: description: "Analyze vehicle booking transactions, booking revenue, and customer booking activity." frequency: "daily" table_sizes: booking: 25000000
Deliverables: optimization_report.md · findings.json · anti_patterns.json · before/after cost comparison · approval-gated apply report

Optimize slow SQL safely

Use InsightWorker to analyze query plans, rewrite expensive SQL, and apply changes only after approval.

Download Free Trial