Redshift SQL Query Performance

Decision tree (start here)

1. Decompose elapsed_time in SYS_QUERY_HISTORY (next section) and find the bucket that grew. First check that the “fast” run wasn’t served from the result cache (result_cache_hit = true): then Redshift returned a stored result without running the query, so its time isn’t a real baseline. Also confirm your fast/slow runs are the same statement (apples-to-apples) before comparing — an unchanged query can still degrade from data, stats, plan, or contention shifts.
2. Branch on the dominant bucket:
   • queue_time → WLM / concurrency contention; find concurrent long-runners (same queue under manual WLM, cluster-wide otherwise) → When queue_time dominates — remediation.
   • compile_time → first-run / post-patch recompile — expected and transient → Segment compilation.
   • planning_time → plan/query complexity, catalog, or datasharing elements; no deeper public telemetry, so if it dominates and you need more detail → escalate to AWS.
   • lock_wait_time → blocking transaction / concurrent DDL.
   • execution_time → real work; drill in by symptom: broadcast/redistribution or bad join → Reading EXPLAIN; SELECT DISTINCT / many LEFT JOINs → Row multiplication (join fan-out); estimate ≠ actual → Estimate divergence (stale/missing statistics); slow after a function on a join/dist key → Functions on join/distribution keys; one slice/node hot → Distribution skew; grew on an unchanged plan → Stalled VACUUM.
3. Same query, slower, plan unchanged? Check data-volume growth, then compare redshift_version across runs for a patch boundary → Did the query change… / escalate.

Escalating to AWS = open an AWS Support case (Support Center → Create case → Technical, service Amazon Redshift) with your evidence: the SYS_QUERY_HISTORY bucket breakdown, the query_id(s), and redshift_version before/after. See Creating support cases (opens in a new tab).

Decompose the time before proposing any fix

When a query “got slow,” do not jump to adding a DISTKEY/SORTKEY, rewriting the query, or running VACUUM. First find which time bucket regressed. elapsed_time is the total wall-clock; the other columns attribute time to individual phases (all in microseconds), all available per query in SYS_QUERY_HISTORY (works on Serverless and provisioned):

These are separate metrics and need not add up exactly to elapsed_time — read them as attribution hints to see which phase grew, not as a strict disjoint sum.

If planning_time is the dominant bucket, the public surface exposes only the single planning_time value — there is no further user-facing telemetry to break it down. It reflects plan/query complexity (large join search space, catalog work, possible datasharing elements); if it dominates and more detail is needed, escalate to AWS.

The pct_* columns turn the raw buckets into shares of elapsed_time — read whichever is largest to decide which phase to investigate.

Compare the buckets across a fast run and a slow run of the same query — the bucket that grew is the one to investigate. Only then pick a fix, targeted at that bucket.

In particular, separate queue wait from execution: if the extra time is queue_time, the query didn’t get slower to execute — it waited behind other work. So investigate what it queued behind (next paragraph), not the queued query itself: other concurrently running queries were using the cluster’s concurrency and memory, and tuning them often relieves it. A large execution_time with small queue_time instead points at the query’s own work — data volume, distribution skew, or spill.

Confirm queue pressure by comparing queue_time to elapsed_time per query in SYS_QUERY_HISTORY (a high queue-time share = WLM contention); on provisioned clusters, query_priority and compute_type (primary-scale = the query ran on a concurrency-scaling cluster) show how queuing and scaling are being applied.

To find what it queued behind, look in SYS_QUERY_HISTORY for concurrent long-running queries whose execution overlapped the slow query’s queue window, ordered by execution_time desc. Scope depends on the WLM mode: under Manual WLM each queue (service_class_id) has fixed slots and a fixed memory share, so filter to the same service_class_id to find the slot-hogs. Under Automatic WLM, concurrency and memory are assigned dynamically per query across the whole cluster — not partitioned per service class — so don’t restrict to one service_class_id; look at the heavy concurrent queries cluster-wide in the overlap window. Either way, those long-runners are the contention source. Then treat the long-runner as the new subject: decompose its time and drill into its dominant bucket (usually execution_time → spill / skew / plan / stats) and tune it — fixing the hog at the source relieves the queue for everything behind it. Reprioritizing (WLM slots/priority, QMR, separate queue) or scaling only redistributes the contention if the query itself isn’t addressed.

