Amazon Redshift Performance: An Expert Guide to the EXPLAIN Command

1. Introduction: The EXPLAIN Command as Your Redshift Performance Compass

The Challenge of Redshift Performance

Amazon Redshift stands as a formidable cloud data warehousing solution, engineered by Amazon Web Services (AWS) to manage and analyze vast datasets, scaling up to exabytes.¹ Its architecture, built upon Massively Parallel Processing (MPP), leverages a leader node to coordinate query execution across multiple compute nodes, which are further divided into slices.² Data is stored in a columnar format, a design inherently optimized for the read-heavy analytical workloads typical of data warehousing.¹ This combination allows Redshift to deliver unparalleled speed on large-scale analytical queries.¹

However, harnessing this power effectively requires a deep understanding of how queries are executed within the cluster. Factors such as table design (including sort keys and distribution styles), data loading practices, query complexity, and workload concurrency significantly impact performance.⁶ While Redshift offers intelligent optimization features, including machine learning-driven query analysis and automatic table optimization ¹, achieving peak performance often necessitates manual intervention guided by precise diagnostics. Inefficient queries can deplete resources, increase costs, and create bottlenecks.⁶

Why EXPLAIN is Crucial

The EXPLAIN command is the cornerstone of performance tuning in Amazon Redshift.⁶ It provides a window into the query optimizer's mind, revealing the detailed execution plan—the sequence of steps Redshift intends to follow to process a given SQL statement—without actually running the query.¹⁸ This plan outlines the operations (like scans, joins, aggregations), the strategies for data movement between nodes, and the estimated computational cost associated with each step.¹⁸

Understanding this plan is fundamental for diagnosing performance bottlenecks. By examining the EXPLAIN output, engineers and analysts can identify inefficient operations, suboptimal join strategies, unnecessary data redistribution, and other issues hindering query speed.¹³ This insight directly informs optimization efforts, guiding decisions about table redesign, query rewriting, or adjustments to cluster configuration. The EXPLAIN command supports common Data Manipulation Language (DML) statements (SELECT, INSERT, UPDATE, DELETE) as well as CREATE TABLE AS (CTAS) operations.¹⁸

What This Report Covers

This report serves as an expert-level guide to effectively utilizing the EXPLAIN command for Amazon Redshift query optimization. It details the process of generating and interpreting execution plans, decoding the various operators and metrics presented in the output, and diagnosing common performance bottlenecks. Crucially, it links the analysis derived from EXPLAIN plans to specific, practical optimization techniques, including table design modifications (sort keys, distribution styles), query tuning strategies, the use of materialized views, and the importance of maintaining up-to-date statistics. Practical examples illustrate these concepts, and advanced analysis techniques involving VERBOSE output and system table correlation are explored.

2. Generating and Reading Redshift EXPLAIN Plans

Basic Syntax

Generating an execution plan in Amazon Redshift is straightforward. The core syntax involves prefixing the target SQL query with the EXPLAIN keyword ¹⁸:

SQL

EXPLAIN query

The query parameter can be any supported SQL statement, including SELECT, INSERT, UPDATE, DELETE, or CREATE TABLE AS.¹⁸ It's important to note that EXPLAIN cannot be used for most Data Definition Language (DDL) commands (like ALTER TABLE) or other database operations.¹²

An optional VERBOSE keyword can be included to obtain a more detailed execution plan ¹⁸:

SQL

EXPLAIN VERBOSE query

The default output provides a summary plan, which is often sufficient for initial analysis. The VERBOSE option yields a more detailed, often JSON-like structure, revealing finer-grained properties of each operator, potentially useful for deeper investigation.¹⁸

Structure of the Output

The EXPLAIN command returns a textual representation of the query plan. This plan is typically presented in a hierarchical, indented format.¹⁸ This structure represents the flow of data and execution steps. Reading the plan generally involves starting from the most indented operations (the innermost steps, often scans) and working outwards or upwards towards the final result step at the top.¹⁸ Each line in the plan usually corresponds to a specific operation or operator that Redshift will perform.¹⁸

Key Components Overview

For each step or operator in the execution plan, EXPLAIN provides several key pieces of information ¹⁸:

• Operator: This identifies the specific action being performed at that step. Examples include Seq Scan (scanning a table), Hash Join (performing a join using a hash table), Sort (sorting data), and Network (transferring data between nodes).

• Cost: This metric provides a relative estimate of the computational expense associated with the operation. It's typically presented as a range (start..total).

• Rows: This indicates the optimizer's estimate of the number of rows that will be output by this specific operation.

• Width: This shows the estimated average size, in bytes, of each row output by the operation.

Beyond these core metrics, the plan output will often include additional details relevant to the specific operator, such as the conditions used for joins (Hash Cond) or filters (Filter), and information about how data is distributed or moved across the cluster nodes (DS_DIST_NONE, DS_BCAST_INNER, etc.).¹⁸ Understanding these components is the first step towards effectively interpreting the plan.

3. Decoding Common EXPLAIN Plan Operators

The EXPLAIN plan is composed of various operators, each representing a distinct task in the query execution process. Understanding the function and implications of common operators is crucial for interpreting the plan accurately.

Scan Operations

Scan operators are responsible for reading data from tables.

• Seq Scan (Sequential Scan): This is the most basic scan type, indicating that Redshift reads the table's data sequentially from disk.²⁰ It typically occurs when the query requires accessing the entire table or when no WHERE clause predicates can effectively utilize sort keys or other optimizations to limit the scan.²⁰ While necessary in some cases, a Seq Scan on a very large table can be a significant performance bottleneck if only a small subset of data is actually needed.¹⁴

• RLS SecureScan / LF SecureScan: These operators signify scans performed on tables protected by Amazon Redshift Row-Level Security (RLS) or AWS Lake Formation policies, respectively.¹⁸ Their presence indicates that security policies are being evaluated during the scan.

• S3 Seq Scan (Redshift Spectrum): When querying data residing in Amazon S3 via Redshift Spectrum, this operator appears in the plan.²³ It signifies that the Spectrum layer is performing a scan directly on the S3 objects. Analyzing these steps is key to understanding data lake query performance.

• Remote PG Seq Scan (Federated Query): For queries accessing data in external databases (e.g., PostgreSQL) through Redshift's federated query feature, this operator indicates a scan being executed on the remote table.¹⁹

