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:

Examining the query plan often reveals one or more of the following:

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.