Amazon Redshift Performance Optimisation Guide: Fix Slow Queries Faster

How Redshift performance works

Understanding the fundamentals makes it much easier to diagnose performance problems. You do not need to memorise all of this upfront. Just know these concepts exist so you can return to them when you hit a specific issue.

MPP: parallel processing across nodes

Redshift is an MPP (massively parallel processing) database. When you run a query, it is split into steps and executed simultaneously across all compute nodes and their slices. A cluster with 4 nodes and 2 slices each runs 8 parallel operations at once. Performance depends heavily on how evenly data is distributed across those slices. An uneven distribution means some slices do much more work than others.

The physical node-and-slice model is covered in detail in the Redshift Architecture guide.

Columnar storage

Unlike a row-based database that stores all column values for a row together, Redshift stores each column separately on disk. A query that reads 3 columns from a 100-column table only reads 3% of the disk. This is why SELECT * is especially harmful in Redshift. It forces a full scan of every column even if your query only uses a few.

Compression encodings

Because each column contains similar values, columns compress very well. Compression directly reduces the physical disk I/O for every query, not just storage cost. A well-compressed table can be 2 to 5 times faster to scan than the same table without compression. Run ANALYZE COMPRESSION tablename; to get Redshift’s own recommendations.

Distribution styles and keys

Distribution controls which slice each row is stored on. The goal is to co-locate rows that will be joined together on the same slice, so Redshift never has to shuffle data across the network to complete the join. When two large tables share the same DISTKEY column, matching rows already live on the same slice and the join is entirely local.

Sort keys and block pruning

Redshift stores data in 1 MB blocks and tracks the minimum and maximum value of the sort key column in each block (called a zone map). When a WHERE clause filter uses the sort key column, Redshift skips entire blocks that cannot contain matching rows. On a large date-partitioned table, a sort key on the date column can eliminate 90% or more of disk reads for a date-range query.

Query planning

Redshift’s query planner reads statistics collected by ANALYZE to estimate how many rows each step will produce, then chooses join strategies and sort orders based on those estimates. When statistics are stale, the planner makes wrong decisions. For example, it might choose a nested-loop join strategy instead of a hash join, which is catastrophically slow on large tables.

Result caching

Redshift caches query results automatically. If the same query runs again against unchanged data, Redshift returns the cached result without re-executing the query. Cache hits return results in milliseconds regardless of query complexity. This is particularly useful for BI dashboards that repeatedly refresh the same aggregations.

When to use this guide

This guide is useful if you are experiencing any of these situations:

• Dashboard queries that used to run in 2 seconds now take 30 seconds or more
• ETL or ELT jobs that are missing their SLA windows as data volume grows
• Joins that become noticeably slower as one table grows beyond 100 million rows
• Users reporting that queries sometimes complete quickly and sometimes take minutes, with no obvious pattern
• A cluster that is getting expensive but performance is not improving in proportion
• Queries that have never been diagnosed and you inherited a cluster that needs auditing

If you are setting up Redshift for the first time, start with Running Your First Redshift Query before this guide. That page covers creating tables with DISTKEY and SORTKEY and running your first EXPLAIN plan.

Distribution styles: eliminating data shuffles

The biggest performance killer in Redshift is data redistribution. This is when Redshift must move rows from one compute node or slice to another to complete a join. The EXPLAIN plan calls these redistribute steps. On large tables, a redistribute can dominate total query time completely.

The four distribution styles

• KEY: Rows with the same DISTKEY value are stored on the same slice. Use this on the join column of your two largest frequently-joined tables. Both tables must share the same DISTKEY column so matching rows co-locate and no shuffle is needed.

• ALL: A full copy of the table is stored on every node. Ideal for small dimension tables (a few million rows or fewer) that are joined frequently. Every join is always local with no network I/O.

• EVEN: Rows are distributed round-robin across all slices. Good for tables not involved in joins, or as a safe starting point before you understand your query patterns.