Join Operations

Join operators combine rows from two tables based on a specified condition. The choice of join operator significantly impacts performance.

• Nested Loop: This operator iterates through every row of the inner table for each row of the outer table. It is generally the least efficient join strategy for large datasets.⁶ Its appearance often signals a problem, such as an omitted join condition resulting in a Cartesian product, or certain types of inequality joins.¹⁵ A Nested Loop join involving large tables is a major performance red flag.²⁶

• Hash Join / Hash: This is a common and generally efficient join method.¹³ Redshift builds an in-memory hash table on the smaller (inner) table (Hash operator) and then probes this table using rows from the larger (outer) table (Hash Join operator).¹⁸ It's typically used when the join columns are not both the distribution key and the sort key for the tables involved.¹³

• Merge Join: Often the fastest join type, a Merge Join requires both input tables to be sorted on the join keys.²⁵ It reads the two sorted streams concurrently and merges matching rows. Redshift employs this strategy for inner and outer joins (but not full joins) only when the join columns serve as both the distribution key and the sort key for both tables, and when the tables are substantially sorted (typically less than 20% unsorted rows, verifiable via SVV_TABLE_INFO).¹³

• Spatial Join: A specialized, typically fast join operator used specifically for proximity-based joins involving GEOMETRY and GEOGRAPHY data types.²⁵

Data Distribution & Movement Operators

In Redshift's MPP architecture, data often needs to be moved between compute nodes to ensure that rows required for a join or aggregation reside on the same node or slice.⁶ Operators prefixed with DS_ describe these data distribution strategies.¹⁵

• DS_DIST_NONE: This is the ideal scenario for joins involving distributed tables. It signifies that no data redistribution is necessary because the relevant rows are already co-located on the same slices, typically because the tables share the same DISTKEY on the join columns.¹³

• DS_BCAST_INNER: The entire inner table is duplicated and broadcast (sent) to all compute nodes.¹⁵ While acceptable for very small inner tables, broadcasting large tables consumes significant network bandwidth and is a common performance bottleneck.¹³ Its presence often indicates a suboptimal distribution strategy (e.g., using DISTSTYLE ALL inappropriately) or potentially a flawed query.¹³

• DS_DIST_ALL_INNER / DS_DIST_ALL_NONE: These relate to tables with DISTSTYLE ALL. DS_DIST_ALL_NONE implies no redistribution is needed because the inner table (with DISTSTYLE ALL) already exists on every node.¹⁵ DS_DIST_ALL_INNER means the inner table is redistributed to a single slice, usually because the outer table uses DISTSTYLE ALL.¹⁵

• DS_DIST_INNER / DS_DIST_OUTER: These indicate that only one of the tables (inner or outer, respectively) needs to be redistributed across the nodes based on the join key values.¹³

• DS_DIST_BOTH: Both the inner and outer tables require redistribution.¹⁵ This typically occurs when the tables are joined on columns that are not their distribution keys, necessitating significant data movement across the network, which can be costly.¹³

• Network: This is a general operator indicating data transfer between nodes or segments, often associated with sending intermediate results to the leader node for final processing like sorting, merging, or aggregation.¹⁸

The appearance and type of these distribution operators are direct consequences of the tables' defined distribution styles (DISTSTYLE) relative to the keys used in joins or aggregations. Seeing costly distribution steps like DS_BCAST_INNER or DS_DIST_BOTH immediately suggests that the table distribution strategy might need re-evaluation for the specific query being analyzed.

Aggregation Operators

These operators handle aggregate functions and GROUP BY clauses.

• Aggregate: Used for scalar aggregate functions (e.g., AVG(column) without a GROUP BY).

• HashAggregate: Performs aggregation on unsorted data, often requiring data to be collected and hashed based on the grouping keys.²⁵

• GroupAggregate: Performs aggregation on data that is already sorted by the grouping keys.²⁵

Sorting and Other Operators

Several other operators frequently appear in execution plans:

• Sort: Represents an explicit sorting operation required by clauses like ORDER BY, SELECT DISTINCT, certain window functions, or as a prerequisite for a Merge Join.²⁵ Sorting large datasets can be resource-intensive.

• Merge: Combines intermediate sorted results generated by parallel operations on different slices into a final, globally sorted result set.¹⁸

• Unique: Removes duplicate rows, used for SELECT DISTINCT and UNION (as opposed to UNION ALL) operations.²⁵

• Limit: Applies the LIMIT clause to restrict the number of rows returned.²⁵ This often happens on the leader node after results are collected from compute nodes.¹³

• Window: Executes window functions defined using the OVER clause (e.g., ROW_NUMBER(), LAG(), SUM() OVER (...)).²⁰

• Materialize: Temporarily stores intermediate results in memory or on disk, often used as input for subsequent operations like Nested Loop or some Merge Joins.²⁵

Understanding these operators allows for a step-by-step deconstruction of the query plan, revealing the logic and potential inefficiencies in how Redshift intends to execute the query. The interplay between table design (sort keys, distribution) and the resulting operators (join types, distribution methods) is particularly important; the plan often reflects the consequences of those design choices.

4. Interpreting EXPLAIN Plan Metrics

Alongside the operators, the EXPLAIN plan provides quantitative metrics – Cost, Rows, and Width – that offer further insight into the planned execution. However, interpreting these metrics, especially Cost, requires careful consideration of their meaning within the Redshift context.

Cost

The cost metric is perhaps the most prominent but also the most frequently misunderstood component of a Redshift EXPLAIN plan.

• Startup vs. Total Cost: It appears in the format cost=start..total.²⁰ The startup cost (start) is the planner's relative estimate of the expense incurred to retrieve the first row from that operation. The total cost (total) is the relative estimate for completing the entire operation and returning all rows.²⁰ Costs are cumulative as one reads up the plan; the total cost of a step includes the costs of all its child steps.²⁵

• Relative Nature: It is crucial to understand that Redshift cost is a relative unit.¹⁸ Its primary utility lies in comparing the estimated expense of different operations within the same execution plan.²⁹ This allows identification of the steps the planner believes will be the most resource-intensive for that specific query execution strategy.¹⁵

