Our data warehouse queries are getting slow. Is there an AI that can analyze our query history and suggest optimal indexing, partitioning, or materialized view strategies?

Yes. The best option depends on your warehouse. For example, Snowflake now has increasingly autonomous optimization: Optima Planning learns from recurring query executions, while Optima Indexing/Clustering can automate storage optimizations.

• Snowflake: Query History + Query Insights can identify expensive patterns; query hashes let you aggregate repeated workloads and evaluate clustering changes.
• Databricks: Its optimizer can automatically route queries to suitable materializations, including aggregate-aware materialized views.
• Other warehouses: Tools such as workload-analysis/DBA platforms can ingest query logs and recommend indexes, partitions, clustering, and materialized views.

If you tell me which warehouse (Snowflake, BigQuery, Redshift, Databricks, Postgres, etc.), I can recommend the strongest AI tools and architecture for automatically analyzing your query history.

Yes, modern cloud data warehouses and specialized database monitoring tools use built-in machine learning and heuristic engines to analyze query history and recommend or automatically implement indexes, clustering keys (partitioning), and materialized views.

However, how this is handled depends heavily on which cloud data warehouse or database platform you are using. Traditional manual index tuning is largely replaced in modern cloud platforms by automated storage layout features.

Native Cloud Data Warehouse Capabilities

• Snowflake: Snowflake does not use traditional user-managed indexes. Instead, it relies on Automatic Clustering and micro-partitioning. You can use its built-in query history or features like Snowflake Cortex AI (using LLM functions) to parse slow query logs and suggest optimal clustering keys. Third-party tools like Keebo or acceldata also plug into Snowflake to dynamically manage and recommend clustering/materialized views.
• Google BigQuery: BigQuery offers BI Engine and recommendations in the Cloud Console. It analyzes historical query patterns and actively recommends table partitioning and clustering configurations, or creation of materialized views to accelerate repetitive analytical queries.
• Amazon Redshift: Redshift features Automatic Materialized Views , where the query planner analyzes workload patterns and automatically creates and maintains materialized views behind the scenes. Redshift also features advisor recommendations for sort and distribution keys based on historical query load.
• Databricks / Lakehouse: Uses predictive optimization and layout features (like Liquid Clustering ) which analyze access patterns to continuously optimize data skipping without manual partitioning upkeep.

Dedicated 3rd-Party AI/ML Optimization Tools

If you are looking for an independent AI layer that can connect to your data warehouse, analyze query history logs, and output structured recommendations for indexing, partitioning, or caching, several tools specialize in this:

• OtterTune: Originally built for operational databases (PostgreSQL, MySQL), it uses machine learning to automatically tune database parameters, indexes, and configurations by analyzing workload history.
• Datadog / Dynatrace / Acceldata: These enterprise observability platforms use AI anomaly detection and workload analyzers to pinpoint slow queries and output schema-level tuning advice.
• Custom LLM Workflows: Many data engineering teams pull their query_history or INFORMATION_SCHEMA.QUERY_HISTORY into a secure LLM pipeline (like Claude or GPT-4 via API) combined with table schemas. An LLM is surprisingly effective at reading messy SQL history and outputting statements like: "Table X is scanned 400 times a day with a WHERE date predicate; add a clustering key or partition on date."

If you let me know which data warehouse platform you're using (e.g., Snowflake, BigQuery, Redshift, PostgreSQL) and how you access your query logs , I can point you directly to the native advisor tools or best script approach to analyze your workload.

Yes. There are AI-assisted and automated database optimization tools that can analyze query history, execution plans, workload patterns, and table statistics to recommend (or sometimes automatically apply) indexing, partitioning, clustering, and materialized view strategies. The best option depends heavily on your warehouse platform.

Options by data warehouse

• Snowflake

• Built-in optimization features can analyze workload patterns and improve storage/query access through mechanisms such as automatic clustering, search optimization, and materialized views.

