Skip to main content
Last updated on

Query performance insights

When your queries run, Databricks returns query performance insights that flag opportunities to improve performance and report optimizations it already applied. Each insight includes a recommendation you can act on, such as compacting small files, collecting statistics, or resizing a warehouse.

Find insights and recommendations for your query​

Insights appear in your query history and in the query profile. The query details panel shows a summary of insights, ranked by their estimated effect on total task duration. The Performance insights tab in the query profile shows the full detail for each insight.

Optimize with Genie Code​

When a query has actionable insights, select Optimize to open Genie Code. For insights that require a query change, Genie Code rewrites the query and presents the changes for your approval. For insights that involve table or compute changes, Genie Code summarizes the recommended actions as plain-language text.

To learn more about working with Genie Code, see Genie Code.

Query optimization insights​

COVERAGE_FILTER_KEYS_CLUSTERING​

The table is clustered by one or more keys that aren't used in filters during the table scan.

Recommendation: Add filters on the clustering keys to reduce bytes read.

COVERAGE_FILTER_KEYS_PARTITIONING​

The table is partitioned by one or more keys that aren't used in filters during the table scan.

Recommendation: Add filters on the partitioning keys to reduce bytes read.

COVERAGE_PHOTON​

Photon can't accelerate this operation, so the query uses the standard runtime engine.

Recommendation: Review Photon limitations and adjust the query to use a supported execution path.

EXPLODING_JOIN​

The join produces significantly more rows than it reads.

Recommendation: Determine which result subset you need, then update the join condition or reduce the number of input rows from both relations.

FLOW_FULL_RECOMPUTE​

The flow runs as a full recompute.

Recommendation: Rewrite the query for incremental support to reduce bytes read.

REDUNDANT_AGGREGATION​

An aggregate operation did not change the query result.

Recommendation: Remove the aggregate or apply primary and foreign key constraints.

REDUNDANT_JOIN​

An outer join did not change the outer side's row count, and no columns from the joined table are used.

Recommendation: Remove the join or apply primary key or unique constraints.

SELECTIVE_JOIN​

The join produces significantly fewer rows than it reads.

Recommendation: Determine which result subset you need, then add filters before the join to reduce input rows.

WIDE_PROJECTION​

The query projects all columns from the table.

Recommendation: Project only the columns you need to reduce bytes read.

Data layout insights​

CONCURRENT_WRITE​

Concurrent writes to the table cause conflicts that are automatically resolved or fail.

Recommendation: Review Delta history to identify concurrent writes and adjust scheduling to avoid conflicts.

COVERAGE_STATS_DELTA​

Delta data skipping statistics are missing or incomplete for the table scan file filters, so the query uses in-file filtering.

The statistics status for each filter can be one of the following:

  • Full: Statistics are available for all filters.
  • Partial: Statistics are available for a subset of filters.
  • Unavailable: Statistics are not available for any filter.
  • Unused: Statistics can't be used because the filter converts the data type.

Recommendation: Collect Delta statistics to reduce bytes read.

COVERAGE_STATS_OPTIMIZER​

Cost-based optimizer statistics are missing or incomplete, so the query plan uses standard heuristics.

Recommendation: Collect statistics to enable the optimizer to produce a better plan.

DATA_FILE_SIZE​

The table scan reads many small files, which increases scan time.

Recommendations:

DATA_SKEW​

Data is distributed unevenly across computing resources.

Recommendation: Review the data distribution, then use key salting or pre-aggregation to balance the workload.

MANUAL_DATA_LAYOUT​

The table is manually optimized and can benefit from Automatic Liquid Clustering.

Recommendations:

  • Convert the table from external to managed for better performance and automatic maintenance.
  • Enable Predictive Optimization on the table for automatic maintenance operations.
  • Enable automatic clustering on the table to reduce bytes read.

Compute and resource insights​

DATA_SPILL​

Data spilled to disk during query execution because the data did not fit in memory.

Recommendation: Increase the warehouse size to add memory. Reduce the number of rows, columns, or the size of large columns (strings, arrays, maps, structs) to reduce memory usage.

EXCESSIVE_QUEUE_TIME​

The query waited in the warehouse queue.

Recommendation: Increase the maximum number of clusters on the warehouse to reduce queue time.

IO_THROTTLING​

A cloud storage request was throttled by the cloud provider.

Recommendation: Contact your administrator to request increased storage request limits from your cloud provider.

Applied accelerations​

These insights describe optimizations that Databricks already applied during query execution. They appear in the Performance insights tab with an Accelerated label and do not require action.

AUTO_LIQUID_CLUSTERING​

This query read less data because its tables use Automatic Liquid Clustering. Databricks continuously clusters each table by the keys it learns from your workload, so scans skip files that can't match your filters. No tuning or manual partitioning is required.

HISTORY_BASED_JOIN_STRATEGY​

Databricks optimized this query's join using measurements from previous runs of similar queries. It chose a broadcast join instead of shuffling data across the cluster, based on your workload history. No query change is required.

METRIC_VIEW_MATERIALIZATION_MATCH​

This query returned faster because Databricks served results from a precomputed materialization instead of recomputing them from the metric view's live source tables. When a materialization is defined for a metric view, the optimizer automatically matches incoming queries against it and serves pre-aggregated results when possible.

SHORT_QUERY_PRIORITIZATION​

Even though the cluster was at capacity, Databricks predicted this query would be short-running and ran it immediately through a fast path instead of holding it in the queue behind heavier queries. This keeps interactive workloads responsive under load.

Additional resources​

For a broader overview of performance best practices, see the Comprehensive Guide to Optimize Databricks, Spark and Delta Lake Workloads.

On this page