• Caveats - Arbitrary & Buggy Costs: A significant caveat is that Redshift's cost values are fundamentally arbitrary numbers chosen by the developers, potentially as a mechanism to influence the query planner.²⁹ They are not directly correlated with real-world execution time, CPU usage, or I/O operations in the same way costs might be in systems like PostgreSQL.²⁹ Examples exist where seemingly simple operations have disproportionately high costs (e.g., sorting a single row potentially costing more than scanning billions).²⁹ Consequently, costs cannot be reliably used to compare the efficiency of different queries or different versions of the same query.²⁹ Furthermore, there are known issues where certain distribution operators, particularly DS_DIST_BOTH and potentially DS_BCAST_INNER, can exhibit astronomically high and effectively meaningless cost values, likely due to software bugs involving uninitialized variables.²⁹

Given these limitations, the cost metric should be treated as a rough pointer rather than an absolute measure. While identifying steps with significantly higher relative costs within a plan remains a valid starting point for analysis ¹⁵, the focus should quickly shift to why that step is considered expensive by the planner (i.e., which operator is being used, like a Nested Loop or DS_BCAST_INNER) rather than dwelling on the specific numeric value.²⁷ The operator choice often reveals more about potential inefficiencies than the arbitrary cost figure itself.

Rows

The rows metric indicates the query planner's estimate of the number of rows that will be output by the specific operation.¹⁸ This estimate is based on table statistics. If the row estimates seem significantly inaccurate when compared to known data characteristics or actual query results, it often points towards outdated or missing table statistics.²⁴ Inaccurate cardinality estimates can lead the optimizer to choose suboptimal join methods or distribution strategies.

Width

The width metric provides the planner's estimate of the average width, in bytes, of each row output by the operation.¹⁸ This can be useful for understanding the volume of data being processed or transferred at different stages of the query plan. A large width combined with a high row count indicates substantial data movement.

In essence, while metrics like rows and width provide useful estimates about data volume, the cost metric must be interpreted with extreme caution in Redshift. It serves best as an internal, relative indicator within a single plan to highlight potentially problematic operations, but its absolute value is often unreliable and should not be the sole basis for optimization decisions.

5. Using EXPLAIN to Diagnose Performance Bottlenecks

The primary value of the EXPLAIN command lies in its ability to help diagnose the underlying reasons for slow query performance. By carefully examining the operators and metrics in the plan, specific bottlenecks can often be identified.

Pinpointing High-Cost Steps

As discussed, while the absolute cost values are arbitrary, identifying steps with significantly higher relative costs compared to other steps in the same plan is a common starting point.¹⁵ This draws attention to the operations the planner anticipates will consume the most resources according to its internal model. However, it's crucial to look beyond the number and analyze the operator responsible for that high relative cost. Is it an inherently inefficient operation like a Nested Loop or a large DS_BCAST_INNER? This focus on the type of operation provides more actionable diagnostic information.

Identifying Inefficient Table Scans

A high cost associated with a Seq Scan operator, particularly on a large table, warrants investigation.¹⁴ If the query includes WHERE clause filters on columns that are defined as sort keys, but the scan still appears costly and processes a large number of estimated rows, it could indicate several issues:

• The sort key is not defined correctly or is ineffective for the query's predicates.

• The table data is highly unsorted, negating the benefit of the sort key (zone maps cannot be effectively used to skip data blocks).⁵ Check SVV_TABLE_INFO for the unsorted percentage.

• The predicates themselves are non-SARGable (e.g., applying a function to the sorted column in the WHERE clause), preventing the optimizer from using the sort key efficiently.³¹

Recognizing Suboptimal Join Strategies

The choice of join operator is a frequent source of performance issues.

• Nested Loops: As repeatedly emphasized, a Nested Loop join involving large tables is almost always a major bottleneck.¹⁵ Its presence demands immediate investigation into the join conditions (are they missing or incorrect?) or the nature of the join (is it an unavoidable inequality join?).

• Costly Hash Joins: While generally efficient, Hash Joins can become bottlenecks if:

• The inner table's hash table is too large to fit in memory, causing it to spill to disk. This significantly slows down the probing phase. While EXPLAIN doesn't explicitly show spills, high costs associated with a hash join, especially if preceded by large data volumes or redistribution, might hint at this possibility (corroborate with system tables, see Section 8).

• Significant data redistribution (DS_DIST_BOTH or DS_BCAST_INNER) is required before the join can occur.²⁴ The cost of moving the data adds to the overall join expense.

• Stale statistics lead the planner to choose the larger table as the inner (build-side) table, increasing the likelihood of spills.²⁴

• Missed Merge Joins: If the tables involved in a join seem ideally suited for a Merge Join (i.e., joined on columns that are both their DISTKEY and SORTKEY), but the plan shows a Hash Join instead, it indicates a potential missed optimization.²⁵ This could be due to the tables being more than 20% unsorted or because of stale statistics.²⁵

Detecting Inefficient Data Distribution

Data movement across the cluster network is often a primary contributor to query latency. The DS_ operators in the EXPLAIN plan reveal these movements.

• Excessive Broadcasting (DS_BCAST_INNER): Broadcasting large inner tables is highly inefficient due to network saturation.¹³ This strongly suggests reviewing the DISTSTYLE of the involved tables. Perhaps the inner table is incorrectly set to ALL, or KEY distribution on the join columns is needed.

• Heavy Redistribution (DS_DIST_BOTH): When both tables in a join need to be redistributed, it indicates a mismatch between their distribution keys and the join keys.¹³ This points towards the need for aligning the DISTKEYs of frequently joined large tables.

• Single Slice Operations (DS_DIST_ALL_INNER): If a significant amount of processing is forced onto a single slice due to DISTSTYLE ALL interactions, it can become a bottleneck by negating the benefits of parallel processing.¹⁵

• Data Skew: While EXPLAIN doesn't explicitly report data skew (where data is unevenly distributed across slices, causing some slices to do disproportionately more work), the plan might offer indirect clues. If operations involving KEY distributed tables consistently perform poorly, or if redistribution steps seem unexpectedly costly, skew could be a contributing factor.⁶ Confirmation requires querying system tables like SVV_TABLE_INFO or analyzing slice-level metrics from SVL_QUERY_REPORT.¹⁶

