Amazon Redshift Nested Loop Alerts

Data Tutorial Amazon Redshift

Amazon Redshift Nested Loop Alerts

Some of your Amazon Redshift source’s tables might contain nested loops which will negatively impact your cluster’s performance by overloading the queue with queries that are taking long amounts of time to execute. Querying your STL Table’s alert logs will reveal nested loop alerts for you.

In this tutorial we will show you a fairly simple query that can be run against your cluster’s STL table to reveal the nested loop alerts, their SQL, and the time at which they were run.

  • Query
  • Query Text
  • Run time

The Breakdown

  1. Query
    • This column is the ID number of the query in your STL tables.
  2. Query Text
    • This column is the SQL Syntax of the query and to ensure the query doesn’t take too long to run and returns enough information to identify the queries source or other identifying information you will want to use the “trim” function to shorten the returned text.

The Query

SELECT 
	query, 
	trim(querytxt) as SQL, 
	starttime 
FROM 
	stl_query 
WHERE 
	query in (
		SELECT
			distinct query 
		FROM 
			stl_alert_event_log 
		WHERE 
			event like 'Nested Loop Join in the query plan%'
		) 
	AND starttime >=  dateadd(day, -{DAYS}, current_date)
ORDER BY starttime DESC;

One takeaway from this query is that you will be able to tell if there are cross joins that are resulting in the cartesian product of the tables being joined. These types of cartesian joins can negatively impact your source’s performance, so understanding where they are and when they are running can be useful information.

REFERENCES:

https://docs.aws.amazon.com/redshift/latest/dg/diagnostic-queries-for-query-tuning.html#identify-queries-that-are-top-candidates-for-tuning

https://docs.aws.amazon.com/redshift/latest/dg/query-performance-improvement-opportunities.html#nested-loop

https://docs.aws.amazon.com/redshift/latest/dg/c-reviewing-query-alerts.html

The above query was made available by Amazon Redshift’s support documentation and was sourced from that site.