Query Optimization

This section contains information on query execution improvements for Vertica Analytic Database 8.1.x.

Flattened Tables

Before release 8.1., Vertica users could denormalize their data by combining all fact and dimension table columns in a single 'fat' table. However, this approach required them to maintain redundant sets of normalized and denormalized data.

Release 8.1. addresses this issue with flattened tables, which include columns that get their values by querying other tables. Operations on the source tables and flattened table are decoupled: changes in one are not automatically propagated to the other. This minimizes the overhead that is otherwise typical of conventional denormalized tables.

For details, see Flattened Tables in Analyzing Data.

Wide Column Data Queried for Insertion into Another Table

Query execution is significantly improved in two cases where a table is populated with variable length columns (VARCHAR/LONG VARCHAR and VARBINARY/LONG VARBINARY) from another table:

If the source and target tables share the same sort order but are segmented differently, Vertica buffers the query results. With release 8.1.x, Vertica optimizes buffering for wide columns, so overall query execution now incurs much less overhead than before.

Note: Performance improvements can vary, depending on the width of the source column data.

Queries with Multiple Distinct Aggregates

Vertica has improved the performance of queries that include multiple distinct aggregates. While all queries that include multiple distinct aggregates experience this improvement, the scale of the performance improvement depends upon your data and how your queries are structured. Queries where all the distinct aggregate columns have a similar number of distinct values experience the greatest performance improvement. Queries where the distinct aggregate columns have a vastly different number of distinct values experience a performance improvement to a lesser extent.