It becomes evident that the EXPLAIN plan primarily reveals the symptoms of performance issues – the inefficient operations the planner chose. It doesn't directly state the root cause. For instance, seeing a Nested Loop is a symptom; the cause might be a missing predicate in the SQL or perhaps an unusual join type requirement. Similarly, DS_BCAST_INNER is a symptom; the cause could be an inappropriate DISTSTYLE ALL setting or the lack of a DISTKEY. Therefore, interpreting EXPLAIN effectively involves mapping these observed symptoms back to potential underlying causes related to table design, query structure, or cluster state (like stale statistics).

6. Optimization Strategies Guided by EXPLAIN Analysis

Once the EXPLAIN plan has helped pinpoint potential bottlenecks, the next step is to apply targeted optimization techniques. The plan's details directly inform which strategies are most likely to yield improvements.

Optimizing Table Design (Informed by Scans, Joins, Distribution)

Many performance issues identified in EXPLAIN plans stem from suboptimal table design.

• Distribution Styles (DISTSTYLE): The choice of DISTSTYLE dictates how table data is physically placed across compute nodes/slices, directly impacting join performance.⁶

• Symptom: EXPLAIN shows costly DS_DIST_BOTH or DS_BCAST_INNER for joins between large tables.

• Optimization: Consider setting a matching DISTKEY on the common join columns for these tables. This co-locates matching rows, enabling efficient DS_DIST_NONE joins.⁵

• Symptom: EXPLAIN shows DS_BCAST_INNER for a frequently joined, relatively small dimension or lookup table.

• Optimization: Consider changing that table's DISTSTYLE to ALL. This replicates the table on every node, eliminating redistribution for joins but increasing storage.⁵ Use judiciously.

• Consideration: If no single join key dominates, or if KEY distribution leads to data skew (check system tables), DISTSTYLE EVEN (which distributes data round-robin) might be used, although it often necessitates redistribution for joins.⁶ AUTO distribution, the default, attempts to intelligently switch between ALL and EVEN based on table size.⁵

• Sort Keys (SORTKEY): Sort keys define the physical order of rows within each slice, enabling Redshift to efficiently skip data blocks during scans based on WHERE clause predicates.⁵

• Symptom: EXPLAIN shows high-cost Seq Scan operations on large tables, even when filters (especially range filters on dates or equality filters) are present in the WHERE clause.

• Optimization: Define a SORTKEY on the column(s) most frequently used in filters. For compound sort keys, the order is critical: place columns used in range filters (like timestamps) or frequently used equality filters first.⁹ This leverages Redshift's zone maps (metadata storing min/max values per block) to minimize I/O.⁵

• Symptom: EXPLAIN shows a Hash Join where a faster Merge Join seems possible.

• Optimization: Ensure the join columns are designated as both the DISTKEY and the SORTKEY for the involved tables. Also, verify the tables are well-sorted by running VACUUM SORT ONLY or checking SVV_TABLE_INFO for unsorted percentages below 20%.¹³

• Compression (ENCODE): Effective compression reduces the amount of data stored on disk and read during scans.⁵

• Symptom: While not directly visible in EXPLAIN, high scan costs can be exacerbated by uncompressed or poorly compressed data.

• Optimization: Apply appropriate compression encodings (e.g., ZSTD, AZ64 are often good choices) to columns. Use the ANALYZE COMPRESSION command to get recommendations.⁹ Crucially, avoid compressing at least the first column of the sort key, as this can hinder the effectiveness of zone map filtering.⁵

• Data Types: Using the narrowest possible data types (e.g., SMALLINT vs BIGINT, appropriate VARCHAR length) reduces storage footprint and the amount of data processed during queries, indirectly improving performance reflected in plan costs.¹⁸

Refining SQL Queries (Informed by Operators, Filters, Costs)

The structure of the SQL query itself heavily influences the execution plan.

• SELECT Lists:

• Symptom: EXPLAIN plan width is large for scan steps, even if only a few columns are used later.

• Optimization: Avoid using SELECT *. Explicitly list only the columns required by the query. This reduces I/O during scans and data volume transferred across the network.⁶

• Predicates (WHERE clauses):

• Symptom: EXPLAIN shows filters being applied late in the plan, or high row counts persisting through early steps.

• Optimization: Apply filtering predicates as early as possible in the query logic. Ensure predicates are SARGable (Search Argument Able) – avoid applying functions directly to table columns within the WHERE clause, especially sorted columns, as this prevents the optimizer from using sort keys or indexes effectively. Instead, apply functions to the literal/comparison value (e.g., WHERE date_col >= '2023-01-01'::date is better than WHERE TRUNC(date_col) >= '2023-01-01'::date).⁶

• Join Logic:

• Symptom: EXPLAIN shows Nested Loop join.

• Optimization: Double-check join conditions for correctness and completeness. Ensure you haven't accidentally created a Cartesian product.¹⁷ Consider if an INNER JOIN could logically replace a LEFT JOIN, as inner joins offer the optimizer more flexibility in reordering.¹⁷ Simplify complex multi-part join conditions if feasible.³¹

• Subqueries & CTEs (Common Table Expressions):

• Symptom: EXPLAIN shows operators like Subquery Scan or Subplan. Performance seems poor.

• Optimization: Analyze how CTEs are being processed. Sometimes, pushing filters inside the CTE definition rather than filtering the CTE result can be more efficient.¹⁴ Be aware that Redshift might internally materialize CTE results, which can sometimes be beneficial or detrimental depending on the context.³⁰ Experiment with rewriting complex queries that heavily use nested subqueries or CTEs.

• Aggregations:

• Symptom: EXPLAIN shows costly Sort operations before a GroupAggregate, or expensive HashAggregate operations.

• Optimization: If grouping by columns that are (or could be) part of the sort key, ensure the GROUP BY clause aligns with the sort key order where possible.¹⁷ Critically evaluate the need for DISTINCT operations (e.g., COUNT(DISTINCT col)), as they often force expensive sorting or hashing steps.⁹ Sometimes alternative logic can achieve the same result more efficiently.

Leveraging Materialized Views (MVs)

Materialized Views store the precomputed results of a query, offering significant speedups for recurring complex operations.

• Symptom: EXPLAIN consistently shows complex, time-consuming joins or aggregations on large tables for frequently executed queries (e.g., dashboard queries).

