Amazon Redshift will no longer support the use of Python UDFs after June 30, 2026.
We will start enforcing it in phases. For more information on the details of Python end of life
and migration options, see the
blog post
Query performance improvement
Following are some common issues that affect Amazon Redshift query performance, with instructions on ways to diagnose and resolve them.
Topics
Table statistics missing or out of date
If table statistics are missing or out of date, you might see the following:
-
A warning message in EXPLAIN command results.
-
A missing statistics alert event in STL_ALERT_EVENT_LOG. For more information, see Reviewing query alerts.
To fix this issue, run ANALYZE.
Nested loop
If a nested loop is present, you might see a nested loop alert event in STL_ALERT_EVENT_LOG. You can also identify this type of event by running the query at Identifying queries with nested loops. For more information, see Reviewing query alerts.
To fix this, review your query for cross-joins and remove them if possible. Cross-joins are joins without a join condition that result in the Cartesian product of two tables. They are typically run as nested loop joins, which are the slowest of the possible join types.
Hash join
If a hash join is present, you might see the following:
-
Hash and hash join operations in the query plan. For more information, see Analyzing the query plan.
-
An HJOIN step in the segment with the highest maxtime value in SVL_QUERY_SUMMARY. For more information, see Using the SVL_QUERY_SUMMARY view.
To fix this issue, you can take a couple of approaches:
-
Rewrite the query to use a merge join if possible. You can do this by specifying join columns that are both distribution keys and sort keys.
-
If the HJOIN step in SVL_QUERY_SUMMARY has a very high value in the rows field compared to the rows value in the final RETURN step in the query, check whether you can rewrite the query to join on a unique column. When a query does not join on a unique column, such as a primary key, that increases the number of rows involved in the join.
Ghost rows or uncommitted rows
If ghost rows or uncommitted rows are present, you might see an alert event in STL_ALERT_EVENT_LOG that indicates excessive ghost rows. For more information, see Reviewing query alerts.
To fix this issue, you can take a couple of approaches:
-
Check the Loads tab of your Amazon Redshift console for active load operations on any of the query tables. If you see active load operations, wait for those to complete before taking action.
-
If there are no active load operations, run VACUUM on the query tables to remove deleted rows.
Unsorted or missorted rows
If unsorted or missorted rows are present, you might see a very selective filter alert event in STL_ALERT_EVENT_LOG. For more information, see Reviewing query alerts.
You can also check to see if any of the tables in your query have large unsorted areas by running the query in Identifying tables with data skew or unsorted rows.
To fix this issue, you can take a couple of approaches:
-
Run VACUUM on the query tables to re-sort the rows.
-
Review the sort keys on the query tables to see if any improvements can be made. Remember to weigh the performance of this query against the performance of other important queries and the system overall before making any changes. For more information, see Sort keys.
Suboptimal data distribution
If data distribution is suboptimal, you might see the following:
-
A serial execution, large broadcast, or large distribution alert event appears in STL_ALERT_EVENT_LOG. For more information, see Reviewing query alerts.
-
Slices are not processing approximately the same number of rows for a given step. For more information, see Using the SVL_QUERY_REPORT view.
-
Slices are not taking approximately the same amount of time for a given step. For more information, see Using the SVL_QUERY_REPORT view.
If none of the preceding is true, you can also see if any of the tables in your query have data skew by running the query in Identifying tables with data skew or unsorted rows.
To fix this issue, review the distribution styles for the tables in the query and see if any improvements can be made. Remember to weigh the performance of this query against the performance of other important queries and the system overall before making any changes. For more information, see Data distribution for query optimization.
Insufficient memory allocated to the query
When a query needs more memory than is allocated to it, Amazon Redshift writes the excess intermediate results to disk. This is called spilling to disk. Amazon Redshift writes spilled data to local disk first, and if it exceeds local disk capacity, the data overflows to Amazon S3. Query steps most likely to spill are hash joins, aggregations (such as GROUP BY and DISTINCT), window functions, and sorts (ORDER BY) over large datasets.
Spilling to disk slows the query that spills, because disk access is much slower than memory. Because local disk and I/O bandwidth are shared across the cluster, heavy spill can also slow down other queries running at the same time. Reducing spill improves both individual query latency and overall cluster throughput.
Identify queries that spill. Query SYS_QUERY_DETAIL and review the
spilled_block_local_disk and spilled_block_remote_disk
columns. Queries with a high spilled_block_remote_disk value are
spilling beyond local disk and benefit most from tuning. On a provisioned cluster,
you can also review the query_temp_blocks_to_disk metric in SVL_QUERY_METRICS_SUMMARY.
How to reduce disk spill
-
Reduce the volume of intermediate data. Filter earlier, select only the columns you need, and pre-aggregate where possible so fewer rows flow into joins, aggregations, and sorts.
-
Break large queries into steps. Materialize intermediate results into temporary tables (CREATE TEMP TABLE AS) so each step runs with a smaller in-memory footprint and is less likely to spill. Give the temp table an appropriate distribution and sort key and run ANALYZE so downstream steps run efficiently.
-
Give the query more memory by temporarily increasing its slot count if you use manual WLM (see wlm_query_slot_count).
-
Add nodes or move to a larger node type when spill is unavoidable at your data volume.
Monitor spill proactively. If you use manual WLM
on a provisioned cluster, create a query monitoring rule (QMR) on the
query_temp_blocks_to_disk metric. This metric measures the temporary
disk space used for intermediate results, in 1 MB blocks. Then choose an
action:
- log
-
Records the query for review.
- abort
-
Stops the query before its spill degrades other workloads.
For example, query_temp_blocks_to_disk > 100000 (about 100 GB) logs
or aborts queries that spill unusually large amounts. Tune the threshold to your
cluster or workgroup. On a provisioned cluster, configure QMR through your WLM queues.
On Redshift Serverless, configure it through workgroup query limits. For more information, see WLM query monitoring rules.
Suboptimal WHERE clause
If your WHERE clause causes excessive table scans, you might see a SCAN step in
the segment with the highest maxtime value in SVL_QUERY_SUMMARY. For
more information, see Using the SVL_QUERY_SUMMARY view.
To fix this issue, add a WHERE clause to the query based on the primary sort column of the largest table. This approach helps minimize scanning time. For more information, see Amazon Redshift best practices for designing tables.
Insufficiently restrictive predicate
If your query has an insufficiently restrictive predicate, you might see a SCAN
step in the segment with the highest maxtime value in SVL_QUERY_SUMMARY
that has a very high rows value compared to the rows value
in the final RETURN step in the query. For more information, see Using the SVL_QUERY_SUMMARY view.
To fix this issue, try adding a predicate to the query or making the existing predicate more restrictive to narrow the output.
Very large result set
If your query returns a very large result set, consider rewriting the query to use UNLOAD to write the results to Amazon S3. This approach improves the performance of the RETURN step by taking advantage of parallel processing. For more information on checking for a very large result set, see Using the SVL_QUERY_SUMMARY view.
Large SELECT list
If your query has an unusually large SELECT list, you might see a
bytes value that is high relative to the rows value for
any step (in comparison to other steps) in SVL_QUERY_SUMMARY. This high
bytes value can be an indicator that you are selecting a lot of
columns. For more information, see Using the SVL_QUERY_SUMMARY view.
To fix this issue, review the columns you are selecting and see if any can be removed.