Reducing Query Run Time
The run time of queries depends on the complexity of the query, the number of operators in the plan, data volumes, and projection design. If the system is bottlenecked on either I/O or CPU, queries could run more slowly than expected. In most cases, high CPU usage can be alleviated by better projection design, and high I/O is usually due to contention because of operations like joins and sorts that spill to disk. However, there is no single solution to fix high CPU or high I/O usage, so queries must be examined and tuned individually.
You can evaluate a slow-running query in two ways:
- Prefix the query with
EXPLAIN
to view the optimizer's query plan. - Examine the execution profile by querying the system table
EXECUTION_ENGINE_PROFILES
Examining the query plan often reveals one or more of the following:
- Suboptimal projection sort order
- Predicate evaluation on an unsorted or unencoded column
- Use of
GROUPBY HASH
instead ofGROUPBY PIPE
See Creating Custom Designs to understand projection design techniques. The Database Designer automatically applies these techniques to suggest optimal designs for queries.
Profiling
Vertica provides profiling mechanisms that let you determine how well the database is performing. For example, Vertica can collect profiling data for a single statement, a single session, or for all sessions on all nodes. For details, see Profiling Database Performance.