Reviewing query alerts - Amazon Redshift

Reviewing query alerts

To use the STL_ALERT_EVENT_LOG system table to identify and correct potential performance issues with your query, follow these steps:

  1. Run the following to determine your query ID:

    select query, elapsed, substring
from svl_qlog
order by query
desc limit 5;

    Examine the truncated query text in the substring field to determine which query value to select. If you have run the query more than once, use the query value from the row with the lower elapsed value. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure that your query is included.

  2. Select rows from STL_ALERT_EVENT_LOG for your query:

    Select * from stl_alert_event_log where query = MyQueryID;
    A sample query result from STL_ALERT_EVENT_LOG.

  3. Evaluate the results for your query. Use the following table to locate potential solutions for any issues that you have identified.

    Note

    Not all queries have rows in STL_ALERT_EVENT_LOG, only those with identified issues.

    Issue Event value Solution value Recommended solution
    Statistics for the tables in the query are missing or out of date. Missing query planner statistics Run the ANALYZE command See Table statistics missing or out of date.
    There is a nested loop join (the least optimal join) in the query plan. Nested Loop Join in the query plan Review the join predicates to avoid Cartesian products See Nested loop.
    The scan skipped a relatively large number of rows that are marked as deleted but not vacuumed, or rows that have been inserted but not committed. Scanned a large number of deleted rows Run the VACUUM command to reclaim deleted space See Ghost rows or uncommitted rows.
    More than 1,000,000 rows were redistributed for a hash join or aggregation. Distributed a large number of rows across the network:RowCount rows were distributed in order to process the aggregation Review the choice of distribution key to collocate the join or aggregation See Suboptimal data distribution.
    More than 1,000,000 rows were broadcast for a hash join. Broadcasted a large number of rows across the network Review the choice of distribution key to collocate the join and consider using distributed tables See Suboptimal data distribution.
    A DS_DIST_ALL_INNER redistribution style was indicated in the query plan, which forces serial execution because the entire inner table was redistributed to a single node. DS_DIST_ALL_INNER for Hash Join in the query plan Review the choice of distribution strategy to distribute the inner, rather than outer, table See Suboptimal data distribution.