When queue_time dominates — remediation. The query queued because other concurrently running queries were using the cluster’s concurrency and memory. Find them (see the paragraph above; the ready-to-run query is in the queries reference); tuning them is often what relieves the queuing.

The levers below are configuration changes, so recommend them to the user rather than making them. Automatic WLM, concurrency scaling, and SQA are provisioned levers; on Serverless, raise capacity instead. WLM query queues and query monitoring rules (QMR) work on both deployments, configured through the wlm_json_configuration parameter: in the cluster’s parameter group on provisioned, and in the workgroup’s configuration parameters on Serverless.

• Confirm Automatic WLM is on — the slow query’s service_class_id in SYS_QUERY_HISTORY tells you: 100–107 is automatic WLM, 6–13 is a manual WLM queue. If it’s manual, recommend switching to automatic WLM so Redshift manages concurrency and memory from the workload rather than fixed manual slots.
• Recommend enabling concurrency scaling — adds transient clusters for spiky/bursty demand. Free-tier credits accrue per running day; confirm the current allowance on the Redshift concurrency-scaling pricing page rather than quoting a figure, and cap spend with a concurrency-scaling usage limit.
• Confirm Short Query Acceleration (SQA) is on (on by default; off only if wlm_json_configuration has "short_query_queue": false) — routes short queries past the queue, cutting their queue wait.
• Separate WLM queues by workload (BI / ETL / ad-hoc) and, on provisioned clusters, set queue priorities so critical queries aren’t stuck behind heavy ones.
• Add query monitoring rules (QMR) to catch runaway queries (nested loops, long runtime, disk spill) before they clog a queue.
• Serverless — scale capacity (no WLM mode or SQA to enable). Two alternative approaches, not simultaneous knobs:
  • AI-driven scaling (on by default for new workgroups). The Price-performance target slider (Optimized for Cost ↔ Optimized for Performance; default Balanced) lets Redshift manage RPUs for you toward a cost/perf goal — you do not hand-set a base capacity in this mode. Bound the spend with Max capacity / Max RPU-hours. Best when you don't know the right base. Nudging the slider toward performance is the lever here.
  • Manual base capacity. Set a fixed Base capacity (RPUs) floor (plus a Max capacity ceiling) when you want a predictable, hand-tuned size instead of AI-driven scaling.

Stalled VACUUM as an execution regression: if execution_time (and blocks_read in SYS_QUERY_DETAIL) grew while the plan is unchanged, suspect table maintenance. When auto-vacuum stops, deleted rows linger as ghost rows (tbl_rows − estimated_visible_rows in SVV_TABLE_INFO) and unsorted% climbs — both defeat sort-key zone-map pruning, so the scan reads more blocks for the same post-filter result. Check every table the slow query reads, one by one: take each table_name from SYS_QUERY_DETAIL for the slow query_id (it covers tables behind views; table_name is db.schema.table), and look up each in SVV_TABLE_INFO. Look first at the table whose scan sits in the segment whose blocks_read grew (blocks_read is recorded on the segment row, not the step). Confirm with SYS_VACUUM_HISTORY (did auto-vacuum stop?) and SVV_TABLE_INFO, then pick the phase the evidence points to: ghost rows → VACUUM DELETE ONLY; unsorted rows → VACUUM SORT ONLY, but only when vacuum_sort_benefit > 0 (high unsorted% alone is not a reason to sort).

For the per-step (operator-level) breakdown of the execution bucket, query SYS_QUERY_DETAIL for the query_id. Columns are recorded at different levels:

• Segment rows only (0 on step rows): blocks_read, spilled_block_local_disk and spilled_block_remote_disk
• Segment rows, copied onto each of their steps: duration and time_skewness, so they can’t tell a segment’s steps apart
• Step rows only: input_rows and output_rows, data_skewness (how unevenly the step’s rows were spread), and alert (missing stats, nested loop, large broadcast)

Find the slow segment first, then the expensive step inside it.

A spill is a symptom, not a root cause. When a segment spilled (the spilled_block_local_disk / spilled_block_remote_disk columns on its segment row), its working set exceeded the memory it was granted, so Redshift wrote intermediate data to disk. The goal is not zero spill: some steps spill even when the query is well written. Check the step against these best practices, and once they’re met, treat any remaining spill as acceptable:

• Reduce the data the step handles: filter earlier, select only the columns needed (no SELECT *), pre-aggregate, and remove any join fan-out.
• For a skewed spill (one slice spills much more than the others), co-locate the join: align the DISTKEYs, or join on the distribution key, so rows aren’t concentrated on one slice.
• Refresh statistics: a low row estimate gives the step too small a memory grant, so run ANALYZE.

Adding nodes or disk does not fix a skewed spill.

Declared VARCHAR width matters only when the planner has no statistics. Rows are stored at their actual length, whatever the declared width. With statistics, the planner uses the column’s real average width and distinct-value count. Without them, it estimates width from the declared width (larger for wider declarations, up to a cap). That oversizes memory grants and can misestimate join row counts. Run ANALYZE to give the planner statistics — except on columns declared very wide (e.g. VARCHAR(65535)), which ANALYZE skips; right-size those instead.

Segment compilation (first run)

Redshift compiles optimized code for each query plan and keeps the compiled segments in a compiled-code cache, locally and in a remote cache that persists across reboots, so later runs skip compilation. For a query whose segments aren’t in that cache yet, composition (on by default) starts it immediately with pre-existing logic while the optimized code compiles in the background, so first-run compile_time is rarely on the critical path (Compiled code (opens in a new tab)).

To confirm compilation is the cause, check whether compile_time is the bucket that grew in SYS_QUERY_HISTORY (SVL_COMPILE gives per-segment detail, provisioned only). If it is, a rerun should be fast.

Patch caveat. A version upgrade / patch clears the compiled-code cache (a plain reboot does not), so first runs after a patch can recompile — but since composition keeps that off the critical path, don’t assume post-patch slowness is “just recompile.” Confirm it’s compile_time that grew and that it vanishes on rerun; if it’s in execution_time / the plan or persists after reruns, treat it as a real regression (compare redshift_version, buckets, plan).

Rule out result-cache hits before comparing runs

If result_cache_hit = true, Redshift returned a cached result — execution_time is near-zero and tells you nothing about the query’s real cost. A benchmark where the “fast” run was a cache hit is not a valid measurement, and a fast→slow comparison against a cached baseline is comparing a cache return to real compute.

• Confirm with result_cache_hit in SYS_QUERY_HISTORY.

• To measure real (uncached) cost, disable the result cache for the session:

  sqlCopy

  SET enable_result_cache_for_session TO off;

  or compare only cache-miss executions (result_cache_hit = false).

Reading EXPLAIN: distribution and join strategy

Two things in an EXPLAIN plan explain most “execution bucket” slowness — data movement and join strategy.

Data distribution operators (the DS_* labels):

Join strategy:

• Merge Join — fastest; both tables distributed and sorted on the join key, and less than 20% of each table unsorted (SVV_TABLE_INFO.unsorted).
• Hash Join — the common case; builds a hash table on the inner side.
• Nested Loop — the correct (and only) strategy for non-equi joins (inequality / range conditions like a.x < b.y) and intentional cross joins. It is a red flag only when it shows up for what should be an equi-join — usually a missing or mistyped join predicate producing an accidental near-Cartesian product (a huge estimated cost is the tell). Check the join condition.