• AUTO: Redshift chooses the distribution style automatically based on table size and observed usage. Small tables start as ALL and transition to KEY or EVEN as they grow. A sensible default when you are not yet ready for manual tuning.

Identifying expensive redistribute steps in EXPLAIN

Run EXPLAIN before a query and look for these distribution markers in the output:

EXPLAIN
SELECT o.region, COUNT(*)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.region;

• DS_DIST_NONE — no redistribution. This is what you want.
• DS_BCAST_INNER — the inner (smaller) table is broadcast to all nodes. Acceptable for small tables.
• DS_DIST_INNER — the inner table is redistributed. Less expensive than DS_DIST_BOTH.
• DS_DIST_BOTH — both tables are being redistributed. The most expensive option.

Checking current distribution

SELECT
  tablename,
  diststyle,
  distkey,
  size AS size_mb
FROM SVV_TABLE_INFO
WHERE tablename IN ('orders', 'customers')
ORDER BY size DESC;

The Redshift Architecture guide has a fuller explanation of how the node-and-slice model affects join performance.

Sort keys and zone map pruning

Sort keys control whether Redshift can skip disk blocks when evaluating WHERE clause filters. If your fact table is sorted by order_date and your query filters to a date range, Redshift reads only the blocks containing rows in that range and skips everything else. This is called zone map pruning.

For a large fact table with a date sort key, a one-month date-range query might read only 5 to 10 percent of the total disk blocks. Without a sort key, the same query reads everything.

Choosing a sort key

• Best candidates: columns used in WHERE clause range filters with >, <, or BETWEEN. Date and timestamp columns on transactional fact tables are the most common example.

• Compound sort keys: define multiple columns in priority order. The first column is the primary sort; subsequent columns only help if the first is also filtered. Good for tables where queries always filter on date and sometimes also on a secondary column like region.

• Interleaved sort keys: give equal weight to multiple columns. Useful when different queries filter on different columns. They require VACUUM REINDEX to maintain, which is significantly more expensive than a regular VACUUM.

Checking whether your sort key is actually pruning blocks

SELECT
  tablename,
  sortkey1,
  unsorted,
  stats_off,
  size AS size_mb
FROM SVV_TABLE_INFO
WHERE tablename = 'orders';

If unsorted is above 20, zone map pruning is being undermined for recently loaded rows. Run VACUUM SORT ONLY orders; to restore sort order.

In your EXPLAIN output, compare the estimated rows for the scan step against total table rows. If the estimate is much lower, pruning is working. If the estimate equals the total row count, the sort key is not being used. Check whether the WHERE clause actually filters on the sort key column, and check unsorted in SVV_TABLE_INFO.

For compound vs interleaved sort keys in detail, see Partitioned Tables: Sort Keys Explained.

How to troubleshoot a slow Redshift query step by step

When a query is slow, follow this sequence. Each step rules out a category of problem before moving on. Do not jump straight to redesigning tables. Queue contention or stale statistics are faster fixes and are often the actual cause.

Step 1: check whether the query was waiting in queue

Before diagnosing the query itself, check if it spent most of its time waiting, not executing. Queue wait time does not show up in EXPLAIN.

SELECT
  query,
  queue_time / 1000000 AS queue_seconds,
  exec_time / 1000000 AS exec_seconds,
  elapsed / 1000000 AS total_seconds,
  substring(querytxt, 1, 80) AS query_text
FROM SVL_QLOG
WHERE userid > 1
ORDER BY starttime DESC
LIMIT 20;

If queue_seconds is large and exec_seconds is small, the problem is WLM queue pressure, not the query. Increase concurrency, enable concurrency scaling, or route the query to a less busy queue.

Step 2: inspect actual execution statistics

For queries that already ran, use SVL_QUERY_REPORT to see how long each step actually took and how many rows each step produced. This is more useful than EXPLAIN because it shows real numbers, not the planner’s estimates.

SELECT
  query,
  segment,
  step,
  label,
  rows,
  bytes,
  elapsed_time / 1000000 AS elapsed_seconds
