
The query_events table captures optimization issues and suggests fixes. Let’s take a look at my demo cluster, checking event description and suggested action grouped and sorted by event count:
select event_description, suggested_action, count(*) from query_events where suggested_action is not null and suggested_action <> 'Informational; No user action is necessary' group by 1,2 order by 3 desc;
event_description suggested_action count
The optimizer encountered a predicate on a column for which it does not have a histogram analyze_statistics('public.dump1090new.generated'); 2762
WOS Full; spilling to a new ROS container Consider DIRECT load, more aggressive Moveout, or a larger WOS 2096
The optimizer ran a query using auto-projections Consider creating projections or run database designer on table public.dump1090new 1950
These suggestions can make a big difference in performance, so I’ll be adding a DIRECT hint to my streaming load, and running DataBase Designer as suggested. Helpful Links:
QUERY_EVENTS table: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_EVENTS.htm Guidelines for improving query performance: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/Optimizations/InitialProcessForImprovingQueryPerformance.htm
Have fun!