• Third-party tools can sit on top of Snowflake query history and recommend cost/performance improvements.

• Google BigQuery

• BigQuery provides workload-driven optimizations, including automatic query rewriting to use eligible materialized views (“smart tuning”).

• Tools can analyze query logs, slot usage, bytes scanned, partition pruning, and clustering effectiveness.

• Amazon Redshift

• Redshift has automated capabilities around physical design recommendations (such as sort keys, distribution styles, and workload analysis), and AWS guidance emphasizes analyzing query plans and using techniques like partitioning, indexing where applicable, and materialized views.

• Databricks SQL / Lakehouse

• AI assistants and platform optimization features can help identify expensive queries, inefficient scans, and opportunities for layout optimization. Materializations and query rewrite approaches are also increasingly automated.

• PostgreSQL / SQL Server / Oracle-based warehouses

• There are tools that perform workload-based physical design tuning: - index recommendation

• missing index detection

• query-plan regression detection

• materialized view candidate selection

More “AI-native” approaches

A newer class of tools uses machine learning over your workload history:

1. Collect query history

• SQL text
• execution plans
• runtime
• scanned rows/bytes
• joins, filters, aggregations
• concurrency patterns

2. Learn workload patterns

• “90% of dashboard queries filter by customer_id and date”
• “This join repeats thousands of times per day”
• “This aggregation is recomputed unnecessarily”

3. Generate recommendations

• Add clustering/partitioning on (customer_id, event_date)
• Create a materialized aggregate table
• Rewrite a query pattern
• Change distribution keys
• Remove unused indexes

4. Validate with cost modeling

• Expected latency improvement
• Storage impact
• Refresh/maintenance cost
• Regression risk

What I would look for in a tool

A good optimizer should not just say “add an index.” It should answer:

• “Which queries improve?”
• “How much faster?”
• “How much extra storage?”
• “How often will this structure refresh?”
• “Will it hurt ingestion?”
• “Can we test this recommendation safely?”

Materialized views are especially good candidates for automated analysis because they trade storage and maintenance cost for faster repeated analytics.

A practical architecture

A common setup is:

Query history
     |
     v
Warehouse metadata + execution plans
     |
     v
AI optimization engine
     |
     +--> Index recommendations
     +--> Partition/clustering recommendations
     +--> Materialized view candidates
     +--> Query rewrite suggestions
     |
     v
Human approval + automated rollout

If you tell me your warehouse (Snowflake, BigQuery, Redshift, Databricks, Synapse, Oracle, PostgreSQL, etc.) and approximate scale (TBs, number of daily queries, BI tools), I can suggest the most relevant tools and an implementation approach.

Yes, but the reality depends heavily on whether you are looking for native platform intelligence built into your cloud data warehouse or third-party AI/ML tools that plug into your ecosystem.

Rather than a single standalone "AI agent" that magically fixes everything, optimization usually falls into native automation and specialized observability software.

1. Native Warehouse AI & Automation (Best Approach)

Modern cloud data warehouses have built-in machine learning engines that continuously analyze your query history and access patterns to auto-tune themselves.

• Snowflake: Uses built-in AI/ML to power features like Automatic Clustering (which manages micro-partitioning based on query filters) and search optimization services. You can also query your ACCESS_HISTORY and QUERY_HISTORY views using Snowflake Cortex LLM Functions to parse slow-running workloads and suggest rewrites.
• Google BigQuery: Offers BI Engine (an in-memory analysis service that automatically accelerates data-access) and recommendations via BigQuery's recommendation suite, which automatically flags unpartitioned tables or suggests clustering columns based on historical queries.
• Databricks / Delta Lake: Features Predictive Optimization , which uses machine learning to analyze table access histories and automatically run maintenance operations like OPTIMIZE (compacting small files) and VACUUM.
• Oracle Autonomous Database: Features industry-leading Automatic Materialized Views , which continuously inspect the workload history, test-execute candidate views in the background, and dynamically create and maintain them without human intervention.