FROM SVL_QUERY_REPORT
WHERE query = <your_query_id>
ORDER BY elapsed_time DESC;

Find the step with the highest elapsed_seconds. That is where to focus. A redistribute step with a high elapsed time points to a DISTKEY problem. A scan step with a high elapsed time points to a missing or ineffective sort key.

Step 3: check for redistribution

Look for DS_DIST_BOTH in your EXPLAIN output, or look for a redistribute label with a high elapsed time in SVL_QUERY_REPORT. If you see redistribution on a large table, the fix is to align DISTKEY columns on both sides of the join.

Step 4: check sort key pruning

In the EXPLAIN output, compare the estimated rows for your scan step against total table rows from SVV_TABLE_INFO. If the estimate equals the full row count, the sort key is not pruning blocks. Check that the WHERE clause filters on the sort key column, and check that unsorted is low.

Step 5: check stale statistics and unsorted rows

SELECT
  tablename,
  stats_off,
  unsorted,
  size AS size_mb
FROM SVV_TABLE_INFO
WHERE stats_off > 10 OR unsorted > 20
ORDER BY stats_off DESC;

If stats_off is above 10, run ANALYZE tablename; to update planner statistics. If unsorted is above 20, run VACUUM SORT ONLY tablename; to restore sort order.

Step 6: check the system alert log

Redshift automatically flags known performance problems. Checking the alert log takes 30 seconds and often tells you exactly what to fix.

SELECT
  query,
  event,
  solution,
  state
FROM STL_ALERT_EVENT_LOG
WHERE query = <your_query_id>
ORDER BY query;

Common alerts include missing statistics, large distribution ratios, and expensive nested loops. The solution column describes the fix.

Step 7: check result-set size

A query can execute fast internally but appear slow because it is returning millions of rows to the client. Check the rows count in SVL_QUERY_REPORT for the final step. If the count is very large, add a LIMIT clause, push aggregations into the query, or write results to a staging table.

Step 8: check table-level key recommendations

SELECT
  type,
  schema,
  tablename,
  new_val,
  impact_estimated
FROM SVV_ALTER_TABLE_RECOMMENDATIONS
WHERE type IN ('SORT KEY', 'DIST KEY')
ORDER BY impact_estimated DESC;

This view shows Redshift’s own recommendations based on observed query patterns. Compare against your diagnosis before applying a change. A DISTKEY change on a large table requires redistributing all the data and takes time.

Step 9: check concurrency scaling pressure

SELECT *
FROM SVCS_CONCURRENCY_SCALING_USAGE
ORDER BY start_time DESC
LIMIT 20;

If queries are frequently spilling to concurrency scaling clusters, the main cluster is regularly at capacity. Consider resizing, adjusting WLM settings, or scheduling heavy workloads off-peak.

VACUUM and ANALYZE

These two commands are routine maintenance that keeps Redshift healthy. Skipping them is one of the most common causes of gradual performance degradation on clusters receiving regular data loads.

VACUUM: reclaim space and restore sort order

When rows are deleted or updated in Redshift, the old rows are marked as deleted but not immediately removed. VACUUM reclaims that space and re-sorts rows that were appended after the initial sort order was established. Unsorted rows undermine zone map pruning. Run it after large DELETE operations or large data loads.

-- Full vacuum: reclaim space and re-sort
VACUUM orders;

-- Only re-sort unsorted rows (faster, no space reclaim)
VACUUM SORT ONLY orders;

-- Only reclaim deleted space (faster, no sort)
VACUUM DELETE ONLY orders;

-- Find tables that need vacuuming
SELECT
  tablename,
  unsorted AS pct_unsorted,
  stats_off AS pct_stats_stale,
  size AS size_mb
FROM SVV_TABLE_INFO
WHERE unsorted > 20 OR stats_off > 20
ORDER BY unsorted DESC;

