Records an alert when the query optimizer identifies conditions that might indicate performance issues. Use the STL_ALERT_EVENT_LOG table to identify opportunities to improve query performance.
A query consists of multiple segments, and each segment consists of one or more steps. For more information, see Query Processing.
STL_ALERT_EVENT_LOG is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.
|Column Name||Data Type||Description|
|userid||integer||ID of the user who generated the entry.|
|query||integer||Query ID. The query column can be used to join other system tables and views.|
|slice||integer||Number that identifies the slice where the query was running.|
|segment||integer||Number that identifies the query segment.|
|step||integer||Query step that executed.|
|pid||integer||Process ID associated with the statement and slice. The same query might have multiple PIDs if it executes on multiple slices.|
|xid||bigint||Transaction ID associated with the statement.|
|event||character(1024)||Description of the alert event.|
|event_time||timestamp||Time in UTC that the query started executing, with 6 digits of precision for fractional seconds. For example:
You can use the STL_ALERT_EVENT_LOG to identify potential issues in your queries, then follow the practices in Tuning Query Performance to optimize your database design and rewrite your queries. STL_ALERT_EVENT_LOG records the following alerts:
Statistics are missing. Run ANALYZE following data loads or significant updates and use STATUPDATE with COPY operations. For more information, see Best Practices for Designing Queries.
A nested loop is usually a Cartesian product. Evaluate your query to ensure that all participating tables are joined efficiently.
Very Selective Filter
The ratio of rows returned to rows scanned is less than 0.05. Rows scanned is the value of
rows_pre_user_filterand rows returned is the value of rows in the STL_SCAN system table. Indicates that the query is scanning an unusually large number of rows to determine the result set. This can be caused by missing or incorrect sort keys. For more information, see Choosing Sort Keys.
Excessive Ghost Rows
A 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. For more information, see Vacuuming Tables.
More than 1,000,000 rows were redistributed for hash join or aggregation. For more information, see Choosing a Data Distribution Style.
More than 1,000,000 rows were broadcast for hash join. For more information, see Choosing a Data Distribution Style.
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. For more information, see Choosing a Data Distribution Style.
The following query shows alert events for four queries.
SELECT query, substring(event,0,25) as event, substring(solution,0,25) as solution, trim(event_time) as event_time from stl_alert_event_log order by query; query | event | solution | event_time -------+-------------------------------+------------------------------+--------------------- 6567 | Missing query planner statist | Run the ANALYZE command | 2014-01-03 18:20:58 7450 | Scanned a large number of del | Run the VACUUM command to rec| 2014-01-03 21:19:31 8406 | Nested Loop Join in the query | Review the join predicates to| 2014-01-04 00:34:22 29512 | Very selective query filter:r | Review the choice of sort key| 2014-01-06 22:00:00 (4 rows)