Find and fix issues from Vertica query events

Bryan_H · August 2019

Vertica offers tools like the Workload Analyzer in Management Console to tune up a Vertica Cluster, but there's a simple way to find and fix issues that Vertica observes and records if you aren't using MC.

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. Have fun!

References:
QUERY_EVENTS table: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_EVENTS.htm
Guidelines for improving query performance: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/InitialProcessForImprovingQueryPerformance.htm