• Optimization: Create a Materialized View that encapsulates the expensive part of the query.⁶ Redshift's automatic query rewrite feature can then potentially use this MV transparently.

• Verification: After creating the MV, run EXPLAIN on the original query again. If the rewrite is successful, the new plan will show a simple Seq Scan on the materialized view (e.g., Seq Scan on mv_tbl__<mv_name>__0) replacing the previously complex join/aggregation structure.¹¹ This provides direct confirmation that the MV is being utilized. Also consider Automated Materialized Views (AutoMV), where Redshift identifies and creates beneficial MVs automatically.¹¹

The Critical Role of ANALYZE and VACUUM

Cluster maintenance operations are fundamental for ensuring the optimizer has accurate information and that physical data layout remains optimal.

• ANALYZE (Statistics):

• Symptom: EXPLAIN shows illogical plan choices (e.g., broadcasting a large table assumed to be small, choosing the wrong join order, using Hash Join when Merge Join seems viable) or highly inaccurate rows estimates.

• Optimization: Run the ANALYZE command on the relevant tables.¹⁵ Up-to-date statistics are essential for the query optimizer to generate efficient plans.⁶ Monitor system views like SVV_TABLE_INFO or STL_ALERT_EVENT_LOG for tables flagged as having stale statistics ("stats off" > 10%).¹⁷

• VACUUM (Reclaiming Space & Sorting):

• Symptom: Query performance degrades over time, particularly scan costs increase, even if EXPLAIN plan structure doesn't change drastically. Merge Joins are not being used despite appropriate keys.

• Optimization: Run VACUUM regularly. VACUUM DELETE ONLY reclaims space from deleted or updated rows ("ghost rows"), which can otherwise bloat tables and slow down scans.⁶ VACUUM SORT ONLY or a full VACUUM re-sorts the table according to its SORTKEY, which is critical for maintaining the effectiveness of sort keys for scan optimization and for enabling efficient Merge Joins.⁶

It's clear that optimizing based on EXPLAIN is not about applying isolated fixes. Improving a join might necessitate changing a DISTKEY, which could impact other queries. Enhancing scan performance relies on both defining a SORTKEY and maintaining its effectiveness through VACUUM. All physical design choices are less effective if the optimizer works with outdated information due to missing ANALYZE runs. Therefore, a holistic and iterative approach is required: analyze the plan, hypothesize the bottleneck, implement a change (design, query, or maintenance), verify the impact (using EXPLAIN again, alongside actual performance metrics), and consider the broader workload implications.

7. Practical Optimization Examples from EXPLAIN Plans

Theory is best understood through application. Let's examine hypothetical scenarios illustrating how EXPLAIN plan analysis leads to specific optimizations.

Case Study 1: Fixing a Bad Join Strategy (Nested Loop / Broadcast)

• Scenario: A query joining a large fact_sales table (billions of rows, DISTSTYLE KEY(product_id)) with a medium-sized dim_product table (millions of rows, DISTSTYLE ALL) on product_id is unexpectedly slow.

• Initial EXPLAIN Plan Snippet:
  XN Hash Join DS_BCAST_INNER (cost=5000.00..1234567890123.45 rows=5000000000 width=150)
  Hash Cond: ("outer".product_id = "inner".product_id)
  -> XN Seq Scan on fact_sales (cost=0.00..50000000.00 rows=5000000000 width=100)
  -> XN Hash (cost=3000.00..3000.00 rows=5000000 width=50)
  -> XN Seq Scan on dim_product (cost=0.00..3000.00 rows=5000000 width=50)
  
  

• Analysis: The plan shows a Hash Join, but crucially uses DS_BCAST_INNER. This means the dim_product table (5 million rows) is being broadcast to all compute nodes to join with fact_sales. While DISTSTYLE ALL was likely chosen for dim_product assuming it was small, millions of rows are too large for efficient broadcasting, causing a network bottleneck.¹³ The extremely high ²⁹ cost estimate also flags this step.

• Optimization: Change the distribution style of dim_product to match fact_sales: ALTER TABLE dim_product ALTER DISTSTYLE KEY(product_id);. Run ANALYZE dim_product;.

• Improved EXPLAIN Plan Snippet:
  XN Hash Join DS_DIST_NONE (cost=5000.00..60000000.00 rows=5000000000 width=150)
  Hash Cond: ("outer".product_id = "inner".product_id)
  -> XN Seq Scan on fact_sales (cost=0.00..50000000.00 rows=5000000000 width=100)
  -> XN Hash (cost=3000.00..3000.00 rows=5000000 width=50)
  -> XN Seq Scan on dim_product (cost=0.00..3000.00 rows=5000000 width=50)
  
  

• Outcome: The join now uses DS_DIST_NONE, indicating that the data is co-located because both tables share the same distribution key (product_id). The costly broadcast is eliminated, leading to significantly faster query execution.¹⁵

Case Study 2: Addressing Inefficient Scans with Sort Keys

• Scenario: A query analyzing recent website activity from a large web_logs table (sorted by user_id, not time) filters for events within the last 24 hours using WHERE event_timestamp >= GETDATE() - interval '1 day'. The query is slow.

• Initial EXPLAIN Plan Snippet:
  XN Seq Scan on web_logs (cost=0.00..987654321.00 rows=10000000 width=200)
  Filter: (event_timestamp >=... )
  
  

• Analysis: The plan shows a high-cost Seq Scan on web_logs. Even though there's a filter on event_timestamp, the table is not sorted by this column. Redshift must scan a large portion (potentially all) of the table to find the relevant rows, as the sort key (user_id) doesn't help with time-based filtering.⁵

• Optimization: Assuming time-based filtering is common, redefine the table with event_timestamp as the primary sort key: CREATE TABLE new_web_logs (... ) SORTKEY(event_timestamp);. Load data into the new table. Ensure regular VACUUM and ANALYZE are performed.

• Improved EXPLAIN Plan Snippet (Conceptual):
  XN Seq Scan on new_web_logs (cost=0.00..12345678.00 rows=10000000 width=200)
  Filter: (event_timestamp >=... )
  
  

• Outcome: Although the EXPLAIN plan might still show Seq Scan and a similar estimated row count ²², the actual performance should improve dramatically. With event_timestamp as the sort key, Redshift can use its zone maps to quickly identify and scan only the data blocks containing records from the last 24 hours, significantly reducing I/O.⁵