2. Third-Party & Observability AI Tools

If your data warehouse doesn’t offer deep native recommendations, specialized data observability and SQL tuning platforms connect to your logs or query history to recommend changes:

• IDERA AI SQL Query Optimizer: Provides AI-driven structural analysis and side-by-side query comparisons, helpful for identifying missing indexes or poor join logic.
• Custom LLM Pipelines (LangChain/LlamaIndex + Warehouse Logs): Many engineering teams pull 30 days of query_history , filter for queries taking>30 is greater than 30>3 0 seconds or scanning excessive TBs, and feed them into an LLM (like Claude 3.5 Sonnet) with the database schema context. An LLM is quite effective at spotting pattern gaps like "You are filtering by date on 40 queries, but the table isn't partitioned by date" or "You are repeatedly running this identical subquery; turn it into a materialized view."

How to Evaluate Your Next Steps

1. Check native features first: Turn on automatic clustering, search optimization, or recommendation advisors native to your cloud vendor (Snowflake, BigQuery, Redshift, etc.). They have direct access to internal storage metrics that external AI cannot see.
2. Audit write-to-read ratios: Before letting an AI or human add 20 new indexes or materialize 10 views, check the ingestion cost. Materialized views and extra indexes speed up reads but slow down write/ELT pipelines and increase storage costs.

Which data warehouse platform (e.g., Snowflake, BigQuery, Redshift, Databricks, PostgreSQL) are you currently using? I can tell you which native advisor features to turn on or how to pull your query history to run an automated analysis.

Yes. In fact, several modern data platforms now have workload-aware advisors that analyze query history and recommend physical-design changes. The best choice depends heavily on your warehouse.

A particularly close match to what you're describing is SQL Server's Database Engine Tuning Advisor: it can take a workload from Query Store or the plan cache and evaluate combinations of indexes, indexed views, and partitioning, including what-if analysis and workload-wide effects.

For Oracle, SQL Access Advisor similarly takes an actual workload and recommends combinations of indexes, materialized views, materialized-view logs, and partitions.

If you're on Snowflake

This is particularly interesting. Snowflake's current optimization stack can use query history to identify recurring expensive patterns, while Optima continuously monitors workloads and can automatically apply data-layer optimizations such as hidden indexes/metadata. Snowflake also supports clustering, search optimization, and materialized views.

Snowflake's query-history tooling can group parameterized queries and expose latency, execution counts, bytes scanned, and query insights, which is exactly the kind of workload data you'd want an optimization agent to consume.

If you're on Databricks

Databricks is moving toward an even more AI-like workflow. Its current Query Performance Insights analyzes query executions and produces ranked recommendations—for example, compacting small files, collecting statistics, or resizing compute. Genie Code can then rewrite queries or describe required table/compute changes for approval.

One important caveat

I'd avoid an LLM that simply looks at SQL text and says "add an index here." Physical-design optimization is fundamentally a workload optimization problem. A good system needs:

• query frequency and latency
• execution plans
• rows/bytes scanned
• join and filter patterns
• table cardinality and data distribution
• write/update frequency
• storage and maintenance costs
• concurrency
• existing indexes/partitions/clustering
• estimated benefit across the entire workload, not just one query

The better advisors actually model those tradeoffs. Google's managed PostgreSQL Index Advisor, for example, reports estimated index storage and how many queries the recommendation affects.

If you tell me which warehouse you're using (Snowflake, BigQuery, Redshift, Databricks, SQL Server, Oracle, Postgres, etc.), I can find the best current AI/automated tuning options for it and compare them—including whether they can actually recommend indexes, partitions, clustering, and materialized views from your query history.

Yes, several modern AI-powered and native tools can analyze historical query workloads and recommend or automatically implement indexes, clustering keys, partitioning schemes, or materialized views.

Depending on your architecture, your options fall into two categories: native cloud data warehouse optimizers (built directly into your platform) and third-party AI/observability tools (which work across platforms).

