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
- Query
- This column is the ID number of the query in your STL tables.
- 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/c-reviewing-query-alerts.html
The above query was made available by Amazon Redshift’s support documentation and was sourced from that site.