Projections and aggregates

MattK

In a current Postgres project, I have summary tables that are populated with a key of fewer columns than the source table, and all of the fact columns SUMmed. In this case, the summary tables are grouped and clustered in a different order from the source. For example, if the source has a key of (col1, col2, col3), the summary table is grouped by col1, col3.

Since aggregates can't be part of a projection, would the performant approach in Vertica 7 be to create a projection with the summary key order, then create a view on top of that with the column SUMs? Or is the projection unnecessary?

Example:

CREATE TABLE source (key1, key2, key3, fact1);
CREATE PROJECTION summary_projection AS SELECT key1, key3, fact1 FROM source ORDER BY key1, key3;
CREATE VIEW summary_view AS SELECT key1, key3, SUM(fact1)  GROUP BY key1, key3;

[Deleted User]

If you create a projection with the summary key order your query may perform better, however it is not necessary to do it. 

Try the query without the query specific projection and see if the performance is good enough, if not they the new query specific projection as you mention. 

You also can run DBD as query specific and see what is the projection design that DBD recommend you. 

Hope this helps, 

Eugenia

MattK

It looks like the projection does make a significant difference, as the query plan for a view selecting from the base table immediately changes to use the projection as soon as it is refreshed.

I will see what the DBD suggests next.