Fix broadcast/redistribution by co-locating the join: set a matching DISTKEY on the join column of both tables, or DISTSTYLE ALL for a small, slow-moving dimension table. ALL copies the whole table to every node, which multiplies its storage and slows every load and update. There’s no fixed size cutoff; when unsure, use DISTSTYLE AUTO, which starts a small table as ALL and changes it as the table grows. To see the executed plan (not just the estimate), read SYS_QUERY_DETAIL (step_name values like hashjoin, nestloop, broadcast, distribute, scan) or SYS_QUERY_EXPLAIN.

Row multiplication (join fan-out)

A SELECT DISTINCT or aggregate over many LEFT JOINs can be slow because the joins fan out — one row matches many in a dimension/lookup table, so an intermediate join step processes billions of rows that DISTINCT then collapses back to a few million.

• Spot it in SYS_QUERY_DETAIL: a join step’s output_rows is far larger than the query’s final returned rows (huge join row count vs small result). Broadcast (DS_BCAST_INNER) on those joins compounds it.
• Find the offending join by testing each LEFT JOIN incrementally with COUNT(*).
• Fix (only when the query’s semantics permit it — i.e. the multiplied rows aren’t actually needed): pre-aggregate or deduplicate the lookup tables in a WITH clause / subquery so each returns one row per key (restore the one-to-one relationship) before joining; and co-locate the joins on the distribution key.

Diff the plan across the fast and slow run

Reading one plan shows the current shape; diffing the fast run’s plan against the slow run’s shows what changed. Do this when the buckets point at execution/plan and you have both a fast and a slow run of the same statement.

1. Pair the two runs. Get one fast and one slow query_id for the same query — group runs by the built-in generic_query_hash / user_query_hash, or by a normalized SQL text fingerprint (mask literal values, keep identifiers). Use whichever is handy.

2. Fingerprint each plan to decide whether the shape changed — before eyeballing nodes. Pull SYS_QUERY_EXPLAIN (populated per child query: query_id + child_query_sequence; a rewritten statement has several child plans). Raw plan text is noisy: plan_info embeds the WHERE literals (Filter: (dt >= '2026-01-01'::date)) and plan_node embeds volatile cost=…/rows=…/width=… — all vary run-to-run even when the shape is identical. So build a normalized plan hash per child_query_sequence:

   • Order rows by plan_node_id; concatenate plan_node + plan_info.
   • Strip the volatile parts: replace literal constants in plan_info ('…', …::type, bare numbers) with a placeholder, and drop the cost=/rows=/width= numbers from plan_node.
   • Keep the structure: operator, DS_* label, join type, and the predicate columns.
   • Hash the result. Equal fast-vs-slow hash → the plan shape did not change — stop diffing the plan and go to the data/stats check (next section) or the other buckets. Different → the plan flipped; drop to the tree diff.

   Reading the plan as a tree. SYS_QUERY_EXPLAIN returns nodes: plan_node_id identifies each node and plan_parent_id points to its parent, so together they reconstruct the operator tree. plan_node_id is within-plan — it also joins to SYS_QUERY_DETAIL (query_id + child_query_sequence + plan_node_id) to show which execution segment/step ran a node — but it is not a stable identity across two runs, so don’t assume node N in the fast run is node N in the slow run. Localize the change by walking parent→child and finding the subtree where plan_node (operator / DS_* / join type) or plan_info (predicate columns) differs.