Redshift runs auto-vacuum in the background but is conservative during busy periods. After a large batch load, trigger VACUUM manually on the affected tables before running performance-sensitive queries.

ANALYZE: update query planner statistics

ANALYZE updates the statistics the query planner uses to estimate row counts and costs. Stale statistics cause bad query plans. For example, the planner underestimates the rows in a join result and chooses a nested-loop join instead of a hash join, which is catastrophically slow on large tables.

-- Analyze a specific table
ANALYZE orders;

-- Analyze only the columns used in joins and filters (faster)
ANALYZE orders (order_date, customer_id, region);

-- Analyze the entire database (runs in the background)
ANALYZE;

Run ANALYZE after large data loads or significant DELETE operations. After a bulk load from S3 or a transform pipeline, include ANALYZE as the final step before reporting the load as complete. See Loading Data into Redshift for how this fits into a COPY-based pipeline.

Automatic table optimisation vs manual tuning

Redshift’s automatic table optimisation (ATO) feature lets Redshift observe query patterns over time and apply DISTKEY and SORTKEY changes automatically when it determines they would improve performance. Changes happen in the background without cluster downtime or manual intervention.

When AUTO is a good choice

• You are just getting started and do not yet know which queries will dominate
• Your workload is varied and hard to predict in advance
• You do not have a dedicated DBA for ongoing table tuning
• The cluster is new and does not yet have enough query history to make informed manual decisions

When manual DISTKEY and SORTKEY still make sense

• You have large, stable fact tables with well-understood, high-frequency join patterns
• ATO has not converged on a good key after several weeks of observation
• You need deterministic table behaviour, since ATO changes can shift performance unexpectedly during the transition period
• You are designing from scratch with known query patterns, such as a classic star schema with a well-defined fact table and a small set of dimension tables

Monitoring ATO recommendations before they apply

SELECT
  type,
  schema,
  tablename,
  new_val,
  impact_estimated
FROM SVV_ALTER_TABLE_RECOMMENDATIONS
ORDER BY impact_estimated DESC;

You can apply a recommendation manually, or set the table to AUTO distribution and let Redshift apply it. Reviewing recommendations against your own domain knowledge is a good sanity check before letting Redshift redistribute a large table.

Redshift Advisor recommendations

Redshift Advisor is a built-in analysis tool visible in the AWS Console under Redshift, then Advisor. It analyses cluster usage and makes recommendations across several categories:

• Table design: tables that would benefit from a different distribution or sort key
• Compression: tables with suboptimal or missing compression encodings
• VACUUM: tables with a high percentage of unsorted or deleted rows
• Workload: WLM or concurrency suggestions based on queue history

Advisor is a good starting point for a performance audit. Treat its recommendations as hypotheses to investigate, not automatic fixes. Always cross-reference with your actual query patterns before applying a DISTKEY or SORTKEY change to a large table, since the change requires redistributing data across the cluster.

Result caching

Redshift caches query results automatically. If the same query runs again against unchanged data, Redshift returns the cached result without re-executing the query, often in milliseconds. This is particularly useful for BI dashboards that repeatedly refresh the same aggregations on large fact tables.

When result caching applies

• The query text is identical. Even a whitespace difference prevents a cache hit.
• The underlying tables have not been modified since the last run. Any write invalidates the cache for those tables.
• The query does not use volatile functions like GETDATE(), NOW(), or RANDOM()
• The user has permission to access all the tables referenced in the cached result

When caching does not help

• ETL jobs have just updated the underlying table. The cache is invalidated automatically.
• Analyst queries are exploratory and rarely repeat with identical text
• Queries use session variables or non-deterministic functions

Verifying cache hits

SELECT
  query,
  from_sp_cache AS cache_hit,
  elapsed / 1000000 AS elapsed_seconds,
  substring(querytxt, 1, 80) AS query_text
FROM SVL_QLOG
WHERE userid > 1
ORDER BY starttime DESC
LIMIT 20;