Case Study 3: Optimizing Distribution Based on EXPLAIN

• Scenario: Two large fact tables, order_details and shipping_details, both with DISTSTYLE EVEN, are frequently joined on order_id. The join operation is identified as a bottleneck.

• Initial EXPLAIN Plan Snippet:
  XN Hash Join DS_DIST_BOTH (cost=10000.00..555555555.00 rows=100000000 width=250)
  Hash Cond: ("outer".order_id = "inner".order_id)
  -> XN Seq Scan on order_details (cost=0.00..20000000.00 rows=100000000 width=150)
  -> XN Hash (cost=8000.00..8000.00 rows=100000000 width=100)
  -> XN Seq Scan on shipping_details (cost=0.00..15000000.00 rows=100000000 width=100)
  
  

• Analysis: The plan shows DS_DIST_BOTH, indicating that both large tables need to be redistributed across the network based on the order_id values before the hash join can occur.¹³ This redistribution is expensive.

• Optimization: Change the distribution style for both tables to use order_id as the key: ALTER TABLE order_details ALTER DISTSTYLE KEY(order_id); ALTER TABLE shipping_details ALTER DISTSTYLE KEY(order_id);. Run ANALYZE on both tables.

• Improved EXPLAIN Plan Snippet:
  XN Hash Join DS_DIST_NONE (cost=10000.00..350000000.00 rows=100000000 width=250)
  Hash Cond: ("outer".order_id = "inner".order_id)
  -> XN Seq Scan on order_details (cost=0.00..20000000.00 rows=100000000 width=150)
  -> XN Hash (cost=8000.00..8000.00 rows=100000000 width=100)
  -> XN Seq Scan on shipping_details (cost=0.00..15000000.00 rows=100000000 width=100)
  
  

• Outcome: The join now uses DS_DIST_NONE. Since rows with the same order_id are now physically stored on the same slices in both tables, the join can proceed without costly network data movement, improving performance.¹⁵

Summary: Common EXPLAIN Patterns -> Implications -> Optimizations

1. Nested Loop Join on Large Tables

• Pattern Observed: Nested loop join used with large tables.

• Likely Cause: Missing or incorrect join condition, leading to a Cartesian product.

• Recommended Action: Verify or add the appropriate join conditions. If necessary, rewrite the query to use a more efficient join type.

• Relevant Sources: 15

2. DS_BCAST_INNER on a Large Inner Table

• Pattern Observed: Broadcast distribution style used on a large inner table.

• Likely Cause: Inefficient distribution method (possibly ALL), causing network bottlenecks.

• Recommended Action: Change the inner table’s DISTSTYLE (consider using KEY). Ensure the join leverages the DISTKEY effectively.

• Relevant Sources: 13

3. DS_DIST_BOTH on Large Tables

• Pattern Observed: Both tables are being redistributed due to DS_DIST_BOTH.

• Likely Cause: Join keys do not match the DISTKEYs, requiring costly reshuffling.

• Recommended Action: Set matching DISTKEYs on the join columns of both tables.

• Relevant Sources: 13

4. High-Cost Sequential Scan Despite Filters

• Pattern Observed: Sequential scan used even though filters are applied.

• Likely Cause: Missing or ineffective SORTKEY; table may not be sorted.

• Recommended Action: Define or optimize the SORTKEY on filtered columns, run a VACUUM operation, and ensure predicates are SARGable.

• Relevant Sources: 5

5. Hash Join Instead of Merge Join

• Pattern Observed: Hash join used in place of a merge join.

• Likely Cause: Join keys are not both DISTKEY and SORTKEY; table may have more than 20% unsorted rows.

• Recommended Action: Align DISTKEY and SORTKEY on the join keys. Run VACUUM SORT ONLY and check table stats using SVV_TABLE_INFO.

• Relevant Sources: 13

6. Inaccurate Row Estimates and Suboptimal Plans

• Pattern Observed: Query planner chooses a poor plan due to row misestimates.

• Likely Cause: Stale table statistics.

• Recommended Action: Run ANALYZE on the relevant tables to update statistics.

• Relevant Sources: 6

7. Sequential Scan on mv_tbl__...

• Pattern Observed: Sequential scan on a table prefixed with mv_tbl__.

• Likely Cause: Query is rewritten to use a Materialized View.

• Recommended Action: Generally positive, but monitor the freshness and usage of the Materialized View to ensure performance stays optimal.

• Relevant Sources: 118. Advanced EXPLAIN Plan Analysis

While the standard EXPLAIN output provides significant insight, advanced techniques involving VERBOSE output and correlation with system tables unlock deeper analysis capabilities.

Leveraging VERBOSE Output

As mentioned earlier, EXPLAIN VERBOSE provides a more detailed breakdown of the execution plan, often presented in a structured, JSON-like format.¹⁸ This output can reveal more granular properties of each operator, such as specific buffer sizes, work memory estimates, or internal flags used by the optimizer. While often overwhelming for initial analysis, the VERBOSE output can be valuable for experienced users investigating complex performance nuances or trying to understand precisely why the optimizer made a specific choice.

Correlating with System Tables (SVL_QUERY_REPORT / SVL_QUERY_SUMMARY)

Perhaps the most critical advanced technique is correlating the EXPLAIN plan with actual execution metrics captured in Redshift's system tables. EXPLAIN shows the intended plan and estimated costs, but system tables reveal what actually happened when the query ran.¹⁶

• SVL_QUERY_REPORT / SVL_QUERY_SUMMARY: These views are indispensable complements to EXPLAIN.¹⁸ After a query executes, these tables provide detailed runtime statistics. Crucially, SVL_QUERY_REPORT breaks down metrics like execution time, rows processed, bytes processed, and whether an operation spilled to disk (is_diskbased) not just by query step, but also by segment and slice.¹⁶

• Identifying Slice Skew and Disk Spills: This slice-level detail is essential for diagnosing problems invisible in the standard EXPLAIN plan. If one slice consistently takes much longer or processes significantly more data than others for a given step, it indicates data skew.¹⁶ If the is_diskbased flag is true for a step (like a Hash Join or Sort), it confirms that the operation ran out of allocated memory and had to use slower disk storage.⁴⁰