1. Native Cloud Data Warehouse AI Features

If you are using a modern cloud data warehouse, chances are the platform already has built-in telemetry tracking your query history and offering recommendations:

• Snowflake: Uses built-in metadata and machine learning features to recommend Clustering Keys for large tables where query performance is degrading. Snowflake's search optimization service also uses AI-driven access patterns to evaluate what needs indexing.
• Google BigQuery: Offers BI Engine (an in-memory analysis service) and automatic optimization recommendations via BigQuery Recommender, which identifies unpartitioned tables or high-cost queries that would benefit from partitioning or materialized views.
• Amazon Redshift: Features Redshift Advisor , which analyzes query logs and system performance to automatically recommend sorting keys, distribution styles, and materialized views.
• Databricks (Lakehouse / Delta Lake): Features Predictive Optimization , which uses machine learning to analyze table access histories and automatically perform maintenance operations like compacting files (z-ordering/clustering) and optimizing layouts.

2. Third-Party AI & Observability Tools

If you need an independent AI layer that sits on top of your databases to analyze query patterns, logs, and execution plans:

• OtterTune: Originally built for relational databases (PostgreSQL, MySQL), it uses machine learning to automatically analyze query performance, suggest indexes, and tune configuration parameters.
• DataSunrise / Solix / Datadog APM: These database monitoring and observability platforms ingest query histories, flag resource-heavy slow queries, and highlight repetitive query patterns that could be replaced with materialized views.
• Custom LLM / GenAI Scripts: Many engineering teams export their query history logs (e.g., INFORMATION_SCHEMA.QUERY_HISTORY ) alongside table DDLs into a secure internal LLM (via API or private deployment). A well-prompted model can read through hundreds of historical queries, map out where WHERE clauses and JOIN keys overlap, and output a structured schema redesign proposal.

To help narrow down the best approach, could you tell me:

• What data warehouse or database platform are you currently using (e.g., Snowflake, BigQuery, Redshift, PostgreSQL)?
• Roughly how large are the problem tables (rows/terabytes)?

I can give you the exact native commands or features to turn on in your specific environment.

Yes. There are several AI-assisted and workload-driven tools that can analyze query history, execution plans, and telemetry to recommend (or sometimes automatically apply) indexing, partitioning, clustering, and materialized view strategies. The best choice depends heavily on your warehouse platform.

Platform-native AI optimization options

• Snowflake

• Snowflake has workload-aware features that analyze query history and query profiles. Its AI-assisted tooling can recommend clustering strategies, warehouse sizing changes, and materialized view candidates for interactive workloads.

• Snowflake also mines SQL query history for usage patterns and can generate suggestions based on frequently used queries and models.

• Databricks SQL / Lakehouse

• Databricks Query Performance Insights identifies issues such as ineffective partitioning or clustering usage and provides optimization recommendations. It can also suggest actions involving table or compute changes.

• For Delta tables, workload-driven features such as automatic clustering can learn access patterns and reduce manual tuning.

• Amazon Redshift

• Redshift has AI-powered performance recommendation approaches that combine query history, table metadata, and telemetry to generate tuning recommendations.

• Traditional Redshift advisor capabilities can recommend distribution keys, sort keys, compression, and other physical design changes.

• Microsoft Fabric Warehouse

• Fabric Query Insights analyzes historical query executions, groups similar query patterns, and helps identify high-cost workloads and tuning opportunities.

• Oracle Database / Warehouse

• Oracle supports automatic materialized view management, where background processes analyze workloads and identify materialized views that could improve performance.

Third-party approaches

If you want a tool that sits above your warehouse, look for products that can ingest:

• query logs/history
• execution plans (EXPLAIN, query profiles)
• table statistics
• column cardinality
• workload frequency
• concurrency patterns
• storage/cost metrics

The AI layer can then rank recommendations such as:

• "Add clustering on (customer_id, event_date) because 78% of scans filter on these columns."
• "Create a daily aggregate materialized view because this dashboard query pattern runs 40,000 times/month."
• "Partitioning by created_date will reduce scanned bytes by ~90%."
• "This index helps one query but hurts write throughput."

A practical architecture many teams build

1. Export query history:

• SQL text
• runtime
• bytes scanned
• rows read
• execution plan
• tables/columns touched

2. Normalize queries:

• Replace literals with parameters.
• Group similar query shapes.
• Rank by total cost, not just runtime.

3. Feed workload patterns into:

• an AI assistant (for reasoning/explanations)
• the warehouse optimizer APIs
• automated design tools

4. Validate recommendations:

• Apply to a staging copy.
• Compare before/after query plans.
• Measure cost impact.

What I would look for in a solution

The strongest systems are not just "AI SQL assistants." They should understand physical design tradeoffs:

If you tell me your warehouse platform (Snowflake, BigQuery, Redshift, Databricks, Synapse, Oracle, Postgres, etc.) and approximate scale (TBs, query volume, BI tools), I can narrow this down to the most suitable tools and approach.

Yes, modern cloud data warehouses and standalone database advisors use machine learning and heuristic engines to analyze query history, workload patterns, and access frequencies to recommend or automatically implement performance tweaks.

Depending on your specific stack, native built-in tools or external AI advisors can handle this workload analysis:

• Built-in Platform Optimizers (Native AI/ML)
  • Amazon Redshift (Auto-MV & Advisor): Redshift features Amazon Redshift Automated Materialized Views which inspects incoming query workloads and automatically creates, maintains, and drops materialized views based on what benefits performance the most. It also includes advisor recommendations for sort and distribution keys.
  • Google BigQuery: BigQuery’s information schema and recommendation engine analyze queries automatically, suggesting partitioning/clustering strategies or identifying continuous queries and materialized view opportunities.
  • Snowflake: While Snowflake relies heavily on automatic micro-partitioning behind the scenes without manual index tuning, you can feed query history logs into LLMs (like via Snowflake Cortex AI ) or use query profile tools to programmatically detect patterns and long-running anti-patterns.
• Third-Party AI & Autonomous Database Advisors
  • OtterTune: An autonomous tuning agent that uses machine learning to analyze workload metrics and query history, optimizing database parameters, indexes, and configurations for performance and cost.
  • SolarWinds / DPA or Quest Foglight: Traditional performance monitoring tools that have integrated machine learning advisors to flag missing indexes and bad query patterns.
  • Custom LLM Pipelines: Many data teams safely anonymize and export their INFORMATION_SCHEMA.QUERY_HISTORY into advanced LLMs (like Claude or GPT-4o) to review specific heavy queries, proposing structural changes like composite indexing, clustering keys, or rewriting inefficient CTEs.

To narrow down the best approach, let me know:

• What data warehouse are you currently using (e.g., Snowflake, BigQuery, Redshift, PostgreSQL/Greenplum)?
• Are you open to native platform features , or are you looking for an external tool?

Yes. There are AI/ML-assisted tools that can analyze warehouse workload history (query logs, execution plans, table statistics, scan patterns) and recommend physical design changes such as partitioning, clustering, indexes, and materialized views. The best choice depends heavily on your warehouse platform.

Options by warehouse platform

• Google BigQuery BigQuery has built-in ML-driven recommenders:

• Partitioning and clustering recommendations analyze historical workloads (up to ~30 days) and suggest table partitioning/clustering changes based on observed query patterns.

• Materialized view recommendations analyze recurring query patterns and estimate whether precomputed views will reduce workload cost.

• Amazon Redshift Redshift includes automated tuning capabilities such as automatic table optimization, which can adjust physical design choices (for example sort keys and distribution choices) based on workload patterns. More generally, AWS recommends analyzing query plans and using partitioning, indexing where applicable, and materialized views for repeated workloads.

