Pre-join Projections

nkarkare

I know the basic use of projections after going through the documentation. I am trying to create this projection but it says, "Only ONE table or projection is allowed in the FROM clause of aggregate projection". Here's what I am trying to do:

CREATE PROJECTION public.fct_asx_star_coverages_test_count_per_source
(
dim_star_coverage_files_source ENCODING RLE,
dim_module_suite_fk ENCODING RLE
)
AS
select dim.dim_star_coverage_files_source, count(fct.dim_module_suite_fk) from fct_asx_star_coverages fct, dim_star_coverage_files dim
WHERE fct.dim_star_coverage_files_fk = dim.dim_star_coverage_files_sk
GROUP BY dim.dim_star_coverage_files_source;

Thoughts?

[Deleted User]

Prejoin projections are deprecated in Vertica 8.0 and higher and I would not encorage you to use them .
If you really care, prejoin projections can't have aggregrations but simple join between two tables haveing PK-FK relationship .
You are trying to create is a live aggregate projections in Vertica but that does not support join between tables and that is the reason you are getting above error .

nkarkare

Thank you @skamat
I recently started testing Vertica. I have a fact table with 8 mil rows. It is just joined with two dimensions. forming a star schema. The above SELECT statement takes approximately 38 seconds to show process the query. What can I do to optimize this query performance? Any tips?

abtsantosbenten

there's nothing you can do to optimize this. vertica does not scale well with joins 1M up in my experience. there's just no way out of it.