• Mapping Plan to Summary: To correlate, identify the query ID from the execution and use it to query SVL_QUERY_REPORT or SVL_QUERY_SUMMARY. The segment and step columns in these views generally correspond to the hierarchical structure of the EXPLAIN plan, allowing a mapping between the planned operation and its actual runtime metrics.¹⁸

• Other Useful System Tables: Other system tables provide further diagnostic information:

• STL_ALERT_EVENT_LOG: Shows alerts generated by the query planner, such as missing statistics or inefficient join types.¹⁷

• STL_SCAN, STL_JOIN, STL_DIST: Provide detailed logs about scan, join, and distribution operations.³⁰

• SVV_TABLE_INFO: Contains information about table skew, unsorted percentage, and statistics status.²⁵

This correlation highlights a key principle: EXPLAIN is necessary to understand the planner's intent, but it is not sufficient for a complete performance picture. Actual runtime behavior, including slice-level performance and resource usage (memory vs. disk), can only be confirmed by analyzing system table data after execution.

Interpreting Plans for Federated Queries & Redshift Spectrum

When using Redshift to query external data sources, the EXPLAIN plan includes specific operators:

• Federated Queries (e.g., to PostgreSQL): Look for XN PG Query Scan followed by Remote PG Seq Scan.¹⁹ Pay attention to the Filter: line beneath the remote scan – this indicates whether predicates from the Redshift query are being successfully "pushed down" to the external database, which is crucial for minimizing data transfer.¹⁹ Also, analyze the distribution operators (DS_BCAST, DS_DIST) applied to the results returned from the federated source, as this data initially arrives at a single Redshift node and must be distributed for further joins.¹⁹

• Redshift Spectrum (Queries on S3): Look for operators prefixed with S3, such as S3 Seq Scan.²³ This confirms that Spectrum is handling that part of the query execution directly against S3 data. While EXPLAIN shows the Spectrum involvement, deeper analysis of Spectrum performance often involves checking SVL_S3QUERY_SUMMARY for metrics related to S3 scan time, data scanned, and considering factors like file size, data format (e.g., Parquet vs. CSV), and partitioning strategies applied in S3.²³

Analyzing these specialized plans involves checking if work is being appropriately pushed down to the external source (filters for Federated, scans for Spectrum) and understanding how the results are integrated into the rest of the Redshift query plan.

9. Conclusion: Synthesized Best Practices for EXPLAIN-Driven Tuning

Mastering the EXPLAIN command is an indispensable skill for optimizing query performance in Amazon Redshift. It provides the critical first step in understanding how Redshift intends to execute a query, revealing the planned sequence of operations, join strategies, data movements, and estimated costs.

The most effective approach to EXPLAIN-driven tuning follows an iterative cycle:

1. Generate Plan: Use EXPLAIN to obtain the execution plan for a slow or resource-intensive query.

2. Identify Bottlenecks: Analyze the plan, focusing on steps with high relative costs and, more importantly, identifying inherently inefficient operators like Nested Loop joins, DS_BCAST_INNER on large tables, DS_DIST_BOTH redistributions, or costly Seq Scans.

3. Analyze Causes: Map the observed plan symptoms back to potential root causes, considering table design (Distribution Style, Sort Keys, Compression), query structure (SELECT list, predicates, join logic), and cluster state (stale statistics).

4. Apply Optimization: Implement targeted changes based on the analysis. This might involve altering table DISTSTYLE or SORTKEY, rewriting the SQL query, running ANALYZE and VACUUM, or creating Materialized Views.

5. Verify & Iterate: Rerun EXPLAIN to see how the plan has changed. Crucially, execute the query and check actual performance metrics using system tables like SVL_QUERY_REPORT to confirm the improvement and identify any unintended consequences or remaining bottlenecks.

Achieving optimal performance requires a holistic perspective. Table design choices (distribution, sorting), query writing patterns, and regular maintenance (ANALYZE, VACUUM) are interconnected and must be considered together. A well-designed table can still perform poorly if queried inefficiently or if the optimizer lacks accurate statistics.

Leveraging complementary AWS tools like the Amazon Redshift Advisor, which provides automated recommendations ⁴, and the Query Profiler in the AWS console, which offers a visual representation of the execution plan ⁴³, can further aid the tuning process.

Finally, performance tuning is not a static, one-time activity. As data volumes grow, query patterns evolve, and Redshift features update, continuous monitoring using EXPLAIN and system table analysis is essential to maintain optimal performance and cost-efficiency over time.³³ By diligently applying the principles outlined in this guide, Redshift users can transform the EXPLAIN command from an intimidating diagnostic tool into a powerful compass navigating the path to faster, more efficient queries.

Works cited

1. AWS Redshift Guide: Use Cases, Pros And Cons, And Pricing - CloudZero, accessed on April 18, 2025, https://www.cloudzero.com/blog/aws-redshift/

2. Understanding Amazon Redshift Pricing and Costs - Pump, accessed on April 18, 2025, https://www.pump.co/blog/understanding-amazon-redshift-pricing

3. The Ultimate Guide to Redshift Pricing | Hightouch, accessed on April 18, 2025, https://hightouch.com/blog/redshift-pricing

4. Exploring Amazon Redshift Architecture: A Comprehensive Guide - ProsperOps, accessed on April 18, 2025, https://www.prosperops.com/blog/amazon-redshift-architecture/

5. Amazon Redshift Queries Best Practices - Rackspace Technology, accessed on April 18, 2025, https://www.rackspace.com/blog/amazon-redshift-best-practices-for-optimizing-query-performance

6. Boosting Amazon Redshift Query Speed Best Practices and Tips, accessed on April 18, 2025, https://www.cloudthat.com/resources/blog/boosting-amazon-redshift-query-speed-best-practices-and-tips

7. Optimize data layout by bucketing with Amazon Athena and AWS Glue to accelerate downstream queries, accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/optimize-data-layout-by-bucketing-with-amazon-athena-and-aws-glue-to-accelerate-downstream-queries/

8. Surface and optimize slow performing queries with Datadog Database Monitoring, accessed on April 18, 2025, https://www.datadoghq.com/blog/database-performance-monitoring-datadog/

