Initial Process for Improving Query Performance
To optimize query performance, begin by performing the following tasks:
Database Designer creates a physical schema for your database that provides optimal query performance. The first time you run Database Designer, you should create a comprehensive design that includes relevant sample queries and data on which to base the design. If you develop performance issues later, consider loading additional queries that you run frequently and then rerunning Database Designer to create an incremental design.
When you run Database Designer, choose the option, Update Statistics. The Vertica query optimizer uses statistics about the data to create a query plan. Statistics help the optimizer determine:
- Multiple eligible projections to answer the query
- The best order in which to perform joins
- Data distribution algorithms, such as broadcast and resegmentation
If your statistics become out of date, run the Vertica function
ANALYZE_STATISTICS function to update statistics for a schema, table, or columns. For more information, see Collecting Database Statistics.
The QUERY_EVENTS system table returns information on query planning, optimization, and execution events.
EVENT_TYPE column provides various event types:
- Some event types are informational.
- Others you should review for possible corrective action.
- Several are most important to address.
A query plan is a sequence of step-like paths that the Verticaquery optimizer selects to access or alter information in your Verticadatabase. There are two ways to get information about the query plan:
- Run the EXPLAIN command. Each step (path) represents a single operation that the optimizer uses for its execution strategy.
- Query the QUERY_PLAN_PROFILES system table. This table provides detailed execution status for currently running queries. Output from the QUERY_PLAN_PROFILES table shows the real-time flow of data and the time and resources consumed for each path in each query plan.