Prejoin projection is deprecated > Flattened Tables

Bellow is the post I made on other discussion, with my problem with more details:

Hello, Ben_Vandiver! I'm having a similar problem here. I have a fact table with millions of registers with some dimensions. And I need to improve a performance of a query, initially I was trying to create a projection and end up with the error "Prejoin projection is deprecated", then I looked up more information about flattened tables that you suggested as a solution.
However, I have some doubts about this:

1- I was planning on creating a flattened table that used the fact table and the dimensions, but do not alter them, and them be able to create other flattened tables to aggregate data from fact and dimension tables.

2- Is there a way to create a flattened table totally based on the query I was using to create a projection? I was expecting something like create table fact_wide set using [query], is there a way to do it?

3- I only found 2 suggestions of use of flattened tables, creating a table based on other (like: CREATE TABLE fact_wide as select a, b,c from fact; and later add dimension columns one by one with a query related), or altering the fact table adding the dimension columns. Then I come to questions, if I create a flattened table like fact_wide, how the fact data can be refreshed on fact_wide?

4- Also if I alter the fact table (which I'm not sure is a good practice), do I really have to add a query column by column even if its the same dimension table (in my case there are a lot of columns in each dimension)

Hi,

You do need to add a "set using" or "default" query for each dimension table you want in the flattened table. God news, you should be able to generate some ALTER TABLE commands to add the columns to the fact table for you:

Simple Example:

dbadmin=> \d dim1;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | dim1  | c1     | int          |    8 |         | t        | t           |
 public | dim1  | c2     | varchar(100) |  100 |         | f        | f           |
 public | dim1  | c3     | varchar(100) |  100 |         | f        | f           |
 public | dim1  | c4     | varchar(100) |  100 |         | f        | f           |
(4 rows)

dbadmin=> \d fact;
                                  List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key |   Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-----------------
 public | fact  | pk     | int  |    8 |         | t        | f           |
 public | fact  | c1     | int  |    8 |         | t        | f           | public.dim1(c1)
(2 rows)

dbadmin=> SELECT * FROM dim1;
 c1 |  c2   |  c3   |  c4
----+-------+-------+-------
  1 | TEST1 | TEST2 | TEST3
(1 row)

dbadmin=> SELECT * FROM fact;
 pk | c1
----+----
  1 |  1
(1 row)

dbadmin=> SELECT pk, c2, c3, c4 FROM fact JOIN dim1 USING (c1);
 pk |  c2   |  c3   |  c4
----+-------+-------+-------
  1 | TEST1 | TEST2 | TEST3
(1 row)

dbadmin=> CREATE PROJECTION fact_dim1_pr AS SELECT pk, c2, c3, c4 FROM fact JOIN dim1 USING (c1);
ROLLBACK 8678:  Prejoin projection is deprecated

dbadmin=> SELECT 'ALTER TABLE ' || cc.table_schema || '.' || cc.table_name || ' ADD COLUMN ' || c.column_name || ' ' || data_type ||
dbadmin->        ' SET USING (SELECT ' || c.table_schema || '.' || c.table_name || '.' || c.column_name || ' FROM ' || c.table_schema || '.' || c.table_name ||
dbadmin->        ' WHERE ' || cc.table_schema || '.' || cc.table_name || '.' || cc.column_name || ' = ' || c.table_schema || '.' || c.table_name || '.' || cc.column_name || ');'
dbadmin->   FROM constraint_columns cc
dbadmin->   JOIN columns c
dbadmin->     ON c.table_id = cc.reference_table_id
dbadmin->    AND c.column_name <> cc.column_name
dbadmin->  WHERE cc.table_schema = 'public' AND cc.table_name = 'fact'
dbadmin->    AND cc.constraint_type = 'f';
                                                                   ?column?
----------------------------------------------------------------------------------------------------------------------------------------------
 ALTER TABLE public.fact ADD COLUMN c2 varchar(100) SET USING (SELECT public.dim1.c2 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
 ALTER TABLE public.fact ADD COLUMN c3 varchar(100) SET USING (SELECT public.dim1.c3 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
 ALTER TABLE public.fact ADD COLUMN c4 varchar(100) SET USING (SELECT public.dim1.c4 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
(3 rows)

dbadmin=> ALTER TABLE public.fact ADD COLUMN c2 varchar(100) SET USING (SELECT public.dim1.c2 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
ALTER TABLE

dbadmin=> ALTER TABLE public.fact ADD COLUMN c3 varchar(100) SET USING (SELECT public.dim1.c3 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
ALTER TABLE

dbadmin=> ALTER TABLE public.fact ADD COLUMN c4 varchar(100) SET USING (SELECT public.dim1.c4 FROM public.dim1 WHERE public.fact.c1 = public.dim1.c1);
ALTER TABLE

dbadmin=> SELECT refresh_columns('public.fact', '', 'REBUILD');
      refresh_columns
---------------------------
 refresh_columns completed
(1 row)

dbadmin=> SELECT * FROM fact;
 pk | c1 |  c2   |  c3   |  c4
----+----+-------+-------+-------
  1 |  1 | TEST1 | TEST2 | TEST3
(1 row)