Should I just add more compute when queries are slow?

Only after checking the design. Bad distribution, missing filters on sort keys, or SELECT star usually cost more than the compute you would add. Fix those first, then scale if a well-designed query is still too slow.

Amazon Redshift Module 5: Performance Tuning and Maintenance

Introduction

Redshift is fast when data and queries line up with its design, and slow when they do not. This module shows how to find the cause of a slow query, the tuning levers you have, and the maintenance Redshift now does for you. System view names can differ by deployment type, so check the documentation for yours.

Prerequisites

Module 4 completed

Step-by-Step Guide

Step 1: Read a query plan with EXPLAIN

EXPLAIN shows how Redshift will run a query without running it. Look for large data redistribution steps (DS_DIST_ALL_INNER, DS_BCAST_INNER, DS_DIST_BOTH), which mean data is moving between nodes because the join keys do not match the distribution keys. Fixing distribution often removes them.

EXPLAIN
SELECT c.city, sum(f.amount)
FROM fact_sales f
JOIN dim_customer c ON c.customer_id = f.customer_id
GROUP BY c.city;

Step 2: Find slow and heavy queries

Use the system monitoring views to see recent queries, their duration, and their status. On Serverless and recent provisioned clusters the SYS views provide a unified history. Sort by elapsed time to find candidates, then examine their plans.

SELECT query_id, user_id, status, elapsed_time/1000000.0 AS seconds, query_text
FROM sys_query_history
WHERE start_time > dateadd(hour, -24, getdate())
ORDER BY elapsed_time DESC
LIMIT 10;

Step 3: Apply the biggest tuning levers

In rough order of impact: select only the columns you need instead of SELECT star, filter on the sort key, join on matching distribution keys, avoid functions on filtered columns, keep statistics fresh, and replace repeated heavy aggregations with materialized views. Fix design problems before adding compute capacity.

-- weak: function on the filtered column prevents block skipping
-- WHERE date_trunc('month', sale_date) = '2026-09-01'

-- better: compare the raw column to a range
WHERE sale_date >= '2026-09-01' AND sale_date < '2026-10-01'

Step 4: Use materialized views for repeated aggregates

A materialized view stores the result of a query and can be refreshed, so dashboards read a small precomputed table. Redshift can refresh them automatically (auto refresh) or on demand, and can rewrite eligible queries to use them.

CREATE MATERIALIZED VIEW mv_daily_revenue AUTO REFRESH YES AS
SELECT sale_date, sum(amount) AS revenue, count(*) AS sales
FROM fact_sales
GROUP BY sale_date;

REFRESH MATERIALIZED VIEW mv_daily_revenue;

Step 5: Understand caching, concurrency, and maintenance

Redshift caches the results of identical queries, so a repeated dashboard query can return instantly when the data has not changed. Concurrency Scaling adds temporary capacity during bursts on provisioned clusters, and Serverless scales on its own. Redshift also runs VACUUM (to re-sort and reclaim space) and ANALYZE (to update statistics) automatically in the background for most tables, so manual runs are rarely needed. Check table health with SVV_TABLE_INFO.

ANALYZE fact_sales;       -- manual statistics refresh, if needed
VACUUM fact_sales;        -- manual re-sort and reclaim, if needed

SELECT "table", size, tbl_rows, unsorted, stats_off
FROM svv_table_info
ORDER BY size DESC
LIMIT 10;

FAQs

Should I just add more compute when queries are slow?: Only after checking the design. Bad distribution, missing filters on sort keys, or SELECT star usually cost more than the compute you would add. Fix those first, then scale if a well-designed query is still too slow.

Summary

Use EXPLAIN to find data redistribution, find slow queries in the SYS views, filter on raw sort key columns, use materialized views for repeated aggregates, and rely on automatic maintenance while checking table health.