A cache_hit value of t means the result was served from cache. To maximise cache hit rate for dashboards, avoid volatile functions in query text and make sure ETL jobs update tables on a predictable schedule so queries run after the load window have a chance to hit the cache.

Compression and encodings

Compression is not just a storage concern in Redshift. It directly affects query speed. Compressed columns require less disk I/O to read, which speeds up every scan. A poorly-compressed table can be 2 to 5 times slower to scan than the same table with good encodings applied.

Getting compression recommendations

ANALYZE COMPRESSION orders;

The output recommends an encoding for each column based on actual data patterns. Common encodings and when to use them:

• AZ64: Redshift’s own algorithm, a good default for numeric columns and dates
• ZSTD: good general-purpose encoding for text and mixed-type columns
• LZO: fast decompression, good for high-cardinality string columns
• BYTEDICT: excellent for low-cardinality columns like status codes, region names, or boolean-like fields with few distinct values
• RAW: no compression. Only use where truly needed, such as sort key columns where Redshift recommends leaving compression off.

To apply new encodings to an existing table, the standard approach is to create a new table using CREATE TABLE AS SELECT (CTAS) with the new encoding definitions, then rename it to replace the original.

Concurrency scaling and short query acceleration

These two features address different versions of the same problem: too many queries competing for resources at the same time.

Short Query Acceleration (SQA)

SQA automatically detects queries that are likely to complete quickly and routes them to a dedicated fast lane, bypassing normal WLM queue wait times. It uses machine learning to predict query duration before execution starts. SQA is enabled by default with Automatic WLM.

SQA helps when fast dashboard queries are stuck waiting behind a slow ETL job in the same queue. You get the isolation benefit without manually splitting queues or routing queries by user group.

Concurrency scaling

When your WLM queues fill up and queries start waiting, Redshift can automatically spin up additional transient cluster capacity to handle the overflow. These extra clusters appear in seconds and are removed when demand drops.

Concurrency scaling is billed by the second and is separate from your main cluster cost. It is most cost-effective for workloads with predictable peak periods such as morning dashboard refreshes, rather than sustained heavy load where resizing the main cluster is usually a better investment.

Enable concurrency scaling at the WLM queue level, not on every queue. Enable it on the queues where you want overflow capacity, typically the dashboard or analyst queue. The cost implications are covered in Redshift Pricing Explained.

Materialized views for repeated workloads

A materialized view is a pre-computed, stored result of a query. Instead of re-executing a complex join and aggregation every time a dashboard loads, Redshift reads a small pre-aggregated result set that was computed once and stored on disk.

When materialized views help most

• BI dashboards that run the same GROUP BY aggregations on large fact tables repeatedly throughout the day
• Queries that join several large tables and then aggregate. The join result is what gets materialised.
• Reporting workloads where source data changes on a known schedule, such as a nightly ETL load

Creating and refreshing a materialized view

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  DATE_TRUNC('day', order_date) AS day,
  region,
  SUM(total_amount) AS revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW daily_revenue;

Schedule REFRESH MATERIALIZED VIEW as the final step of your ETL job, immediately after the underlying tables are updated. That way, the next dashboard query reads fresh pre-aggregated data.

Automated materialized views

Redshift can automatically create and maintain materialized views based on observed query patterns, with no SQL required from you. It identifies repeated patterns and transparently rewrites queries to use the materialized view. This is part of automatic query optimisation and is enabled by default on clusters using Automatic WLM.

Workload Management (WLM)

WLM is a queuing system that prevents one bad query from blocking everything else. Without it, a single query scanning 10 billion rows can monopolise all compute resources while dashboard queries wait. WLM lets you define multiple queues with different memory allocations and concurrency limits, and route queries to the right queue based on who is running them.

Automatic WLM vs Manual WLM

A typical manual WLM queue setup

Routing a query to a specific queue

SET query_group TO 'dashboard';
SELECT region, SUM(revenue) FROM orders GROUP BY region;
RESET query_group;

Query monitoring rules (QMRs)