3. Tree diff (only when the hashes differ), per child_query_sequence:

   • plan_node — a DS_DIST_NONE / DS_DIST_ALL_NONE that became DS_BCAST_INNER / DS_DIST_INNER / DS_DIST_BOTH, or a Hash/Merge Join that became a Nested Loop, is the regression.
   • plan_info — join conditions and WHERE restrictions; a predicate that moved (e.g. a filter no longer applied at the Seq Scan but late in a join) shows predicate placement changed — often the cause of a redistribution flip.
   • SYS_QUERY_EXPLAIN also emits a ----- Tables missing statistics ----- node — a direct stale/missing-stats signal.
   • Tie the flip back to the execution evidence — map node → segment. A plan change that raised execution_time should be traceable to a specific segment, but the changed node isn’t always the slowest one: a flip in an earlier segment (e.g. a redistribution that lands rows badly) can make a later segment the bottleneck. So map the changed plan_node to its segment, pull the slowest segments for the slow run from SYS_QUERY_DETAIL (order by duration), and check whether the slowest segment is, or is fed by, the changed node — comparing the same segments against a successful (fast) run. If the slowest segment can’t be traced back to the plan change, the flip may not be the cause — re-check the other buckets.

4. Estimate drift on the base-table scans (no separate hash — just read rows=). For the Seq Scan nodes on base tables, compare the scan’s rows= estimate between the fast and slow run. A rows= estimate that changed run-to-run — or a ----- Tables missing statistics ----- node — means the stats/data moved under the query, so the plan didn’t have to change shape to get slower. Read the estimate off the scan node. To compare it with the actual rows, use the queries reference’s plan-diff Step 4, which joins each step to its own plan node, so a table scanned several times is still matched correctly. This is the hand-off to the next section.

If the two plans are identical (same hash) but the timing still differs, the plan shape is not the cause. Four common culprits: (a) the WHERE literals select more rows — same plan, but a wider predicate reads/returns more data (see the data/stats section below); (b) the base tables grew — same plan, but more input rows to scan/join means linearly more execution_time; confirm by comparing the scan steps’ input_rows (and output_rows) in SYS_QUERY_DETAIL between the comparable runs, not SVV_TABLE_INFO.tbl_rows (current snapshot only, no history); or (c) concurrency contention — more queries running concurrently during the slow run’s window, especially long-runners or heavy spillers competing for compute / memory / disk (this is not queue-slot waiting, which shows up as queue_time; see the concurrent long-runner query in the queue section); or (d) duplicate data / join fan-out — new duplicate keys (or an accidental near-cartesian join) make an intermediate join step explode to far more rows even when the final returned rows look close to the fast run, so the extra work is invisible in the result count; confirm by comparing a join step’s intermediate output_rows in SYS_QUERY_DETAIL between the runs (see Row multiplication (join fan-out) above). If none fits, re-decompose the time buckets (top of this doc) to see which one actually grew.

An identical plan doesn’t guarantee identical row counts per step. On hash joins, Redshift can build a runtime (Bloom) filter from the build side’s join keys and apply it inside the probe-side scan, dropping rows that can’t match before they reach the join. EXPLAIN doesn’t show it, and SYS_QUERY_DETAIL gives it no step of its own. It shows only as that scan’s output_rows, which also reflects any filter in the plan, so the two can’t be separated. Whether the filter is used, and how much it removes, can vary between runs of the same plan. So when two same-plan runs show different output_rows on a hash join’s probe-side scan, a runtime filter behaving differently is a candidate cause, not only a change in the data.

Across environments (e.g. a dc2→ra3 migration or a rebuilt table), also confirm the underlying tables share the same physical design — compare diststyle / distkey / sortkey via SVV_TABLE_INFO (or SHOW TABLE) on each side. DISTSTYLE AUTO / SORTKEY AUTO can pick different keys per environment, which by itself changes the plan.

Data changes flip the plan — even when no ANALYZE has run

The optimizer builds each plan from the row counts and statistics it holds at plan time. Those inputs change without any change to the query: data loads and deletes change row counts, and ANALYZE (manual or automatic) refreshes the statistics. So the same SQL can get a new plan (or the same plan, run slower) with no query-text change. When the plan hash changed, or the base-table scan’s rows= estimate differed between the runs (above), work this checklist to find what moved underneath the query:

1. Statistics were refreshed between the runs. An ANALYZE — manual or auto — updates the statistics, which alone can flip the plan. Confirm one ran between the two runs in SYS_ANALYZE_HISTORY (is_automatic tells you which; check for either).
2. Row counts grew (feeds the cost model). A larger table raises estimated scan/join cost, so a co-located DS_DIST_NONE join can flip to DS_BCAST_INNER / redistribution past a threshold. The signal is the base-table scan's rows= estimate differing between the comparable runs (above). Don’t try to prove growth from SVV_TABLE_INFO.tbl_rows — it is only the current row count (a snapshot, no history), and a WHERE predicate would filter the scan estimate below the full-table count anyway, so the two aren’t comparable.
3. Stats went stale. As data changes, the planner’s whole-table estimate drifts from reality. Read staleness from SVV_TABLE_INFO.stats_off (0 = current, 100 = fully stale), and check whether DML has landed since the last ANALYZE (last-analyze time is in SYS_ANALYZE_HISTORY). Use SVV_TABLE_INFO (stats_off, tbl_rows) for this — not pg_class.reltuples, which is only a rough estimate. A stale estimate flips the plan before you would think to run ANALYZE. Check every referenced table (see the next section).
4. The physical design changed — auto or manual. A dist/sort-key change alters the plan: Redshift can auto-apply dist/sort recommendations, and a manual ALTER TABLE … ALTER DISTKEY/SORTKEY (or a table rebuild) does the same. Check both: recent DDL history for manual changes, and SYS_AUTO_TABLE_OPTIMIZATION for automatic ones.
5. Sort order / pruning degraded. Appends raise unsorted%, weakening zone-map pruning so scans read more blocks for the same rows. This slows the query on an unchanged plan. The signal is the scan’s segment blocks_read rising in SYS_QUERY_DETAIL while its input_rows stays comparable across the runs. Confirm with SVV_TABLE_INFO.unsorted and vacuum_sort_benefit for that table, and with SYS_VACUUM_HISTORY (did auto-vacuum stop or fall behind?). Fix: VACUUM SORT ONLY <schema_table, identifier, no quotes>;, but only when vacuum_sort_benefit > 0. See Stalled VACUUM above.

The common thread: a plan or timing change on unchanged SQL usually traces to statistics, row-count, physical-design, or sort-order movement, not the query — verify these before rewriting the query or forcing DDL.

Estimate divergence: stale or missing statistics

When EXPLAIN‘s estimated rows are wildly off from the actual rows, the optimizer is planning on stale or missing statistics, and a bad estimate produces a bad plan. Not every gap means bad statistics. For example, on a hash join’s probe-side scan a runtime filter can make the actual rows much lower than the estimate even when the statistics are current (see the runtime-filter note under Diff the plan across the fast and slow run).

• Check staleness with SVV_TABLE_INFO.stats_off (0 = current, 100 = fully stale).
• Fix by running ANALYZE <schema_table, identifier, no quotes>;. It refreshes both the row counts and the column statistics (distinct values, most common values) that filter and join estimates depend on. ANALYZE, not VACUUM, is what updates the planner statistics.
• Confirm the divergence by comparing the estimate (EXPLAIN rows=) to the actual input_rows / output_rows per step in SYS_QUERY_DETAIL (the queries reference’s plan-diff Step 4 shows both side by side).
• Before recommending a manual ANALYZE, check SYS_ANALYZE_HISTORY — auto-analyze may already have refreshed the statistics.
• Check stats_off for every table the query references (all joined tables / CTE sources), not just the largest or most obvious one — a single stale table’s bad estimate can flip the whole plan. ANALYZE each stale table.

Functions on join / distribution keys slow the join

Wrapping a join or distribution key in a function or expression — ON LOWER(a.id) = LOWER(b.id), a.id || '' = b.id || '', CAST, SUBSTRING, etc. — makes the join slower, for two reasons:

1. It defeats DISTKEY co-location. The expression’s value no longer matches the column’s distribution, so Redshift must redistribute or broadcast instead of doing a co-located join.
2. It defeats column statistics. Statistics are collected on columns, not on expressions, so the optimizer can’t estimate the expression’s cardinality and falls back to a poor default — an underestimate that can flip a co-located join to broadcast or redistribution.

The expression isn’t always in the SQL you’re looking at. If the query reads from a view, the function can come from the view’s definition, and redefining the view changes the join without any change to the query text. Check the join condition in the plan (Hash Cond in SYS_QUERY_EXPLAIN.plan_info), not only the SQL.

Fix: join on the raw column. If the transformation is genuinely needed, persist it as a materialized/precomputed column (and distribute/sort on that) so both the distkey and the statistics stay usable.

Distribution skew: one slice doing all the work

If one slice/node is doing far more work than the others while the rest sit idle, the query has skew. Two distinct causes:

• Table distribution skew — a DISTKEY on a low-cardinality or heavily-repeated column piles disproportionate rows onto one slice; a fixed property of the table. Detect with SVV_TABLE_INFO.skew_rows — the ratio of the most-populated to least-populated slice (near 1.0 is even; large values are skewed).
• Runtime (redistribution) skew — a hash join or broadcast redistributes rows so one slice receives most of them for a step, even when the base tables are evenly distributed; per-query, not a table property.

Detect runtime skew with SYS_QUERY_DETAIL‘s data_skewness and time_skewness (0–100%, higher = more unbalanced), which report how unevenly work fell across slices. data_skewness is per step (how unevenly the step’s rows were spread); time_skewness is the segment’s value, repeated on each of its steps. They flag that a step or segment is skewed, not where the skew sits; you don’t need that to fix it. Tell table skew from runtime skew (SVV_TABLE_INFO.skew_rows vs SYS_QUERY_DETAIL.data_skewness), then apply the remediation below.

Remediation depends on the case: table skew is typically addressed by a better distribution (a higher-cardinality DISTKEY, or DISTSTYLE EVEN); runtime redistribution skew by co-locating the join (aligning DISTKEYs / joining on the distribution key). Whether a distribution change is worth it depends on the query mix, table size, and how other queries join the table — so prefer DISTSTYLE AUTO, which chooses the distribution from the observed workload, and check SVV_ALTER_TABLE_RECOMMENDATIONS for a pending recommendation before choosing a key by hand. Resizing the cluster or adding nodes does not fix skew — it just spreads the same skewed data across more slices, so one slice still carries the disproportionate load.

Did the query change, or something around it?

Before assuming “the query got slower,” confirm whether the SQL text even changed:

• SYS_QUERY_HISTORY exposes user_query_hash (same text including literals) and generic_query_hash (same text ignoring literals). If the hash is unchanged across the fast and slow runs, the query did not change — look elsewhere.
• If the text is unchanged, investigate what changed around it: data-volume growth (compare the base-table scan steps’ input_rows in SYS_QUERY_DETAIL between the fast and slow runs), stale stats, or a plan change. Compare the fast vs slow executions with SYS_QUERY_HISTORY (time buckets) and SYS_QUERY_DETAIL (per-step rows and the alert column for plan-quality warnings).
• Patch/version regression: only after ruling out buckets, data volume, stats, plan, and spill/skew, compare redshift_version across the before/after runs in SYS_QUERY_HISTORY — a version change lining up with the regression points at a patch. A patch is far more credible when the regression affects many unrelated queries across the cluster at the same date boundary — plans flipping across multiple queries at comparable row counts and stats — not just this one; a single query regressing usually has a local cause, so confirm the symptom spans multiple queries across the cluster first. Patch internals and restart reasons are not public, so conclude “a patch caused it” only once the above are eliminated, and escalate with the bucket + version + multi-query evidence rather than blaming the patch from timing alone.

Source

• references/redshift-sql-query-performance.md(opens in a new tab)
• Raw file(opens in a new tab)
• aws/agent-toolkit-for-aws(opens in a new tab)