Projection Types
A Vertica table typically has multiple projections, each defined to contain different content. Content for the projections of a given table can differ in scope and how it is organized. These differences can generally be divided into the following projection types:
Superprojections
For each table in the database, Vertica requires at least one superprojection that contains all columns in the table. In the absence of query-specific projection, Vertica uses the table's superprojection, which can support any query and DML operation.
Under certain conditions, Vertica automatically creates a table's superprojection immediately on table creation. Vertica also creates a superprojection when you first load data into that table, if none already exists. CREATE PROJECTION can create a superprojection if it specifies to include all table columns. A table can have multiple superprojections.
While superprojections can support all queries on a table, they do not facilitate optimal execution of specific queries.
Query-Specific Projections
A query-specific projection is a projection that contains only the subset of table columns to process a given query. Query-specific projections significantly improve the performance of those queries for which they are optimized.
Aggregate Projections
Queries that include expressions or aggregate functions such as SUM and COUNT can perform more efficiently when they use projections that already contain the aggregated data. This is especially true for queries on large quantities of data.
Vertica provides three types of projections for storing data that is returned from aggregate functions or expressions:
- Projection that contains expressions: Projection with columns whose values are calculated from anchor table columns.
- Live aggregate projection: Projection that contains columns with values that are aggregated from columns in its anchor table. You can also define live aggregate projections that include user-defined transform functions.
- Top-K projection: Type of live aggregate projection that returns the top k rows from a partition of selected rows. Create a Top-K projection that satisfies the criteria for a Top-K query.
For more information, see Pre-Aggregating Data in Projections.