Within each WLM queue, define monitoring rules that automatically abort or hop queries exceeding certain thresholds. For example: abort any query in the dashboard queue that runs for more than 60 seconds. This prevents runaway queries from blocking the queue indefinitely while you investigate.

Reading EXPLAIN plans to diagnose slow queries

EXPLAIN shows the execution plan Redshift intends to use. It is most useful for understanding the cause of a slow query before you run it, or for confirming that a fix worked. For queries that already ran, use SVL_QUERY_REPORT or SVL_QUERY_SUMMARY for actual timing data.

EXPLAIN
SELECT c.country, SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY c.country
ORDER BY revenue DESC;

Key things to look for

• Seq Scan rows estimate: if the rows estimate matches total table size, the sort key is not pruning blocks for this filter. Check that the WHERE clause filters on the sort key column and that unsorted is low in SVV_TABLE_INFO.

• Hash Join vs Nested Loop: hash joins are efficient. Nested loops appear when join conditions are non-equi or when statistics are stale. On large tables they are orders of magnitude slower.

• DS_DIST_BOTH: both tables are being redistributed. Fix by aligning the DISTKEY column on both sides of the join.

• DS_BCAST_INNER: the smaller table is being broadcast to all nodes. Acceptable for small dimension tables. Consider switching to DISTSTYLE ALL if this table is always the inner side of joins.

• Sort steps: an in-memory sort for ORDER BY adds cost. Always include LIMIT when you only need top-N results. This allows Redshift to stop early.

Getting actual execution stats for a query that already ran

SELECT
  query,
  segment,
  step,
  label,
  rows,
  bytes,
  elapsed_time / 1000000 AS seconds
FROM SVL_QUERY_SUMMARY
WHERE query = <your_query_id>
ORDER BY elapsed_time DESC;

Real-world optimisation examples

Three common performance scenarios and how to fix them.

Example 1: BI dashboard queries getting slower over time

Symptom: Dashboard queries that ran in 2 seconds six months ago now take 25 seconds, and the issue is getting worse each month.

Likely cause: The fact table has grown significantly. More rows means more blocks to scan. The growing proportion of unsorted rows from nightly loads is undermining zone map pruning. Statistics are stale after bulk loads.

Fix:

• Run VACUUM SORT ONLY orders; to restore sort order on the fact table
• Run ANALYZE orders; to update planner statistics
• Create materialized views for the most frequently run aggregations and refresh them at the end of each ETL run
• Add VACUUM and ANALYZE as the final steps of your nightly data pipeline so this does not become a recurring problem

Example 2: nightly ETL job missing its SLA

Symptom: A nightly ETL job that should complete by 6 AM is now finishing at 8 AM, delaying fresh data for analysts.

Likely cause: The ETL job is competing for WLM resources with early morning dashboard queries. Large table joins also take longer as staging tables have grown.

Fix:

• Route ETL queries to a dedicated WLM queue with its own memory allocation and no timeout
• Check EXPLAIN for DS_DIST_BOTH on staging table joins. Staging tables often use EVEN distribution, causing shuffles when joining to DISTKEY fact tables.
• If transforms are running in AWS Glue, consider pushing them into Redshift SQL using CTAS or INSERT INTO SELECT. See ETL vs ELT for when this trade-off makes sense.

Example 3: analyst queries unpredictably slow

Symptom: Analyst exploratory queries sometimes return in seconds and sometimes take minutes, with no obvious pattern.

Likely cause: Queries are competing for the same WLM queue during busy periods. When the queue fills up, new queries wait. The slowness is not caused by the queries themselves.

Fix:

• Check SVL_QLOG. Compare queue_time vs exec_time. If queue time is high, the problem is concurrency, not query design.
• Enable concurrency scaling on the analyst WLM queue to handle peak periods automatically
• Add a short query timeout and hop rule so runaway analyst queries exit the queue rather than blocking it
• Encourage analysts to add LIMIT to exploratory queries to reduce result sets and shorten execution time