9. 10 SQL Query Optimization Tips for Faster Redshift Performance (2025) - Chaos Genius, accessed on April 18, 2025, https://www.chaosgenius.io/blog/optimizing-redshift-performance/

10. Why is Redshift a no-go? : r/dataengineering - Reddit, accessed on April 18, 2025, https://www.reddit.com/r/dataengineering/comments/12sf3kg/why_is_redshift_a_nogo/

11. Optimize your Amazon Redshift query performance with automated materialized views, accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/optimize-your-amazon-redshift-query-performance-with-automated-materialized-views/

12. Redshift EXPLAIN | Performance of EXPLAIN Command | Examples - EDUCBA, accessed on April 18, 2025, https://www.educba.com/redshift-explain/

13. A Deep Dive on Redshift Execution Plans — Chariot Solutions, accessed on April 18, 2025, https://chariotsolutions.com/blog/post/redshift-execution-plans/

14. Optimizing Redshift SQL Queries Via Query Plan Estimates ..., accessed on April 18, 2025, https://luppeng.wordpress.com/2019/07/25/optimizing-redshift-sql-queries-via-query-plan-estimates/

15. KB486763: Custom MicroStrategy AI Bot Interface run in Terminal ..., accessed on April 18, 2025, https://community.microstrategy.com/s/article/How-to-optimize-a-slow-query-in-Amazon-Redshift

16. How do I correlate the query plan with the query report in Amazon Redshift? - AWS re:Post, accessed on April 18, 2025, https://repost.aws/knowledge-center/redshift-query-plan

17. How to Optimize SQL Queries in Amazon Redshift? - Secoda, accessed on April 18, 2025, https://www.secoda.co/learn/how-to-optimize-sql-queries-in-amazon-redshift

18. EXPLAIN - Amazon Redshift - AWS Documentation, accessed on April 18, 2025, https://docs.aws.amazon.com/redshift/latest/dg/r_EXPLAIN.html

19. Best practices for Amazon Redshift Federated Query | AWS Big Data ..., accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/amazon-redshift-federated-query-best-practices-and-performance-considerations/

20. amazon web services - What does this EXPLAIN query plan output ..., accessed on April 18, 2025, https://stackoverflow.com/questions/74815006/what-does-this-explain-query-plan-output-mean-for-my-redshift-query

21. Redshift UPDATE uses Seq Scan very slow - sql - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/42363690/redshift-update-uses-seq-scan-very-slow

22. Redshift Understanding the "EXPLAIN" Statement - Systems and Tech Thoughts, accessed on April 18, 2025, https://on-systems.tech/blog/122-redshift-understanding-explain/

23. From Data Lake to Data Warehouse: Enhancing Customer 360 with ..., accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/from-data-lake-to-data-warehouse-enhancing-customer-360-with-amazon-redshift-spectrum/

24. Analyzing the query plan - Amazon Redshift - AWS Documentation, accessed on April 18, 2025, https://docs.aws.amazon.com/redshift/latest/dg/c-analyzing-the-query-plan.html

25. Creating and interpreting a query plan - Amazon Redshift, accessed on April 18, 2025, https://docs.aws.amazon.com/redshift/latest/dg/c-the-query-plan.html

26. Comparison of Explain Statement Output on Amazon Redshift - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/49558782/comparison-of-explain-statement-output-on-amazon-redshift

27. AWS redshift range join slow - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/79440290/aws-redshift-range-join-slow

28. How to read the redshift query plan? - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/64890299/how-to-read-the-redshift-query-plan

29. amazon redshift - How to interpret huge costs in a query plan - Stack ..., accessed on April 18, 2025, https://stackoverflow.com/questions/67858897/how-to-interpret-huge-costs-in-a-query-plan

30. Learning Redshift EXPLAIN plan - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/75108315/learning-redshift-explain-plan

31. improving query performance for a table in redshift - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/78093356/improving-query-performance-for-a-table-in-redshift

32. How to Optimize Amazon Redshift Performance | Chartio Blog, accessed on April 18, 2025, https://chartio.com/blog/how-to-optimize-amazon-redshift-performance/

33. Best Practice: Optimize Redshift Performance by Reducing Network Traffic Across a Cluster, accessed on April 18, 2025, https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/Best-Practice-Optimize-Redshift-Performance-by-Reducing-Network/ta-p/587275

34. 15 Performance Tuning Techniques for Amazon Redshift | Integrate.io, accessed on April 18, 2025, https://www.integrate.io/blog/15-performance-tuning-techniques-for-amazon-redshift/

35. 12 Amazon Redshift Query Optimization Techniques - Eyer.ai, accessed on April 18, 2025, https://eyer.ai/blog/12-amazon-redshift-query-optimization-techniques/

36. Amazon Redshift- Best Practices for Optimal Performance and Efficiency - Airbyte, accessed on April 18, 2025, https://airbyte.com/data-engineering-resources/amazon-redshift-best-practices

37. Optimize your analytical workloads using the automatic query ... - AWS, accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/optimize-your-analytical-workloads-using-the-automatic-query-rewrite-feature-of-amazon-redshift-materialized-views/

38. Improving performance reading from large Redshift table - Stack Overflow, accessed on April 18, 2025, https://stackoverflow.com/questions/67133807/improving-performance-reading-from-large-redshift-table

39. Amazon Redshift Best Practices for Performance - MicroStrategy Community, accessed on April 18, 2025, https://community.microstrategy.com/s/article/Amazon-Redshift-Best-Practices-for-Performance

40. Top 10 performance tuning techniques for Amazon Redshift | AWS Big Data Blog, accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-techniques-for-amazon-redshift/

41. Amazon Redshift Optimization: 12 Tuning Techniques To Boost Performance - ProsperOps, accessed on April 18, 2025, https://www.prosperops.com/blog/redshift-optimization/

42. Redshift Pricing Explained + Controlling Costs with ETL - Upsolver, accessed on April 18, 2025, https://www.upsolver.com/blog/amazon-redshift-pricing-guide-understanding-the-costs

43. Simplify your query performance diagnostics in Amazon Redshift with Query profiler - AWS, accessed on April 18, 2025, https://aws.amazon.com/blogs/big-data/simplify-your-query-performance-diagnostics-in-amazon-redshift-with-query-profiler/