Reducing query run time

Query run time depends on the complexity of the query, the number of operators in the plan, data volumes, and projection design. I/O or CPU bottlenecks can cause queries to run slower than expected. You can often remedy high CPU usage with better projection design. High I/O can often be traced to contention caused by joins and sorts that spill to disk. However, no single solution addresses all queries that incur high CPU or I/O usage. You must analyze and tune each queryindividually.

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 system tables QUERY_CONSUMPTION or EXECUTION_ENGINE_PROFILES.

Examining the query plan can reveal one or more of the following:

• Suboptimal projection sort order

• Predicate evaluation on an unsorted or unencoded column

• Use of GROUPBY HASH instead of GROUPBY PIPE

Profiling

Vertica provides profiling mechanisms that help you evaluate database performance at different levels. For example, you can collect profiling data for a single statement, a single session, or for all sessions on all nodes. For details, see Profiling database performance.