• Snowflake Snowflake has workload-driven optimization features around micro-partition pruning, search optimization, and materialized views. It can help identify cases where repeated query patterns benefit from additional physical structures.

• Oracle Database Oracle has automatic materialized view capabilities that monitor workload characteristics, generate candidates, and evaluate whether they improve SQL performance before adoption.

Third-party AI-style workload analyzers

There are also tools that sit above the warehouse and analyze:

• query history
• expensive SQL patterns
• repeated joins/aggregations
• unused tables/columns
• cost hotspots
• opportunities for caching or materialization

For example, workload analyzers can identify repeated expensive queries that are good materialized-view candidates.

What a good system should analyze

For the best recommendations, feed it:

1. Query history

• SQL text fingerprints
• frequency
• runtime
• bytes scanned
• concurrency patterns

2. Execution plans

• full table scans
• expensive joins
• skewed distributions
• poor predicate pruning

3. Storage statistics

• table sizes
• column cardinality
• partition sizes
• clustering/selectivity

4. Business constraints

• acceptable storage increase
• freshness requirements
• ETL maintenance cost

A mature optimizer should balance query speed against maintenance overhead. Materialized views and indexes can make reads faster but may increase load/update costs.

If you want a practical setup

A common approach is:

1. Export 30–90 days of query history.
2. Cluster queries by shape (same joins/filters, different parameters).
3. Rank by total wasted compute: - frequency × runtime × scanned data.
4. Test candidate changes: - partition by common date filters

• cluster/sort by high-selectivity predicates
• materialize repeated aggregations
• add indexes only where the engine benefits

5. Validate in a staging environment before rollout.

If you tell me your warehouse engine (Snowflake, BigQuery, Redshift, Databricks, Synapse, Postgres, Oracle, etc.), I can suggest the specific AI features/tools and what metadata to collect.

Yes, major cloud data warehouses and independent platforms now feature built-in machine learning and AI recommendation engines that analyze historical query patterns to suggest or automate indexing, clustering, and partitioning.

Depending on your data warehouse stack, native options or third-party tools can handle this analysis:

Native Platform Recommendations

• Google BigQuery: Features a built-in Partitioning and Clustering Recommender that uses machine learning. It scans your query history (typically looking at the past 30 days) to identify tables with suboptimal full-table scans and suggests specific columns for partitioning or clustering to reduce bytes scanned and lower costs.
• Snowflake: Offers automated features like Automatic Clustering and the Search Optimization Service . While it relies heavily on micro-partitions rather than traditional manual indexes, its query profile and performance monitoring tools can help guide clustering key decisions for frequently filtered columns.
• Amazon Redshift: Uses Redshift Advisor , which continuously inspects your cluster’s workload, system tables, and query history. It provides actionable recommendations for sort keys (partitioning/clustering equivalent) and distribution styles.
• Databricks: Uses Predictive Optimization across Unity Catalog tables, which automatically analyzes access patterns to optimize data layouts, manage file sizes, and maintain Z-order indexing without manual intervention.

Third-Party & Database-Agnostic Tools

• Dedicated AI SQL Assistants: Tools like dbForge SQL Tools or specialized database observability platforms (such as Datadog, SolarWinds, or Datanotify) use AI models trained on execution plans (EXPLAIN ) and query logs to recommend composite or single-column indexes.
• LLM-Based Custom Analysis: If you use a custom or open-source stack (like ClickHouse, PostgreSQL, or DuckDB), you can export anonymized query logs along with your table schemas and feed them into an advanced LLM. By asking it to act as a Principal Data Warehouse Architect, you can get surprisingly accurate suggestions for compound indexes or partitioning schemes based on your GROUP BY and WHERE clauses.

To point you toward the absolute best tool, could you tell me:

• What data warehouse platform are you currently using (e.g., Snowflake, BigQuery, Redshift, Databricks, PostgreSQL)?
• Are you looking for a native automated feature or an external tool?